Hibernate: A Developer's Notebook

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