How to dynamically search between two dates in hql?

Question

I have two search fields by date: from and to. I need to get records from a user table whose startDate is between the dates and dates entered in the search fields, and if the values ​​from dates and dates are zero, I have to get all the records from the user table.

I tried the following hql query:

FROM USER WHERE :start_flag =1 OR STARTDATE between :from and :to 

Here start_flag is of type int, which is set to 1 if from and to is null.

 query.setParameter("from",startDt); query.setParameter("to",endDt); query.setParameter("start_flag",startFlag); l= query.list(); 

Here are the data types:

startDt - java.util.Date

endDt- java.util.Date

startFlag-int

When I run the above query with and equal to zero , I get the following exception:

SQL Error: 932, SQLState: 42000

ORA-00932: inconsistent data types: expected DATE received BINARY

Could you tell me how to write an HQL query to achieve the above functions?

Answer 1

filter it as follows:

 String query = "FROM USER WHERE :start_flag =1 "; if(startDt!=null && endDt!=null){ query +="OR STARTDATE between :start_date and :end_date"; } TypedQuery<USER> hql = entityManager.createQuery(query); if(startDt!=null && endDt!=null){ hql.setParameter("start_date",startDt).setParameter("end_date",endDt); } List<USER> result = hql.setParameter("start_flag",startFlag).list();