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._jsp
Service(__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
- testQery.rar (1.7 KB)
- 下载次数: 0
相关文章
