Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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>
 
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;
}
   }
Posted 26-Sep-11 8:21am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
dhruv1707 at 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 at 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 at 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 at 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 at 26-Sep-11 15:38pm
   
Having separate connections for each task should work. Good luck!

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 598
1 OriginalGriff 280
2 George Jonsson 258
3 CPallini 190
4 Animesh Datta 130
0 OriginalGriff 5,985
1 Sergey Alexandrovich Kryukov 5,341
2 CPallini 4,760
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 26 Sep 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100