在做一个关于办公系统,现遇到一个问题:在数据库是写了存储过程实现考勤计数的统计,例如:本月内员工A的考勤信息,具体代码如下:
报错:
com.microsoft.sqlserver.jdbc.SQLServerException: 结果集已关闭。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(Unknown Source)
at dao.ManualSignDAO.getSignSum(ManualSignDAO.java:111)
at dao.ManualSignDAO.main(ManualSignDAO.java:139)
0
3
0
调试发现程序每一步都正常,走完try{}之后就进入了catch(){}
--------------------------------------------------------------------------------------
存储过程在数据库中运行没有错误,得到所有out参数结果后并且可以分组查询出用户具体的考勤计算-所有考勤统计
alter proc p_SignSum
@userId int,--用户ID
@beginday date,--查询起始时间
@endday date,--查询结束时间
@outbeOK int output,--正常上班数
@outbeNo int output, --迟到数
@outmoNo int output, --早退
@outxiu int output --休息
as
--正常上班数
select @outbeOK=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=1 and signday between @beginday and @endday
--迟到数
select @outbeNo=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=0 and signday between @beginday and @endday
--早退
select @outmoNo=count(ismove) from dbo.Tb_ManualSign where userId=@userId and ismove=0 and signday between @beginday and @endday
--休息
select @outxiu=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=3 and ismove=3 and signday between @beginday and @endday
select userId, @outbeOK as '上班',@outbeNo as '迟到',@outmoNo as '早退',@outxiu as '休息' from dbo.Tb_ManualSign group by userId
go
declare @beOK int
declare @beNO int
declare @moNo int
declare @xiu int
exec p_SignSum 19,'2010-3-1','2010-03-31',@beOK output,@beNO output,@moNo output,@xiu output
------------------------------------------------------------------------------------------------------------------------------------------------------
--我的考勤统计
alter proc p_SignMySum
@userId int,--用户ID
@beginday date,--查询起始时间
@endday date,--查询结束时间
@outbeOK int output,--正常上班数
@outbeNo int output, --迟到数
@outmoNo int output, --早退
@outxiu int output --休息
as
--正常上班数
select @outbeOK=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=1 and signday between @beginday and @endday
--迟到数
select @outbeNo=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=0 and signday between @beginday and @endday
--早退
select @outmoNo=count(ismove) from dbo.Tb_ManualSign where userId=@userId and ismove=0 and signday between @beginday and @endday
--休息
select @outxiu=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=3 and ismove=3 and signday between @beginday and @endday
select userId, @outbeOK as '上班',@outbeNo as '迟到',@outmoNo as '早退',@outxiu as '休息' from dbo.Tb_ManualSign where userId=@userId group by userId
go
declare @beOK int
declare @beNO int
declare @moNo int
declare @xiu int
exec p_SignMySum 19,'2010-3-1','2010-03-31',@beOK output,@beNO output,@moNo output,@xiu output
------------------------------------------------------------------------------------------------------------------------------------------------------------
DAO
public List<SignBean> getSignSum(int userId,String beginday,String endday,boolean a){
List<SignBean>signs=new ArrayList<SignBean>();
//Tb_UserInfo use=new ManualSignDAO().getUser(userId);
String sql="";
if(a==false){//普通员工
sql +="{call p_SignMySum(?,?,?,?,?,?,?)}";
}else{
sql +="{call p_SignSum(?,?,?,?,?,?,?)}";//所有考勤统计
}
int outbeOK=0,outbeNO=0,outmoNO=0,xiu=0;
Connection con=ConnectionManager.getConnection();
try {
CallableStatement cs=con.prepareCall(sql);
cs.setInt(1, userId);
cs.setString(2, beginday);
cs.setString(3, endday);
cs.registerOutParameter(4, java.sql.Types.INTEGER);
cs.registerOutParameter(5, java.sql.Types.INTEGER);
cs.registerOutParameter(6, java.sql.Types.INTEGER);
cs.registerOutParameter(7, java.sql.Types.INTEGER);
cs.execute();
ResultSet rs=cs.executeQuery();
while(rs.next()){
SignBean sig=new SignBean();
outbeOK=cs.getInt(4);
outbeNO=cs.getInt(5);
outmoNO=cs.getInt(6);
xiu=cs.getInt(7);
//把数据放入临时Bean中
//sig.setUserId(cs.getInt("userId"));
sig.setIsbeOK(outbeOK);
sig.setIsbeNO(outbeNO);
sig.setIsmoNO(outmoNO);
sig.setXiu(xiu);
signs.add(sig);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//finally{
// try {
// con.close();
// } catch (SQLException e) {
// System.out.println("统计考勤数据后关闭连接");
// e.printStackTrace();
// }
// }
return signs;
}Main()
public static void main(String[] args) {
List<SignBean>a=newManualSignDAO().getSignSum(19,"2010-03-1","2010-03-31",false);
for (int i = 0; i < a.size(); i++) {
SignBean sb=a.get(i);
//System.out.println(sb.getUserId());
System.out.println(sb.getIsbeOK());
System.out.println(sb.getIsbeNO());
System.out.println(sb.getIsmoNO());
}
}
报错:
com.microsoft.sqlserver.jdbc.SQLServerException: 结果集已关闭。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(Unknown Source)
at dao.ManualSignDAO.getSignSum(ManualSignDAO.java:111)
at dao.ManualSignDAO.main(ManualSignDAO.java:139)
0
3
0
调试发现程序每一步都正常,走完try{}之后就进入了catch(){}
--------------------------------------------------------------------------------------
存储过程在数据库中运行没有错误,得到所有out参数结果后并且可以分组查询出用户具体的考勤计算-所有考勤统计
alter proc p_SignSum
@userId int,--用户ID
@beginday date,--查询起始时间
@endday date,--查询结束时间
@outbeOK int output,--正常上班数
@outbeNo int output, --迟到数
@outmoNo int output, --早退
@outxiu int output --休息
as
--正常上班数
select @outbeOK=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=1 and signday between @beginday and @endday
--迟到数
select @outbeNo=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=0 and signday between @beginday and @endday
--早退
select @outmoNo=count(ismove) from dbo.Tb_ManualSign where userId=@userId and ismove=0 and signday between @beginday and @endday
--休息
select @outxiu=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=3 and ismove=3 and signday between @beginday and @endday
select userId, @outbeOK as '上班',@outbeNo as '迟到',@outmoNo as '早退',@outxiu as '休息' from dbo.Tb_ManualSign group by userId
go
declare @beOK int
declare @beNO int
declare @moNo int
declare @xiu int
exec p_SignSum 19,'2010-3-1','2010-03-31',@beOK output,@beNO output,@moNo output,@xiu output
------------------------------------------------------------------------------------------------------------------------------------------------------
--我的考勤统计
alter proc p_SignMySum
@userId int,--用户ID
@beginday date,--查询起始时间
@endday date,--查询结束时间
@outbeOK int output,--正常上班数
@outbeNo int output, --迟到数
@outmoNo int output, --早退
@outxiu int output --休息
as
--正常上班数
select @outbeOK=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=1 and signday between @beginday and @endday
--迟到数
select @outbeNo=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=0 and signday between @beginday and @endday
--早退
select @outmoNo=count(ismove) from dbo.Tb_ManualSign where userId=@userId and ismove=0 and signday between @beginday and @endday
--休息
select @outxiu=count(isbelate) from dbo.Tb_ManualSign where userId=@userId and isbelate=3 and ismove=3 and signday between @beginday and @endday
select userId, @outbeOK as '上班',@outbeNo as '迟到',@outmoNo as '早退',@outxiu as '休息' from dbo.Tb_ManualSign where userId=@userId group by userId
go
declare @beOK int
declare @beNO int
declare @moNo int
declare @xiu int
exec p_SignMySum 19,'2010-3-1','2010-03-31',@beOK output,@beNO output,@moNo output,@xiu output
------------------------------------------------------------------------------------------------------------------------------------------------------------
DAO
public List<SignBean> getSignSum(int userId,String beginday,String endday,boolean a){
List<SignBean>signs=new ArrayList<SignBean>();
//Tb_UserInfo use=new ManualSignDAO().getUser(userId);
String sql="";
if(a==false){//普通员工
sql +="{call p_SignMySum(?,?,?,?,?,?,?)}";
}else{
sql +="{call p_SignSum(?,?,?,?,?,?,?)}";//所有考勤统计
}
int outbeOK=0,outbeNO=0,outmoNO=0,xiu=0;
Connection con=ConnectionManager.getConnection();
try {
CallableStatement cs=con.prepareCall(sql);
cs.setInt(1, userId);
cs.setString(2, beginday);
cs.setString(3, endday);
cs.registerOutParameter(4, java.sql.Types.INTEGER);
cs.registerOutParameter(5, java.sql.Types.INTEGER);
cs.registerOutParameter(6, java.sql.Types.INTEGER);
cs.registerOutParameter(7, java.sql.Types.INTEGER);
cs.execute();
ResultSet rs=cs.executeQuery();
while(rs.next()){
SignBean sig=new SignBean();
outbeOK=cs.getInt(4);
outbeNO=cs.getInt(5);
outmoNO=cs.getInt(6);
xiu=cs.getInt(7);
//把数据放入临时Bean中
//sig.setUserId(cs.getInt("userId"));
sig.setIsbeOK(outbeOK);
sig.setIsbeNO(outbeNO);
sig.setIsmoNO(outmoNO);
sig.setXiu(xiu);
signs.add(sig);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//finally{
// try {
// con.close();
// } catch (SQLException e) {
// System.out.println("统计考勤数据后关闭连接");
// e.printStackTrace();
// }
// }
return signs;
}Main()
public static void main(String[] args) {
List<SignBean>a=newManualSignDAO().getSignSum(19,"2010-03-1","2010-03-31",false);
for (int i = 0; i < a.size(); i++) {
SignBean sb=a.get(i);
//System.out.println(sb.getUserId());
System.out.println(sb.getIsbeOK());
System.out.println(sb.getIsbeNO());
System.out.println(sb.getIsmoNO());
}
}
解决方案 »
- 刷新后提示不能获取数据库连接
- 求一本设计web的书
- ireport+jasperreports 柱状图如何制作
- 表单提交等待
- 终于找到这本书的电子版啦,和csdn里的兄弟姐妹们共享~~~~~~~~~~
- Caused by: org.jboss.resource.JBossResourceException: Apparently wrong driver cl
- 大家上午好,我想请教大家关于预编译Sql语句的问题!
- [求助]如何将用jsp写成的动态页面(*.jsp), 转换成静态页面(*.html)???
- 新人求(js)动态树型菜单的 源代码
- JSP+JavaBean+servlet结构中怎么在后台上传图片后在前端显示?
- 求一些能够关联jsp,xml,java等之类文件后有醒目的图标的编辑器或软件.
- HTTP Status 500 -
//sig.setUserId(cs.getInt("userId"));
如果不注释掉的话运行会报:没不为输出参数输出1
请问这是什么意思呀???是不可以用call对象得到除了out参数以外的数据吗???
select userId, @outbeOK as '上班',@outbeNo as '迟到',@outmoNo as '早退',@outxiu as '休息' from dbo.Tb_ManualSign group by userId
这句是后面修改后加上的,加之前在DAO中测试没问题,因为没有用到ResultSet对象,加上以后在数据库中运行没问题,在DAO类中编辑通过,运行没过。请再看看。。