Hibernate 5 to Hibernate 6 - JPA Query - Calendar subtraction doesn't work

Question

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.

Answer 1

Instead of subtracting 1 in the query, subtract 1 DAY

i.e.

@Query("select ue.* FROM UserEntity ue where ue.id = :ueId AND ue.createdUtc - 1 DAY >= :date")