Migration from Hibernate 5 to 6

Question

I am trying to migrate a spring boot 2.3 application running on java 8 to spring boot 3 java 17. The issue is in a repository.

It has a query method as follows:

@Query(value = "SELECT ca FROM CorporateActionsV2 ca WHERE ( ca.dividendAmount, TO_CHAR(TO_DATE(ca.effectiveDate) - 5,'IYYY'), TO_CHAR(TO_DATE(ca.effectiveDate) - 5,'IW'), ca.tickerSerial) IN (SELECT c.dividendAmount, TO_CHAR(TO_DATE(c.effectiveDate) - 5,'IYYY'), TO_CHAR(TO_DATE(c.effectiveDate) - 5,'IW'), c.tickerSerial FROM CorporateActionsV2 c WHERE c.dividendAmount IS NOT NULL AND c.eventLabel = 'Dividend' AND c.actionStatus != 'Deleted'" +
        " GROUP BY c.dividendAmount, TO_CHAR(TO_DATE(c.effectiveDate) - 5,'IYYY'), TO_CHAR(TO_DATE(c.effectiveDate) - 5,'IW'), c.tickerSerial HAVING COUNT(*) > 1 )")
List<CorporateActionsV2> getDuplicateActions();

The entity CorporateActionsV2 has following date structure:

@Temporal(TemporalType.DATE)
@Column(name = "EFFECTIVE_DATE")
@JsonProperty("EFFECTIVE_DATE")
private Date effectiveDate;

When spring boot starts I get the following exception:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'corporateActionV2Service' defined in file [D:\company\content_contribution_platform\application\target\classes\com\mubasher\ccp\services\CorporateActionV2Service.class]: Unsatisfied dependency expressed through constructor parameter 1: Error creating bean with name 'corporateActionV2Repository' defined in com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository defined in @EnableJpaRepositories declared on MSHistoryConfig: Could not create query for public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions(); Reason: Validation failed for query for method public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions()
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:793)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:242)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1344)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1188)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:561)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521)
    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.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:961)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:915)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:584)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:730)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:432)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1291)
    at com.mubasher.ccp.Application.main(Application.java:29)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'corporateActionV2Repository' defined in com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository defined in @EnableJpaRepositories declared on MSHistoryConfig: Could not create query for public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions(); Reason: Validation failed for query for method public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions()
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1751)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:599)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521)
    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.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:254)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1405)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1325)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:880)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:784)
    ... 24 common frames omitted
Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions(); Reason: Validation failed for query for method public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions()
    at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:101)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:115)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:99)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:88)
    at java.base/java.util.Optional.map(Optional.java:260)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:357)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:279)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:229)
    at org.springframework.data.util.Lazy.get(Lazy.java:113)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:285)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:132)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1797)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1747)
    ... 35 common frames omitted
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.mubasher.ccp.repositories.ms.history.CorporateActionV2Repository.getDuplicateActions()
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:100)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:70)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:55)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:170)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:95)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:111)
    ... 47 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Parameter 1 of function to_date() has type STRING, but argument is of type java.util.Date
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:141)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:175)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:182)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:761)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:663)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:127)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:360)
    at jdk.proxy3/jdk.proxy3.$Proxy139.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94)
    ... 53 common frames omitted
