title: getting java.sql.SQLException: Closed Connection when accessing large resultset
body: <p>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.</p>
  
  <pre><code>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.&lt;init&gt;(PhysicalConnection.java:414)
      at oracle.jdbc.driver.T4CConnection.&lt;init&gt;(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
  </code></pre>
  
  <p>My java code is here:-</p>
  
  <pre><code>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&lt;Long&gt; list = new ArrayList&lt;Long&gt;();
  
              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");
                  //BigDecimal geom_l = rs.getBigDecimal("geom_length");
                  //String geom_l = rs.getString("geom_length");
                  //Long geom_length = Long.parseLong(rs.getString("geom_length"));
                  String pipe_matrl = rs.getString("pipe_matrl");
  
                  if(pipe_dia.equalsIgnoreCase("null")){
                      pipe_dia = "0";
  
                  }
  
                  //long geom_length = Long.parseLong(geom_l.trim());
  
                   //int pipe_diameter = Integer.parseInt(pipe_dia);
  
                  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 &lt; list.size(); i++){
                      sum = sum + list.get(i);
                  }
  
  
  
              return sum;
          }
  
  
  
      }
  </code></pre>
  
  <p>other class which perform calculations:-</p>
  
  <pre><code>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 int drain_rock_cost;
      public int backfill_cost;
      public int asphalt_cost;
      public double shoring_cost;
      public int dumping_cost;
      public int fabric_cost;
      public double labor_cost;
      public double steel_material_cost;
      public double pvc_material_cost;
      public double other_material_cost;
      public double pipe_material_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");
              drain_rock_cost = rs.getInt("DRAIN_ROCK_COST");
              backfill_cost = rs.getInt("BACKFILL_COST");
              asphalt_cost = rs.getInt("ASPHALT_COST");
              shoring_cost = rs.getDouble("SHORING_COST");
              dumping_cost = rs.getInt("DUMPING_COST");
              fabric_cost = rs.getInt("FABRIC_COST");
              labor_cost = rs.getDouble("LABOR_COST");
              steel_material_cost = rs.getDouble("STEEL_MATERIAL_COST");
              pvc_material_cost = rs.getDouble("PVC_MATERIAL_COST");
              other_material_cost = rs.getDouble("OTHER_MATERIAL_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 drain_rock_pricing = drain_rock_cost_fx(drain_rock_cost, drain_rock_depth, trench_width,geom_length);
  
              long backfill_pricing = backfill_cost_fx(backfill_cost, backfill_depth, trench_width, geom_length);
  
              long asphalt_installed_pricing = asphalt_cost_fx(asphalt_cost, asphalt_depth, trench_width, geom_length );
  
              long shoring_pricing = shoring_cost_fx(shoring_cost, geom_length, trench_depth);
  
              long dumping_pricing = dumping_cost_fx(dumping_cost, trench_volume);
  
              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 drain_rock_cost_fx(int drain_rock_cost, int drain_rock_depth, int trench_width,double geom_length){
  
          long cost = (long) (drain_rock_cost * (drain_rock_depth * trench_width * geom_length * (1.5525/27)));
  
          return cost;
      }
  
     public long backfill_cost_fx(int backfill_cost, int backfill_depth, int trench_width, double geom_length){
  
  
         long cost = (long) (backfill_cost * (backfill_depth * trench_width * geom_length * (1.5525/27)));
  
         return cost;
     }
  
     public long asphalt_cost_fx(int asphalt_cost, double asphalt_depth, int trench_width, double geom_length ){
  
         long cost = (long)(asphalt_cost * (asphalt_depth * trench_width * geom_length * (2025/27)));
         return cost;
     }
  
     public long shoring_cost_fx(double shoring_cost, double geom_length, int trench_depth){
  
        long cost = (long) (shoring_cost * (geom_length * trench_depth * 2));
        return cost;
     }
  
     public long dumping_cost_fx(int dumping_cost, long trench_volume){
  
         long cost = dumping_cost * (trench_volume / 27);
         return cost;
     }
  
     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; 
     }
  
  }
  </code></pre>
  
  <p>Connection manager class :--</p>
  
  <pre><code>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;
  }
     }
  </code></pre>
  
  <p>How can i handle large set of data? Please guide me with some solution.Thanks</p>