java.sql.SQLException: 结果集已耗尽

最新推荐文章于 2021-03-10 04:53:15 发布

听雨1

最新推荐文章于 2021-03-10 04:53:15 发布

阅读量5.6k

点赞数

分类专栏： Java 文章标签： java java.sql.SQLException: 结果集已耗尽 ORA-01013 java设置查询时间

Java 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

2013年10月21日星期一 21时43分23秒

说明：继上次的java.sql.SQLException: ORA-01013: user requested cancel of current operation 错误。查了两天也没查出具体原因。后在同事的建议下，延长查询时间。因为生产库不能随意重启，
于是就写了个testQuery.jsp测试页面来测试生产环境中延长脚本查询时间是否可以完全生成文件并导出：

<%@page import="com.poson.cb.lib.DBConn"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFRichTextString"%>
<%@page import="java.io.FileOutputStream"%>
<%@page import="java.io.File"%>
<%@page import="java.io.PrintWriter"%>
<%@page import="com.poson.cb.lib.CBConfig2"%>
<%@page import="javax.servlet.jsp.tagext.TryCatchFinally"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.text.SimpleDateFormat"%><META HTTP-EQUIV="pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache, must-revalidate">
<META HTTP-EQUIV="expires" CONTENT="0">
<%@ page contentType="text/html; charset=GBK" language="java"%>
<%@ page import="java.util.*" %>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean"%>
<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic"%>
<%@ taglib uri="/WEB-INF/struts-tiles.tld" prefix="tiles" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>移动客户出账全量稽核</title>
<style type="text/css">
.table-b table td
{
border:0px solid #F00;
font-size:smaller
}
.table-c table
{
border-right:1px solid #F00;border-bottom:1px solid #F00
}
.STYLE2 {font-size: 12px}
td
{
font-size: 13px
}
</style>
</head>
<script type="text/javascript">

</script>
<%
System.out.println("页面测试开始......");
String sql1="select count(0) from file_reg_mobile where bil_billing_cycle_id = 201309 and billing_flag = 'S' and file_name like 'LIST%' ";
String sql2=" select p.province_name,m.file_name, t.error_code from tl_error_record_201309 t " +
"left join file_reg_ext_mobile f on t.batch_id = f.serial left join province p " +
"on f.province_use = p.prov_code left join file_reg_mobile m on m.serial = t.batch_id " +
"group by p.province_name,m.file_name, t.error_code";

DBConn con=new DBConn();
Statement statement1=null,statement2=null;
ResultSet set1=null,set2=null;
String filepath="";
int rowCount1=0;
try{
statement1=con.getConn().createStatement();
//延长脚本执行时间
statement1.setQueryTimeout(1000*60*2);
set1= statement1.executeQuery(sql1);
while(set1.next()){
rowCount1=set1.getInt(1);
}
}catch(SQLException e){
System.out.println("脚本1执行错误！"+e);

}finally{
set1.close();
statement1.close();
}

try{
statement2=con.getConn().createStatement();
//设置查询时间
statement2.setQueryTimeout(1000*60*2);
System.out.println("查询开始时间:"+new SimpleDateFormat("yyyy-mm-dd hh24:mm:ss").format(new Date()));
set2= statement2.executeQuery(sql2);
System.out.println("查询结束时间:"+new SimpleDateFormat("yyyy-mm-dd hh24:mm:ss").format(new Date()));
HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet sheet1=wb.createSheet("测试");
while(set2.next()){
HSSFRow row= sheet1.createRow(sheet1.getLastRowNum()+1);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set2.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set2.getString(2)));
row.createCell((short)2).setCellValue(new HSSFRichTextString(set2.getString(3)));
}
filepath=System.getProperty("user.home")+File.separator+"web"+File.separator+"CB"+File.separator+"test.xls";
FileOutputStream file=new FileOutputStream(new File(filepath));
wb.write(file);
System.out.println("写完Excel时间:"+new SimpleDateFormat("yyyy-mm-dd hh24:mm:ss").format(new Date()));
}catch(SQLException e){
System.out.println("脚本2执行错误！"+e);
}finally{
set2.close();
statement2.close();
}
System.out.println("下载访问路径:"+"http://localhost:7001"+filepath);
%>

<body border="0" bordercolor="#000000" style="border-collapse:collapse;">
<form method="post" name="form1">
<table width="100%" border="1">
<tr>
<td>set1的记录数为：</td>
<td><%= rowCount1%></td>
</tr>
<tr>
<td></td>
<td><a href="<%="http://localhost:7001"+filepath%>">点击下载</a> </td> </tr>
</table>
</form>
</body>
</html>

页面测试开始......
查询开始时间:2013-27-21 1124:27:05
查询结束时间:2013-27-21 1124:27:24
写完Excel时间:2013-27-21 1124:27:24
下载访问路径:http://localhost:7001/CTCB/web/CB/test.xls

测试结果发现：第二个查询结果最长也才用了20s而已。而我登陆生产环境（晚上）测试，完全没问题。问题应该是白天资源忙加上网络速度慢导致无法将生成的文件导出。

==================================================================================================
刚开始界面报如下错误：
<2013-10-21 下午09时41分51秒 GMT+08:00> <Error> <HTTP> <BEA-101017> <[ServletContext(id=1106085697,name=CB,context-path=/CB)] Root cause of ServletException.
java.sql.SQLException: 结果集已耗尽
at weblogic.jdbc.wrapper.ResultSet.getInt(ResultSet.java:278)
at jsp_servlet.__testqery._jspService(__testqery.java:197)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:33)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1077)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:28)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:172)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at com.poson.cb.util.comface.EncodingFilter.doFilter(EncodingFilter.java:164)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:7053)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3902)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2773)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

上面是修改的代码，造成这个错误的原因是出现此错误是因为，执行的数据库查询没有查询到任何结果，却调用了rs.next() 或者，游标到了查询出来的最后一条记录，又调用了rs.next()则，会找不到next条记录
ResultSet set1=statement.executeQuery(sql1);
ResultSet set2=statement.executeQuery(sql2);

2013-10-22 0:41 记@sanliheludongkou.xichengqu.beijing