Post springboot upgrade to 3.2.4 the jpql query with expression date(current_date) - date(entity.modDate) is not working and is part both Select and where clause.
Also in where clause we are checking if the difference is IN(25,26,27,28,29,30)
1 Like
You will have to be more specific than that. Please post the entity model and the full query as well as the full stack trace that youâre seeing.
Hello, @Jazzzzyyy
After upgrading to Spring Boot 3.2.4, if youâre encountering issues with JPQL queries involving date calculations, itâs possible that there have been changes in how dates are handled. To resolve the issue with the expression date(current_date) - date(entity.modDate) not working, consider the following steps:
Ensure that the JPQL syntax for date operations is correct. JPQL may not support direct date subtraction. You might need to use a database-specific function or JPQLâs FUNCTION keyword to call a native SQL function for date subtraction.
If your database supports it, use the DATEDIFF function to calculate the difference between dates. For example:
@Query(âSELECT e FROM Entity e WHERE FUNCTION(âDATEDIFFâ, current_date, e.modDate) IN :daysâ)
List findEntitiesWithDateDifference(Set days);
Replace Entity with your actual entity class and days with a parameter containing the set of day differences youâre interested in.
Ensure that youâre using a compatible version of Spring Data JPA with Spring Boot 3.2.4. Incompatibilities might lead to unexpected behavior.
I hope my information is helpful to you.
Entity
Public Class User{
Id
GeneratedValue(strategy=Generation type.IDENTITY)
private Long id;
private String userId;
private String phoneno;
private String modDate;
private Boolean flag;
//Default constructor
}
UserRepo
public interface UserRepo extends Jpa repository<User, Long>{
Query(" Select New entity.User(u.userId, u.phoneno, (Date(current_date) - date(u.modDate))) from User where Date(current_date) - date(u.modDate) IN (25,26, 28, 30) and user.flag=false ")
List findLastModUserId();
//Some code logic
}
stack trace
Caused by: org.hibernate.query.SemanticException: Operand of is of type âjava.lang.Objectâ which is not a numeric type (it is no TypecheckUtil.assertOperable (TypecheckUtil.java:499)
at org.hibernate.query.sqm.internal. at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAdditionExpression (SemanticQueryBuilder.java:2958)
at org.hibernate.grammars.hql.HqlParser$AdditionExpressionContext.accept(HqlParser.java:711 r.java:7113)
org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedExpression (SemanticQueryBuilder. java:3038)
at grammars.hql.HqlParser$GroupedExpressionContext.accept(HqlParser.java:7231)
at org.hibernate.grammars. org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)
at visitExpressionor (Halparser sevisitor.java:856)
at at org.hibernate.grammars.hql.HqlParser$ExpressionOrPredicateContext.accept(HqlParser.java:7927) at org.antir.v4.runtime.tree.AbstractParseTreevisitor. visitChildren(AbstractParseTreevisitor.java:46)
at org.hibernate.grammars.hql.HqlParserBaseVisitor. visitInstantiationArgumentExpression(HlParserBaseVisitor.java:371) at org.hibernate.grammars.hql.HqlParser$InstantiationArgumentExpressionContext.accept(HqlParser.java:4012)
org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiationArgument(SemanticQueryBuilder.java:1426)
at at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiation (SemanticQueryBuilder.java:1398)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiation (SemanticQueryBuilder.java:269)
at .hibernate.grammars.hql.HqlParser$InstantiationContext.accept(HqlParser.java:3761)
.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectableNode(SemanticQueryBuilder.java:1355) at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelection(SemanticQueryBuilder.java:1309)
at
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectClause(SemanticQueryBuilder.java:1302)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1154)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression (SemanticQueryBuilder.java:941) org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression (SemanticQueryBuilder.java:269)
at at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1869)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup (SemanticQueryBuilder.java:926)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:269) at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1740)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement (SemanticQueryBuilder.java:443)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement (SemanticQueryBuilder.java:402)
at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel (SemanticQueryBuilder.java:311) at org.hibernate.query.hql.internal.StandardHq1Translator.translate (StandardHq1Translator.java:71)
at org.hibernate.query.internal. QueryInterpretationCacheStandardImpl.createHqlInterpretation (QueryInterpretationCacheStan
at org.hibernate.query.internal.Query InterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheSta
at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:790)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery (AbstractSharedSessionContract.java:840)
71 common frames omitted
Hi Ryan1969 ,
I have tried above way as well.Datediff is not identified as a function.We are using Postgres db.
Thanks,
Jazzzzyyy
Hello, If you want any help please tell me. I am happy to help you.
Best Regard,
ryan1969 California Healthcare
@Jazzzzyyy you should be able to use the datediff or timestampdiff functions with the following syntax across all Hibernate supported databases: datediff(day, current_date, e.modDate).