存储过程代码:
create procedure dividePage
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第n页
@pagesize int --每页行数
as
set nocount on
declare @p1 int, --p1是游标的id
@rowcount int
exec sp_cursoropen @p1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
--select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize java中调用代码:
String sql = "select * from Quetion";
CallableStatement csm = con.prepareCall("{call dividePage(?,?,?)}");//在数据库中执行,得到2个结果集,
//如何返回第2个结果集
csm.setString(1, sql);
csm.setInt(2, 2);
csm.setInt(3, 10);
csm.execute();
csm.getMoreResults();//
//此处java.sql.SQLException: [Microsoft][SQLServer JDBC Driver] Unexpected token type: TABNAME
//但是在一般的存储过程返回两个结果集则可以使用
ResultSet rs = csm.getResultSet();
create procedure dividePage
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第n页
@pagesize int --每页行数
as
set nocount on
declare @p1 int, --p1是游标的id
@rowcount int
exec sp_cursoropen @p1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
--select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize java中调用代码:
String sql = "select * from Quetion";
CallableStatement csm = con.prepareCall("{call dividePage(?,?,?)}");//在数据库中执行,得到2个结果集,
//如何返回第2个结果集
csm.setString(1, sql);
csm.setInt(2, 2);
csm.setInt(3, 10);
csm.execute();
csm.getMoreResults();//
//此处java.sql.SQLException: [Microsoft][SQLServer JDBC Driver] Unexpected token type: TABNAME
//但是在一般的存储过程返回两个结果集则可以使用
ResultSet rs = csm.getResultSet();
csm.registerOutParameter(5,Types.xxxx);
...........csm.execute();
csm.getXXXX(xx);
..........