Suppose you have a SQL-statement where you select custom fields and or the result of some functions. To make it more interesting, we want to combine this with a group-by statement and an ordering. Let’s us for example say you want to execute the following query in MySQL:
SELECT uuid, GROUP_CONCAT(field1, ':', field2),
MIN(ss_date_started),
MAX(ss_date_ended)
FROM my_table
WHERE key = 'xxx'
GROUP BY group_key
ORDER BY id ASC
The functions GROUP_CONCAT, MIN and MAX are availble in MySQL. The intention of the GROUP_CONCAT is to get some information out of all the group-rows with issuing additional selects. The question is how we can create the criteria for this query and how we can retrieve the results.
We want the results to be stored in the following object:
public class MyGroup implements Serializable {
private String uuid;
private Map map = new HashMap();
private Date dateStarted;
private Date dateEnded;
public MyGroup() {
}
}
In the map we want to store the GROUP_CONCAT strings as a map to avoid other SELECTs. First of all, we get the CriteriaBuilder and the root of the query. The MyEntityForTheTable is the entity which is mapped to the table. This object will act as the base to map our fields.
final CriteriaBuilder cb =
this.getEntityManager().getCriteriaBuilder();
final CriteriaQuery cq = ...; // we'll talk about this later
final Root root =
cq.from(MyEntityForTheTable.class);
The WHERE clause is pretty simple:
cq.where(cb.equal(root.get(MyEntityForTheTable_.key), key));
The GROUP-BY and the ORDER-BY is even more simple:
cq.groupBy(root.get(MyEntityForTheTable_.groupKey)).
orderBy(cb.asc(root.get(MyEntityForTheTable_.id)));
Now we need to construct the function calls. We extract the MIN and MAX date out of the group using by writing the following expressions:
cb.function("MIN", Date.class, root.get(MyEntityForTheTable_.dateStarted));
cb.function("MAX", Date.class,
root.get(MyEntityForTheTable_.dateEnded))
We start with the GROUP_CONCAT in MySQL which can be written as:
cb.function("GROUP_CONCAT", byte[].class,
root.get(MyEntityForTheTable_.field1),
cb.literal(":"),
root.get(MyEntityForTheTable_.field2))
First of all, we cannot take String.class to read out the results. MySQL returns the string as byte-code, so we need to map it to a byte[]. The following arguments are the parameters for the function. The first and the last is the result of the columns. We also want to have a semicolon between the two values which can be achieved by cb.literal(String) function.
Now we need to execute the queries and map it to an object. We can use the construct() method to instantiate a new object inside our query. Unfortunately, our main domain object does not have the proper constructor. So we wrap the POJO class in a new class and add the specific constructor.
public static class WrappedGroup extends MyGroup {
public WrappedGroup() { }
public WrappedGroup(
final String uuid,
final byte[] serviceStatus,
final Date dateStarted,
final Date dateEnded) { }
}
We make sure that this constructor has the byte[] parameter. In this constructor we can convert the byte-array to a string. The you can convert the string to a map of your choice. So, we are almost done. Here is the complete code:
final CriteriaBuilder cb =
this.getEntityManager().getCriteriaBuilder();
final CriteriaQuery cq =
cb.createQuery(WrappedGroup.class);
final Root root =
cq.from(MyEntityForTheTable.class);
And the select part looks like;
cq.select(cb.construct(WrappedGroup.class,
root.get(MyEntityForTheTable_.uuid),
cb.function("GROUP_CONCAT", byte[].class,
root.get(MyEntityForTheTable_.field1),
cb.literal(":"),
root.get(MyEntityForTheTable_.field2)),
cb.function("MIN", Date.class,
root.get(MyEntityForTheTable_.dateStarted)),
cb.function("MAX", Date.class,
root.get(MyEntityForTheTable_.dateEnded))));
We then use a TypedQuery to get the results, and we are done:
final TypedQuery query =
this.getEntityManager().createQuery(cq).
setFirstResult(startResult).
setMaxResults(maxResults);
The resulting list can be cast to List.
Et voilá, we have a very dynamic query which we can easily refactor and adapt.
Brain/Drain/Pain 