9.5 Writing Native SQL Queries

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