类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
页面导航：
正文内容：sql server分页使用存储过程要更高效些
下面这个存储过程是从sql区找到的
-----------------------------------------------------------------------------------------------------------------------------------
create proc p_show
@querystr varchar(8000), --表名、视图名、查询语句
@pagesize int=10, --每页的大小(行数)
@pagecurrent int=1, --要显示的页
@fdshow varchar (8000)=, --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@fdorder nvarchar (3000)= --排序字段列表
as
declare @fdname nvarchar(550) --表中的主键或表、临时表中的标识列名
,@id1 varchar(80),@id2 varchar(80) --开始和结束的记录号
,@obj_id int --对象id
--表中有复合主键的处理
declare @strfd nvarchar(4000) --复合主键列表
,@strjoin varchar(8000) --连接字段
,@strwhere nvarchar(4000) --查询条件
select @obj_id=object_id(@querystr)
,@fdshow=case isnull(@fdshow,) when then * else +@fdshow end
,@fdorder=case isnull(@fdorder,) when then else order by +@fdorder end
,@querystr=case when @obj_id is not null then +@querystr else (+@querystr+) a end
--如果显示第一页，可以直接用top来完成
if @pagecurrent=1
begin
select @id1=cast(@pagesize as varchar(50))
exec(select top +@id1+@fdshow+ from +@querystr+@fdorder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @obj_id is not null and objectproperty(@obj_id,istable)=1
begin
select @id1=cast(@pagesize as varchar(50))
,@id2=cast((@pagecurrent-1)*@pagesize as varchar(50))
select @fdname=name from syscolumns where id=@obj_id and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@obj_id and xtype=pk)
goto lbusetemp --如果表中无主键,则用临时表处理
select @fdname=name from syscolumns where id=@obj_id and colid in(
select colid from sysindexkeys where @obj_id=id and indid in(
select indid from sysindexes where @obj_id=id and name in(
select name from sysobjects where xtype=pk and parent_obj=@obj_id
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd=,@strjoin=,@strwhere=
select @strfd=@strfd+,[+name+]
,@strjoin=@strjoin+ and a.[+name+]=b.[+name+]
,@strwhere=@strwhere+ and b.[+name+] is null
from syscolumns where id=@obj_id and colid in(
select colid from sysindexkeys where @obj_id=id and indid in(
select indid from sysindexes where @obj_id=id and name in(
select name from sysobjects where xtype=pk and parent_obj=@obj_id
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec(select top +@id1+@fdshow+ from +@querystr
+ where +@fdname+ not in(select top
+@id2+ +@fdname+ from +@querystr+@fdorder
+)+@fdorder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec(select +@fdshow+ from(select top +@id1+ a.* from
(select top 100 percent * from +@querystr+@fdorder+) a
left join (select top +@id2+ +@strfd+
from +@querystr+@fdorder+) b on +@strjoin+
where +@strwhere+) a
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @fdname=[id_+cast(newid() as varchar(80))+]
,@id1=cast(@pagesize*(@pagecurrent-1) as varchar(50))
,@id2=cast(@pagesize*@pagecurrent-1 as varchar(50))
exec(select +@fdname+=identity(int,0,1),+@fdshow+
into #tb from+@querystr+@fdorder+
select +@fdshow+ from #tb where +@fdname+ between
+@id1+ and +@id2
)
go
--------------------------------------------------------------------------------------------------------------------------------
这里需要建立一个分页类resultgatherpro.java
--------------------------------------------------------------------------------------------------------------------------------
package com;
import conn.dbconnmanager;//这个是数据库连接池，可以替换成自己的
import java.sql.*;
import java.util.enumeration;
import java.util.list;
import java.util.map;
import java.util.arraylist;
import java.util.hashmap;
import javax.servlet.http.httpservletrequest;
import com.util.myredirect;
public class resultgatherpro
{
private string sql;
private int intpagesize; //每页行数
private int introwcount;
private int intpagecount;
private int intpage; //页号
private string counter;
public resultgatherpro()
{
}
public resultgatherpro(string sqlcom,int rownum,int pagenum,string counter)
{
sql=sqlcom;
intpagesize = rownum;
intpage = pagenum;
counter = counter;
}
public list selectrs(string sqlcom,int rownum,int pagenum,string counter)
{
this.sql = sqlcom;
this.intpagesize = rownum;
this.intpage = pagenum;
this.counter = counter;
return selectrs();
}
public list selectrs()
{
list rsall = new arraylist();
map rstree;
dbconnmanager conn = null;
connection con = null;
callablestatement stmt = null;
resultset rs = null;
statement st = null;
resultset rsc = null;
try{
conn = dbconnmanager.getinstance();
con = conn.getconnection("mssql");
st = con.createstatement();
rsc=st.executequery(counter);
while(rsc.next())
{
introwcount=rsc.getint("allrow");
}
stmt = con.preparecall("{call p_show("+sql+","+intpagesize+","+intpage+")}");
rs = stmt.executequery();
resultsetmetadata rsmd = rs.getmetadata();
int numberofcolumns = rsmd.getcolumncount();
object[] aa = new object[numberofcolumns-1];
intpagecount = (introwcount+intpagesize-1)/intpagesize;
while(rs.next())
{
rstree = new hashmap(numberofcolumns);
for(int r=1;r<numberofcolumns+1;r++)
{
rstree.put(rsmd.getcolumnname(r),rs.getobject(r));
}
rsall.add(rstree);
}
}catch(java.lang.exception ex){
ex.printstacktrace();
}finally{
try{
if(rsc!=null)
rsc.close();
if(st!=null)
st.close();
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.releaseconnection("mssql",con);
}catch(exception e){
system.out.println(e);
}
}
return rsall;
}
public string changepage(httpservletrequest request)
{
string urlchange=null;
string pagename = request.getrequesturi().substring(request.getrequesturi().lastindexof("/")+1);
string url = geturl(request);
if(intpage>1 && intpage<intpagecount)
{
urlchange="<a href="+pagename+"?pagenum="+(intpage-1)+url+">上一页</a><a href="+pagename+"?pagenum="+(intpage+1)+url+"> 下一页</a> 第"+intpage+"页 共"+intpagecount+"页 共"+introwcount+"条";
}else if(intpage==1 && introwcount<=intpagesize){
urlchange="上一页 下一页 第"+intpage+"页 共"+intpagecount+"页 共"+introwcount+"条";
}else if(intpage==intpagecount && intpage!=1){
urlchange="<a href="+pagename+"?pagenum="+(intpage-1)+url+">上一页</a> 下一页 第"+intpage+"页 共"+intpagecount+"页 共"+introwcount+"条";
}else if(intpage==1 && introwcount>=intpagesize){
urlchange="上一页 <a href="+pagename+"?pagenum="+(intpage+1)+url+"> 下一页</a> 第"+intpage+"页 共"+intpagecount+"页 共"+introwcount+"条";
}else{
urlchange = "<a href="+pagename+">第一页</a>";
}
return urlchange;
}
public string gotopage(httpservletrequest request)
{
string url = geturl(request);
string javascript = "<script>function checksearch()\n{if(find.pagenum.value==\"\" || find.pagenum.value==\"0\"){alert(请输正确入页数！);find.pagenum.focus();return false;}\nif(isnan(find.pagenum.value)){alert(请输入数字！);find.pagenum.focus();return false;}}</script>";
string form = "<table border=0 cellpadding=0 cellspacing=0>\n"+javascript;
form += "<form name=find action= onsubmit=return checksearch()>\n<tr><td width=40 align=center>\n<input name=pagenum type=text size=2>\n";
if(url!=null)
{
if(url.indexof("&")>0)
{
string[] param = new string[(url.split("&")).length];
param = url.split("&");
for(int i = 0;i<param.length;i++)
{
form += "<input type=hidden name="+param[i].substring(0,param[i].indexof("="))+" value="+param[i].substring(param[i].indexof("=")+1)+">\n";
}
}else{
if(url.indexof("=")>0)
{
form += "<input type=hidden name="+url.substring(0,url.indexof("="))+" value="+url.substring(url.indexof("=")+1)+">\n";
}
}
}
form += "</td><td width=25 align=center><input type=submit name=submit value=go>\n</td>\n</tr>\n</form>\n</table>";
return form;
}
private string geturl(httpservletrequest request)
{
string url = "";
enumeration param = request.getparameternames();
while(param.hasmoreelements())
{
string pname = param.nextelement().tostring();
if(!pname.equalsignorecase("pagenum"))
url += pname+"="+request.getparameter(pname)+"&";
}
if(url.endswith("&"))
{
url = url.substring(0,url.lastindexof("&"));
}
return url;
}
public string intercept(string str,int num,string last)
{
if(str.length()<=num)
return str;
else
return str.substring(0,num)+last;
}
}
--------------------------------------------------------------------------------------------------------
在action里可以这样调用
--------------------------------------------------------------------------------------------------------------
string sql = "select * from usertable";
string sqlcount = "select count(*) from usertable";//为了得到总行数
int pagesize= 18;
int pagenum = 1;
if(request.getparameter("pagenum")!=null)
{
pagenum = java.lang.integer.parseint(request.getparameter("pagenum"));
}
resultgatherpro rs = new resultgatherpro(sql,pagesize,pagenum,sqlcount);
request.setattribute("liststill",rs.selectrs());
request.setattribute("changepage",rs.changepage(request));
request.setattribute("gotopage",rs.gotopage(request));
-----------------------------------------------------------------------------------------------------------------------------------
最后 jsp里可以这样写
-------------------------------------------------------------------------------------------------------------------------
<logic:iterate id="listuser" name="liststill" type="map">
<bean:write name=listuser property=username/>
<bean:write name=listuser property=useremail/>
</logic:iterate>
<bean:write name=changepage filter="false"/>
<bean:write name=gotopage filter="false"/>
上面两个一定要设置filter="false" 不然会过滤html部分