In Part One of this series, I reviewed the basic design goals of a Data Access Layer (DAL), those being:
- Contain all of the information about the persistence mechanism; and
- Contain none of the business logic.
I then discussed what can happen if your application needs to operate on large volumes of data: performance may demand running more sophisticated queries; but if hard-coded queries encode business logic, then it becomes impossible to meet both of the above design goals. Such queries contain both business logic and information about the persistence mechanism. They must either live inside the DAL (compromising objective #2) or outside the DAL (compromising objective #1).
One solution is to build the query dynamically. The DAL can offer methods that incrementally add logic to a query, and then the business logic can issue a series of requests like:
- Start building a query
- Add a set of order items named "oi" to the query
- Restrict data set "oi" on the condition "order id is 12345″
- Get the total cost of inventory by executing the query
Of course you probably wouldn’t use this technique for a query that simple, but from comparably basic building blocks the DAL can be instructed to build and execute much more involved queries based on business logic distributed across any number of objects in the business layer.
This all sounds good on paper, but how do you actually do it? The key thing that you need is a mechanism for building queries. Dynamic query generation can be a tough problem. It’s important to control the scope of the effort – e.g. focus on just the query operations you actually need.
If you’re using JPA2 for persistence and JPQL is expressive enough for your query needs, then you can use your JPA provider itself as the query-building mechanism via the Criteria API. However, in Part One of this series, I made the case that JPA as a whole should be kept internal to your DAL, and the same applies to the Criteria API (which is fundamentally a part of JPA). What follows, then, is not a universal solution; it’s a specific implementation example. This implies the need for a bit of caution:
With the Criteria API, the effort to support a wide range of query-building operations is within reason. It may seem that each operation you implement will give you more options. DBMS query optimizers aren’t perfect, so sometimes having two (or three, or more) ways to write a query offers better odds that you can find at least one way that’s efficient.
But remember, if you change persistence mechanisms down the road – and this exercise aims to reduce the pain if that should happen – then the Criteria API may no longer be an option. The more operations you support, the harder it will be to build the new implementation.
This approach is at its best when a handful of simple building blocks can be combined in a wide range of permutations to support many business computations. Add a new operation only when there’s a compelling reason, and if you find that a single query alone creates the need for a particularly complex operation, you might consider just hard-coding the query in that unique case.
With those caveats out of the way, let’s look at how something like this might actually be built. To start, we’ll review the basics of the Criteria API. Using the API can be thought of in the following steps:
- Obtain the CriteriaBuilder
- Create a CriteriaQuery
- Add Query Logic
- a) FROM clause
- b) WHERE clause
- Finish and Execute the Query
Step 1: Obtain the CriteriaBuilder
The CriteriaBuilder is a key factory for various objects used in the API. It’s pretty easy to get: you can just request it from the EntityManager.
(Editor’s Note: Many code blocks in this blog build on previous code. To differentiate changes, the "new" code has been highlighted in blue.)
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
}
Here’s I’m assuming something like Spring dependency injection provides the EntityManager; you might even prefer that Spring inject the CriteriaBuilder directly. The point is just that it ultimately comes from the EntityManager and is a resource to be kept concealed in your DAL to be used by the various methods that build your queries.
Step 2: Create a CriteriaQuery
Each query you’re building is represented by a CriteriaQuery object. The CriteriaQuery interface provides methods for adding query logic, some of which also act as factories for objects that represent the corresponding fragment of the query. (Many of these methods are inherited from AbstractQuery.)
The CriteriaQuery is not itself an executable JPQL query. When you’re done adding logic to it, you pass it to the EntityManager (via one of the createQuery() methods), which returns a TypedQuery.
To make the best use of generics, you’d declare the CriteriaQuery’s return type when you create it. Our general approach is dynamic enough that maintaining full type safety can be a challenge; but for the sake of argument, let’s suppose your business layer will always know up front what type of result it’s asking for. This example assumes the query returns a type that would be known outside the DAL; this is simple for cases like returning counts (Long), and is possible even for returning object instances (using SELECT NEW syntax).
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
private CriteriaQuery<?> criteriaQuery = null;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
public void createQuery(Class<?> returnType) {
if (criteriaQuery != null) {
throw new IllegalStateException("Already building a query");
}
criteriaQuery = criteriaBuilder.createQuery(returnType);
}
}
Note that here I’ve stored the CriteriaQuery as private instance data; this implies that the single DAO will contain all the operations you need to build your query, which may not be realistic if the query involves various domain objects. Also it supposes that you’re only going to build one query at a time (which may be more realistic, depending on your threading model, but still is not a necessary restriction).
You could instead return the CriteriaQuery from the createQuery() method, and then expect it as a parameter in the methods that act on the query. You’d want to use Object in your signature, though, since the outside world isn’t supposed to know about (and therefore potentially depend on) the fact that you’re using CriteriaQuery. Alternately you could create some sort of query registry within your DAL; createQuery() would then return an identifier that could later be used to find a CriteriaQuery in the registry. For simplicity, I’ll continue with the single-DAO / single-query assumption throughout this example.
Step 3: Add Query Logic
Now it’s time to tell your query what it’s going to do. Here you have a lot of flexibility to choose a design that suits your needs. Remember that you should keep the number of "building blocks" minimal.
Any query has to give an indication of the base data set to be read – i.e. the FROM clause in a JPQL query. Most opportunities to incrementally add logic to a query involve constraints on the result set – the WHERE clause. You might also want to support aggregates (GROUP BY, HAVING, etc.), but this has the potential to add considerable complexity and JPA-dependence if not done with care. You also could provide operations here for specifying which values to return (the SELECT clause), but I’ve left that to be part of the execute() method in this example design.
So let’s look at FROM and WHERE. We’ll look at FROM first, and in fact you generally will add a data set to the FROM clause and then add related elements to the WHERE clause. Other than that, you can alternate back and forth between adding FROM logic and adding WHERE logic if doing so is beneficial, though.
Step 3a: FROM Clause
A JPQL query’s FROM clause can be thought of as a comma-separated list of roots, each potentially carrying any number of attached joins. (For completeness, a root could also have "fetch joins" in JPQL, but that’s not something we’re going to use here.)
When you add a root to a query, you specify an entity type. A root represents a list of all instances of the specified entity (though joins and the contents of other query clauses can limit how much of that list the query really uses).
If a query contains multiple roots, then it operates on a list containing each combination of one element from the first root, one element from the second root, etc.
You add a root to a CriteriaQuery by calling its from() method. You can specify the entity type using either an EntityType object (from the metamodel) or a Class object. The from() method returns an object representing the newly-added root; the interface type it gives for this object is Root.
Root is an extension of From, which makes it a factory for joins (more on that later) and a parameter to many CriteriaBuilder methods that build expressions for use in other clauses (WHERE, SELECT, etc.).
From is also an extension of Path, meaning it can be used as a JPQL path expression (and to build other path expressions through its get() methods). This is like using the range variable alias (or join variable alias) by itself as an operand in a predicate; for example:
SELECT hire_dt
FROM Employee e
WHERE e = :emp
would expect an Employee instance for parameter :emp and would return that same employee’s hire date. (However, note that at least historically some providers have gotten this wrong. Hibernate fixed it in version 3.5.6.)
Path in turn extends Expression, which extends Selection and TupleElement. As you can see, a Root can fit into a query in a lot of different ways. While you can get a list of roots from the CriteriaQuery, you still may want to keep track of them in a manner that makes it easier for subsequent query-building operations to refer to them.
But this touches on another point: Your business layer (and therefore your DAL interface) shouldn’t talk about Root or From instances since they’re part of JPA. Even returning the From as an Object reference may not be ideal. A From represents a collection of a particular entity type (which in turn will usually represent a table in the query); but the business layer should be thinking about business object types, which may or may not correspond one-for-one to entity types.
In our example, we’ll assume that the business object types do correspond one-for-one to entity types at the moment; but we won’t assume this could never change. Still, we’ll assert that even if some future business object should map to a combination of entities, one particular entity collection will always be able to stand as a token representing the related collection of business objects. So, when adding a set of business objects to the query, we’ll return an Object reference that we know, internally, refers to a From. Subsequent calls that might modify the query in ways related to that set of objects will expect the same object reference as a parameter.
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
private CriteriaQuery<?> criteriaQuery = null;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
public void createQuery(Class<?> returnType) {
if (criteriaQuery != null) {
throw new IllegalStateException("Already building a query");
}
criteriaQuery = criteriaBuilder.createQuery(returnType);
}
public Object addEmployeeSet() {
Root root = criteriaQuery.from(Employee.class);
return root;
}
}
Note that if criteriaQuery is null (e.g. you haven’t called createQuery()), this will throw a null pointer exception; in practice you should probably test for this condition and throw a more meaningful exception.
If you want more control over your support for multiple-rooted queries, you could make the above createQuery() and addEmployeeSet() methods private and expose a public createEmployeeQuery() method that calls each of them in turn. Again, balance the flexibility your interface offers against the complexity of creating a non-JPA implementation according to your needs.
You might be able to get by with no more FROM-clause support than that; but more likely, you’ll also want to support some JPQL joins. In a lot of cases, SQL joins can be specified implicitly without an actual JPQL join (i.e. by using path expressions that navigate through them); but implicit joins are always inner joins, and also there are situations where a multi-valued path expression isn’t acceptable but a join variable is.
Suppose our Employee entity has a property named "manager", which is mapped as a relationship to another Employee. In JPQL you could write:
SELECT e, m
FROM Employee e JOIN manager m
The JOIN here will generate tuples, each consisting of an employee (e) paired with that employee’s manager (m). Assuming that multiple employees report to each manager, many tuples might have the same m instance. If the "manager" relationship were many-to-many (maybe you have eight bosses?), then you could get multiple tuples back for a single e instance.
Presumably at least one employee (the CEO, maybe) has no manager at all. You would get no record back with such an employee as the e instance, unless you specify an outer join:
SELECT e, m
FROM Employee e LEFT JOIN manager m
In that case, you get a record where e is the CEO and m is null.
To add a join to a CriteriaQuery, you call one of the join() methods on a From instance associated with the query. This call often needn’t refer to the actual CriteriaQuery object in any way, yet the JPQL query it represents is modified.
You specify the property that maps the relationship using either a metamodel element that represents it or the property name as a String. In this example, I just use Strings; you might be able to make better use of generics by getting the metamodel representation of the attribute. Optionally, you can pass JoinType.LEFT as a second parameter to specify an outer join.
The join() method returns a Join object. Like Root, Join extends From; so you can build joins from joins, use them as path expressions, etc.
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
private CriteriaQuery<?> criteriaQuery = null;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
public void createQuery(Class<?> returnType) {
if (criteriaQuery != null) {
throw new IllegalStateException("Already building a query");
}
criteriaQuery = criteriaBuilder.createQuery(returnType);
}
public Object addEmployeeSet() {
Root root = criteriaQuery.from(Employee.class);
return root;
}
public Object joinManagerFromEmployee(Object employeeSetToken) {
From employeeSet = (From)employeeSetToken;
Join managerSet = employeeSet.join("manager");
return join;
}
}
Again, a real implementation should check for various error conditions that would, in this example, result in pretty generic exceptions (e.g. employeeSetToken isn’t a From, or is a From for a type with no property named "manager") or in no exception at all (e.g. employeeSetToken is a From for a type other than Employee that happens to have a "manager" property, or it doesn’t belong to our query).
In this case, we’re assuming that if you join to manager, you’re not interested in employees with no manager.
Since we let the caller tell us which set of employees to use as the starting point for the join, we can easily handle cases where the query contains more than one From of type Employee. For example, since "manager" joins to another Employee and Join extends From, we could easily get the manager’s manager with a second call to the same method (passing in the value returned by the first call).
Step 3b: WHERE Clause
Now that you’ve specified some data to read in, you probably want to limit the set of results according to some logical conditions (beyond just the join conditions inferred from the entity mappings).
The CriteriaQuery has a where() method; you pass in one or more Predicate instances (each of which represents a boolean expression), and the query will only return records for which they all evaluate to true. (If for some reason you have an Expression<Boolean> that can’t be cast to Predicate, you can pass that to where(), but in this case you’ll only be allowed a single Expression.)
This may sound limiting. Multiple expressions passed to where() are always combined by AND; what if you need OR? Luckily, the CriteriaBuilder provides very flexible methods for building a single Predicate containing arbitrary logic; for example, you can pass multiple Predicate instances to its or() method to get a single Predicate representing their disjunction.
Predicate and Expression<Boolean> mean almost exactly the same thing. Predicate extends Expression<Boolean> and it doesn’t add very much; its main purpose is that, because it doesn’t directly use generics, it plays nicer with the varargs construct. For that reason, methods that know the expressions they return are Boolean-typed will tend to return Predicate references.
Suppose we want to allow for queries that return information about a particular employee (by employee number):
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
private CriteriaQuery<?> criteriaQuery = null;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
public void createQuery(Class<?> returnType) {
if (criteriaQuery != null) {
throw new IllegalStateException("Already building a query");
}
criteriaQuery = criteriaBuilder.createQuery(returnType);
}
public Object addEmployeeSet() {
Root root = criteriaQuery.from(Employee.class);
return root;
}
public Object joinManagerFromEmployee(Object employeeSetToken) {
From employeeSet = (From)employeeSetToken;
Join managerSet = employeeSet.join("manager");
return join;
}
public void limitEmployeeByEmployeeNumber(Object employeeSetToken,
Long employeeNumber) {
From employeeSet = (From)employeeSetToken;
Expression empNoProperty = employeeSet.get("empNo");
Predicate test = criteriaBuilder.equal(empNoProperty, employeeNumber);
Predicate whereClause = criteriaQuery.getRestriction();
if (whereclause != null) {
test = criteriaBuilder.and(whereClause, test);
}
criteriaQuery.where(test);
}
}
We could probably get better compile-time type safety using the metamodel and generics; in the above example, the compiler won’t know that empNoProperty is really an Expression<Long>, because we’ve only given it the property name (empNo) as a String.
Notice that after we build the Predicate representing our new condition, we check if there were already a where clause; if so, we build a single predicate representing the conjunction of the two. Another option would be to store the conditions as instance data separate from the query (say, in a List<Predicate >) until we’re preparing to execute the query, at which time we would call where() just once passing in all of the Predicates.
This method can operate on any set of employees; so we could ask about a specific employee’s manager
ExampleDao myDao = new ExampleDao();
myDao.createQuery(Long.class);
Object empToken = myDao.addEmployeeSet();
Object mgrToken = myDao.joinManagerFromEmployee(empToken);
myDao.limitEmployeeByEmployeeNumber(empToken, 12345);
... or we could ask about all employees with a given manager.
ExampleDao myDao = new ExampleDao();
myDao.createQuery(Long.class);
Object empToken = myDao.addEmployeeSet();
Object mgrToken = myDao.joinManagerFromEmployee(empToken);
myDao.limitEmployeeByEmployeeNumber(mgrToken, 12345);
Individual methods can be very flexible because they need only look at the part of the query they affect; a method generally doesn’t have to know about the overall query structure.
The CriteriaBuilder has numerous methods for building and composing Predicates and other Expressions. You can test for null, test for equality, test if one operand is greater than (or less than) another to which it’s comparable, trim strings, and so on. The basic mathematical operations are supported. You can perform aggregate calculations (such as COUNT), though you do have to follow the usual SQL-like rules for mixing the resulting Expressions with non-aggregates.
Basically you can build any valid JPQL query. (But again, remember: if you later want to get away from JPA, the more of this power you’ve used the harder the rewrite is going to be.) In fact, the interfaces may let you build queries that are not valid JPQL, though the EntityManager should then throw an exception when you try to convert the result to a TypedQuery; the apparent flexibility of the API can be deceptive. For example, the count() function takes an Expression, which could be just about anything, as its argument. You could build, say, a CASE expression; I’ve found count(CASE WHEN … END) to be a fairly useful idiom in SQL. Using Hibernate 3.5.6 as my provider, I can pass such an Expression to count() and it doesn’t complain. But as of JPA2, you can’t COUNT() any but a few types of expression. The EntityManager’s createQuery() method renders the query string you’d expect, and then it promptly throw an exception because this isn’t valid JPQL.
Step 4: Finish and Execute the Query
Your query still needs a SELECT clause. Depending on the operations you created in Step 3, it may need other finishing touches as well. For this example I’m lumping those steps into the execute method. (I like this approach because it avoids any confusion about the sequence of calls for steps that need to be done just once.)
Let’s suppose we want queries that return lists of employee ID numbers, which are NUMERIC in our data model and mapped as Long on our entity.
public class ExampleDAO {
@Resource(name="entityManager")
private EntityManager entityManager;
private CriteriaBuilder criteriaBuilder;
private CriteriaQuery<?> criteriaQuery = null;
public ExampleDAO() {
criteriaBuilder = entityManager.getCriteriaBuilder();
}
public void createQuery(Class<?> returnType) {
if (criteriaQuery != null) {
throw new IllegalStateException("Already building a query");
}
criteriaQuery = criteriaBuilder.createQuery(returnType);
}
public Object addEmployeeSet() {
Root root = criteriaQuery.from(Employee.class);
return root;
}
public Object joinManagerFromEmployee(Object employeeSetToken) {
From employeeSet = (From)employeeSetToken;
Join managerSet = employeeSet.join("manager");
return join;
}
public void limitEmployeeByEmployeeNumber(Object employeeSetToken,
Long employeeNumber) {
From employeeSet = (From)employeeSetToken;
Expression empNoProperty = employeeSet.get("empNo");
Predicate test = criteriaBuilder.equal(empNoProperty, employeeNumber);
Predicate whereClause = criteriaQuery.getRestriction();
if (whereclause != null) {
test = criteriaBuilder.and(whereClause, test);
}
criteriaQuery.where(test);
}
public List<Long> executeForEmployeeIds(Object employeeSetToken) {
From employeeSet = (From)employeeSetToken;
CriteriaQuery<Long> cq = (CriteriaQuery<Long>)criteriaQuery;
Expression<Long> empNoProperty =
(Expression<Long>)employeeSet.get("empNo");
cq.select(empNoProperty);
TypedQuery<Long> query = entityManager.createQuery(cq);
List<Long> ids = query.getResultList();
criteriaQuery = null;
return ids;
}
}
Recall that when we first created the query, we specified its return type. We then stored it using a reference with a wildcard type parameter. When we set the SELECT clause, we’re going to get specific about the return type again; so we do a (technically unsafe) cast; if the caller isn’t consistent, we could get class cast exceptions.
The select() method wants a Selection whose type matches the query return type; here we know that the empNo property is of type Long, and Expression extends Selection, so we’re fine.
You could also have called the query’s distinct() method to indicate that you don’t want to see duplicates. You could add an ORDER BY clause by passing one or more Order instances to the query’s orderBy() method. (You get Order instances from the CriteriaBuilder asc() and desc() methods.)
We pass the finished CriteriaQuery to createQuery(), and then we have a regular old JPQL TypedQuery object which we can execute in the usual way.
In this simple example, we haven’t provided enough functionality to build the type of query that really calls for this solution; but still we have enough to demonstrate a few basic queries. To get the ID of a given employee’s manager, our business layer could use the DAO as follows:
ExampleDao myDao = new ExampleDao();
myDao.createQuery(Long.class);
Object empToken = myDao.addEmployeeSet();
Object mgrToken = myDao.joinManagerFromEmployee(empToken);
myDao.limitEmployeeByEmployeeNumber(empToken, 12345);
List<Long> ids = myDao.executeForEmployeeIds(mgrToken);
Or to get the ID numbers of all employees who report to a given manager:
ExampleDao myDao = new ExampleDao();
myDao.createQuery(Long.class);
Object empToken = myDao.addEmployeeSet();
Object mgrToken = myDao.joinManagerFromEmployee(empToken);
myDao.limitEmployeeByEmployeeNumber(mgrToken, 12345);
List<Long> ids = myDao.executeForEmployeeIds(empToken);
What if you want more than one value back from each record? There are several options. JPA has a construct called a TupleElement, but if you use this as your return type then you aren’t fully hiding JPA inside your DAO. You can set the query up to return an array of Objects for each record. Or you can define a class whose constructor takes arguments corresponding to the query return values (possibly just storing them to be read via getter methods) and set the query up to return an instance of that class for each record. (See the documentation for the CriteriaBuilder construct() method for details.)
When working with multiple return values per record, don’t forget that if you mix aggregate expressions (e.g. those created with the count() method) with non-aggregate expressions, the non-aggregate expressions must be included in a GROUP BY clause.
And that’s about it. There’s much more detail, of course, but if you’ve got the above concepts down then it’s easy enough to find specific methods you might need in the JavaDoc.
A practical implementation should have much better error handling. It will probably be spread out over various DAO’s, each focusing on a different business object. It might have a more robust mechanism for keeping track of the various objects that represent parts of queries, and it might hide those objects more completely from the outside world (perhaps keeping them in private lists or maps and using indexes or key values to name them).
Also, you’ll probably find that you want to support both traditional CRUD methods and query-building methods. If I’m putting both on a single DAO, then I put a common prefix on all of the query-building methods’ names so they’ll stand out. An alternative would be to provide one DAO for CRUD operations and a separate one for query building. (Or one object with two interfaces, if you prefer.)
You might find that this approach works for 99% of your queries, but then you have the occasional request that can’t be expressed in JPQL. I haven’t found a really satisfying solution for this problem. It doesn’t really make sense to provide a second query-building mechanism, and even if you did you wouldn’t want the business layer to know enough details that it could tell you which to use. So for now, that’s my new threshold for when to punt and accept that a little hand-coded SQL, business logic and all, might still have to live inside the DAL.
But if I’ve made those intersections between the business logic and the persistence mechanism less common, and if I flag them with warning comments so we at least know where the few remaining headaches are going to be, then I can still probably get by with claiming a win.
– Mark Adelsberger
