/*
 * 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));