hibernate分页转换为sql命令

使用hibernate进行分页，设置起始记录数和记录条数：

 

if (limit > 0) {
				c.setFirstResult(start);
				c.setMaxResults(limit);
			}

执行查询后转换为sql语句分为两种情况，第一种是当起始位置为0时，那么会使用top方式：

 

select top ? this_.POSTID as POSTID23_3_, this_.cityPK as cityPK23_3_, this_.content as content23_3_, this_.countryPK as countryPK23_3_, this_.createtime as createtime23_3_, this_.ip as ip23_3_, this_.lastmodtime as lastmodt7_23_3_, this_.OBJECTPK as OBJECTPK23_3_, this_.parentid as parentid23_3_, this_.provincePK as provincePK23_3_, this_.recommended as recomme10_23_3_, this_.replyCount as replyCount23_3_, this_.SERVERNODEID as SERVERN12_23_3_, this_.title as title23_3_, this_.townPK as townPK23_3_, this_.USERPK as USERPK23_3_, this_.viliagePK as viliagePK23_3_, obj2_.OBJECTPK as OBJECTPK0_0_, obj2_.addDate as addDate0_0_, obj2_.currVisitCount as currVisi3_0_0_, obj2_.depth as depth0_0_, obj2_.description as descript5_0_0_, obj2_.historyVisitCount as historyV6_0_0_, obj2_.objectID as objectID0_0_, obj2_.objectType as objectType0_0_, obj2_.PARENTPK as PARENTPK0_0_, obj2_.password as password0_0_, obj2_.popular as popular0_0_, obj2_.postID as postID0_0_, obj2_.recommended as recomme12_0_0_, obj2_.ROOTPK as ROOTPK0_0_, obj2_.serverNodeID as serverN13_0_0_, obj2_.status as status0_0_, obj2_.updateDate as updateDate0_0_, obj2_.USERPK as USERPK0_0_, user3_.USERPK as USERPK6_1_, user3_.addDate as addDate6_1_, user3_.address as address6_1_, user3_.OBJECTPK as OBJECTPK6_1_, user3_.answer as answer6_1_, user3_.CONFIG as CONFIG6_1_, user3_.description as descript6_6_1_, user3_.EMAIL as EMAIL6_1_, user3_.OCCUPATION as OCCUPATION6_1_, user3_.PASSWORD as PASSWORD6_1_, user3_.PHOTO as PHOTO6_1_, user3_.plainpasswd as plainpa11_6_1_, user3_.question as question6_1_, user3_.SERVERNODEID as SERVERN13_6_1_, user3_.SEX as SEX6_1_, user3_.STATUS as STATUS6_1_, user3_.tel as tel6_1_, user3_.USERID as USERID6_1_, user3_.USERNAME as USERNAME6_1_, user3_.userType as userType6_1_, user4_.USERPK as USERPK6_2_, user4_.addDate as addDate6_2_, user4_.address as address6_2_, user4_.OBJECTPK as OBJECTPK6_2_, user4_.answer as answer6_2_, user4_.CONFIG as CONFIG6_2_, user4_.description as descript6_6_2_, user4_.EMAIL as EMAIL6_2_, user4_.OCCUPATION as OCCUPATION6_2_, user4_.PASSWORD as PASSWORD6_2_, user4_.PHOTO as PHOTO6_2_, user4_.plainpasswd as plainpa11_6_2_, user4_.question as question6_2_, user4_.SERVERNODEID as SERVERN13_6_2_, user4_.SEX as SEX6_2_, user4_.STATUS as STATUS6_2_, user4_.tel as tel6_2_, user4_.USERID as USERID6_2_, user4_.USERNAME as USERNAME6_2_, user4_.userType as userType6_2_ 
from POSTS this_ 
inner join OBJECTS obj2_ on this_.OBJECTPK=obj2_.OBJECTPK 
left outer join USERS user3_ on obj2_.USERPK=user3_.USERPK 
inner join USERS user4_ on this_.USERPK=user4_.USERPK 
where this_.parentid=? and this_.recommended<>? and (this_.OBJECTPK=? or this_.OBJECTPK=?) 
order by this_.POSTID desc

上面显示一堆信息，其它它们的关系，我们可以不看，就看它实现的方式，这里是使用了top方式来进行查询的。

 

