Click here to Skip to main content
15,885,914 members
Search within:


getting java.sql.SQLException: Closed Connection when accessing large resultset
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

Java
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>


My java code is here:-

Java
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:-

Java
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 :--

Java
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;
}
   }
Posted
Updated 20-Apr-19 10:59am
Add a Solution

1 solution

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.
 
Share this answer
 
Posted
Comments
dhruv1707 26-Sep-11 14:35pm    
Thanks Marc, I'm new to java and jdbc. Can you suggest me more elaborately. Should I not close the connection for pipe_calculations.pipe_parameters_costing()? Is it wrong to have single connection manager? What should i use instead of static? can you please give me a brief snippet to help me understand. :)
Marc A. Brown 26-Sep-11 14:46pm    
You're quite welcome. If you're using transactions, you should probably avoid a shared connection since it would be possible for you to lump together unrelated stuff into a transaction if you're not careful (and lucky). If you really only need a single connection, you're doing it basically right. Closing the connection after use is also right; however, what you need to do is to somehow determine whether it is safe to close that connection before going ahead and doing it. One way would be to add a counter to the ConnectionManager class that increments whenever a connection is requested. Then you add a method to ConnectionManager to close the connection instead of using Connection.close() everywhere. This method would decrement the counter mentioned above, then close the connection (using con.close()) if the counter is zero. This works, but you have to be disciplined enough not to directly close your connections from anywhere else. This might not be the best way to do it, but it works.
Marc A. Brown 26-Sep-11 14:52pm    
A follow-up to my other comment. If you're dealing with transactions, you may want to research connection pooling.
dhruv1707 26-Sep-11 15:34pm    
Hey, thanks once again for your reply, really appreciated. I am trying to separate out all the connections so that there is no collision of connection manager. Probably this may work?
Marc A. Brown 26-Sep-11 15:38pm    
Having separate connections for each task should work. Good luck!
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Javax.servlet.servletexception: java.sql.sqlexception: exhausted resultset
Java jdbc records from console to database java.sql.sqlexception:
Can not access closed stream
Closing a connection
Ho to connect Microsoft access 2021 to a SQL server and create linked tables, using VBA.
Validation for resultset in javascript
How to Fix "Exception in thread "main" java.sql.SQLException: ORA-12505: TNS:listener does n ot currently know of SID given in connect descriptor" Error
Show large resultset from database into jtable with scroll pagination in java swing
Error when opening a SSL connection with httpclient
Force to close connection

Advertise
Privacy
Cookies
Terms of Use
Last Updated 13 May 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900