CODEWITHSUNDEEP
javahibernate
rajputhch
rajputhch

Reputation: 627

Hibernate Named Query excluding null values

I am using the following named query to get results from the database based on parameters I am passing from a JSP page.

<query name="getData"><![CDATA[
select
    a.name,a.desc,a.reportname,b.manname,b.mandata
from
    product as a inner join
    a.manufacturer as m
where
    m.name like :manufacturer and
    a.description like :description and
    ((a.percentSize <= :sizeMax and
    a.percentSize >= :sizeMin) or
    a.percentSize is null) and
    ((a.wingSpanInches <= :spanMax and
    a.wingSpanInches >= :spanMin) or
    a.wingSpanInches is null) and
    ((a.recommendedAuwMinLbs <= :auwMax and
    a.recommendedAuwMaxLbs >= :auwMin) or
    a.recommendedAuwMaxLbs is null)
]]></query>

Not all the search parameters are mandatory (some values I am getting are null). How do I exclude parameters if they are null?

Upvotes: 0

Views: 3141

Answers (4)

Simon
Simon

Reputation: 1

It is possible if you add an extra variable eg. :active then your query shoul look something like this.

...
where
(m.name like :manufacturer and 1 = :active) or
(m.name like :manufacturer and a.description like :description  and 2 = :active) or
(m.name like :manufacturer and a.description like :description and a.percentSize <= :sizeMax and 3 = :active) or ...

And so on... With the :active you can then later decide which of the arguments should "count". Or do nothing by setting :active to e.g null.

Do you get the idea?

Upvotes: 0

CMR
CMR

Reputation: 985

You could use findByExample().
If the object has a null value field, it is not included in the generated SQL's where clause.

However, this means you cannot use a named query.

Upvotes: 1

Tom Anderson
Tom Anderson

Reputation: 47183

You really shouldn't be doing queries straight from a JSP page. Ouch.

You do have to set every parameter (at least, you do with PostgreSQL), but it's okay to set them to null. If they are null, the query you already have will do the right thing, and not apply the corresponding test.

So, you just need to make sure that all the parameters are passed, with any optional ones set to null if they aren't specified.

Upvotes: 0

Bozho
Bozho

Reputation: 597114

You can't do it. You'd have to build the query dynamically (using a StringBuilder).

if (firName != null) {
    sb.append(" WHERE ....");
}

session.createQuery(sb.toString());

Upvotes: 0

Related Questions