This chapter explains query options that you may consider optional or advanced: transforming query results, filtering collections, and the Hibernate criteria query API. First, we discuss Hibernate’s ResultTransformer API, with which you can apply a result transformer to a query result to filter or marshal the result with your own code instead of Hibernate’s default behavior.
In previous chapters, we always advised you to be careful when mapping collections, because it’s rarely worth the effort. In this chapter, we introduce collection filters, a native Hibernate feature that makes persistent collections more valuable. Finally, we look at another proprietary Hibernate feature, the org.hibernate.Criteria query API, and some situations when you might prefer it to the standard JPA query-by-criteria.
Let’s start with the transformation of query results.
You can apply a result transformer to a query result so that you can filter or marshal the result with your own procedure instead of the Hibernate default behavior. Hibernate’s default behavior provides a set of default transformers that you can replace and/or customize.
The result you’re going to transform is that of a simple query, but you need to access the native Hibernate API org.hibernate.Query through the Session, as shown in the following listing.
Session session = em.unwrap(Session.class); org.hibernate.Query query = session.createQuery( "select i.id as itemId, i.name as name, i.auctionEnd as auctionEnd from Item i" );
Without any custom result transformation, this query returns a List of Object[]:
List<Object[]> result = query.list(); <enter/> for (Object[] tuple : result) { Long itemId = (Long) tuple[0]; String name = (String) tuple[1]; Date auctionEnd = (Date) tuple[2]; // ... }
Each object array is a “row” of the query result. Each element of that tuple can be accessed by index: here index 0 is a Long, index 1 a String, and index 2 a Date.
The first result transformer we introduce instead returns a List of Lists.
All the mples in this section are for queries written in JPQL created with the org.hibernate.Query API. If you write a JPA CriteriaQuery using a Criteria-Builder, you can’t apply a Hibernate org.hibernate.transform.Result-Transformer: this is a Hibernate-only interface. Even if you obtain the native API for your criteria query (through HibernateQuery casting, as shown in section 14.1.3), you can’t set a custom transformer. For JPA CriteriaQuery, Hibernate applies a built-in transformer to implement the JPA contracts; using a custom transformer would override this and cause problems. You can set a custom transformer for JPQL queries created with javax.persistence.Query, though, after obtaining the native API with HibernateQuery. In addition, later in this chapter, you see the native org.hibernate.Criteria API, an alternative query-by-criteria facility that supports overriding the org.hibernate.transform.ResultTransformer.
Let’s say you want to use indexed access but are unhappy with the Object[] result. Instead of a list of Object[]s, each tuple can also be represented as a List, using the ToListResultTransformer:
query.setResultTransformer( ToListResultTransformer.INSTANCE ); <enter/> List<List> result = query.list(); <enter/> for (List list : result) { Long itemId = (Long) list.get(0); String name = (String) list.get(1); Date auctionEnd = (Date) list.get(2); // ... }
This is a minor difference but a convenient alternative if code in other layers of your application already works with lists of lists.
The next transformer converts the query result to a Map for each tuple, where the query projection assigns aliases mapped to the projection elements.
The AliasToEntityMapResultTransformer returns a List of java.util.Map, one map per “row.” The aliases in the query are itemId, name, and auctionEnd:
If you don’t know the aliases used in the query and need to obtain them dynamically, call org.hibernate.Query#getReturnAliases().
The example query returns scalar values; you may also want to transform results that contain persistent entity instances. This example uses aliases for projected entities and a List of Maps:
org.hibernate.Query entityQuery = session.createQuery( "select i as item, u as seller from Item i join i.seller u" ); <enter/> entityQuery.setResultTransformer( AliasToEntityMapResultTransformer.INSTANCE ); <enter/> List<Map> result = entityQuery.list(); <enter/> for (Map map : result) { Item item = (Item) map.get("item"); User seller = (User) map.get("seller"); <enter/> assertEquals(item.getSeller(), seller); // ... }
More useful is the next transformer, mapping the query result to JavaBean properties by alias.
In section 15.3.2, we showed how a query can return instances of a JavaBean dynamically by calling the ItemSummary constructor. In JPQL, you achieve this with the new operator. For criteria queries, you use the construct() method. The ItemSummary class must have a constructor that matches the projected query result.
Alternatively, if your JavaBean doesn’t have the right constructor, you can still instantiate and populate its values through setters and/or fields with the AliasToBeanResultTransformer. The following example transforms the query result shown in listing 16.1:
query.setResultTransformer( new AliasToBeanResultTransformer(ItemSummary.class) ); <enter/> List<ItemSummary> result = query.list(); <enter/> for (ItemSummary itemSummary : result) { Long itemId = itemSummary.getItemId(); String name = itemSummary.getName(); Date auctionEnd = itemSummary.getAuctionEnd(); // ... }
You create the transformer with the JavaBean class you want to instantiate, here ItemSummary. Hibernate requires that this class either has no constructor or a public no-argument constructor.
When transforming the query result, Hibernate looks for setter methods and fields with the same names as the aliases in the query. The ItemSummary class must either have the fields itemId, name, andauctionEnd, or the setter methods setItemId(), setName(), and setAuctionEnd(). The fields or setter method parameters must be of the right type. If you have fields that map to some query aliases and setter methods for the rest, that’s fine too.
You should also know how to write your own ResultTransformer when none of the built-in ones suits you.
The built-in transformers in Hibernate aren’t sophisticated; there isn’t much difference between result tuples represented as lists, maps, or object arrays. Implementing the ResultTransformer interface is trivial, though, and custom conversion of query results can tighten the integration between the layers of code in your application. If your user interface code already knows how to render a table of List<ItemSummary>, let Hibernate return it directly from a query.
Next, we show you how to implement a ResultTransformer. Let’s assume that you want a List<ItemSummary> returned from the query shown in listing 16.1, but you can’t let Hibernate create an instance of ItemSummary through reflection on a constructor. Maybe your ItemSummary class is predefined and doesn’t have the right constructor, fields, and setter methods. Instead, you have an ItemSummaryFactory to produce instances of ItemSummary.
The ResultTransformer interface requires that you implement the methods transformTuple() and transformList():
As you can see in the example, you transform query results in two steps: first you customize how to convert each “row” or tuple of the query result to whatever value you desire. Then you work on the entire List of these values, wrapping or converting again.
Next, we discuss another convenient Hibernate feature (where JPA doesn’t have an equivalent): collection filters.
In chapter 7, you saw reasons you should (or rather, shouldn’t) map a collection in your Java domain model. The main benefit of a collection mapping is easier access to data: you can call item.getImages() or item.getBids() to access all images and bids associated with an Item. You don’t have to write a JPQL or criteria query; Hibernate will execute the query for you when you start iterating through the collection elements.
The most obvious problem with this automatic data access is that Hibernate will always write the same query, retrieving all images or bids for an Item. You can customize the order of collection elements, but even that is a static mapping. What would you do to render two lists of bids for an Item, in ascending and descending order by creation date? You could go back to writing and executing custom queries and not call item.getBids(); the collection mapping might not even be necessary.
Instead, you can use a Hibernate proprietary feature, collection filters, that makes writing these queries easier, using the mapped collection. Let’s say you have a persistent Item instance in memory, probably loaded with the EntityManager API. You want to list all bids made for this Item but further restrict the result to bids made by a particular User. You also want the list sorted in descending order by Bid#amount.
Item item = em.find(Item.class, ITEM_ID); User user = em.find(User.class, USER_ID); <enter/> org.hibernate.Query query = session.createFilter( item.getBids(), "where this.bidder = :bidder order by this.amount desc" ); <enter/> query.setParameter("bidder", user); List<Bid> bids = query.list();
The session.createFilter() method accepts a persistent collection and a JPQL query fragment. This query fragment doesn’t require a select or from clause; here it only has a restriction with the where clause and an order by clause. The alias this always refers to elements of the collection, here Bid instances. The filter created is an ordinary org.hibernate.Query, prepared with a bound parameter and executed with list(), as usual.
Hibernate doesn’t execute collection filters in memory. The Item#bids collection may be uninitialized when you call the filter and, and if so, remains uninitialized. Furthermore, filters don’t apply to transient collections or query results. You may only apply them to a mapped persistent collection currently referenced by an entity instance managed by the persistence context. The term filter is somewhat misleading, because the result of filtering is a completely new and different collection; the original collection isn’t touched.
To the great surprise of everyone, including the designer of this feature, even trivial filters turn out to be useful. For example, you can use an empty query to paginate collection elements:
Here, Hibernate executes the query, loading the collection elements and limiting the returned rows to two, starting with row zero of the result. Usually, you’d use an order by with paginated queries.
You don’t need a from clause in a collection filter, but you can have one if that’s your style. A collection filter doesn’t even need to return elements of the collection being filtered.
This next filter returns any Item sold by any of the bidders:
Item item = em.find(Item.class, ITEM_ID); <enter/> org.hibernate.Query query = session.createFilter( item.getBids(), "from Item i where i.seller = this.bidder" ); <enter/> List<Item> items = query.list();
With a select clause, you can declare a projection. The following filter retrieves the names of users who made the bids:
Item item = em.find(Item.class, ITEM_ID); <enter/> org.hibernate.Query query = session.createFilter( item.getBids(), "select distinct this.bidder.username order by this.bidder.username asc" ); <enter/> List<String> bidders = query.list();
All this is a lot of fun, but the most important reason for the existence of collection filters is to allow your application to retrieve collection elements without initializing the entire collection. For large collections, this is important to achieve acceptable performance. The following query retrieves all bids made for the Item with an amount greater or equal to 100:
Item item = em.find(Item.class, ITEM_ID); <enter/> org.hibernate.Query query = session.createFilter( item.getBids(), "where this.amount >= :param" ); <enter/> query.setParameter("param", new BigDecimal(100)); List<Bid> bids = query.list();
Again, this doesn’t initialize the Item#bids collection but returns a new collection.
Before JPA 2, query-by-criteria was only available as a proprietary Hibernate API. Today, the standardized JPA interfaces are equally as powerful as the old org.hibernate.Criteria API, so you’ll rarely need it. But several features are still only available in the Hibernate API, such as query-by-example and embedding of arbitrary SQL fragments. In the following section, you find a short overview of the org.hibernate.Criteria API and some of its unique options.
Using the org.hibernate.Criteria and org.hibernate.Example interfaces, you can build queries programmatically by creating and combining org.hibernate.criterion.* instances. You see how to use these APIs and how to express selection, restriction, joins, and projection. We assume that you’ve read the previous chapter and that you know how these operations are translated into SQL. All query examples shown here have an equivalent JPQL or JPA criteria example in the previous chapter, so you can easily flip back and forth if you need to compare all three APIs.
Let’s start with some basic selection examples.
The following query loads all Item instances:
org.hibernate.Criteria criteria = session.createCriteria(Item.class); List<Item> items = criteria.list();
You create an org.hibernate.Criteria using the Session. Alternatively, you can create a DetachedCriteria without an open persistence context:
DetachedCriteria criteria = DetachedCriteria.forClass(Item.class); <enter/> List<Item> items = criteria.getExecutableCriteria(session).list();
When you’re ready to execute the query, “attach” it to a Session with getExecutableCriteria().
Note that this is a unique feature of the Hibernate criteria API. With JPA, you always need at least an EntityManagerFactory to get a CriteriaBuilder.
You can declare the order of the results, equivalent to an order by clause in JPQL. The following query loads all User instances sorted in ascending order by first and last name:
List<User> users = session.createCriteria(User.class) .addOrder(Order.asc("firstname")) .addOrder(Order.asc("lastname")) .list();
In this example, the code is written in the fluent style (using method chaining); methods such as addOrder() return the original org.hibernate.Criteria.
Next, we look at restricting the selected records.
The following query returns all Item instances with name “Foo”:
List<Item> items = session.createCriteria(Item.class) .add(Restrictions.eq("name", "Foo")) .list();
The Restrictions interface is the factory for individual Criterion you can add to the Criteria. Attributes are addressed with simple strings, here Item#name with “name”.
You can also match substrings, similar to the like operator in JPQL. The following query loads all User instances with username starting with “j” or “J”:
List<User> users = session.createCriteria(User.class) .add(Restrictions.like("username", "j", MatchMode.START).ignoreCase()) .list();
MatchMode.START is equivalent to the wildcard j% in JPQL. The other modes are EXACT, END, and ANYWHERE.
You can name nested attributes of embeddable types, such as the Address of a User, using dot-notation
List<User> users = session.createCriteria(User.class) .add(Restrictions.eq("homeAddress.city", "Some City")) .list();
A unique feature of the Hibernate Criteria API is the ability to write plain SQL fragments in restrictions. This query loads all User instances with a username shorter than eight characters:
List<User> users = session.createCriteria(User.class) .add(Restrictions.sqlRestriction( "length({alias}.USERNAME) < ?", 8, StandardBasicTypes.INTEGER )).list();
Hibernate sends the SQL fragment to the database as is. You need the {alias} placeholder to prefix any table alias in the final SQL; it always refers to the table the root entity is mapped to (USERS, in this case). You also apply a position parameter (named parameters aren’t supported by this API) and specify its type as StandardBasicTypes.INTEGER.
The Hibernate criteria query system is extensible: you could wrap the LENGTH() SQL function in your own implementation of the org.hibernate.criterion.Criterion interface.
After you perform selection and restriction, you want to add projection to your query to declare the data you want to retrieve.
The following query returns the identifier value, the username, and the homeAddress of all User entities:
List<Object[]> result = session.createCriteria(User.class) .setProjection(Projections.projectionList() .add(Projections.property("id")) .add(Projections.property("username")) .add(Projections.property("homeAddress")) ).list();
The result of this query is a List of Object[], one array for each tuple. Each array contains a Long (or whatever the type of the user’s identifier is), a String, and an Address.
Just as with restrictions, you can add arbitrary SQL expressions and function calls to projections:
List<String> result = session.createCriteria(Item.class) .setProjection(Projections.projectionList() .add(Projections.sqlProjection( "NAME || ':' || AUCTIONEND as RESULT", new String[]{"RESULT"}, new Type[]{StandardBasicTypes.STRING} )) ).list();
This query returns a List of Strings, where strings have the form “[Item name]:[Auction end date]”. The second parameter for the projection is the name of the alias(es) you used in the query: Hibernate needs this to read the value of the ResultSet. The type of each projected element/alias is also needed: here, StandardBasicTypes.STRING.
Hibernate supports grouping and aggregation. This query counts users’ last names:
List<Object[]> result = session.createCriteria(User.class) .setProjection(Projections.projectionList() .add(Projections.groupProperty("lastname")) .add(Projections.rowCount()) ).list();
The rowCount() method is equivalent to a count() function call in JPQL. The following aggregation query returns average Bid amounts grouped by Item:
List<Object[]> result = session.createCriteria(Bid.class) .setProjection(Projections.projectionList() .add(Projections.groupProperty("item")) .add(Projections.avg("amount")) ).list();
Next, you see that joins are also available with the Criteria API.
You express inner joins of an associated entity with nested Criterias:
This query returns all Bid instances of any Item sold by User “johndoe” that doesn’t have a buyNowPrice. The first inner join of the Bid#item association is made with createCriteria("item") on the root Criteria of the Bid. This nested Criteria now represents the association path, on which another inner join is made with createCriteria("seller"). Further restrictions are placed on each join Criteria; they will be combined with logical and in the where clause of the final SQL query.
Alternatively, inner joins can be expressed with createAlias() on a Criteria. This is the same query:
Dynamic eager fetching with outer joins are declared with setFetchMode():
List<Item> result = session.createCriteria(Item.class) .setFetchMode("bids", FetchMode.JOIN) .list();
This query returns all Item instances with their bids collection initialized in the same SQL query.
As with JPQL and JPA criteria queries, Hibernate may return duplicate Item references! See our previous discussion in section 15.4.5.
Similar to JPQL and JPA criteria, Hibernate can filter out duplicate references in-memory with a “distinct” operation:
List<Item> result = session.createCriteria(Item.class) .setFetchMode("bids", FetchMode.JOIN) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list();
Here, you can also see that a ResultTransformer, as discussed earlier in this chapter, can be applied to a Criteria.
You can fetch multiple associations/collections in one query:
List<Item> result = session.createCriteria(Item.class) .createAlias("bids", "b", JoinType.LEFT_OUTER_JOIN) .setFetchMode("b", FetchMode.JOIN) .createAlias("b.bidder", "bdr", JoinType.INNER_JOIN) .setFetchMode("bdr", FetchMode.JOIN) .createAlias("seller", "s", JoinType.LEFT_OUTER_JOIN) .setFetchMode("s", FetchMode.JOIN) .list();
This query returns all Item instances, loads the Item#bids collection with an outer join, and loads Bid#bidder with an inner join. The Item#seller is also loaded: because it can’t be null, it doesn’t matter whether an inner or outer join is used. As always, don’t fetch several collections in one query, or you’ll create a Cartesian product (see section 15.4.5).
Next, you see that subqueries with criteria also work with nested Criteria instances.
The following subquery returns all User instances who are selling more than one item:
DetachedCriteria sq = DetachedCriteria.forClass(Item.class, "i"); sq.add(Restrictions.eqProperty("i.seller.id", "u.id")); sq.setProjection(Projections.rowCount()); <enter/> List<User> result = session.createCriteria(User.class, "u") .add(Subqueries.lt(1l, sq)) .list();
The DetachedCriteria is a query that returns the number of items sold restricted by a given User. The restriction relies on the alias u, so this is a correlated subquery. The “outer” query then embeds the DetachedCriteria and provides the alias u. Note that the subquery is the right operand of the lt() (less than) operation, which translates into 1 < ([Result of count query]) in SQL.
Hibernate also supports quantification. This query returns items where all bids are less or equal than 10:
DetachedCriteria sq = DetachedCriteria.forClass(Bid.class, "b"); sq.add(Restrictions.eqProperty("b.item.id", "i.id")); sq.setProjection(Projections.property("amount")); <enter/> List<Item> result = session.createCriteria(Item.class, "i") .add(Subqueries.geAll(new BigDecimal(10), sq)) .list();
Again, the position of the operands dictates that the comparison is based on geAll() (greater or equal than all) to find the bids with “less or equal than 10” amount.
So far, there are a few good reasons to use the old org.hibernate.Criteria API. You really should use the standardized JPA query languages in new applications, though. The most interesting features of the old proprietary API we’ve shown are embedded SQL expressions in restrictions and projections. Another Hibernate-only feature you may find interesting is query-by-example.
The idea behind example queries is that you provide an example entity instance, and Hibernate loads all entity instances that “look like the example.” This can be convenient if you have a complex search screen in your user interface, because you don’t have to write extra classes to hold the entered search terms.
Let’s say you have a search form in your application where you can search for User instances by last name. You can bind the form field for “last name” directly to the User#lastname property and then tell Hibernate to load “similar” User instances:
Because you’ve written the User entity class following JavaBean rules, binding it to a UI form should be trivial. It has regular getter and setter methods, and you can create an “empty” instance with the public no-argument constructor (remember our discussion of constructor design in section 3.2.3.)
One obvious disadvantage of the Example API is that any string-matching options, such as ignoreCase() and enableLike(), apply to all string-valued properties of the template. If you searched for both lastname and firstname, both would be case-insensitive substring matches.
By default, all non-null valued properties of the given entity template are added to the restriction of the example query. As shown in the last code snippet, you can manually exclude properties of the entity template by name with excludeProperty(). Other exclusion options are exclusion of zero-valued properties (such as int or long) with excludeZeroes() and disabling exclusion altogether with excludeNone(). If no properties are excluded, any null property of the template is added to the restriction in the SQL query with an is null check.
If you need more control over exclusion and inclusion of properties, you can extend Example and write your own PropertySelector:
class ExcludeBooleanExample extends Example { ExcludeBooleanExample(Object template) { super(template, new PropertySelector() { @Override public boolean include(Object propertyValue, String propertyName, Type type) { return propertyValue != null && !type.equals(StandardBasicTypes.BOOLEAN); } }); } }
This selector excludes any null properties (like the default selector) and additional excludes any Boolean properties (such as User#activated).
After adding an Example restriction to a Criteria, you can add further restrictions to the query. Alternatively, you can add multiple example restrictions to a single query. The following query returns all Item instances with names starting with “B” or “b” and a seller matching a User example:
Item itemTemplate = new Item(); itemTemplate.setName("B"); <enter/> Example exampleItem = Example.create(itemTemplate); exampleItem.ignoreCase(); exampleItem.enableLike(MatchMode.START); exampleItem.excludeProperty("auctionType"); exampleItem.excludeProperty("createdOn"); <enter/> User userTemplate = new User(); userTemplate.setLastname("Doe"); <enter/> Example exampleUser = Example.create(userTemplate); exampleUser.excludeProperty("activated"); <enter/> List<Item> items = session .createCriteria(Item.class) .add(exampleItem) .createCriteria("seller").add(exampleUser) .list();
At this point, we invite you to take a step back and consider how much code would be required to implement such a search using hand-coded SQL/JDBC.
3.138.67.27