The last two evenings I stucked in searching for more or less complex examples of different JPA 2 queries. By complex I mean, constructing constructor queries with multiple joins, group by and an average function (avg). The JPQL solution (using a normal query defined as string) wasn't to hard to find, compared to the Criteria API version (introduced in the JPA 2 specification).
In the following article I'm going to describe three semantically identical queries using:
- JPQL query
- Criteria API string based Query
- Criteria API dynamic, typesafe Query (using a canonical Metamodel)
I don't want to go to much in details by explaining Criteria API here, because there are obviously enough resources on the net (see references). To construct a complex query, I use three Entity Beans: Car, CarModel, CarManufacturer. The dependencies between the classes are straightforward: a car has a reference to a model and the model refer to a car manufacturer. The car also has a property mileage (Double), consider a used car. Furthermore, both, car model and manufacturer, also have a name property (String). Here is the graphical representation of this class structure.
All subsequent queries correspond to the following SQL-Statement:
SELECT cmf.name, AVG(c.mileage)
FROM Car c
INNER JOIN CarModel cm
ON c.carModel = cm.uuid
INNER JOIN CarManufacturer cmf
ON cm.manufacturer = cmf.uuid
GROUP BY cmf.name
meaning: Get all car manufacturer name and the belonging average mileage for all cars of the manufacturer.
Before getting started with the interesting stuff, I must introduce an additional class that is essential for this examples. The problem is, if you want to compound your Query result, you have to construct a value object class like CompoudElement I use. It is important that you have a constructor that explicitly matches your Query Selection-clause. So, I want to receive a String (name) and a Double (avg value) for my query, so the class looks something like this:
public class CompoundElement
{
private String name
;
private Double dValue
;
public CompoundElement
(String name,
Double value
) {
this.
name = name
;
this.
dValue = value
;
}
/* needed for Criteria query */
public CompoundElement
(String name,
Object num
) {
this.
name = name
;
this.
dbValue = (Double)num
;
}
/* getter an setter */
} The JPQL query isn't so far away from the SQL one. This query is also called constructor query, because of the previous mentioned CompoundElement class you need.
public class SomeClassJPQL {
@PersistenceContext
private EntityManager em;
public List<CompoundElement> findAvgMileageForManufacturer() {
Query query = em.createQuery(
"SELECT NEW aw.queries.api.vo.CompoundElement(cmfJoin.name, AVG(c.mileage)) " +
"FROM Car c " +
"JOIN c.carModel cm " +
"JOIN cm.manufacturer cmfJoin " +
"GROUP BY cmfJoin.name"
);
return (List<CompoundElement>)query.getResultList();
}
}
While the query is executed, the JPA creates a List of CompoundElements, adds a new CompoundElement for every result tuple and returns the constructed result list (see getResultList).
Now lets view the Criteria API code for that string based query. In contrast to the JPQL code, the query here uses the
constructor. So we have to cast the Object to Double inside the constructor (see CompoundElement definition).
public class SomeClassStrings {
@PersistenceContext
private EntityManager em;
public List<CompoundElement> findAvgMileageForManufacturer() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CompoundElement> q = cb.createQuery(CompoundElement.class);
Root<Car> c = q.from(Car.class);
Join<Car, CarModel> cmJoin = c.join("carModel");
Join<CarModel, CarManufacturer> cmfJoin = cmJoin.join("manufacturer");
Expression<Double> mileage = c.get("mileage");
CompoundSelection<CompoundElement> cSelect = cb.construct(CompoundElement.class, cmfJoin.get("name"), cb.avg(mileage));
q.select(cSelect);
Expression<String> manuName = cmfJoin.get("name");
q.groupBy(manuName);
TypedQuery<CompoundElement> query = em.createQuery(q);
return (List<CompoundElement>)query.getResultList();
}
}
The last example uses the Criteria API typesafe query based on a metamodel which you have to define [JavaEE6Tut]. This is obviously a very annoying task, thats why eclipseLink provides you a metamodel generator [genCanonicalModel]. I had some strange experiences with this generation process using maven and NetBeans 6.8.
Using the standard canonical classes definition results in: Car_, CarModel_ and CarManufacturer_. The query for the typesafe version looks like this:
public class SomeClassDynamic {
@PersistenceContext
private EntityManager em;
public List<CompoundElement> findAvgMileageForManufacturer() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CompoundElement> q = cb.createQuery(CompoundElement.class);
Root<Car> c = q.from(Car.class);
Join<Car, CarModel> cmJoin = c.join(Car_.carModel);
Join<CarModel, CarManufacturer> cmfJoin = cmJoin.join(CarModel_.manufacturer);
CompoundSelection<CompoundElement> cSelect =
cb.construct(CompoundElement.class, cmfJoin.get(CarManufacturer_.name), cb.avg(c.get(Car_.mileage)));
q.select(cSelect);
q.groupBy(cmfJoin.get(CarManufacturer_.name));
TypedQuery<CompoundElement> query = em.createQuery(q);
return (List<CompoundElement>)query.getResultList();
}
}
References:
- [1] IBM developer works - Dynamic, typesafe queries in JPA 2.0
- [2] Oracle (Sun) - The Java EE 6 Tutorial Part I
- [3] In Relation to ... - A typesafe criteria query API for JPA
- [4] The little island of excitement - Generating JPA 2.0 Criteria canonical metamodels ...
- [5] Sirius ICT - A Look at JPA 2.0 Criteria API