Caused by: org.hibernate.QueryException: Parameter 1 of function to_date() has type STRING, but argument is of type java.util.Date
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.throwError(ArgumentTypesValidator.java:214)
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.checkType(ArgumentTypesValidator.java:169)
    at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.validate(ArgumentTypesValidator.java:74)
    at org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor.generateSqmExpression(AbstractSqmFunctionDescriptor.java:104)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGenericFunction(SemanticQueryBuilder.java:3664)
    at org.hibernate.grammars.hql.HqlParser$GenericFunctionContext.accept(HqlParser.java:10209)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitFunction(HqlParserBaseVisitor.java:1112)
    at org.hibernate.grammars.hql.HqlParser$FunctionContext.accept(HqlParser.java:9977)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:1432)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$FunctionExpressionContext.accept(HqlParser.java:6849)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitBarePrimaryExpression(HqlParserBaseVisitor.java:671)
    at org.hibernate.grammars.hql.HqlParser$BarePrimaryExpressionContext.accept(HqlParser.java:6437)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAdditionExpression(SemanticQueryBuilder.java:2613)
    at org.hibernate.grammars.hql.HqlParser$AdditionExpressionContext.accept(HqlParser.java:6395)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitExpressionOrPredicate(HqlParserBaseVisitor.java:811)
    at org.hibernate.grammars.hql.HqlParser$ExpressionOrPredicateContext.accept(HqlParser.java:7186)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGenericFunctionArguments(SemanticQueryBuilder.java:3781)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGenericFunctionArguments(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$GenericFunctionArgumentsContext.accept(HqlParser.java:10559)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGenericFunction(SemanticQueryBuilder.java:3563)
    at org.hibernate.grammars.hql.HqlParser$GenericFunctionContext.accept(HqlParser.java:10209)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitFunction(HqlParserBaseVisitor.java:1112)
    at org.hibernate.grammars.hql.HqlParser$FunctionContext.accept(HqlParser.java:9977)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:1432)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitFunctionExpression(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$FunctionExpressionContext.accept(HqlParser.java:6849)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitBarePrimaryExpression(HqlParserBaseVisitor.java:671)
    at org.hibernate.grammars.hql.HqlParser$BarePrimaryExpressionContext.accept(HqlParser.java:6437)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
    at org.hibernate.grammars.hql.HqlParserBaseVisitor.visitExpressionOrPredicate(HqlParserBaseVisitor.java:811)
    at org.hibernate.grammars.hql.HqlParser$ExpressionOrPredicateContext.accept(HqlParser.java:7186)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitExpressions(SemanticQueryBuilder.java:2736)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitTupleExpression(SemanticQueryBuilder.java:2724)
    at org.hibernate.grammars.hql.HqlParser$TupleExpressionContext.accept(HqlParser.java:6465)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:2282)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$InPredicateContext.accept(HqlParser.java:5466)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:1949)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5290)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:857)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:629)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1218)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:623)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:243)
    at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1131)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:399)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:358)
    at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:285)
    at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:81)
    at org.hibernate.internal.AbstractSharedSessionContract.lambda$createQuery$2(AbstractSharedSessionContract.java:748)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:745)
    ... 62 common frames omitted

What am I missing from hibernate-5 to hibernate-6? The code was working fine in older version and from docs there is not much difference between hibernate 5 & 6.

Answer 1

Your error says: Parameter 1 of function to_date() has type STRING, but argument is of type java.util.Date.

In your query, is written: TO_CHAR(TO_DATE(ca.effectiveDate) - 5,'IYYY'). So based on this, I guess your ca.effectiveDate is already of type Date and it needs a string.

You should write something like this: TO_CHAR(ca.effectiveDate - 5,'IYYY')

Answer 2

As correctly answered by Octavia Adler, it makes no sense to pass a date to the to_date() function.

As of Hibernate 6, the argument types of HQL functions are now typechecked, and so Hibernate will pick up errors of this nature and report them with a sensible error message, in this case:

org.hibernate.QueryException: Parameter 1 of function to_date() has type STRING, but argument is of type java.util.Date

Perhaps you missed this message because Spring appears to be wrapping the helpful exception from Hibernate in not just one or two but four different useless exceptions that fail to preserve the error message of the root cause.

You should complain to the Spring developers about this, because this is just a terrible practice in terms of library design, IMO.

Answer 3

We had a similar error since upgrading to SpringBoot 3. However, it affects trunc and only occurs with oracle. It works wonderfully against an H2 database. Our error

Looks like a bug. The assumption by beikov (Hibernate team member):

It seems that the trunc function has multiple overloads but Hibernate 6 only supports the numeric variant right now

I have already created the following issue on Hibernate tracker.

Should be a similar problem here.