Binding null parameter to query

trumpc · **Posted:** Wed Oct 29, 2003 11:08 am

Hi

I would like to execute query:

SELECT p FROM Person AS p WHERE ((? is null) OR (p.firstname = ?)) AND ((? is null) OR (p.lastname = ?))

This query assumes that person can be searched by firstname, lastname or both. Right now hibernate throws NullPointerException when I'm trying to bind null parameter because it tries to guess parameter type and ...
Maybe in Hibernate we need to introduce NullType for scalar values ? I know that is dialect specific but it would be awesome to have it.

Reason for such funcionality is to have dynamic searches from UI when user can enter bunch of search parameters and not all parameters are required.

PS:

I know that query above can be expressed by search criteria but this is just an example real query is more complicated and it can not be expressed with Criteroia.

trumpc · **Posted:** Wed Oct 29, 2003 11:16 am

Righ now code:

Code:

private Type guessType(Object param) throws HibernateException 
{
      Class clazz = HibernateProxyHelper.getClass(param);
      return guessType(clazz);
}

throws null pointer exception

After introduction of null type:

Code:

private Type guessType(Object param) throws HibernateException 
{
    if(param == null)
     {
           return Hibernate.nullType();
     }
    Class clazz = HibernateProxyHelper.getClass(param);
    return guessType(clazz);
}

cutie · **Posted:** Wed Oct 29, 2003 1:51 pm

I agree, why don't you post a JIRA issue and update this thread with its URL so we can voter for it.

gavin · **Posted:** Wed Oct 29, 2003 7:09 pm

Its not really possible to implement this, since JDBC setNull() requires a type code.

OTOH, you could probably use setParameter(null, Hibernate.INTEGER), for example.

gpeel · **Posted:** Mon Dec 01, 2003 12:53 pm

I just tried out, but the null values keep filtering to the request
example :
Query q = session.createQuery("from HbSalle as salle, where salle.nom like :nom and salle.capacite = :capacite");

q.setParameter("nom", null, Hibernate.STRING);
q.setParameter("capacite", null, Hibernate.LONG);

On Mysql query log, it shows :

select hbsalle0_.ID as ID, hbsalle0_.OPTIMIST_LOCK as OPTIMIST2_, hbsalle0_.NOM as NOM, hbsalle0_.LOCALISATION as LOCALISA4_, hbsalle0_.CAPACITE as CAPACITE from SALLE hbsalle0_ where (hbsalle0_.NOM like null )and(hbsalle0_.CAPACITE=null )

gavin · **Posted:** Mon Dec 01, 2003 7:15 pm

ummm, yes, of course....

this is a completely different query to the one in the first post!