I am using querydsl-jpa and querydsl-sql 4.1.4
Want to run simple sql
SELECT created FROM user_info WHERE TRUNC (created) <= TO_DATE ('01.01.2016', 'dd.mm.yyyy')
My date comparison method
protected BooleanExpression dateCompare(DateTimePath<java.util.Date> dateTimePath, Date date) { DateExpression<Date> dbDate = SQLExpressions.datetrunc(DatePart.day, Expressions.asDate(dateTimePath)); DateExpression<Date> compareDate = Expressions. asDate(date); return dbDate.eq(compareDate); }
But I have a mistake
Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing [select transferLog from TransferLog transferLog where trunc_day(transferLog.transaction) = ?1]. [54, 93] The expression is not a valid conditional expression. at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1616)
How to use TRUNC and TO_DATE in QueryDSL ???
For example, in JPA I use this code
if (transferFilter.getTransactionEnd() != null) { Expression<Date> transactionEnd = qb.function("TRUNC", Date.class, root.get(TransferLog_.transaction)); Expression<Date> tillDate = qb.function("TO_DATE", Date.class, qb.literal(DateUtils.formatRuDate(transferFilter.getTransactionEnd(), "")), qb.literal("dd.mm.yyyy")); predicates.add(qb.greaterThanOrEqualTo(transactionEnd, tillDate)); }
I try to use such
protected BooleanExpression dateCompare(DateTimePath<java.util.Date> dateTimePath, Date date) { DateExpression<Date> dbDate = Expressions.dateTemplate(Date.class, "TRUNC({0})", dateTimePath); DateExpression<Date> compareDate = Expressions.dateTemplate(Date.class, "TO_DATE({0}, {1})", DateUtils.formatRuDate(date), ORACLE_DATE_FORMAT);
But there is a mistake
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [select transferLog from TransferLog transferLog where TRUNC(transferLog.transaction) = TO_DATE(?1, ?2)]. [54, 102] The expression is not a valid conditional expression. at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1614) ... 219 more