HQL queries have already been used a few times in previous chapters. It's worth spending a little time looking at how HQL differs from SQL and some of the useful things you can do with it. As with the rest of this notebook, our intention is to provide a useful introduction and some examples, not a comprehensive reference.
Example 9-1. The simplest HQL query
from Track
There's not much to it, is there? This is the HQL equivalent of Example 8-1, in which we built a criteria query on the Track class and supplied no criteria.
By default, Hibernate automatically "imports" the names of each class you map, which means you don't have to provide a fully qualified package name when you want to use it, the simple class name is enough. As long as you've not mapped more than one class with the same name, you don't need to use fully qualified class names in your queries. You are certainly free to do so if you prefer—as I have in this book to help readers remember that the queries are expressed in terms of Java data beans and their properties, not database tables and columns (like you'd find in SQL). Example 9-2 produces precisely the same result as our first query.
Example 9-2. Explicit package naming, but still pretty simple
from com.oreilly.hh.Track
If you do have more than one mapped class with the same name, you can either use the fully qualified package name to refer to each, or you can assign an alternate name for one or both classes using an import tag in their mapping documents. You can also turn off the auto-import facility for a mapping file by adding auto-import="false" to the hibernatemapping tag's attributes.
You're probably used to queries being case-insensitive, since SQL behaves this way. For the most part, HQL acts the same, with the important exceptions of class and property names. Just as in the rest of Java, these are case-sensitive, so you must get the capitalization right.
Let's look at an extreme example of how HQL differs from SQL, by pushing its polymorphic query capability to its logical limit.
9.1.1 How do I do that?
A powerful way to highlight the fundamental difference between SQL and HQL is to consider what happens when you query "from java.lang.Object". At first glance this might not even seem to make sense! In fact, Hibernate supports queries that return polymorphic results. If you've got mapped classes that extend each other, or have some shared ancestor or interface, whether you've mapped the classes to the same table or to different tables, you can query the superclass. Since every Java object extends Object, this query asks Hibernate to return every single entity it knows about in the database.
We can test this by making a quick variant of our query test. Copy QueryTest.java to QueryTest3.java, and make the changes shown in Example 9-3 (most of the changes, which don't show up in the example, involve deleting example queries we don't need here).
Example 9-3. Look, Toto, we're not in SQL anymore
1 package com.oreilly.hh;
2
3 import net.sf.hibernate.*;
4 import net.sf.hibernate.cfg.Configuration;
5
6 import java.util.*;
7
8 /**
9 * Retrieve all persistent objects
10 */
11 public class QueryTest3 {
12
13 /**
14 * Look up and print all entities when invoked from the command line.
15 */
16 public static void main(String args[]) throws Exception {
17 // Create a configuration based on the properties file we've put
18 // in the standard place.
19 Configuration config = new Configuration();
20
21 // Tell it about the classes we want mapped, taking advantage of
22 // the way we've named their mapping documents.
23 config.addClass(Track.class).addClass(Artist.class);
24 config.addClass(Album.class);
25
26 // Get the session factory we can use for persistence
27 SessionFactory sessionFactory = config.buildSessionFactory();
28
29 // Ask for a session using the JDBC information we've configured
30 Session session = sessionFactory.openSession();
31 try {
32 // Print every mapped object in the database
33 List all = session.find("from java.lang.Object");
34 for (ListIterator iter = all.listIterator() ;
35 iter.hasNext() ; ) {
36 System.out.println(iter.next());
37 }
38 } finally {
39 // No matter what, close the session
40 session.close();
41 }
42
43 // Clean up after ourselves
44 sessionFactory.close();
45 }
46 }
We added line 24 because Hibernate will only return objects whose mappings it has been asked to load, and we'd like to see everything we can put in the database. To be sure you've got all the sample data created, run ant schema ctest atest . Line 33 invokes the odd and powerful query, and line 36 prints what we've found. We can't do much more than call toString() on the objects we get back because we don't know what they might be. Object is not very deep as a shared interface.
There's one more step we need to take before we can run this. Add another target to build.xml, as shown in Example 9-4.
NOTE
As usual, these examples assume you've set up the environment by following the preceding chapters. You can also just use the downloadable sample source for this chapter.
Example 9-4. Invoking the über-query
<target name="qtest3" description="Retrieve all mapped objects"
depends="compile">
<java classname="com.oreilly.hh.QueryTest3" fork="true">
<classpath refid="project.class.path"/>
<java>
</target>
Then you can test it by running ant qtest3 . You'll see results like Example 9-5.
Example 9-5. Everything Hibernate can find
% ant qtest3
Buildfile: build.xml
prepare:
compile:
[javac] Compiling 1 source file to /Users/jim/Documents/Work/OReilly/
Hibernate/Examples/ch09/classes
qtest3:
[java] com.oreilly.hh.Album@397cea[id=0,title=Counterfeit e.p.,tracks=[com.
oreilly.hh.AlbumTrack@5e60f2[track=com.oreilly.hh.
Track@aaa10[id=7,title=Compulsion,sourceMedia=Compact Disc]], com.oreilly.hh.
AlbumTrack@2ed1e8[track=com.oreilly.hh.Track@231e35[id=8,title=In a Manner of
Speaking,sourceMedia=Compact Disc]], com.oreilly.hh.AlbumTrack@d6f84a[track=com.
oreilly.hh.Track@9432e0[id=9,title=Smile in the Crowd,sourceMedia=Compact Disc]],
com.oreilly.hh.AlbumTrack@46ca65[track=com.oreilly.hh.
Track@9830bc[id=10,title=Gone,sourceMedia=Compact Disc]], com.oreilly.hh.
AlbumTrack@91e365[track=com.oreilly.hh.Track@a79447[id=11,title=Never Turn Your
Back on Mother Earth,sourceMedia=Compact Disc]], com.oreilly.hh.
AlbumTrack@e823ac[track=com.oreilly.hh.Track@f801c4[id=12,title=Motherless
Child,sourceMedia=Compact Disc]]]]
[java] com.oreilly.hh.Artist@e7736c[id=0,name=PPK,actualArtist=<null>]
[java] com.oreilly.hh.Artist@a59727[id=1,name=The
Buggles,actualArtist=<null>]
[java] com.oreilly.hh.Artist@1f7fbe[id=2,name=Laurie
Anderson,actualArtist=<null>]
[java] com.oreilly.hh.Artist@f42d53[id=3,name=William
Orbit,actualArtist=<null>]
[java] com.oreilly.hh.Artist@ba63a2[id=4,name=Ferry
Corsten,actualArtist=<null>]
[java] com.oreilly.hh.Artist@e668c2[id=5,name=Samuel
Barber,actualArtist=<null>]
[java] com.oreilly.hh.Artist@d67d61[id=6,name=ATB,actualArtist=<null>]
[java] com.oreilly.hh.Artist@e9a555[id=7,name=Pulp
Victim,actualArtist=<null>]
[java] com.oreilly.hh.Artist@6a4aef[id=8,name=Martin L.
Gore,actualArtist=<null>]
[java] com.oreilly.hh.Track@4dba7f[id=0,title=Russian
Trance,sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@906563[id=1,title=Video Killed the Radio
Star,sourceMedia=VHS Videocassette Tape]
[java] com.oreilly.hh.Track@f068b9[id=2,title=Gravity's
Angel,sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@6b54ef[id=3,title=Adagio for Strings (Ferry
Corsten Remix),sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@954549[id=4,title=Adagio for Strings (ATB
Remix),sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@f7dab1[id=5,title=The World
'99,sourceMedia=Digital Audio Stream]
[java] com.oreilly.hh.Track@36d1d7[id=6,title=Test Tone 1,sourceMedia=<null>]
[java] com.oreilly.hh.Track@aaa10[id=7,title=Compulsion,sourceMedia=Compact
Disc]
[java] com.oreilly.hh.Track@231e35[id=8,title=In a Manner of
Speaking,sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@9432e0[id=9,title=Smile in the
Crowd,sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@9830bc[id=10,title=Gone,sourceMedia=Compact
Disc]
[java] com.oreilly.hh.Track@a79447[id=11,title=Never Turn Your Back on Mother
Earth,sourceMedia=Compact Disc]
[java] com.oreilly.hh.Track@f801c4[id=12,title=Motherless
Child,sourceMedia=Compact Disc]
BUILD SUCCESSFUL
Total time: 17 seconds
9.1.2 What just happened?
Well, it's pretty remarkable if you think about it, because Hibernate had to do several separate SQL queries in order to obtain the results for us. A whole lot of work went on behind the scenes to hand us that list of every known persisted entity. Although it's hard to imagine a situation where you'll actually need to do something exactly like this, it certainly highlights some of the interesting capabilities of HQL and Hibernate.
And there are certainly times when slightly less comprehensive queries will be very useful to you, so it's worth keeping in mind that tablespanning polymorphic queries are not only possible, but easy to use.
There are some limitations that come into play when you run an HQL query that requires multiple separate SQL queries to implement. You can't use order by clauses to sort the entire set of results, nor can you use the Query interface's scroll() method to walk through them.
9.1.3 What about...
...Associations and joins? These are easy to work with as well. You can traverse associations by simply following the property chain, using periods as delimiters. To help you refer to a particular entity in your query expressions, HQL lets you assign aliases, just like SQL. This is particularly important if you want to refer to two separate entities of the same class, for example:
from com.oreilly.hh.Track as track1
which is equivalent to
from com.oreilly.hh.Track track1
The version you'll use will most likely depend on what you're used to, or the style guidelines established for your project.
We'll see examples of joins below, once we introduce enough other HQL elements to make them interesting.
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 Strings.
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.)
9.3 Sorting
It should come as no surprise that you can use a SQL-style "order by" clause to control the order in which your output appears. I've alluded to this several times in earlier chapters, and it works just like you'd expect. You can use any property of the objects being returned to establish the sort order, and you can list multiple properties to establish sub-sorts within results for which the first property values are the same.
9.3.1 How do I do that?
Sorting is very simple: you list the values that you want to use to sort the results. As usual, where SQL uses columns, HQL uses properties. For Example 9-13, let's update the query in Example 9-10 so that it displays the results in reverse alphabetical order.
NOTE
As in SQL, you specify an ascending sort using "asc" and a descending sort with "desc".
Example 9-13. Addition to Track.hbm.xml that sorts the results backwards by title
<query name="com.oreilly.hh.tracksNoLongerThan">
<![CDATA[
select track.id, track.title from com.oreilly.hh.Track as track
where track.playTime <= :length
order by track.title desc
]]>
</query>
The output from running this is as you'd expect (Example 9-14).
Example 9-14. Titles and IDs in reverse alphabetical order
% ant qtest
Buildfile: build.xml
prepare:
[copy] Copying 1 file to /Users/jim/Documents/Work/OReilly/Hibernate/
Examples/ch09/classes
compile:
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]
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!
9.5 Writing Native SQL Queries
Given the power and convenience of HQL, and the way it dovetails so naturally with the objects in your Java code, why wouldn't you want to use it? Well, there might be some special feature supported by the native SQL dialect of your project's database that HQL can't exploit. If you're willing to accept the fact that using this feature will make it harder to change databases in the future, Hibernate will let you write queries in that native dialect while still helping you write expressions in terms of properties and translate the results to objects. (If you didn't want this help, you could just use a raw JDBC connection to run a plain SQL query, of course.)
Another circumstance in which it might be nice to meet your database halfway is if you're in the process of migrating an existing JDBC-based project to Hibernate, and you want to take small steps rather than thoroughly rewriting each query right away.
9.5.1 How do I do that?
If you're embedding your query text inside your Java source code, you use the Session method createSQLQuery() instead of Example 3-8's createQuery(). Of course, you know better than to code like that, so I won't even show you an example. The better approach is to put the query in a mapping document like Example 3-9. The difference is that you use a sql-query tag rather than the query tag we've seen up until now. You also need to tell Hibernate the mapped class you want to return, and the alias that you're using to refer to it (and its properties) in the query.
As a somewhat contrived example, suppose we want to know all the tracks that end exactly halfway through the last minute they're playing (in other words, the time display on the jukebox would be h:mm:30). An easy way to do that would be to take advantage of HSQLDB's built-in SECOND function, which gives you the seconds part of a Time value. Since HQL doesn't know about functions that are specific to HSQLDB's SQL dialect, this will push us into the realm of a native SQL query. Example 9-23 shows what it would look like; add this after the HQL queries in Track.hbm.xml.
Example 9-23. Embedding a native SQL dialect query in a Hibernate mapping
<sql-query name="com.oreilly.hh.tracksEndingAt">
<return alias="track" class="com.oreilly.hh.Track"/>
<![CDATA[
select {track.*}
from TRACK as {track}
where SECOND({track}.PLAYTIME) = :seconds
]]>
</sql-query>
The return tag tells Hibernate we're going to be using the alias track in our query to refer to a Track object. That allows us to use the shorthand {track.*} in the query body to refer to all the columns from the TRACK table we need in order to create a Track instance. (Notice that everywhere we use the alias in the query body we need to enclose it in curly braces. This gets us "out of" the native SQL environment so we can express things in terms of Hibernate-mapped classes and properties.)
The where clause in the query uses the HSQLDB SECOND function to narrow our results to include only tracks whose length has a specified number in the seconds part. Happily, even though we're building a native SQL query, we can still make use of Hibernate's nice named query parameters. In this case we're passing in a value named "seconds" to control the query. (You don't use curly braces to tell Hibernate you're using a named parameter even in an SQL query; its parser is smart enough to figure this out.)
The code that uses this mapped SQL query is no different than our previous examples using HQL queries. The getNamedQuery() method is used to load both kinds, and they both implement the Query interface. So our Java method invoking this query should look familiar. Add the code in Example 9-24 after the printTrackSummary() method in QueryTest.java.
Example 9-24. Calling a native SQL mapped query
/**
* Print tracks that end some number of seconds into their final minute.
*
* @param seconds, the number of seconds at which we want tracks to end.
* @param session the Hibernate session that can retrieve data.
* @throws HibernateException if there is a problem.
**/
public static void printTracksEndingAt(int seconds, Session session)
throws HibernateException
{
Query query = session.getNamedQuery("com.oreilly.hh.tracksEndingAt");
query.setInteger("seconds", seconds);
List results = query.list();
for (ListIterator iter = results.listIterator() ; iter.hasNext() ; ) {
Track aTrack = (Track)iter.next();
System.out.println("Track: " + aTrack.getTitle() +
", length=" + aTrack.getPlayTime());
}
}
Finally, add some lines to main() that call this method. Example 9-25 shows them added after the invocation of printTrackSummary().
Example 9-25. Calling printTracksEndingAt() to display tracks ending at a half minute
...
printTrackSummary(CreateTest.getArtist("Samuel Barber",
false, session), session);
System.out.println("Tracks ending halfway through final minute:");
printTracksEndingAt(30, session);
} finally {
// No matter what, close the session
...
These changes produce the additional output shown in Example 9-26 when ant qtest is run.
Example 9-26. Sample output from the native SQL query
qtest:
[java] Track: Video Killed the Radio Star [ID=1]
...
[java] Summary of tracks by Samuel Barber:
[java] Total tracks: 2
[java] Shortest track: 00:06:35
[java] Longest track: 00:07:39
[java] Tracks ending halfway through final minute:
[java] Track: Russian Trance, length=00:03:30
There's a lot more tedium and attention to detail required in using a native SQL query than an HQL query (especially when your query starts getting complex or referring to multiple tables), but it's nice to know that it is possible on the rare occasions where you really need one.
9.5.2 What about...
...Well, lots of things? You undoubtedly suspect this chapter barely scratches the surface of what you can do with HQL. That's definitely true. When you start combining some of these capabilities, and working with collections, associations, and powerful expressions, you can achieve some remarkable things. We can't possibly cover them all in this introduction, so you'll want to take a close look at the HQL section and examples in the Hibernate reference documentation, and do some experimentation on your own.
When you look through the Hibernate Query Language chapter in the reference documentation, be sure to look at the interesting things you can use in expressions, especially as they apply to collections. Don't miss the way you can use array bracket notation to select elements of indexed collections—you can even put arithmetic expressions inside the brackets.
The "Tips and Tricks" section that follows their longer examples gives some useful advice about working efficiently in different database environments, and using a variety of Hibernate interfaces to achieve useful results in ways you might not think of, especially if you're coming from a SQL background.
Hopefully, this discussion has helped you get a grounding in the basics, and it will serve as a starting point and anchor for the explorations on which you will embark!
NOTE
This isn't your father's SQL....
UconsreZpers_ta Patty Walker https://wakelet.com/wake/A6pNeqeJ75YBgfhCzoo5U
ReplyDeleteconschenlare
WinpuMconba Haley Johnson WebcamMax
ReplyDeleteWinRar
Everest
sahedkiefi
brasliario_ji Jamie Smith software
ReplyDeletehotlisere