hibernate sql²éѯ list
±¾ÎÄ×ªÔØ: http://hi.baidu.com/%D2%BB%C6%F0%C8%A5%CC%DF%C7%F2/blog/item/802027355899fe83a61e12ee.html
ʹÓÃSQLQuery
¶ÔÔÉúSQL²éѯִÐеĿØÖÆÊÇͨ¹ýSQLQuery½Ó¿Ú½øÐеģ¬Í¨¹ýÖ´ÐÐSession.createSQLQuery
()
»ñÈ¡Õâ¸ö½Ó¿Ú¡£×î¼òµ¥µÄÇé¿öÏ£¬ÎÒÃÇ¿ÉÒÔ²ÉÓÃÒÔÏÂÐÎʽ£º
List
cats
=
sess.createSQLQuery
(
"
select * from cats
"
).addEntity(Cat.
class
).list
();
Õâ¸ö²éѯָ¶¨ÁË:
SQL²éѯ×Ö·û´®
²éѯ·µ»ØµÄʵÌå
ÕâÀ½á¹û¼¯×Ö¶ÎÃû±»¼ÙÉèΪÓëÓ³ÉäÎļþÖÐÖ¸Ã÷µÄ×Ö¶ÎÃûÏàͬ¡£¶ÔÓÚÁ¬½ÓÁ˶à¸ö±íµÄ²éѯ£¬Õâ¾Í¿ÉÄÜÔì³ÉÎÊÌ⣬ÒòΪ¿ÉÄÜÔÚ¶à¸ö±íÖгöÏÖͬÑùÃû×ÖµÄ×ֶΡ£ÏÂ
ÃæµÄ·½·¨¾Í¿ÉÒÔ±ÜÃâ×Ö¶ÎÃûÖØ¸´µÄÎÊÌâ:
List
cats
=
sess.createSQLQuery
(
"
select {cat.*} from cats cat
"
).addEntity(
"
cat
"
, Cat.
class
).list
();
Õâ¸ö²éѯָ¶¨ÁË:
SQL²éѯÓï¾ä£¬Ëü´øÒ»¸öռλ·û£¬¿ÉÒÔÈÃHibernateʹÓÃ×ֶεıðÃû.
²éѯ·µ»ØµÄʵÌ壬ºÍËüµÄSQL±íµÄ±ðÃû.
addEntity()·½·¨½«SQL±íµÄ±ðÃûºÍʵÌåÀàÁªÏµÆðÀ´£¬²¢ÇÒÈ·¶¨²éѯ½á¹û¼¯µÄÐÎ̬¡£
addJoin()·½·¨¿ÉÒÔ±»ÓÃÓÚÔØÈëÆäËûµÄʵÌåºÍ¼¯ºÏµÄ¹ØÁª.
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
();
ÔÉúµÄSQL²éѯ¿ÉÄÜ·µ»ØÒ»¸ö¼òµ¥µÄ±êÁ¿Öµ»òÕßÒ»¸ö±êÁ¿ºÍʵÌåµÄ½áºÏÌå¡£
Double max
=
(Double)
sess.createSQLQuery
(
"
select max(cat.weight)
as maxWeight from cats cat
"
)
.addScalar(
"
maxWeight
"
, Hibernate.DOUBLE);
.uniqueResult();
³ý´ËÖ®Í⣬Ä㻹¿ÉÒÔÔÚÄãµÄhbmÎļþÖÐÃèÊö½á¹û¼¯Ó³ÉäÐÅÏ¢£¬ÔÚ²éѯÖÐʹÓá£
List
cats
=
sess.createSQLQuery
(
"
select {cat.*}, {kitten.*} from cats cat, cats kitten where
kitten.mother = cat.id
"
)
.setResultSetMapping(
"
catAndKitten
"
)
.list
();
ÃüÃûSQL²éѯ
¿ÉÒÔÔÚÓ³ÉäÎĵµÖж¨Òå²éѯµÄÃû×Ö,È»ºó¾Í¿ÉÒÔÏóµ÷ÓÃÒ»¸öÃüÃûµÄHQL²éѯһÑùÖ±½Óµ÷ÓÃÃüÃûSQL²éѯ.ÔÚÕâÖÖÇé¿öÏÂ,ÎÒÃDz»
ÐèÒ
Ïà¹ØÎĵµ£º
×ÔÁ¬½ÓÊÇÖ¸Ò»¸ö±íÓëÆä×ÔÉí½øÐÐÁ¬½Ó¡£
¾ÙÒ»¸ö¼òµ¥µÄʵÀý£¬Ò»¸öѧÉú¿Î³Ì³É¼¨±íStudentScore£¬ÆäÖаüÀ¨Ñ§Éú±àºÅ/studentid£¬ËùÑ¡¿Î³Ì/coursename,¿Î³Ì³É¼¨/score
Ñ¡ÔñÿÃſγ̵ÄǰÁ½ÃûѧÉú,²¢½«ËûÐÅÏ¢Êä³ö³öÀ´
´ËʱÄãµÄµÚÒ»·´Ó¦¾ÍÊÇÒª°ÑѧÉú°´ËùÑ¡¿Î³Ì½øÐзÖ×飬Ȼºó±È½Ï»ñµÃÿÃſγÌÀïÃæÇ°Á½Î»µÄѧÉú
·Ö×éµÄ·½Ê½ÓÐgroup b ......
½éÉÜGROUP BY ºÍ HAVING ×Ó¾äǰ£¬ÎÒÃDZØÐèÏȽ²½²sqlÓïÑÔÖÐÒ»ÖÖÌØÊâµÄº¯Êý:¾ÛºÏº¯Êý£¬ÀýÈçSUM, COUNT, MAX, AVGµÈ¡£ÕâЩº¯ÊýºÍÆäËüº¯ÊýµÄ¸ù±¾Çø±ð¾ÍÊÇËüÃÇÒ»°ã×÷ÓÃÔÚ¶àÌõ¼Ç¼ÉÏ¡£
SELECT SUM(population) from bbc
ÕâÀïµÄSUM×÷ÓÃÔÚËùÓзµ»Ø¼Ç¼µÄpopulation×Ö¶ÎÉÏ£¬½á¹û¾ÍÊǸòéѯֻ·µ»ØÒ»¸ö½á¹û£¬¼´ËùÓйú¼ÒµÄ×ÜÈË¿ ......
-- Ò»£º ´¦Àíδ²éµ½µÄ¿ÕÖµ
select
case when exists(select EndPrice from T_stock where BOMSN='0109')
then (select EndPrice from T_stock where BOMSN='0109')
else 0 end
--¶þ£º¸üÐÂ×Ö¶ÎÖµ
--strNum ÊýÁ¿
--strId ÐòºÅ
update T_buy_Pro set Co ......
1.¶àwhere£¬ÉÙhaving
whereÓÃÀ´¹ýÂËÐУ¬havingÓÃÀ´¹ýÂË×é
2.¶àunion all£¬ÉÙunion
unionɾ³ýÁËÖØ¸´µÄÐУ¬Òò´Ë»¨·ÑÁËһЩʱ¼ä
3.¶àExists£¬ÉÙin
ExistsÖ»¼ì²é´æÔÚÐÔ£¬ÐÔÄܱÈinÇ¿ºÜ¶à£¬ÓÐЩÅóÓѲ»»áÓÃExists£¬¾Í¾Ù¸öÀý×Ó
Àý£¬ÏëÒªµÃµ½Óе绰ºÅÂëµÄÈ˵Ļù±¾ÐÅÏ¢£¬table2ÓÐÈßÓàÐÅÏ¢
select * from table1;--(id,n ......