home

Java Hibernate AND and OR SQL Query with Parentheses

If you want to do something like this in Hibernate:

mysql> SELECT * FROM table_t WHERE user='bob' AND (status='ACTIVE' OR status='RETIRED');


The code looks something like this. Unfortunately, I have to hide proprietary stuff, so I can't guarantee that this is perfect or even compiles, but hopefully it'll give you the idea.

// this may be too many imports, but better too many than too few
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Disjunction;

// whatever class container starts here.  I'm leaving that out.


// I have no idea whether this usually starts with session
// myModel would be an Object representation of the table (a Model in the Controller View Model paradigm)
Criteria crit = session.createCriteria(myModel.class);

crit.add(Restrictions.eq("user", "bob"));


// assume an arbitrary number of "OR" terms from an array
String[] statuses = getORedStatuses(); // imaginary / example function to "getORed..."

// if there is only one, then there is no need for an OR
if (statuses.length == 1) crit.add(Restrictions.eq("status",statuses[0]));
else if (statusFilter.length > 1) { 

	Disjunction disjunction = Restrictions.disjunction();

	// In the XHTML source, I use the < entity for XHTML validation.  
	// It's of course the "less than" symbol in Java code.
	for (int i=0; i < status.length; i++) 
		disjunction.add(Restrictions.eq("status",statuses[i]));
	
	crit.add(disjunction);
}

// and then the actual call to get the result, which is well-explained elsewhere, I hope