hibernate调用存储过程来分页
????? 虽然Hibernate帮助我们完成了分页功能，但若有的数据库不支技分页查询，Hibernate的分页的效率可就不高了，它先查询出一部分然后现在内存当中取出你所要的那一页。
????? Hibernate支持Native SQL（使用SQL Query）也、支持存储过程下面就来说说Hibernate调用存储过程来分页
在数据库创建Ture_Page存储过程
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER? PROCEDURE Ture_Page
??? @PageSize??? int,??????? --每页的行数
??? @PageIndex??? int,??????? --1 代表第一页
??? @Col??????? varchar(250),??? --要显示的字段
??? @Table??????? varchar(200),??? --所用到的表,复条的话就写from与where之间的内容
??? @Where??? varchar(200)='',??? --所用到的条件
??? @OKey??????? varchar(50),??? --排序字段
??? @Order??????? varchar(20)='ASC'?? --排序方式
as
?declare @cmdstr varchar(2000)
?declare @cmdstrcount? varchar(2000)
?set nocount on
???????
??????? set @cmdstr='select top '
??????? set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
??????? if @Order='DESC' and @PageIndex>1
?set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'<'
??????? else if @PageIndex=1
?set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>='
??????? else
?set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>'
??????? if @PageIndex>1
????????? begin
?? if @Order='ASC'
???????????? set @cmdstr=@cmdstr+'(select max ('+@OKey+') from (select top '
??else
??????? set @cmdstr=@cmdstr+'(select min ('+@OKey+') from (select top '
??????????? set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)
??? ? if @Where<>''
?????????? ?? set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+'?? where?? '+@Where+'?? order by '+@OKey+' '+@Order+') as t) '
??else
????????????? ?? set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+'??? order by '+@OKey+' '+@Order+') as t) '
????????? end
??????? else
????????? set @cmdstr=@cmdstr+' 0 ' --convert(nvarchar,0)
???????? print @cmdstr
??????? if @Where<>''
??????????? set @cmdstr=(@cmdstr+'? and '+@Where+' order by '+@OKey+' '+@Order)
??????? else
??????????? set @cmdstr=(@cmdstr+'? order by '+@OKey+' '+@Order)
???????
?????? print @cmdstr
??????? exec(@cmdstr)
??? set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Hibernate的配置文件：
??? <hibernate-mapping>
??? <class name="com.mengya.entity.TDepart" table="t_depart" schema="dbo" catalog="oaproject">
??????? <id name="DId" type="java.lang.Integer">
??????????? <column name="d_id" />
??????????? <generator />
??????? </id>
??????? <property name="DName" type="java.lang.String">
??????????? <column name="d_name" length="50" />
??????? </property>
??????? <property name="DRemark" type="java.lang.String">
??????????? <column name="d_remark" />
??????? </property>
??????? <set name="TEmps" inverse="true">
??????????? <key>
??????????????? <column name="d_id" />
??????????? </key>
??????????? <one-to-many />
??????? </set>??????
??? </class>
????<!--<sql-query>不在<class>内面-->
????<sql-query name="TDepartTure_Page" callable="true">
??????? ?<return alias="TDepart" column="d_id"/>
??????? ??<return-property name="DName" column="d_name"/>
??????? ??<return-property name="DRemark" column="d_remark"/>
??????? ?</return>
??????? ?{call Ture_Page(?,?,?,?,?,?,?)}
??????? ?<query-param name="PageSize" type="int"/>
???? ?<query-param name="PageIndex" type="int"/>
???? ?<query-param name="Col" type="String"/>
???? ?<query-param name="Table" type="String"/>
???? ?<query-param name="Where" type="String"/>
???? ?<query-param name="OKey" type="String"/>
???? ?<query-param name="Order" type="String"/>???? ?
??????? </sql-query>
</hibernate-mapping>????
?调用存储过程：
??? ......
/*
? * 分页查询所有部门信息
? */
?public List getPageDepart(final int pagesize,final int pageindex){
??List pagelist=null;
??try {
?? //这里我使用的Hibernate的getHibernateTemplate()方法也要可直接得到session
???pagelist=getHibernateTemplate().executeFind(new HibernateCallback(){
????public Object doInHibernate(Session session) throws HibernateException, SQLException {
?????Query query=session.getNamedQuery("TDepartTure_Page");
?????query.setInteger(0, pagesize);
?????query.setInteger(1, pageindex);
?????query.setString(2, "d_id,d_name,d_remark");
?????query.setString(3, "t_depart");
?????query.setString(4, "");
?????query.setString(5, "d_id");
?????query.setString(6, "desc");
?????return query.list();
????}
????
???});
??} catch (RuntimeException e) {
???e.printStackTrace();
???throw e;
??}
??return pagelist;
?}
同样，这样做的话也会带来弊端可移植性差。改库的话也要创建该存储过程
