CREATE PROCEDURE pagination
@sqlstr nvarchar(4000), --查询字符串
@pagecount int, --第N页
@pagesize int
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount intexec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@pagecount as 当前页
set @pagecount=(@pagecount-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1
GO
@sqlstr nvarchar(4000), --查询字符串
@pagecount int, --第N页
@pagesize int
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount intexec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@pagecount as 当前页
set @pagecount=(@pagecount-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1
GO
{
TranContext dbTrans = new TranContext(DataUtil.DbName.TO_MGR_DB); ArrayList list = new ArrayList(); ResultSet rs1 = null;
ResultSet rsInfo = null;
ResultSet rs = null;
CallableStatement callProc = null; try
{
dbTrans.prepareCall(sqlProc); callProc = dbTrans.getCallableStatement();
callProc.setString(1,sqlParam);
callProc.setInt(2,ipage);
callProc.setInt(3,DataUtil.pageLength); boolean retval = callProc.execute(); //# 记录集1
rs1 = callProc.getResultSet(); //# 记录集2
retval = callProc.getMoreResults();
rsInfo = callProc.getResultSet(); // @返回的记录数
if(rsInfo.next())
{
m_rowCount = rsInfo.getInt("TotalCount");
} //# 记录集3
retval = callProc.getMoreResults();
rs = callProc.getResultSet(); while(rs.next()) {
Customer record= new Customer();
//<find>
record.setFullName(rs.getString("FullName"));
........
//</find>
list.add(record);
record = null;
}
}
catch(Exception ex)
{
DebugTracer.except(ex);
}
finally
{
try {
if (rs1 != null) {
rs1.close();
rs1 = null;
}
}
catch (SQLException e) {
} try {
if (rsInfo != null) {
rsInfo.close();
rsInfo = null;
}
}
catch (SQLException e) {
} try {
if (rs != null) {
rs.close();
rs = null;
}
}
catch (SQLException e) {
}
try {
if (callProc != null) {
callProc.close();
callProc = null;
}
}
catch (SQLException e) {
} try {
dbTrans.close();
}
catch (SQLException e) {
}
dbTrans.freeCon();
return list;
}
}
这是什么啊 jdk里没有啊
你注意getMoreResults()怎么用就行了
可是我的总提示这样的错误
select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@pagecount as 当前页
返回的数据;
但是第二个有结果的结果集即分页数据却得不到:
exec sp_cursorfetch @P1,16,@pagecount,@pagesize 返回的数据;
代码中:
if(stmt.getMoreResults())
{
rs=stmt.getResultSet();
while(rs.next())
{
out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
但是,rs.next()是false,得不到数据啊。
http://community.csdn.net/Expert/topic/3448/3448617.xml?temp=.7503931