Chapter 13. Native SQL Queries - Hibernate reference 2.1.8 ga 正式版英文参考手册

SQL queries are exposed through the same Query interface, just like ordinary HQL queries. The only difference is the use of Session.createSQLQuery().

Query sqlQuery = sess.createSQLQuery("select {cat.*} from cats {cat}", "cat", Cat.class);
sqlQuery.setMaxResults(50);
List cats = sqlQuery.list();

The three parameters provided to createSQLQuery() are:

The alias name is used inside the sql string to refer to the properties of the mapped class (in this case Cat). You may retrieve multiple objects per row by supplying a String array of alias names and a Class array of corresponding classes.

The {cat.*} notation used above is a shorthand for "all properties". You may even list the properties explicity, but you must let Hibernate provide SQL column aliases for each property. The placeholders for these column aliases are the property name qualified by the table alias. In the following example, we retrieve Cats from a different table (cat_log) to the one declared in the mapping metadata. Notice that we may even use the property aliases in the where clause.

String sql = "select cat.originalId as {cat.id}, "
    + "  cat.mateid as {cat.mate}, cat.sex as {cat.sex}, "
    + "  cat.weight*10 as {cat.weight}, cat.name as {cat.name}"
    + "     from cat_log cat where {cat.mate} = :catId"
List loggedCats = sess.createSQLQuery(sql, "cat", Cat.class)
    .setLong("catId", catId)
    .list();

Note: if you list each property explicitly, you must include all properties of the class and its subclasses!

Named SQL queries may be defined in the mapping document and called in exactly the same way as a named HQL query.

List people = sess.getNamedQuery("mySqlQuery")
    .setMaxResults(50)
    .list();

<sql-query name="mySqlQuery">
    <return alias="person" class="eg.Person"/>
    SELECT {person}.NAME AS {person.name},
           {person}.AGE AS {person.age},
           {person}.SEX AS {person.sex}
    FROM PERSON {person} WHERE {person}.NAME LIKE 'Hiber%'
</sql-query>