Hibernate Community • View topic - set a query with or without a parameter

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

set a query with or without a parameter

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

pfenn

Post subject: set a query with or without a parameter

Posted: Tue Jun 14, 2005 5:15 am

Beginner

Joined: Fri Jul 16, 2004 3:21 am
Posts: 40

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Code between sessionFactory.openSession() and session.close():

if got a Query over to tables:
Query query = session.createQuery("select u from Usedmachine as u inner join u.Category as Category where u.Typ=:typ and u.Sellyear=:year");
query.setString("typ",this.type);
query.setInteger("year",this.year);

this runs well. But if one of the two parameters cannot be set, because i want all usedmachine form any years.

How can i set this in the query?

Name and version of the database you are using:DB2 8.2.1

rgds stefan

Top

Rilux

Post subject:

Posted: Tue Jun 14, 2005 6:32 am

Regular

Joined: Tue Nov 23, 2004 7:42 am
Posts: 82
Location: London, England

Dynamically build the query depending on whether the values are null/empty/zero or not?

Code:

StringBuffer buffer = new StringBuffer("select u from Usedmachine as u inner join u.Category as Category");

if ( (this.type != null && !this.type.equals("")) || (this.year != null && this.year.intValue() > 0) ) {
    buffer.append(" where");
    if (this.type != null && !this.type.equals("")) {
        buffer.append(" u.Typ=:typ");
        if (this.year != null && this.year.intValue() > 0) {
            buffer.append(" and");
        }
    }
    if (this.year != null && this.year.intValue() > 0) {
        buffer.append(" u.Sellyear=:year");
    }
}

Query query = session.createQuery(buffer.toString());
if (this.type != null && !this.type.equals("")) {
    query.setString("typ",this.type);
}
if (this.year != null && this.year.intValue() > 0) {
    query.setInteger("year",this.year); 
}

This is REALLY ugly, I'd admit. I hate all those nested if statements. You could also do something cleaner and clearer with criteria queries. In fact, perhaps you should build a criteria query based on whether your parameters are null or not.

Code:

Criteria criteria = session.createCriteria(Usedmachine.class);
if (this.type != null && !this.type.equals("")) {
    criteria.add(Restrictions.eq("typ", this.type);
}
if (this.year != null && this.year.intValue() > 0) {
    criteria.add(Restrictions.eq("year", this.year);
}
List list = criteria.list();

Isn't that cleaner?

Top

pfenn

Post subject:

Posted: Tue Jun 14, 2005 9:38 am

Beginner

Joined: Fri Jul 16, 2004 3:21 am
Posts: 40

Hi Rilux

looks great, can i make Criteria over more then two tables.

eg if a had another Criteria with Category?

rgds Stefan

Top

Rilux

Post subject:

Posted: Tue Jun 14, 2005 9:42 am

Regular

Joined: Tue Nov 23, 2004 7:42 am
Posts: 82
Location: London, England

Of course. Read this.

Top

jesse_sweetland

Post subject:

Posted: Tue Jun 14, 2005 10:30 am

Regular

Joined: Mon Jul 26, 2004 2:28 pm
Posts: 86
Location: Pensacola, Florida

Another option:

Code:

select *
from foo f
where (:bar1 is null or f.bar1=:bar1) and (:bar2 is null or f.bar2=:bar2)

And then supply null values for bar1 and bar2 in the case where you want all values.

Top

pfenn

Post subject:

Posted: Tue Jun 14, 2005 12:47 pm

Beginner

Joined: Fri Jul 16, 2004 3:21 am
Posts: 40

Runs perfect with the criteria :

Code:

Usedmachine usedmachine;
        Criteria criteria = session.createCriteria(Usedmachine.class);
        if (year!=-1) {
           criteria.add(Expression.eq("Sellyear",new Integer(year)));
        }
        if (!this.type.equals("-1")) {
           criteria.add(Expression.eq("Typ",type));
        }
        if (!this.category.equals("-1")) {
          criteria.createCriteria("Category").add(Expression.eq("Category",category));
        }
        List searchResult = criteria.list();

thanks for the help

rgds Stefan

Top

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum