16.2. 命名SQL查询 · Java 开源项目中文文档

# 16.2\. 命名SQL查询可以在映射文档中定义查询的名字,然后就可以象调用一个命名的HQL查询一样直接调用命名SQL查询.在这种情况下,我们_不_ 需要调用`addEntity()`方法. ``` <sql-query name="persons"> <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 :namePattern </sql-query> ``` ``` List people = sess.getNamedQuery("persons") .setString("namePattern", namePattern) .setMaxResults(50) .list(); ``` `<return-join>`和 `<load-collection>` 元素是用来连接关联以及将查询定义为预先初始化各个集合的。 ``` <sql-query name="personsWith"> <return alias="person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex}, adddress.STREET AS {address.street}, adddress.CITY AS {address.city}, adddress.STATE AS {address.state}, adddress.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS adddress ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query> ``` 一个命名查询可能会返回一个标量值.你必须使用`<return-scalar>`元素来指定字段的别名和 Hibernate类型 ``` <sql-query name="mySqlQuery"> <return-scalar column="name" type="string"/> <return-scalar column="age" type="long"/> SELECT p.NAME AS name, p.AGE AS age, FROM PERSON p WHERE p.NAME LIKE 'Hiber%' </sql-query> ``` 你可以把结果集映射的信息放在外部的`<resultset>`元素中，这样就可以在多个命名查询间，或者通过`setResultSetMapping()`API来访问。(此处原文即存疑。原文为：You can externalize the resultset mapping informations in a `<resultset>` element to either reuse them accross several named queries or through the `setResultSetMapping()` API.) ``` <resultset name="personAddress"> <return alias="person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> </resultset> <sql-query name="personsWith" resultset-ref="personAddress"> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex}, adddress.STREET AS {address.street}, adddress.CITY AS {address.city}, adddress.STATE AS {address.state}, adddress.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS adddress ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query> ``` 另外,你可以在java代码中直接使用hbm文件中的结果集定义信息。 ``` List cats = sess.createSQLQuery( "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id" ) .setResultSetMapping("catAndKitten") .list(); ```