Searches and Queries - Beginning Hibernate: From Novice to Professional - page 210

Using Native SQL
Although you should probably use HQL whenever possible, Hibernate does provide a way to
use native SQL statements directly through Hibernate. One reason to use native SQL is that
your database supports some special features through its dialect of SQL that are not supported
in HQL. Another reason is that you may want to call stored procedures from your Hibernate
application. We discuss stored procedures and other database-specific integration solutions in
Appendix A. Rather than just providing an interface to the underlying JDBC connection, like
other Java ORM tools, Hibernate provides a way to define the entity (or join) that the query
uses. This makes integration with the rest of your ORM-oriented application easy.
You can modify your SQL statements to make them work with Hibernate's ORM layer. You
do need to modify your SQL to include Hibernate aliases that correspond to objects or object
properties. You can specify all properties on an object with {objectname.*} , or you can specify
the aliases directly with {objectname.property} . Hibernate uses the mappings to translate your
object property names into their underlying SQL columns. This may not be the exact way you
expect Hibernate to work, so be aware that you do need to modify your SQL statements for full
ORM support. You will especially run into problems with native SQL on classes with subclasses—
be sure you understand how you mapped the inheritance across either a single table or multi-
ple tables, in order that you select the right properties off of the table.
Underlying Hibernate's native SQL support is the org.hibernate.SQLQuery interface,
which extends the org.hibernate.Query interface already discussed. Your application will cre-
ate a native SQL query from the session with the createSQLQuery() method on the Session
interface.
public SQLQuery createSQLQuery(String queryString) throws HibernateException
After you pass a string containing the SQL query to the createSQLQuery() method, you
should associate the SQL result with an existing Hibernate entity, a join, or a scalar result.
The SQLQuery interface has addEntity() , addJoin() , and addScalar() methods. For the
entities and joins, you can specify a lock mode, which we discuss in Chapter 8. The
addEntity() methods take an alias argument and either a class name or an entity name.
The addJoin() methods take an alias argument and a path to join.
Using native SQL with scalar results is the simplest way to get started with native SQL.
Our Java code looks like this:
String sql = "select avg(product.price) as avgPrice from Product product";
SQLQuery query = session.createSQLQuery(sql);
query.addScalar("avgPrice",Hibernate.DOUBLE);
List results = query.list();
Because we did not specify any entity aliases, Hibernate executes exactly the same SQL
that we passed through:
select avg(product.price) as avgPrice from Product product
The SQL is regular SQL (we did not have to do any aliasing here). We created an SQLQuery
object, and then added a scalar mapping with the built-in double type (from the
org.hibernate._Hibernate class). We needed to map the avgPrice SQL alias to the object type.
The results are a List with one object—a Double .