How to dynamically search between two dates in hql?

Question

I have two Date search field namely from and to. I have to retrieve records from the user table whose startDate lies between the from and to dates entered in the search fields and if the from and to dates are null I have to retrieve 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 are null.

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

Here datatypes are:

startDt - java.util.Date

endDt- java.util.Date

startFlag- int

When I run the above query with to and from equal to null I get the following exception:

SQL Error: 932, SQLState: 42000

ORA-00932: inconsistent datatypes: expected DATE got BINARY

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

Answer 1

filter it like this:

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();