spring org.hibernate.QueryException:函数trim()的参数3的类型为STRING，但实参的类型为java.lang.Integer

rkttyhzu  于 2023-09-29  发布在  Spring
关注(0)|答案(2)|浏览(127)

我正在将Springboot应用程序从2.7版本升级到3.1版本。以下查询、pom声明和app.properties在2.7版本中未出现错误。

查询：

package com.dba.tableQuery;

import jakarta.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;

@Entity
@Table(name="TestTable")
@NamedQuery(name = "findTestTable", query = "SELECT e FROM TestTable e " +
        "WHERE trim(e.Column2) = trim(:id) ")
public class TableQuery implements Serializable {

    @EmbeddedId
    private TestTablePK id;

    @Column(name="Column2",  insertable=false, updatable=false)
    private int column2;

    @Column(name="Column3",  insertable=false, updatable=false)
    private String column3;

    @Column(name="Column4",  insertable=false, updatable=false)
    private String column4;

    //getter and setter are declared
}

POM：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>11.2.3.jre17</version>
</dependency>

Application.properties文件：

#Datasource configuration
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.properties.hibernate.proc.param_null_passing=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=false

#SQL logging configuration
spring.datasource.url=jdbc:sqlserver://test;databaseName=testTable;encrypt=true;trustServerCertificate=true
spring.datasource.username=testUser
spring.datasource.password=testPassword

错误：

但是在Springboot 3.x中，我在运行应用程序时遇到了异常。

Error in named query: findTestTable
org.hibernate.QueryException: Parameter 3 of function trim() has type STRING, but argument is of type java.lang.Integer
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.throwError(ArgumentTypesValidator.java:253)
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.checkType(ArgumentTypesValidator.java:204)
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.validate(ArgumentTypesValidator.java:98)
    at org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor.generateSqmExpression(AbstractSqmFunctionDescriptor.java:104)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitTrimFunction(SemanticQueryBuilder.java:4915)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitTrimFunction(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$TrimFunctionContext.accept(HqlParser.java:14335)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitStandardFunction(HqlParserBaseVisitor.java:1392)
    at org.hibernate.grammars.hql.HqlParser$StandardFunctionContext.accept(HqlParser.java:13723)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitFunction(HqlParserBaseVisitor.java:1217)
    at org.hibernate.grammars.hql.HqlParser$FunctionContext.accept(HqlParser.java:11171)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:1758)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$FunctionExpressionContext.accept(HqlParser.java:7476)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitBarePrimaryExpression(HqlParserBaseVisitor.java:720)
    at org.hibernate.grammars.hql.HqlParser$BarePrimaryExpressionContext.accept(HqlParser.java:7064)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2453)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6111)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2297)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5910)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1161)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:937)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1818)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:931)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253)
    at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:418)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:377)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:295)
    at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:81)
    at org.hibernate.query.internal.NamedObjectRepositoryImpl.lambda$checkNamedQueries$5(NamedObjectRepositoryImpl.java:242)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128)
    at org.hibernate.query.internal.NamedObjectRepositoryImpl.checkNamedQueries(NamedObjectRepositoryImpl.java:239)
    at org.hibernate.query.internal.NamedObjectRepositoryImpl.validateNamedQueries(NamedObjectRepositoryImpl.java:212)
    at org.hibernate.query.spi.QueryEngine.validateNamedQueries(QueryEngine.java:220)
    at org.hibernate.boot.internal.SessionFactoryObserverForNamedQueryValidation.sessionFactoryCreated(SessionFactoryObserverForNamedQueryValidation.java:39)
    at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:292)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:431)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1455)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:75)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:352)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:598)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:520)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1155)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:932)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:608)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:436)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:312)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295)
    
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: 
[PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.HibernateException: Errors in named queries: findTestTable

尝试了这里提供的解决方案，但没有成功：Migration to SpringBoot 3: trunc a date with oracle driver no longer works (hibernate)

spring

2条答案

按热度按时间
hjzp0vay

hjzp0vay1#

SQL TRIM（）函数作用于字符串，在示例中，您提供了NamedQuery应用e.Column2上的修剪，这是一个int。

@NamedQuery(name = "findTestTable", query = "SELECT e FROM TestTable e " +
        "WHERE trim(e.Column2) = trim(:id) ")

@Column(name="Column2",  insertable=false, updatable=false)
    private int column2;

所以我明白为什么Hibernate会抱怨。我不明白为什么它以前工作。
你希望findTestTable做什么？我认为你要么删除trim函数，要么将Column2转换为字符串。

赞(0）分享  回复(0）举报  2023-09-29
af7jpaap

af7jpaap2#

这似乎是Hibernate 6中的一个bug。根据以下文档，Trim应使用String：
https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html#query-language

赞(0）分享  回复(0）举报  2023-09-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com