Using Named Queries

[Kim Kantola]

Hi Yall,
I am starting to use named queries, and am not sure how to handle one thing. I have a query like "from myTable". If the user passes in a firstName, or lastName, or Date of birth, the query should be

"from myTable where fname= :firstName AND lname= :lastName and DOB =:DOB.

however, at runtime, I am not sure if the user is going to pass any or all of the parameters.

I want to have this query defined in my file NamedQuery.hbm.xml

<hibernate-mapping>


  <query name="myNamedQuery">from myTable where fname= :firstName AND lname= :lastName and DOB =:DOB.</query>
  
</hibernate-mapping>    

I just don't know how to handle deciding on the fly if I need to remove one of the things like lname = :lastName from my query.

Can I do something like this in the code (use a wildcard)

if lname=null{
		query.setParameter("lname", "*");
}   

Thanks if anyone has an idea.

[Andreas Erber]

Hi Kim Kantola,

I've never used named queries myself but my first guess is if you want to use a wildcard you should probably try the SQL-wildcard of your DBMS. That is, most likely, the per-cent sign "%".

CU
Froestel

[Kim Kantola]

Thank you very much, yes, using the "%" did the trick.

It however brought me to a new problem, one of the fields I am trying to compare is a date, so if I have this in my query:

and DOB =:DOB 

as part of my query, and my DOB value is null so I do this :

   query.setParameter("DOB", "%"); 

I then get the follwing error :

java.lang.ClassCastException
at org.hibernate.type.DateType.toString(DateType.java:78)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:89)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:63)
at org.hibernate.loader.hql.QueryLoader.bindNamedParameters(QueryLoader.java:491)


Is there a special wildcard to use for dates ?

Thanks!

[Mark Spritzler]

Usually for queries which are dynamic like that, it is easier to use the Criteria object instead.

then you can have code like

from myTable where fname= :firstName AND lname= :lastName and DOB =OB

  
Criteria dynamicQuery = session.createCriteria(MyTable.class)

if (firstName != null) {
    dynamicQuery.add(Restrictions.eq("fName", firstName);
}

if (lastName != null) {
    dynamicQuery.add(Restrictions.eq("lName", lastName);
}

if (DOB != null) {
    dynamicQuery.add(Restrictions.eq("DOB", DOB);
}

List results = dynamicQuery.list();
  

Mark