O'Reilly logo

Hibernate: A Developer's Notebook by James Elliott

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required