Building Dynamic where cluase with or using criteria query

manishoswal · **Joined:** Wed Jun 18, 2008 3:51 am **Posts:** 4

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

Hibernate version:
3.2
Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle 9i
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

how do i build a dynamic where cluase with or conditions using criteria query.

where (typeid = ? and name = ? ) or (typeid= ? and address = ?) or (typeid = ? and name = ? and address = ?)

Each of the criteria is determined at run time based on the no of types selected.

i have considered building the sql query using string concatenation and add it to criteria as sql restriction.

Please suggest if this is the best way to do it or is there any alternative.

Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Cameron McKenzie · **Posted:** Mon Jun 23, 2008 11:07 am

The criteria API makes it all real easy. Just use the Restrictions class and you'll find this is very easy to do, substituting real values into the restrictions at runtime.

http://jpa.ezhibernate.com/Javacode/lea ... riteriaapi

Here's a little sample code from my website. As you can see, I create dynamic SQL using the Restrictions class, asking for values that are less than one number, greater than another, while making sure the email address is not null and has an email address that ends in .com. It's all real easy!

Code:

public static void main(String args[]) {
  Session session = HibernateUtil.beginTransaction();
  Criterion c1 = Restrictions.gt("id", (long)2);
  Criterion c2 = Restrictions.lt("id", (long)8);
  Criterion c3 = Restrictions.isNotNull("emailAddress");
  User user = new User();
  user.setEmailAddress(".com");
  Example c4 = Example.create(user);
  c4.enableLike(MatchMode.END);
  c4.ignoreCase();
  Criteria criteria = session.createCriteria(User.class);
  criteria.add(c1);
  criteria.add(c2);
  criteria.add(c3);
  criteria.add(c4);
  List results = criteria.list();
  HibernateUtil.commitTransaction();
  for (int i = 0; i<results.size(); i++) {
    System.out.println(results.get(i).toString());
  }
}

Here's the full Hibernate3 Tutorial teaching you how to use the Hiberante Criteria API:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

manishoswal · **Joined:** Wed Jun 18, 2008 3:51 am **Posts:** 4

hi Cameron,

Thanks for the reply.
In the given example , the query will be build using "and" condition .
i.e c1 and c2 and c3 and c4.
what i was looking at is c1 or c2 or c3 or c4.
We can do it using conjunction and disjunction,
use conjunction for the (typeid= ? and name = ?) and add it to a disjunction.

manishoswal · **Joined:** Wed Jun 18, 2008 3:51 am **Posts:** 4

Just to add on , we can do it using conjunction and disjunction,
use conjunction for the (typeid= ? and name = ?) and add it to a disjunction.