9.4 Working with Aggregate Values
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!
|
