Hibernate: A Developer's Notebook

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.1.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(" ...