hibernate DetachedCriteria join

hibernateInnerJoin.java

//http://stackoverflow.com/questions/13075390/hibernate-subquery-detachedcriteria
DetachedCriteria exampleSubquery = DetachedCriteria.forClass(MyPersistedObject.class)
    .setProjection(Property.forName("id"))
    // plus any other criteria...
    ;

Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class)
    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    .add(Subqueries.propertyIn("myPersistedObjectId", exampleSubquery)));
// For multiple subqueries, you can use a boolean operator like Restrictions.or():

DetachedCriteria anotherSubquery = DetachedCriteria.forClass(MyPersistedObject.class)
    .setProjection(Property.forName("id"))
    // plus any other criteria...
    ;

Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class)
    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    .add(Restrictions.or(
        Subqueries.propertyIn("myPersistedObjectId", exampleSubquery),
        Subqueries.propertyIn("myPersistedObjectId", anotherSubquery)));
/*
 * 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));