| So far we've been looking at the properties of a single class in forming our criteria. Of course, in our real systems, we've got a rich set of associations between objects, and sometimes the details we want to use to filter our results come from these associations. Fortunately, the criteria query API provides a straightforward way of performing such searches. 8.3.1 How do I do that? Let's suppose we're interested in finding all the tracks associated with particular artists . We'd want our criteria to look at the values contained in each Track's artists property, which is a collection of associations to Artist objects. Just to make it a bit more fun, let's say we want to be able to find tracks associated with artists whose name property matches a particular SQL string pattern. Let's add a new method to QueryTest.java to implement this. Add the method shown in Example 8-13 after the end of the tracksNoLongerThan() method. Example 8-13. Filtering tracks based on their artist associations 1 /** 2 * Retrieve any tracks associated with artists whose name matches a 3 * SQL string pattern. 4 * 5 * @param namePattern the pattern which an artist's name must match 6 * @param session the Hibernate session that can retrieve data. 7 * @return a list of {@link Track}s meeting the artist name restriction. 8 * @throws HibernateException if there is a problem. 9 */ 10 public static List tracksWithArtistLike(String namePattern, Session session) 11 throws HibernateException 12 { 13 Criteria criteria = session.createCriteria(Track.class); 14 Criteria artistCriteria = criteria.createCriteria("artists"); 15 artistCriteria.add(Expression.like("name", namePattern)); 16 criteria.addOrder(Order.asc("title")); 17 return criteria.list(); 18 }
Line 14 creates a second Criteria instance, attached to the one we're using to select tracks, by following the tracks' artists property. We can add constraints to either criteria (which would apply to the properties of the Track itself), or to artistCriteria , which causes them to apply to the properties of the Artist entities associated with the track. In this case, we are only interested in features of the artists, so line 15 restricts our results to tracks associated with at least one artist whose name matches the specified pattern. (By applying constraints to both Criteria , we could restrict by both Track and Artist properties.) Line 16 requests sorting on the tracks we get back, so we'll see the results in alphabetical order. In the current implementation of criteria queries, you can only sort the outermost criteria, not the subcriteria you create for associations. If you try, you'll be rewarded with an UnsupportedOperationException. NOTE At least its error message is helpful and descriptive! To see all this in action, we need to make one more change. Modify the main() method so that it invokes this new query, as shown in Example 8-14. Example 8-14. Calling the new track artist name query ... // Ask for a session using the JDBC information we've configured Session session = sessionFactory.openSession(); try { // Print tracks associated with an artist whose name ends with "n" List tracks = tracksWithArtistLike("%n", session); for (ListIterator iter = tracks.listIterator() ; ...
Running ant qtest now gives the results shown in Example 8-15. Example 8-15. Tracks associated with an artist whose name ends with the letter n qtest: [java] Track: "Adagio for Strings (Ferry Corsten Remix)" (Ferry Corsten, William Orbit, Samuel Barber) 00:06:35, from Compact Disc [java] Track: "Gravity's Angel" (Laurie Anderson) 00:06:06, from Compact Disc [java] Track: "The World '99" (Ferry Corsten, Pulp Victim) 00:07:05, from Digital Audio Stream
8.3.2 What just happened ? If you look at the lists of artists for each of the three tracks that were found, you'll see that at least one of them has a name ending in 'n' as we requested . Also notice that we have access to all the artists associated with each track, not just the ones that matched the name criterion. This is what you'd expect and want, given that we've retrieved the actual Track entities. You can run criteria queries in a different mode, by calling setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP) , which causes it to return a list of hierarchical Map s in which the criteria at each level have filtered the results. This goes beyond the scope of this notebook, but there are some examples of it in the reference and API documentation. NOTE You can also create aliases for the associations you're working with, and use those aliases in expressions. This starts getting complex but it's useful. Explore it someday.  | If the table from which you're fetching objects might contain duplicate entries, you can achieve the equivalent of SQL's 'select distinct' by calling setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) on your criteria. | |
| 