Hibernate DetachedCriteria using subqueries exists clause, while eager fetching the lazy collection that is in the exists clause, and running via Spring HibernateTemplate findByCriteria, and limit results to max of 2000.

gistfile1.java

/*
 * HQL would look something like this:
 *
 * from Person p join fetch p.addresses address
 * where exists (
 *   from Address addr left join addr.state st
 *   where addr.personId = p.id
 *   and st.abbreviation = :abbreviation
 * )
 */

DetachedCriteria criteria = DetachedCriteria.forClass(Person.class, "p");

//addresses is set to lazy=true in hbm.xml file, but we want to pull back all
//addresses for a Person, so we must make it eager with JOIN
criteria.setFetchMode("addresses", FetchMode.JOIN);

DetachedCriteria addressCriteria = DetachedCriteria.forClass(Address.class, "addr");
addressCriteria.createAlias("state", "st");
addressCriteria.add(Restrictions.eq("st.abbreviation", abbreviation));
addressCriteria.add(Restrictions.eqProperty("addr.personId", "p.id"));

//Finds all Persons that have an Address with state abbreviation of :abbreviation
criteria.add(Subqueries.exists(addressCriteria.setProjection(Projections.property("addr.id"))));

//Limit results to first 2000 Persons that have an address matching the given state abbreviation
List<Person> results = getHibernateTemplate().findByCriteria(criteria, 0, 2000);



/**
 * A second option can be used
 * if addresses is set as lazy="false" and fetch="subselect" in hbm.xml
 * (also use this if the previous JOIN method causes duplicate rows)
 */
DetachedCriteria criteria = DetachedCriteria.forClass(Person.class, "p");

//To pull back ALL addresses use FULL_JOIN. To only pull back matching addresses use LEFT_JOIN
int joinType = showAllAddresses ? CriteriaSpecification.FULL_JOIN : CriteriaSpecification.LEFT_JOIN;
criteria.createAlias("addresses", "addr", joinType);

criteria.createAlias("addr.state", "st");
criteria.add(Restrictions.eq("st.abbreviation", abbreviation));