我在sql server 2008数据库里创建 pageSelect存储过程
create procedure pageSelect
@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
exec sp_cursorclose @P1
set nocount off
执行结果如图
http://a3.qpic.cn/psb?/V10p0TXC0NjahB/iWFzfeE863NVdcEkgMc*DabQDAgXHzSySWuONazBxRo!/m/dOtLuOOYEgAAnull&bo=YASMAQAAAAADB8s!&rf=photolist&t=5
在java中调用存储过程后结果怎么接收的?
我是这样写的
String sql="select left(loginDate,10) ,skipUrl,count(loginDate) FROM loginlog group by skipUrl,left(loginDate,10) order by left(loginDate,10) desc ";
String sqlval="Exec pageSelect '"+sql+"',1,5";
session=sessionFactory.getCurrentSession();
Query query= session.createSQLQuery(sqlval);
List list = query.list();