在网上找了一个sqlserver分页的存储过程,在查询分析器里面出现两个结果集.一个是空的
一个是有结果的 请问在java中如何得到游标的结果呢?
在存储过程的开始不写output关键字
在里面一个变量写output这个能否讲解一下?
sp_cursorfetch 16是什么意思啊?
把select @rowcount as 'rowCount'这一行留下
然后用调用CallableStatement.getMoreResults();
rs 可以得到'rowCount'这个值 然后在调用CallableStatement.getMoreResults();这个方法,以为可以得到结果,
但是就是不出现结果. create PROC cursor_page
@SqlStr NVARCHAR(4000), --查询字符串
@CurrentPage INT, --第N页
@PageSize INT --每页行数
AS
BEGIN
SET NOCOUNT ON
DECLARE @P1 INT, --P1是游标的id
@rowcount int
EXEC sp_cursoropen @P1 OUTPUT,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @rowcount as 'rowCount'
--SELECT CEILING(1.0*@rowcount/@pagesize) AS 总页数--,@rowcount as 总行数,@currentpage as 当前页
SET @currentpage=(@currentpage-1)*@pagesize+1
EXEC sp_cursorfetch @P1,16,@currentpage,@pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF
END
java代码:Connection conn = getconnection();
CallableStatement cstmt = null;
ResultSet rs = null;
String sql = "{call cursor_page(?,?,?)}";
StringBuffer sqlq = new StringBuffer();
sqlq.append("select wii.seq_id,dc.customer_code ");
sqlq.append("from work_invoice_info wii,bill_invoice_info bii ");
sqlq.append("left join data_customer dc on dc.customer_id = bii.customer_id ");
sqlq.append("where wii.bill_info_id = bii.seq_id ");
sqlq.append("order by dc.customer_code");
cstmt = conn.prepareCall(sql);
cstmt.setString(1,sqlq.toString());
cstmt.setInt(2, 1);
cstmt.setInt(3, 20);
cstmt.execute();
cstmt.getMoreResults();
rs =cstmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("rowCount"));//可以得到一个数字
}
cstmt.getMoreResults();
rs =cstmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("seq_id"));//得不到结果
}
一个是有结果的 请问在java中如何得到游标的结果呢?
在存储过程的开始不写output关键字
在里面一个变量写output这个能否讲解一下?
sp_cursorfetch 16是什么意思啊?
把select @rowcount as 'rowCount'这一行留下
然后用调用CallableStatement.getMoreResults();
rs 可以得到'rowCount'这个值 然后在调用CallableStatement.getMoreResults();这个方法,以为可以得到结果,
但是就是不出现结果. create PROC cursor_page
@SqlStr NVARCHAR(4000), --查询字符串
@CurrentPage INT, --第N页
@PageSize INT --每页行数
AS
BEGIN
SET NOCOUNT ON
DECLARE @P1 INT, --P1是游标的id
@rowcount int
EXEC sp_cursoropen @P1 OUTPUT,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @rowcount as 'rowCount'
--SELECT CEILING(1.0*@rowcount/@pagesize) AS 总页数--,@rowcount as 总行数,@currentpage as 当前页
SET @currentpage=(@currentpage-1)*@pagesize+1
EXEC sp_cursorfetch @P1,16,@currentpage,@pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF
END
java代码:Connection conn = getconnection();
CallableStatement cstmt = null;
ResultSet rs = null;
String sql = "{call cursor_page(?,?,?)}";
StringBuffer sqlq = new StringBuffer();
sqlq.append("select wii.seq_id,dc.customer_code ");
sqlq.append("from work_invoice_info wii,bill_invoice_info bii ");
sqlq.append("left join data_customer dc on dc.customer_id = bii.customer_id ");
sqlq.append("where wii.bill_info_id = bii.seq_id ");
sqlq.append("order by dc.customer_code");
cstmt = conn.prepareCall(sql);
cstmt.setString(1,sqlq.toString());
cstmt.setInt(2, 1);
cstmt.setInt(3, 20);
cstmt.execute();
cstmt.getMoreResults();
rs =cstmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("rowCount"));//可以得到一个数字
}
cstmt.getMoreResults();
rs =cstmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("seq_id"));//得不到结果
}
如果和页面上写的结果不一样,那么就检查一下语句吧。
rs = cstmt.execute();一般我都这么写。为什么后面还要调用那两个方法?
cstmt就为null了,后面当然没有结果输出了
所以调用这一句cstmt.getMoreResults(); 是移动到下一个结果集.
-------------------- -------------------- rowCount
-----------
27seq_id customer_code
-------------------- --------------------
8122309321 002
8122309531 002
8122310071 002
8122310182 002
8122310231 001
8122310251 001
8122310252 001
8122311361 123
8122311372 123
8122311383 123
8122515251 002
8122515451 001
8122515511 NULL
8122515522 NULL
8122516211 NULL
8122516212 NULL
8122516313 NULL
8122516314 NULL
8122516315 NULL
8122516326 NULL
@RETURN_VALUE = 0