0

I am trying to implement the following SQL query in hibernate HSQL. Instead of value '7' I have to pass a dynamic value 'delivery'.

SELECT quote_key , delivery_date 
  FROM Orders 
  WHERE delivery_date >= DATE_ADD(now(), INTERVAL '-7' DAY);

DAO Layer:

String sql = " FROM Orders where deliveryDate >= DATE_ADD(now(), INTERVAL -(:delivery) " + " DAY)";

Query query = session.createQuery(sql);
query.setParameter("delivery", delivery);
List<Orders> mergedData = query.list();

I am getting the following error:

unexpected token: DAY

for any syntax I try. Is there any way to implement it using HSQL itself rather than Native SQL?

Adam Richardson
  • 2,518
  • 1
  • 27
  • 31
Majo
  • 15
  • 6

1 Answers1

0

You can use the CREATE FUNCTION syntax to create a function that will take that integer parameter, create your own dialect and use the JPQL function('myfunction', :param), as described in this article.

Otherwise you will have to use native SQL.

coladict
  • 4,799
  • 1
  • 16
  • 27