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();
If you think something is wrong please feel free to comment :)

Leave a reply.