Hibernate Named Query excluding null values

Question

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?

This does not answer your question, but I think you should take a look at the Criteria API, it makes creating queries of this kind a lot more simple. — Holm
– Holm, Commented Mar 7, 2011 at 14:35

CMR · Accepted Answer · 2011-03-07 14:08:08Z

1

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.

edited Mar 7, 2011 at 14:08

answered Mar 5, 2011 at 23:49

CMR

9857 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bozho · Accepted Answer · 2011-03-05 22:47:58Z

0

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

edited Mar 5, 2011 at 22:47

answered Mar 5, 2011 at 18:18

Bozho

599k147 gold badges1.1k silver badges1.2k bronze badges

10 Comments

rajputhch Over a year ago

@Bozho:But in named query ,it is manadatory to set all the params right?

rajputhch Over a year ago

@Bozho:I dont understnad what your saying,I think you got my issue.

Bozho Over a year ago

@raj what do you want your code that will handle the result of the query look like?

rajputhch Over a year ago

@Bozho: Suppose if i have parameters first name and last name.from my jsp if first name coming as null,i want to remove this parameter from query before it hits the db.

rajputhch Over a year ago

@ Bozho:Thanks,After preparing clauses ,how can i set The values dynamically to the parameters.

|

Tom Anderson · Accepted Answer · 2011-03-05 23:40:24Z

0

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.

answered Mar 5, 2011 at 23:40

Tom Anderson

47.4k17 gold badges96 silver badges138 bronze badges

Comments

Simon · Accepted Answer · 2011-06-22 23:22:42Z

0

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?

answered Jun 22, 2011 at 23:22

Simon

1

Collectives™ on Stack Overflow

Hibernate Named Query excluding null values

4 Answers 4

Comments

10 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related