Chapter 16. Native SQL
You may also express queries in the native SQL dialect of your database. This is useful if you want to utilize database specific features such as query hints or the CONNECT keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.
Hibernate3 allows you to specify handwritten SQL (including stored procedures) for all create, update, delete, and load operations.
16.1. Using a SQLQuery
Execution of native SQL queries is controlled via the SQLQuery interface, which is obtained by calling Session.createSQLQuery(). In extremely simple cases, we can use the following form:
List cats = sess.createSQLQuery("select * from cats")
.addEntity(Cat.class)
.list();
This query specified:
*
the SQL query string
*
the entity returned by the query
Here, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries which join multiple tables, since the same column names may appear in more than one table. The following form is not vulnerable to column name duplication:
List cats = sess.createSQLQuery("select {cat.*} from cats cat")
.addEntity("cat", Cat.class)
.list();
This query specified:
*
the SQL query string, with a placeholder for Hibernate to inject the column aliases
*
the entity returned by the query, and its SQL table alias
The addEntity() method associates the SQL table alias with the returned entity class, and determines the shape of the query result set.
The addJoin() method may be used to load associations to other entities and collections.
List cats = sess.createSQLQuery(
"select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
)
.addEntity("cat", Cat.class)
.addJoin("kitten", "cat.kittens")
.list();
A native SQL query might return a simple scalar value or a combination of scalars and entities.
Double max = (Double) sess.createSQLQuery("select max(cat.weight) as maxWeight from cats cat")
.addScalar("maxWeight", Hibernate.DOUBLE);
.uniqueResult();
You can alternatively describe the resultset mapping informations in your hbm files and use them for your queries
List cats = sess.createSQLQuery(
"select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
)
.setResultSetMapping("catAndKitten")
.list();
