Skip to content

Instantly share code, notes, and snippets.

@jeffsheets
Last active July 20, 2018 14:39
Embed
What would you like to do?
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.
/*
* 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));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment