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 ?

Chon

Did you try setDate (or something similar) instead of setParameter? Are the date types mapped correctly to a date column (or had it accidentally been serialized)? – Ivory

No I did not try setDate() but I guess setParameteer() should work – Chon

In my experience with NHibernate, it does not. – Ivory

I tried setDate(), it also throws the same exception – Chon

What about the mapping? Which type is used in the database? – Ivory

I am using Oracle db, since it is HQL it is database independent right? – Chon

You misunderstand my question. What type has the column, where the date is stored in? Had it accidentally be serialized? – Ivory

In the mapping it is declared as java.sql.Date and in db column type is DATE, I dont know if it was serialized – Chon

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

Adoree

Sorry that was a typing mistake this is not the solution, it wont work – Chon

For dynamically building of type queries using of Criteria API is more appropriate. docs.oracle.com/javaee/6/tutorial/doc/gjitv.html – Franke