获取java.sql.SQLException：访问大型结果集时关闭连接 [英] getting java.sql.SQLException: Closed Connection when accessing large resultset
本文介绍了获取java.sql.SQLException：访问大型结果集时关闭连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好我的oracle 10g数据库中有大数据，并且必须对结果集的每一行执行一些计算。所以我在while（rs.next）循环中获取单行的值后调用一个单独的计算类。但这实际上给了我多个java.sql.SQLException：Closed Connection错误。它就像每次循环迭代这个消息都显示在控制台上。所以我每次都在JSP上获得不同的结果值。
我应该遵循哪种替代方法？请指导我。
Hello i have big data in my oracle 10g database and have to perform some calculations on every row of resultset. So i call a separate calculation class after fetching value of single row in the while(rs.next) loop. But this actually gives me multiple java.sql.SQLException: Closed Connection errors. Its like every time loop iterates this message is shown on console. So i get different result values every time on my JSP.
Is there any alternate approach which I should follow? Please guide me.
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:dir
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at asset.management.arms.loginmodule.ConnectionManager.getConnection(ConnectionManager.java:23)
at asset.management.arms.utilitiesreport.pipe_calculations.pipe_parameters_costing(pipe_calculations.java:49)
at asset.management.arms.utilitiesreport.Afline.afline_renwcost(Afline.java:55)
at asset.management.arms.utilitiesreport.UtilitiesDAO.utility(UtilitiesDAO.java:17)
at asset.management.arms.utilitiesreport.Utilitiesreportrequest.doPost(Utilitiesreportrequest.java:24)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: Closed Connection
</init></init>
我的java代码在这里： -
My java code is here:-
package asset.management.arms.utilitiesreport;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import asset.management.arms.loginmodule.ConnectionManager;
public class Afline {
Connection currentCon = null;
ResultSet rs = null;
Statement stmt = null;
public long afline_renwcost(){
long sum = 0;
ArrayList<long> list = new ArrayList<long>();
String sq="select pipe_dia, geom_length,pipe_matrl,status from sp_afline where status = 'ACTIVE'";
try{
currentCon = ConnectionManager.getConnection();
stmt=currentCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sq);
while(rs.next()){
String pipe_dia = rs.getString("pipe_dia");
double geom_length = rs.getDouble("geom_length");
String pipe_matrl = rs.getString("pipe_matrl");
if(pipe_dia.equalsIgnoreCase("null")){
pipe_dia = "0";
}
pipe_calculations pipe = new pipe_calculations(pipe_dia, geom_length,pipe_matrl);
list.add(pipe.pipe_parameters_costing());
}
}catch (Exception ex)
{
System.out.println(" " + ex);
}
finally
{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
for(int i=0; i < list.size(); i++){
sum = sum + list.get(i);
}
return sum;
}
}</long></long>
执行计算的其他类。我正在给这个课程的片段只是为了了解我正在做的事情： -
other class which perform calculations. I am giving snippet of this class just to get the idea of what i'm doing:-
package asset.management.arms.utilitiesreport;
import java.sql.*;
import asset.management.arms.loginmodule.ConnectionManager;
public class pipe_calculations {
public String pipe_dia = null;
public double geom_length = 0;
public String pipe_matrl = null;
public pipe_calculations(String pipe_dia, double geom_length, String pipe_matrl){
this.pipe_dia = pipe_dia;
this.geom_length = geom_length;
this.pipe_matrl = pipe_matrl;
}
Connection currentCon = null;
ResultSet rs = null;
Statement stmt = null;
public int trench_depth;
public double asphalt_depth;
public int drain_rock_depth;
public int excavation_cost;
public int dewatering_cost;
public long pipe_parameters_costing(){
long total_pipe_cost = 0;
String sq= "Select * from pipe_parameters_and_pricing";
try{
currentCon = ConnectionManager.getConnection();
stmt=currentCon.createStatement();
rs = stmt.executeQuery(sq);
while(rs.next()){
trench_depth = rs.getInt("TRENCH_DEPTH");
asphalt_depth = rs.getDouble("ASPHALT_DEPTH");
drain_rock_depth = rs.getInt("DRAIN_ROCK_DEPTH");
excavation_cost = rs.getInt("EXCAVATION_COST");
dewatering_cost = rs.getInt("DEWATERING_COST");
int trench_width = trench_width_fx(pipe_dia);
int backfill_depth = backfill_depth_fx(trench_depth,asphalt_depth,drain_rock_depth);
long trench_volume = trench_volume_fx(trench_width, trench_depth, geom_length);
long excavation_cost_pricing = excavation_cost_fx(excavation_cost, trench_volume);
long dewatering_pricing = dewatering_cost_fx(dewatering_cost,geom_length);
long fabric_pricing = fabric_cost_fx(fabric_cost, geom_length);
long dig_cost = excavation_cost_pricing + dewatering_pricing + drain_rock_pricing + backfill_pricing + asphalt_installed_pricing
+ shoring_pricing + dumping_pricing + fabric_pricing;
long labor_costing = labor_cost_fx(labor_cost,geom_length);
long material_cost = material_cost_fx(pipe_matrl,geom_length,steel_material_cost,pvc_material_cost,other_material_cost);
total_pipe_cost = (dig_cost + labor_costing + material_cost)/30;
}
}catch (Exception ex)
{
System.out.println(" " + ex);
}
finally
{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
return total_pipe_cost;
}
public int trench_width_fx(String pipe_dia){
int pipe_diameter = Integer.parseInt(pipe_dia);
int trench_width1 = pipe_diameter + 24;
return trench_width1;
}
public int backfill_depth_fx(int trench_depth, double asphalt_depth, int drain_rock_depth){
int backfill_depth1 = (int) (trench_depth - (asphalt_depth + drain_rock_depth));
return backfill_depth1;
}
public long trench_volume_fx(int trench_width, int trench_depth, double geom_length){
long trench_vol = (long) (trench_width * trench_depth * geom_length);
return trench_vol;
}
public long excavation_cost_fx(int excavation_cost, long trench_volume){
long excavation_cst = excavation_cost * (trench_volume / 27);
return excavation_cst;
}
public long dewatering_cost_fx(int dewatering_cost, double geom_length){
long dewatering = (long) (dewatering_cost * geom_length);
return dewatering;
}
public long fabric_cost_fx(int fabric_cost, double geom_length){
long cost = (long) (fabric_cost * geom_length);
return cost;
}
public long labor_cost_fx(double labor_cost, double geom_length){
long cost = (long) (labor_cost * geom_length);
return cost;
}
public long material_cost_fx(String pipe_matrl,double geom_length,double steel_material_cost, double pvc_material_cost, double other_material_cost){
long cost = 0;
if(pipe_matrl.equalsIgnoreCase("stl")){
cost = (long) (steel_material_cost * geom_length);
}
else if (pipe_matrl.equalsIgnoreCase("pvc")){
cost = (long) (pvc_material_cost * geom_length);
}
else{
cost = (long) (other_material_cost * geom_length);
}
return cost;
}
}
连接经理课： -
Connection manager class :--
package asset.management.arms.loginmodule;
import java.sql.*;
public class ConnectionManager {
static Connection con;
static String url;
public static Connection getConnection()
{
try
{
String url = "jdbc:oracle:thin:@localhost:1521:dir";
// assuming "DataSource" is your DataSource name
Class.forName("oracle.jdbc.driver.OracleDriver");
try
{
con = DriverManager.getConnection(url,"hr","hr");
}
catch (SQLException ex)
{
ex.printStackTrace();
}
}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
return con;
}
}
推荐答案
Http11ConnectionHandler.processConnection（Http11BaseProtocol.java： 665 ）
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket（PoolTcpEndpoint.java： 528 ）
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:< span class =code-digit> 81 ）
at org.apache.tomcat.util.threads .ThreadPool
Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81) at org.apache.tomcat.util.threads.ThreadPool
ControlRunnable.run（ThreadPool.java： 689 ）
at java.lang.Thread.run（未知来源）
java.sql.SQLException：已关闭连接
< / init>< / init>
ControlRunnable.run(ThreadPool.java:689) at java.lang.Thread.run(Unknown Source) java.sql.SQLException: Closed Connection </init></init>
我的java代码在这里： -
My java code is here:-
package asset.management.arms.utilitiesreport;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import asset.management.arms.loginmodule.ConnectionManager;
public class Afline {
Connection currentCon = null;
ResultSet rs = null;
Statement stmt = null;
public long afline_renwcost(){
long sum = 0;
ArrayList<long> list = new ArrayList<long>();
String sq="select pipe_dia, geom_length,pipe_matrl,status from sp_afline where status = 'ACTIVE'";
try{
currentCon = ConnectionManager.getConnection();
stmt=currentCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sq);
while(rs.next()){
String pipe_dia = rs.getString("pipe_dia");
double geom_length = rs.getDouble("geom_length");
String pipe_matrl = rs.getString("pipe_matrl");
if(pipe_dia.equalsIgnoreCase("null")){
pipe_dia = "0";
}
pipe_calculations pipe = new pipe_calculations(pipe_dia, geom_length,pipe_matrl);
list.add(pipe.pipe_parameters_costing());
}
}catch (Exception ex)
{
System.out.println(" " + ex);
}
finally
{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
for(int i=0; i < list.size(); i++){
sum = sum + list.get(i);
}
return sum;
}
}</long></long>
执行计算的其他类。我正在给这个课程的片段只是为了了解我正在做的事情： -
other class which perform calculations. I am giving snippet of this class just to get the idea of what i'm doing:-
package asset.management.arms.utilitiesreport;
import java.sql.*;
import asset.management.arms.loginmodule.ConnectionManager;
public class pipe_calculations {
public String pipe_dia = null;
public double geom_length = 0;
public String pipe_matrl = null;
public pipe_calculations(String pipe_dia, double geom_length, String pipe_matrl){
this.pipe_dia = pipe_dia;
this.geom_length = geom_length;
this.pipe_matrl = pipe_matrl;
}
Connection currentCon = null;
ResultSet rs = null;
Statement stmt = null;
public int trench_depth;
public double asphalt_depth;
public int drain_rock_depth;
public int excavation_cost;
public int dewatering_cost;
public long pipe_parameters_costing(){
long total_pipe_cost = 0;
String sq= "Select * from pipe_parameters_and_pricing";
try{
currentCon = ConnectionManager.getConnection();
stmt=currentCon.createStatement();
rs = stmt.executeQuery(sq);
while(rs.next()){
trench_depth = rs.getInt("TRENCH_DEPTH");
asphalt_depth = rs.getDouble("ASPHALT_DEPTH");
drain_rock_depth = rs.getInt("DRAIN_ROCK_DEPTH");
excavation_cost = rs.getInt("EXCAVATION_COST");
dewatering_cost = rs.getInt("DEWATERING_COST");
int trench_width = trench_width_fx(pipe_dia);
int backfill_depth = backfill_depth_fx(trench_depth,asphalt_depth,drain_rock_depth);
long trench_volume = trench_volume_fx(trench_width, trench_depth, geom_length);
long excavation_cost_pricing = excavation_cost_fx(excavation_cost, trench_volume);
long dewatering_pricing = dewatering_cost_fx(dewatering_cost,geom_length);
long fabric_pricing = fabric_cost_fx(fabric_cost, geom_length);
long dig_cost = excavation_cost_pricing + dewatering_pricing + drain_rock_pricing + backfill_pricing + asphalt_installed_pricing
+ shoring_pricing + dumping_pricing + fabric_pricing;
long labor_costing = labor_cost_fx(labor_cost,geom_length);
long material_cost = material_cost_fx(pipe_matrl,geom_length,steel_material_cost,pvc_material_cost,other_material_cost);
total_pipe_cost = (dig_cost + labor_costing + material_cost)/30;
}
}catch (Exception ex)
{
System.out.println(" " + ex);
}
finally
{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
return total_pipe_cost;
}
public int trench_width_fx(String pipe_dia){
int pipe_diameter = Integer.parseInt(pipe_dia);
int trench_width1 = pipe_diameter + 24;
return trench_width1;
}
public int backfill_depth_fx(int trench_depth, double asphalt_depth, int drain_rock_depth){
int backfill_depth1 = (int) (trench_depth - (asphalt_depth + drain_rock_depth));
return backfill_depth1;
}
public long trench_volume_fx(int trench_width, int trench_depth, double geom_length){
long trench_vol = (long) (trench_width * trench_depth * geom_length);
return trench_vol;
}
public long excavation_cost_fx(int excavation_cost, long trench_volume){
long excavation_cst = excavation_cost * (trench_volume / 27);
return excavation_cst;
}
public long dewatering_cost_fx(int dewatering_cost, double geom_length){
long dewatering = (long) (dewatering_cost * geom_length);
return dewatering;
}
public long fabric_cost_fx(int fabric_cost, double geom_length){
long cost = (long) (fabric_cost * geom_length);
return cost;
}
public long labor_cost_fx(double labor_cost, double geom_length){
long cost = (long) (labor_cost * geom_length);
return cost;
}
public long material_cost_fx(String pipe_matrl,double geom_length,double steel_material_cost, double pvc_material_cost, double other_material_cost){
long cost = 0;
if(pipe_matrl.equalsIgnoreCase("stl")){
cost = (long) (steel_material_cost * geom_length);
}
else if (pipe_matrl.equalsIgnoreCase("pvc")){
cost = (long) (pvc_material_cost * geom_length);
}
else{
cost = (long) (other_material_cost * geom_length);
}
return cost;
}
}
Connection manager class :--
Connection manager class :--
package asset.management.arms.loginmodule;
import java.sql.*;
public class ConnectionManager {
static Connection con;
static String url;
public static Connection getConnection()
{
try
{
String url = "jdbc:oracle:thin:@localhost:1521:dir";
// assuming "DataSource" is your DataSource name
Class.forName("oracle.jdbc.driver.OracleDriver");
try
{
con = DriverManager.getConnection(url,"hr","hr");
}
catch (SQLException ex)
{
ex.printStackTrace();
}
}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
return con;
}
}You’re closing the connection in pipe_calculations.pipe_parameters_costing(). Given that you’re sharing a single connection between all classes (the connection in ConnectionManager is declared static), you’re closing it for all objects.
You're closing the connection in pipe_calculations.pipe_parameters_costing(). Given that you're sharing a single connection between all classes (the connection in ConnectionManager is declared static), you're closing it for all objects.
这篇关于获取java.sql.SQLException：访问大型结果集时关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
