9.2 Selecting Properties and Pieces

The queries we've been using so far have returned entire persistent objects. This is the most common use of an object/relational mapping service like Hibernate, so it should come as no surprise. Once you've got the objects, you can use them in whatever way you need to within the familiar realm of Java code. There are circumstances where you might want only a subset of the properties that make up an object, though, such as producing reports . HQL can accommodate such needs, in exactly the same way you'd use ordinary SQL ”projection in a select clause.

9.2.1 How do I do that?

Suppose we want to change QueryTest.java to display only the titles of the tracks that meet our search criteria, and we want to extract only that information from the database in the first place. We'd start by changing the query of Example 3-9 to retrieve only the title property. Edit Track.hbm.xml to make the query look like Example 9-6.

Example 9-6. Obtaining just the titles of the short tracks

<query name="com.oreilly.hh.tracksNoLongerThan">

  <![CDATA[

select track.title

from com.oreilly.hh.Track as track

      where track.playTime <= :length

    ]]>

</query>

Make sure the tracksNoLongerThan() method in QueryTest.java is set up to use this query. (If you edited it to use criteria queries in Chapter 8, change it back to the way it was in Example 3-10. To save you the trouble of hunting that down, it's reproduced as Example 9-7.)

Example 9-7. HQL-driven query method, using the query mapped in Example 9-6

public static List tracksNoLongerThan(Time length, Session session)

    throws HibernateException

{

    Query query = session.getNamedQuery(

                      "com.oreilly.hh.tracksNoLongerThan");

    query.setTime("length", length);

    return query.list();

}

Finally, the main() method needs to be updated, as shown in Example 9-8, to reflect the fact that the query method is now returning the title property rather than entire Track records. This property is defined as a String , so the method now returns a List of String s.

Example 9-8. Changes to QueryTest's main() method to work with the title query

// Print the

titles of

tracks that will fit in five minutes

List

titles

= tracksNoLongerThan(Time.valueOf("00:05:00"),

                                 session);

for (ListIterator iter =

titles

.listIterator() ;

     iter.hasNext() ; ) {

String aTitle

= (

String

)iter.next();

    System.out.println("Track: " +

aTitle

);

}

Those changes are pretty simple, and the relationship between the return type of the query and the list elements we see in Java is straightforward. Depending on what data you've set up, running this version using ant qtest will result in output similar to Example 9-9. (If you've not got any data, or you want it to look just like this, recreate the test data before displaying it by running ant schema ctest atest qtest .)

Example 9-9. Listing just the titles of tracks no more than five minutes long

qtest:

     [java] Track: Russian Trance

     [java] Track: Video Killed the Radio Star

     [java] Track: Test Tone 1

     [java] Track: In a Manner of Speaking

     [java] Track: Gone

     [java] Track: Never Turn Your Back on Mother Earth

     [java] Track: Motherless Child

9.2.2 What about...

...Returning more than one property? You can certainly do this, and the properties can come from multiple objects if you're using a join, or if your query object has components or associations (which are, after all, a very convenient form of object-oriented join). As you'd expect from SQL, all you do is list the properties you'd like, separated by commas. As a simple example, let's get the IDs as well as the titles for our tracks in this query. Tweak Track.hbm.xml so the query looks like Example 9-10.

Example 9-10. Selecting multiple properties from an object

<query name="com.oreilly.hh.tracksNoLongerThan">

  <![CDATA[

      select

track.id

, track.title from com.oreilly.hh.Track as track

      where track.playTime <= :length

    ]]>

</query>

We don't need to change the query method at all; it still invokes this query by name, passes in the same named parameter, and returns the resulting list. But what does that list contain now? We'll need to update our loop in main() so that it can show both the IDs and the titles.

In situations like this, when it needs to return multiple, separate values for each "row" in a query, each entry in the List returned by Hibernate will contain an array of objects. Each array contains the selected properties, in the order they're listed in the query. So we'll get a list of twoelement arrays; each array will contain an Integer followed by a String .

Example 9-11 shows how we can update main() in QueryTest.java to work with these arrays.

Example 9-11. Working with multiple, separate properties in query results

// Print

IDs and

titles of tracks that will fit in five minutes

List titles = tracksNoLongerThan(Time.valueOf("00:05:00"),

                                 session);

for (ListIterator iter = titles.listIterator() ;

     iter.hasNext() ; ) {

Object[] aRow

= (

Object[]

)iter.next();

Integer anID = (Integer)aRow[0];

    String aTitle = (String)aRow[1];

System.out.println("Track: " + aTitle

+ " [ID=" + anID + ']'

);

}

Running ant qtest after these changes produces output like Example 9-12.

Example 9-12. Listing titles and IDs

qtest:

     [java] Track: Russian Trance [ID=0]

     [java] Track: Video Killed the Radio Star [ID=1]

     [java] Track: Test Tone 1 [ID=6]

     [java] Track: In a Manner of Speaking [ID=8]

     [java] Track: Gone [ID=10]

     [java] Track: Never Turn Your Back on Mother Earth [ID=11]

     [java] Track: Motherless Child [ID=12]

I hope that while looking at this example you thought "that's an awkward way to work with Track properties." If you didn't, compare Example 9-11 with lines 48-56 of Example 3-5. The latter is more concise and natural, and it prints even more information about the tracks. If you're extracting information about a mapped object, you're almost always better off taking full advantage of the mapping capability to extract an actual instance of the object, so you can work with its properties with the full expressive and type-safe capabilities of Java.

NOTE

Was this some sort of cruel joke?

So why did I show it at all? Well, there are situations where retrieving multiple values in an HQL query can make sense: you might want just one property from each of a couple of mapped classes, for example. Or you might want to return a group of related classes by listing the class names in the select clause. For such cases it's worth knowing this technique. There may also be significant performance benefits if your mapped object has dozens of large (or non-lazily associated) properties, and you're only interested in one or two.

There is another surprising trick you can use to impose a good object structure even when you're building reports that select a bunch of properties from disparate mapped objects. HQL lets you construct and return an arbitrary object within your select clause. So you could create an adhoc reporting class whose properties reflect the values needed by your report, and return instances of this class in the query instead of cryptic Object arrays. If we'd defined a TrackSummary class with id and title properties and an appropriate constructor, our query could have used:

select new TrackSummary(track.id, track.title)

instead of:

select track.id, track.title

and we wouldn't have needed any of the array manipulation in the code that works with the results. (Again, in this case, it would still have made more sense to simply return the entire Track , but this is useful when you're working with properties from multiple objects or even synthetic results like aggregate functions, as demonstrated below.)