dynamic query with date data type in spel using spring data

Question

I'm struggling in creating a dynamic query with null check on date in spel using spring data. My query is:

 @Query(nativeQuery = true, value = "select a.* from TABLE a where"
        + "(:#{#DateFrom} is null or a.F_DATE >= trunc(:#{#DateFrom}))")
List<Acc> getAccList(@Param("DateFrom") @Temporal(TemporalType.DATE) Date datefrom);

When I run this query with valid date it works, but when I pass null date I get following error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Stack trace:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at com.ing.cbp.commons.util.logging.sql.LogablePreparedStatement.executeQuery(LogablePreparedStatement.java:77)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
... 78 common frames omitted

user9462436 · Accepted Answer · 2018-03-08 13:20:37Z

1

You need add one whitespace after 'where' keyword.

@Query(nativeQuery = true, value = "select a.* from TABLE a where "
    + "(:#{#DateFrom} is null or a.F_DATE >= trunc(:#{#DateFrom}))")
List<Acc> getAccList(@Param("DateFrom") @Temporal(TemporalType.DATE) Date datefrom);

answered Mar 8, 2018 at 13:20

user9462436

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Jens Schauder · Accepted Answer · 2017-11-30 08:08:42Z

0

It seems oracle considers the NULL value a binary and than fails when it tries to perform trunc on it. Casting it to a Date should help. So the resulting query should look like this:

select a.* from TABLE a where (:#{#DateFrom} is null 
or a.F_DATE >= trunc(CAST(:#{#DateFrom} AS DATE))

answered Nov 30, 2017 at 8:08

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

2 Comments

oOXAam Over a year ago

Tried it, got same exception :(

Jens Schauder Over a year ago

Can you remove one part of the condition at a time in order to determine which one is causing the problem? Also please log the SQL statement that actually gets executed. Finally, can you execute the SQL statement with an actual null literal instead of the bind variable?

Collectives™ on Stack Overflow

dynamic query with date data type in spel using spring data

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related