Prior to Hibernate 6, I was using JPA Query had no issues executing the below query
@Query("select ue.* FROM UserEntity ue where ue.id = :ueId AND ue.createdUtc - 1 >= :date")
List<UserEntity> findUser(@Param("id") int id, @Param("date") Calendar date);
However, after upgrading to hibernate 6 and spring-data-jpa 3.x, I am getting the following exception.
Operand of - is of type 'java.util.Calendar' which is not a numeric type (it is not an instance of 'java.lang.Number', 'java.time.Temporal', or 'java.time.TemporalAmount')
This is because "ue.createdUtc" is a calendar but when trying to apply the date subtract operation (ue.createdUtc - 1) this issue happens
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Operand of - is of type 'java.util.Calendar' which is not a numeric type (it is not an instance of 'java.lang.Number', 'java.time.Temporal', or 'java.time.TemporalAmount')
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:143)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:167)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:173)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:825)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:730)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:132)
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.proxy2/jdk.proxy2.$Proxy97.createQuery(Unknown Source)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94)
... 118 more
Caused by: org.hibernate.query.SemanticException: Operand of - is of type 'java.util.Calendar' which is not a numeric type (it is not an instance of 'java.lang.Number', 'java.time.Temporal', or 'java.time.TemporalAmount')
at org.hibernate.query.sqm.internal.TypecheckUtil.assertOperable(TypecheckUtil.java:453)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAdditionExpression(SemanticQueryBuilder.java:2918)
at org.hibernate.grammars.hql.HqlParser$AdditionExpressionContext.accept(HqlParser.java:7113)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedExpression(SemanticQueryBuilder.java:2998)
at org.hibernate.grammars.hql.HqlParser$GroupedExpressionContext.accept(HqlParser.java:7231)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.createComparisonPredicate(SemanticQueryBuilder.java:2428)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2391)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6164)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2270)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$OrPredicateContext.accept(HqlParser.java:6255)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:2251)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$GroupedPredicateContext.accept(HqlParser.java:6084)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:2261)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$AndPredicateContext.accept(HqlParser.java:6039)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:2251)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$GroupedPredicateContext.accept(HqlParser.java:6084)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:2261)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$AndPredicateContext.accept(HqlParser.java:6039)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:2251)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$GroupedPredicateContext.accept(HqlParser.java:6084)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2270)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$OrPredicateContext.accept(HqlParser.java:6255)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:2251)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$GroupedPredicateContext.accept(HqlParser.java:6084)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:2261)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$AndPredicateContext.accept(HqlParser.java:6039)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2243)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5905)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1158)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:940)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1869)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:925)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:268)
at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1740)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:442)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:401)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:310)
at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:71)
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165)
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147)
at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:767)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:817)
... 127 more
I am aware that changing this to native query is one possible solution. But there are many other complex queries which probably are time consuming to convert. Please suggest some suggestions.
Note: I am using spring-boot 3.1.5, spring-boot-starter-data-jpa 3.1.5 and Hibernate-core 6.3.1.Final.
I upgraded from spring-boot 2.x to spring-boot 3.x which eventually upgrades hibernate 5.x to hibernate 6.x. I expected the JPA query with date manipulation still works.
Instead of subtracting
1
in the query, subtract1 DAY
i.e.