使用SQLQuery
對原生SQL查詢執(zhí)行的控制是通過SQLQuery接口進(jìn)行的,通過執(zhí)行Session.createSQLQuery()獲取這個(gè)接口。最簡單的情況下,我們可以采用以下形式:
List cats
=
sess.createSQLQuery(
"
select * from cats
"
).addEntity(Cat.
class
).list();
這個(gè)查詢指定了:
SQL查詢字符串
查詢返回的實(shí)體
這里,結(jié)果集字段名被假設(shè)為與映射文件中指明的字段名相同。對于連接了多個(gè)表的查詢,這就可能造成問題,因?yàn)榭赡茉诙鄠€(gè)表中出現(xiàn)同樣名字的字段。下面的方法就可以避免字段名重復(fù)的問題:
List cats
=
sess.createSQLQuery(
"
select {cat.*} from cats cat
"
).addEntity(
"
cat
"
, Cat.
class
).list();
這個(gè)查詢指定了:
SQL查詢語句,它帶一個(gè)占位符,可以讓Hibernate使用字段的別名.
查詢返回的實(shí)體,和它的SQL表的別名.
addEntity()方法將SQL表的別名和實(shí)體類聯(lián)系起來,并且確定查詢結(jié)果集的形態(tài)。
addJoin()方法可以被用于載入其他的實(shí)體和集合的關(guān)聯(lián).
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();
" select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id " )
.addEntity( " cat " , Cat. class )
.addJoin( " kitten " , " cat.kittens " )
.list();
原生的SQL查詢可能返回一個(gè)簡單的標(biāo)量值或者一個(gè)標(biāo)量和實(shí)體的結(jié)合體。
Double max
=
(Double) sess.createSQLQuery(
"
select max(cat.weight) as maxWeight from cats cat
"
)
.addScalar( " maxWeight " , Hibernate.DOUBLE);
.uniqueResult();
.addScalar( " maxWeight " , Hibernate.DOUBLE);
.uniqueResult();
除此之外,你還可以在你的hbm文件中描述結(jié)果集映射信息,在查詢中使用。
List cats
=
sess.createSQLQuery(
" select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id " )
.setResultSetMapping( " catAndKitten " )
.list();
" select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id " )
.setResultSetMapping( " catAndKitten " )
.list();
命名SQL查詢
可以在映射文檔中定義查詢的名字,然后就可以象調(diào)用一個(gè)命名的HQL查詢一樣直接調(diào)用命名SQL查詢.在這種情況下,我們不 需要調(diào)用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 >
< 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();
.setMaxResults( 50 )
.list();