| Especially when writing reports , you'll often want summary information from the database: "How many? What's the average? The longest?" HQL can help with this, by offering aggregate functions like those in SQL. In HQL, of course, these functions apply to the properties of persistent classes. 9.4.1 How do I do that? Let's try some of this in our query test framework. First, add the query in Example 9-15 after the existing query in Track.hbm.xml . Example 9-15. A query collecting aggregate information about tracks <query name="com.oreilly.hh.trackSummary"> <![CDATA[ select count(*), min(track.playTime), max(track.playTime) from com.oreilly.hh.Track as track ]]> </query>
I was tempted to try asking for the average playing time as well, but unfortunately HSQLDB doesn't know how to calculate averages for nonnumeric values, and this property is stored in a column of type date . Next we need to write a method to run this query and display the results. Add the code in Example 9-16 to QueryTest.java , after the tracksNoLongerThan() method. Example 9-16. A method to run the trackSummary query /** * Print summary information about all tracks. * * @param session the Hibernate session that can retrieve data. * @throws HibernateException if there is a problem. **/ public static void printTrackSummary(Session session) throws HibernateException { Query query = session.getNamedQuery("com.oreilly.hh.trackSummary"); Object[] results = (Object[])query.uniqueResult(); System.out.println("Summary information:"); System.out.println(" Total tracks: " + results[0]); System.out.println(" Shortest track: " + results[1]); System.out.println(" Longest track: " + results[2]); }
Since we're only using aggregate functions in the query, we know we'll only get one row of results back. This is another opportunity to use the uniqueResult() convenience method offered by the Query interface. It saves us the trouble of getting back a list and extracting the first element. As discussed in the "Selecting Properties and Pieces" section above, since we've asked for multiple distinct values, that result will be an Object array, whose elements are the values we requested in the same order we put them in the query. We also need to add a line to main() to call this method. We can put it after the end of the loop in which we print details about selected tracks, as shown in Example 9-17. Example 9-17. Addition to main() in QueryTest.java to display the new summary information ... System.out.println("Track: " + aTitle + " [ID=" + anID + ']'); } printTrackSummary(session); } finally { // No matter what, close the session ...
With these additions, we get new output when running ant qtest (Example 9-18). Example 9-18. The summary output ... qtest: [java] Track: Video Killed the Radio Star [ID=1] [java] Track: Test Tone 1 [ID=6] [java] Track: Russian Trance [ID=0] [java] Track: Never Turn Your Back on Mother Earth [ID=11] [java] Track: Motherless Child [ID=12] [java] Track: In a Manner of Speaking [ID=8] [java] Track: Gone [ID=10] [java] Summary information: [java] Total tracks: 13 [java] Shortest track: 00:00:10 [java] Longest track: 00:07:39
That was pretty easy. Let's try something trickier ”pulling information from joined tables. Tracks have a collection of artists associated with them. Suppose we want to get summary information about the tracks associated with a particular artist, rather than for all tracks. Example 9-19 shows what we'd add to the query. Example 9-19. Summarizing tracks associated with an artist <query name="com.oreilly.hh.trackSummary"> <![CDATA[ select count(*), min(track.playTime), max(track.playTime) from com.oreilly.hh.Track as track where :artist in elements(track.artists) ]]> </query>
We've added a where clause to narrow down the tracks we want to see, using a named parameter, artist . HQL provides another use for the in operator. While you can use it in the normal SQL sense to give a list of possible values for a property, you can also do what we've done here. This statement tells Hibernate we are interested in tracks whose artists collection contains a specified value. To call this version of the query, beef up printTrackSummary() a little, as shown in Example 9-20. Example 9-20. Enhancing printTrackSummary() to work with a specific artist /** * Print summary information about tracks associated with an artist. * * @param artist the artist in whose tracks we're interested * @param session the Hibernate session that can retrieve data. * @throws HibernateException if there is a problem. **/ public static void printTrackSummary( Artist artist , Session session) throws HibernateException { Query query = session.getNamedQuery("com.oreilly.hh.trackSummary"); query.setParameter("artist", artist); Object[] results = (Object[])query.uniqueResult(); System.out.println("Summary of tracks by " + artist.getName() + ':' ); System.out.println(" Total tracks: " + results[0]); System.out.println(" Shortest track: " + results[1]); System.out.println(" Longest track: " + results[2]); }
Wasn't much to that, was there? Finally, the line that calls this method needs another parameter to specify an artist. Use the handy getArtist() method in CreateTest.java once again. Change the method call in QueryTest.java 's main() method to look like it does in Example 9-21. Example 9-21. Calling the enhanced printTrackSummary() ... System.out.println("Track: " + aTitle + " [ID=" + anID + ']'); } printTrackSummary( CreateTest.getArtist("Samuel Barber", false, session), session); } finally { // No matter what, close the session ...
Now when you run ant qtest you'll see information that looks like Example 9-22. Example 9-22. Running the summary query for tracks by Samuel Barber qtest: [java] Track: Video Killed the Radio Star [ID=1] [java] Track: Test Tone 1 [ID=6] [java] Track: Russian Trance [ID=0] [java] Track: Never Turn Your Back on Mother Earth [ID=11] [java] Track: Motherless Child [ID=12] [java] Track: In a Manner of Speaking [ID=8] [java] Track: Gone [ID=10] [java] Summary of tracks by Samuel Barber: [java] Total tracks: 2 [java] Shortest track: 00:06:35 [java] Longest track: 00:07:39
9.4.2 What just happened ? This took so little effort that it's worth taking a minute to appreciate how much Hibernate actually did for us. The getArtist() method we called returned the Artist instance corresponding to Samuel Barber. We were able to pass this entire object as a named parameter to our HQL query, and Hibernate knows enough about how to put together join queries using the Artist 's id property and the TRACK_ARTISTS table to implement the complicated condition we expressed so concisely in Example 9-19. The results we got reflect the two remixes of "Adagio for Strings" in the sample data. They don't show up in the detailed track list because they're both longer than five minutes. NOTE Just try doing something like that with vanilla SQL! | 