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.
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.