JPA Criteria and JPQL
JPA2 introduced its “Criteria” queries, providing an API for typesafe query generation without the need to hardcode field names etc in queries; it built on the approach of Hibernate Criteria.
In these examples we have two classes, Inventory and Product, where Inventory has a set of products.
Select of persistable objects with simple filter
JPQL single-string would be
SELECT p FROM Product p WHERE p.name = 'MP3 Extra'
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(Product.class); Root productRoot = criteria.from(Product.class); criteria.select(productRoot); criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra")); List products = em.createQuery(criteria).getResultList();
JPQL single-string would be
SELECT p.value, p.manufacturer FROM Product p WHERE p.name = 'MP3 Extra'
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(); Root productRoot = criteria.from(Product.class); criteria.multiselect(productRoot.get(Product_.value), productRoot.get(Product_.manufacturer); criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra")); List <Object[]> results = em.createQuery(criteria).getResultList();
Select of aggregate of attribute of persistable objects
JPQL single-string would be
SELECT MAX(p.value) FROM Product p WHERE p.name = "MP3 Extra"
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(Integer.class); Root productRoot = criteria.from(Product.class); criteria.select(builder.max(productRoot.get(Product_.value))); criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra")); Object result = em.createQuery(criteria).getSingleResult();
Select of persistable objects with simple filter and parameter
JPQL single-string would be
SELECT p FROM Product p WHERE p.name = :param
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(Product.class); Root productRoot = criteria.from(Product.class); criteria.select(productRoot); ParameterExpression valueParam = builder.parameter(String.class); criteria.where(builder.equal(productRoot.get(Product_.name), valueParam)); TypedQuery query = em.createQuery(criteria); query.setParameter(valueParam, "MP3 Extra"); List products = query.getResultList();
Select of persistable objects with joined filter condition
JPQL single-string would be
SELECT i FROM Inventory i JOIN i.products p WHERE (p.name = 'MP3 Extra')
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(Inventory.class); Root invRoot = criteria.from(Inventory.class); criteria.select(invRoot); JoinInventory productJoin = invRoot.join(Inventory_.products); criteria.where(builder.equal(productJoin.get(Product_.name), "MP3 Extra")); List inventories = em.createQuery(criteria).getResultList();
Select of persistable objects with subquery filter
JPQL single-string would be
SELECT p FROM Product p WHERE p.value < (SELECT AVG(q.value) FROM Product q)
JPA Criteria would be
CriteriaQuery criteria = builder.createQuery(Product.class); Root productRoot = criteria.from(Product.class); criteria.select(productRoot); Subquery sub = criteria.subquery(Double.class); Root subRoot = sub.from(Product.class); criteria.where(builder.lt(productRoot.get(Product_.value), sub.select(builder.avg(subRoot.get(Product_.value))))); List products = em.createQuery(criteria).getResultList();