虽然Hibernate帮助我们完成了分页功能,但若有的数据库不支技分页查询,Hibernate的分页的效率可就不高了,它先查询出一部分然后现在内存当中取出你所要的那一页。
Hibernate支持Native SQL(使用SQL Query)也、支持存储过程下面就来说说Hibernate调用存储过程来分页
第一步在数据库创建Ture_Page存储过程
Create 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 no____count 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 '+@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 no____count off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
第二部 在相应Hibernate中entity实体相对应的xx.hbm.sml中作如下的配置:
<hibernate-mapping>
<class name="com.svse.entity.TNew" table="t_new" schema="dbo" catalog="shoa">
<id name="NId" type="java.lang.Integer">
<column name="n_id" />
<generator class="native" />
</id>
<property name="NTitle" type="java.lang.String">
<column name="n_title" length="50" />
</property>
<property name="NContent" type="java.lang.String">
<column name="n_content" />
</property>
<property name="NDate" type="java.util.Date">
<column name="n_date" length="23" />
</property>
<property name="NFlag" type="java.lang.Integer">
<column name="n_flag" />
</property>
</class>
<!-- 存储过程 -->
<sql-query name="newsTurnPage" callable="true">
<return alias="TNew" class="com.svse.entity.TNew">
<return-property name="NId" column="n_id"/>
<return-property name="NTitle" column="n_title"/>
<return-property name="NContent" column="n_content" />
<return-property name="NDate" column="n_date"/>
<return-property name="NFlag" column="n_flag" />
</return>
{call Ture_Page(?,?,?,?,?,?,?)}
</sql-query>
</hibernate-mapping>
第三部 查询方法的实现
//分页查询
public List turnPage(int size, int currpage) {
List list=new ArrayList();
try {
this.beginTran();
Query query=this.getSession().getNamedQuery("newsTurnPage");
query.setInteger(0,size);
query.setInteger(1, currpage);
query.setString(2, "n_id,n_title,n_content,n_date,n_flag");
query.setString(3, "t_new");
query.setString(4,"");
query.setString(5, "n_id");
query.setString(6, "desc");
list = query.list();
} catch (Exception e) {
e.printStackTrace();
}finally{
this.end();
}
return list;
}
第四步 分页相关设置
public ActionForward getAllNews(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
//查出所有的新闻
List list = nd.getAllNews();
int flag = Integer.parseInt(request.getParameter("flag"));
//新闻分页
String action=request.getParameter("action");
String cp=request.getParameter("curpage");
int curpage=0;
//总共有多少也
int count=list.size();
//每一页有多少条记录
int i=10;
//计算一共也多少页
int allpage=count%i;
if(allpage>0)
{
allpage=(count/i)+1;
}
else
{
allpage=count/i;
}
//判断传来的当前页
if(cp==null)
{
curpage=1;
}
else
{
curpage=Integer.parseInt(cp);
}
//判断操作
if(action==null)
{
action="first";
}
//第一次进来或是首页
if(action.equalsIgnoreCase("first"))
{
curpage=1;
}
//前一页
if(action.equalsIgnoreCase("front"))
{
if(curpage<=1)
{
curpage=1;
}
else
{
curpage=curpage-1;
}
}
//后一页
if(action.equalsIgnoreCase("next"))
{
if(curpage>=list.size())
{
curpage=curpage;
}
else
{
curpage=curpage+1;
}
}
//最后一夜
if(action.equalsIgnoreCase("last"))
{
curpage=allpage;
}
//获得分页信息
List list1=nd.turnPage(i, curpage);
request.setAttribute("NewsList", list1);
request.setAttribute("count", new Integer(count));
request.setAttribute("curpage", new Integer(curpage));
request.setAttribute("allpage", new Integer(allpage));
if (flag == 1) {
return mapping.findForward("showNews");
} else {
return mapping.findForward("showNews2");
}
}
第五 页面应用
在本类中,调用查询类存储过程时,调用session.getNamedQuery("…")方法来获得User.hbm.xml中配置的查询存储过程。在其余的存储过程调用的测试中,首先通过hibernate的session获得connection,然后调用connection对象的相应方法来实现存储过程的调用。
同样,这样做的话也会带来弊端可移植性差。改库的话也要创建该存储过程