How to do AND/OR type SQL queries using Hibernate AND/OR JPA using disjunctions

If you have been using Hibernate or JPA for a while you will know that the Criteria and Restrictions classes are very handy for querying the database without writing any SQL code and without worrying about the real column names (these are all captured in the annotations that you have applied to the domain objects).

This is fine when you want to search for objects (rows) which are a combination of columns such as forename = "Andrew" AND surname = "Beacock" but what if you want to search for all people who either have a forename of Andrew and/or a surname of Beacock?

This is where a "disjunction" comes in. This is one of the types hanging off the Restrictions class and deals with AND/OR situations. The code below is looking through the 'products' table for any product that has a code or name starting with the text supplied:

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
 
Criteria criteria = session.createCriteria(Product.class);
criteria.add(
  Restrictions.disjunction()
    .add(Restrictions.ilike("code", codeOrName + "%"))
    .add(Restrictions.ilike("name", codeOrName + "%"))
);
return criteria.list();

You can obviously add as many additional Restrictions as you like to the disjunction to make it as simple or complex as you like...