第二种情况是起始位置不为0的时候，那么会使用limit方式进行查询：

select limit ? ? this_.POSTID as POSTID23_3_, this_.cityPK as cityPK23_3_, this_.content as content23_3_, this_.countryPK as countryPK23_3_, this_.createtime as createtime23_3_, this_.ip as ip23_3_, this_.lastmodtime as lastmodt7_23_3_, this_.OBJECTPK as OBJECTPK23_3_, this_.parentid as parentid23_3_, this_.provincePK as provincePK23_3_, this_.recommended as recomme10_23_3_, this_.replyCount as replyCount23_3_, this_.SERVERNODEID as SERVERN12_23_3_, this_.title as title23_3_, this_.townPK as townPK23_3_, this_.USERPK as USERPK23_3_, this_.viliagePK as viliagePK23_3_, obj2_.OBJECTPK as OBJECTPK0_0_, obj2_.addDate as addDate0_0_, obj2_.currVisitCount as currVisi3_0_0_, obj2_.depth as depth0_0_, obj2_.description as descript5_0_0_, obj2_.historyVisitCount as historyV6_0_0_, obj2_.objectID as objectID0_0_, obj2_.objectType as objectType0_0_, obj2_.PARENTPK as PARENTPK0_0_, obj2_.password as password0_0_, obj2_.popular as popular0_0_, obj2_.postID as postID0_0_, obj2_.recommended as recomme12_0_0_, obj2_.ROOTPK as ROOTPK0_0_, obj2_.serverNodeID as serverN13_0_0_, obj2_.status as status0_0_, obj2_.updateDate as updateDate0_0_, obj2_.USERPK as USERPK0_0_, user3_.USERPK as USERPK6_1_, user3_.addDate as addDate6_1_, user3_.address as address6_1_, user3_.OBJECTPK as OBJECTPK6_1_, user3_.answer as answer6_1_, user3_.CONFIG as CONFIG6_1_, user3_.description as descript6_6_1_, user3_.EMAIL as EMAIL6_1_, user3_.OCCUPATION as OCCUPATION6_1_, user3_.PASSWORD as PASSWORD6_1_, user3_.PHOTO as PHOTO6_1_, user3_.plainpasswd as plainpa11_6_1_, user3_.question as question6_1_, user3_.SERVERNODEID as SERVERN13_6_1_, user3_.SEX as SEX6_1_, user3_.STATUS as STATUS6_1_, user3_.tel as tel6_1_, user3_.USERID as USERID6_1_, user3_.USERNAME as USERNAME6_1_, user3_.userType as userType6_1_, user4_.USERPK as USERPK6_2_, user4_.addDate as addDate6_2_, user4_.address as address6_2_, user4_.OBJECTPK as OBJECTPK6_2_, user4_.answer as answer6_2_, user4_.CONFIG as CONFIG6_2_, user4_.description as descript6_6_2_, user4_.EMAIL as EMAIL6_2_, user4_.OCCUPATION as OCCUPATION6_2_, user4_.PASSWORD as PASSWORD6_2_, user4_.PHOTO as PHOTO6_2_, user4_.plainpasswd as plainpa11_6_2_, user4_.question as question6_2_, user4_.SERVERNODEID as SERVERN13_6_2_, user4_.SEX as SEX6_2_, user4_.STATUS as STATUS6_2_, user4_.tel as tel6_2_, user4_.USERID as USERID6_2_, user4_.USERNAME as USERNAME6_2_, user4_.userType as userType6_2_ 
from POSTS this_ 
inner join OBJECTS obj2_ on this_.OBJECTPK=obj2_.OBJECTPK 
left outer join USERS user3_ on obj2_.USERPK=user3_.USERPK 
inner join USERS user4_ on this_.USERPK=user4_.USERPK 
where this_.parentid=? and this_.recommended<>? and (this_.OBJECTPK=? or this_.OBJECTPK=?) 
order by this_.POSTID desc

 

 

limit和top应该不是标准的sql命令，再转：

 

 

做分页效果时发现一直不能成功，最后发现问题在top * N from tablename这条语句上。在mysql中select top用法和ms sqlserver有所区别。

若在mysql中实现select top功能得用：select * from tablename limit M,N

这里M表示从（M+1）条记录开始，N表示返回的记录条数。