Search code examples
mysqlspringhsqldbdaospring-jdbc

I am trying to fetch orders for last "N" months. In the following query I need to dynamically set the value for months

I am getting java exception while setting the value for months : 

SELECT * 
  FROM {Order} 
 WHERE creationtime >= NOW() 
   AND creationtime <= DATE_SUB(NOW(),  INTERVAL ?months MONTH)

However this query is working properly in mysql.

expection trace :

ERROR [hybrisHTTP34] [FlexibleSearch] Flexible search error occured... May 08, 2017 1:53:18 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [DispatcherServlet] in context with path [/jafrastorefront] threw exception [Request processing failed; nested exception is de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error - enable the property 'flexible.search.exception.show.query.details' for more details] with root cause org.hsqldb.HsqlException: unexpected token: ?

Solution

  • Not sure about java exception, but logic wise, you code has this problem.

    If NOW() is 5th May, DATE_SUB(NOW(),INTERVAL 1 MONTH) will be 5th April, So you are selecting records > 5th May and < 5th April at the same time. Hence you will get no results.

    Use between to avoid confusion, use BETWEEN.

    SELECT * FROM {Order} 
WHERE creationtime BETWEEN
   DATE_SUB(NOW(),  INTERVAL 1 MONTH )
AND   
   NOW()