Page 1 of 2 12 LastLast
Results 1 to 20 of 36
  1. #1
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default How to close resultset and statement in a finally when called in many functions?

    We have the following code snippet. We initialize each statement and resultset and close it immediately after using it with the try and catch. The problem is that we cant use one finally to close all the statement and resultset as we need to commit all the statement not just in this function run but those in function1 and function2. Thus if any one is having problem it throws the exception and in the function run we run a try and exception. So in the exception we do a dbconn.rollback(); . We have checked through all the resultset and statement have been closed but yet in the jmap we notice the total resultset keep increasing. What could be the issue? We do close the connection even though its a pooling but that we dont expect to close the statement or resultset.


    Java Code:
    BoneCP connectionPool = null;
      class ConnectionHandler implements Runnable {
        Connection dbconn = null;
        public void run() { // etc
         BufferedWriter writeBuffer = null;
         BufferedReader readBuffer = null;
         String capturedMessage="";
         try{
            dbconn = connectionPool.getConnection();
            dbconn.setAutoCommit(false);
            while ((nextChar=readBuffer.read()) != -1){          
              capturedMessage += (char) nextChar;
              if (nextChar == '*'){
               try{
    
                    ///For select we do this  
                    Statement stmt2 = null;
                    stmt2 = dbconn.createStatement(); 
                    String selectQuery2= .........
                    ResultSet rs2 = stmt2.executeQuery(selectQuery2);
                    if(rs2.next()){
                    }
                    try{
                     if ( rs2!= null ){  
                         rs2.close();
                     }   else{
                     System.out.println("No rs2 exist");
                     }
                     if ( stmt2!= null ){ 
                          stmt2.close();
                     }   else{
                     System.out.println("No stm2 exist");
                     }
                    }catch(SQLException ex)
                    {   
                    System.out.println("SQLException has been caught for stmt2");
                    ex.printStackTrace(System.out);
                    }
                    funct1();
                    funct2();
                    dbconn.commit
               }
               catch (SQLException ex){
                    ex.printStackTrace(System.out);
                     try{  
                        dbconn.rollback();
                     }
                    catch (Exception rollback){  
                       rollback.printStackTrace(System.out);
                    }
               }
               catch (Exception e){
                   e.printStackTrace(System.out);
                   try{  
                      dbconn.rollback();
                   }
                   catch (Exception rollback){  
                      rollback.printStackTrace(System.out);
                   }
               }
               finally{
               }
         }
         catch (SocketTimeoutException ex){
               ex.printStackTrace();
         }
         catch (IOException ex){
               ex.printStackTrace();
         }
         catch (Exception ex){
               ex.printStackTrace(System.out);
         }    
         finally{
            try{
             if ( dbconn != null ){
               dbconn.close();
             }
             else{
              System.out.println("dbConn is null in finally close");
             }
            }
            catch(SQLException ex){
                ex.printStackTrace();
            }
            try{
              if ( writeBuffer != null ){
                writeBuffer.close();
                //new changes added but no effect.
                readBuffer = null; 
                writeBuffer =null;
                receivedSocketConn1=null;
              }
              else{
                System.out.println("writeBuffer is null in finally close");
              }
            }
            catch(IOException ex){
                ex.printStackTrace(System.out);
            }
           }
          }
    
          void funct1() throws Exception
          {
                    ///For select we do this  
                    Statement stmt16 = null;
                    stmt16 = dbconn.createStatement(); 
                    String selectQuery16= .........
                    ResultSet rs16 = stmt16.executeQuery(selectQuery16);
                    if(rs16.next()){
                    }
                    try{
                     if ( rs16!= null ){  
                         rs16.close();
                     }   
                     else{
                         System.out.println("No rs16exist");
                     }
                     if ( stmt16 != null ){ 
                          stmt16 .close();
                     }   
                     else{
                         System.out.println("No stmt16 exist");
                     }
                    }
                    catch(SQLException ex){   
                        System.out.println("SQLException has been caught for stmt16 ");
                        ex.printStackTrace(System.out);
                    }
    
          }
          void funct2() throws Exception
          {
                    ///For select we do this  
                    Statement stmt18 = null;
                    stmt18 = dbconn.createStatement(); 
                    String selectQuery18= .........
                    ResultSet rs18= stmt18.executeQuery(selectQuery18);
                    if(rs18.next()){
                    }
                    try{
                     if ( rs18!= null ){  
                         rs18.close();
                     }   
                     else{
                        System.out.println("No rs18exist");
                     }
                     if ( stmt18 != null ){ 
                          stmt18 .close();
                     }   
                     else{
                        System.out.println("No stmt18 exist");
                     }
                    }
                    catch(SQLException ex){   
                       System.out.println("SQLException has been caught for stmt18 ");
                       ex.printStackTrace(System.out);
                    }
    
            }
        }

  2. #2
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,758
    Rep Power
    5

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Closing the statements should close any resultset associated with it though, so not all statements are closed perhaps. And if I look at your code, the closure of the statements is not inside a finally clause so there is no guarantee that they are closed if an exception happens.

    You have a mountain of exception handling logic here, I would put some effort into reducing it to more expose hidden problems within it. For example, it pays to create utility safe close() methods like this:

    Java Code:
    public static void close(Statement stm){
      if(stm != null){
        try { stm.close(); } catch(Throwable t){}
      }
    }
    This way you don't have to do annoying repeating null-checks and SHOULD the close() of one statement fail for some reason, you're not going to create the highly unwanted situation of an exception being thrown from a finally clause.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Quote Originally Posted by gimbal2 View Post
    Closing the statements should close any resultset associated with it though, so not all statements are closed perhaps. And if I look at your code, the closure of the statements is not inside a finally clause so there is no guarantee that they are closed if an exception happens.

    You have a mountain of exception handling logic here, I would put some effort into reducing it to more expose hidden problems within it. For example, it pays to create utility safe close() methods like this:

    Java Code:
    public static void close(Statement stm){
      if(stm != null){
        try { stm.close(); } catch(Throwable t){}
      }
    }
    This way you don't have to do annoying repeating null-checks and SHOULD the close() of one statement fail for some reason, you're not going to create the highly unwanted situation of an exception being thrown from a finally clause.
    Actually, if you are already catching throwable there is no reason to check for null. ;-)

  4. #4
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,758
    Rep Power
    5

    Default Re: How to close resultset and statement in a finally when called in many functions?

    If I can prevent an exception from being thrown I will darnit!
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    I like the structure I was introduced to by EJP over at OTN:
    Java Code:
    Connection conn = null;
    try {
       conn = pool.getConnection();
       Statement s;
       try {
          s = conn.createStatement();
          ResultSet rs;
          try {
             rs = s.executeQuery("some query");
    etc etc.
          } finally {
             s.close();  // s cannot be null at this point.
          }
       } finally {
          conn.close();  // conn cannot be null at this point.
       }
    } catch (SQLException ex) {
    // log exception.
    }
    Removes the need for null checks (if I've got the above correct...:)).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,758
    Rep Power
    5

    Default Re: How to close resultset and statement in a finally when called in many functions?

    It is clever usage of try/finally, but I absolutely detest these nested trees of exception handling blocks, it is just an eye sore. I'd rather just do this:

    Java Code:
    Connection conn = null;
    Statement s1 = null;
    Statement s2 = null;
    
    try {
       conn = pool.getConnection();
       s1 = conn.createStatement();
       ResultSet rs1 = s1.executeQuery("some query");
    
       s2 = conn.createStatement();
       ResultSet rs2 = s2.executeQuery("some query");
    
       // do things and stuff with the resultsets
    
       rs1.close();
       rs2.close();
    
    } catch (SQLException ex) {
    // log exception.
    } finally {
      close(s1);
      close(s2); 
      close(conn);
    }
    Old fashioned style, I haven't really had a reason yet to use the new try-with-resource thingie of Java 7 which can probably strip it down even further :)
    Last edited by gimbal2; 11-28-2013 at 03:35 PM.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Quote Originally Posted by Tolls View Post
    I like the structure I was introduced to by EJP over at OTN:
    Java Code:
    Connection conn = null;
    try {
       conn = pool.getConnection();
       Statement s;
       try {
          s = conn.createStatement();
          ResultSet rs;
          try {
             rs = s.executeQuery("some query");
    etc etc.
          } finally {
             s.close();  // s cannot be null at this point.
          }
       } finally {
          conn.close();  // conn cannot be null at this point.
       }
    } catch (SQLException ex) {
    // log exception.
    }
    Removes the need for null checks (if I've got the above correct...:)).

    Yes, and that is about how I do it, with another try finally rs.close around the use of the rs.

    Of course, there is also no need for so much "external" declaration of the objects

    Java Code:
        try {
          Connection conn = pool.getConnection();
          try {
            Statement s = conn.createStatement();
            try {
              ResultSet rs = s.executeQuery("some query");
              try {
                // do something with the rs
              } finally { try { rs.close(); } catch (Throwable t) {} }
            } finally { try { s.close(); } catch (Throwable t) {} }
          } finally { try { conn.close(); } catch (Throwable t) {} }
        } catch (SQLException ex) {
          // log exception.
        }
    Edit: Of course I usually have the parts broken down into separate methods, but if you "flatten out" the method call chain, it leaves you with this.

    Edit Again: And, using the try with resources you can simply eliminate the finally blocks BUT that means that an SQLException on a close (which you can't do anything about anyway) will cause the block to exit.
    Last edited by masijade; 11-28-2013 at 03:41 PM.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Quote Originally Posted by masijade View Post
    Of course, there is also no need for so much "external" declaration of the objects
    Ha!
    I knew I'd get something wrong in my haste!
    :)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear All,
    I need to thank all of you guys so many solutions. I hope I am clear with my problem. The problem is that I do have many functions where in there it self so many queries are ran using the resultset and statement so that this why I have one big try and catch incase an exception everything is rollback that is very crucial for me. So what is the best solution cause I got many resultset and statement and currently I have checked many rounds all resultset and statement are closed using my current method. I am wondering why could there be any leakage with my current method of closing both resultset and statement?

    try {

    //many functions called with queries in it.
    }
    catch (SQLException ex){
    ex.printStackTrace(System.out);
    try{
    dbconn.rollback();
    }
    catch (Exception rollback){
    rollback.printStackTrace(System.out);
    }
    }
    catch (Exception e){
    e.printStackTrace(System.out);
    try{
    dbconn.rollback();
    }
    catch (Exception rollback){
    rollback.printStackTrace(System.out);
    }
    }

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Let's just look at this bit:
    Java Code:
               try{
                    ///For select we do this  
                    Statement stmt2 = null;
                    stmt2 = dbconn.createStatement(); 
                    String selectQuery2= .........
                    ResultSet rs2 = stmt2.executeQuery(selectQuery2);
    ... Snip bits ...
               }
               catch (SQLException ex){
                    ex.printStackTrace(System.out);
                     try{  
                        dbconn.rollback();
                     }
                    catch (Exception rollback){  
                       rollback.printStackTrace(System.out);
                    }
               }
    That stmt2 is never closed inside a finally block.
    If the above query throws an exception you will leak statements (and associated resultsets).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  11. #11
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,758
    Rep Power
    5

    Default Re: How to close resultset and statement in a finally when called in many functions?

    (didn't I already say that?)
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Quote Originally Posted by gimbal2 View Post
    (didn't I already say that?)
    I know.
    I just decided to give an example as it clearly hadn't sunk in...:)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  13. #13
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear Toll,
    I agree with your in depth explanation but in normal case if there is no exception then the try and catch where I do the closing as I snipped below should work right ? Ok back to the many solution what do you recommend best for my solution with few function dependency for the db to commit finally ? I want a solution which will really ensure no more leaks from resultset and statement and what is the best way to determine is it the jmap instances?

    try{
    if ( rs2!= null ){
    rs2.close();
    } else{
    System.out.println("No rs2 exist");
    }
    if ( stmt2!= null ){
    stmt2.close();
    } else{
    System.out.println("No stm2 exist");
    }
    }catch(SQLException ex)
    {
    System.out.println("SQLException has been caught for stmt2");
    ex.printStackTrace(System.out);
    }

  14. #14
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    The structure has been given above.
    You may as well go the traditional route and choose gimbal's one.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  15. #15
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear Tolls,
    But in my case if I use gimbal's finally method how am I going to close all those statement and resultset in the relevant function because those variables wont appear in the main function ? But If I call them immediately after using the statement like this close(stmt1) is just doing a null check like how I am doing now? So then it wont resolve the issue rite?

    finally {
    close(s1);
    close(s2);
    close(conn);
    }

  16. #16
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,856
    Rep Power
    19

    Default Re: How to close resultset and statement in a finally when called in many functions?

    funct1 and funct2 only have a single statement and result set each.
    So you can fix them easily.

    The block of code that calls them also only has a single one of each...so I don't see the problem with sorting that out properly either.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  17. #17
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,758
    Rep Power
    5

    Default Re: How to close resultset and statement in a finally when called in many functions?

    I completely and utterly do not understand any of what is being asked and replied at this point of the thread, if you'll allow me to be bluntly honest :/ In any case I don't know what more can be said about this subject to make it any more clear.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  18. #18
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear Tolls,
    For the sake of readability I didnt show everything I got more then 2 functions and each of it got minimum of 20 queries inclusive of select, insert and updates so roughlt I got more then 20 statements in each of the these functions. The block of code which calls them itself got close to 50 queries mix of select, insert and updates. I hope I am clearer now on the complexity which I am facing now. Sorry I could not show all the queries else no body will even read my codes.

  19. #19
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear Tolls and Gimbal,
    Hopefully I did not offend any one with my question I was just clarifying my problem.

  20. #20
    newbie14 is offline Member
    Join Date
    Feb 2010
    Posts
    63
    Rep Power
    0

    Default Re: How to close resultset and statement in a finally when called in many functions?

    Dear Masijade,
    I am trying to apply your method looks like if I dont close the resultset or statement via the finally it keep hanging. So just simplifying my current method of closing statement using a function like void close(Statement stm) does not solve the problem? How else can this be simplified via a function calling? How to create method for this? How am I going to check for this then if (nextChar == '*') because when I found '*' then I need to start processing?
    Last edited by newbie14; 12-02-2013 at 10:42 AM.

Page 1 of 2 12 LastLast

Similar Threads

  1. If Statement Never Gets Called (SUPER EASY!)
    By skaterboy987 in forum New To Java
    Replies: 3
    Last Post: 10-16-2011, 03:44 AM
  2. Replies: 1
    Last Post: 05-17-2011, 09:43 AM
  3. Replies: 2
    Last Post: 10-08-2010, 12:08 PM
  4. Replies: 13
    Last Post: 04-09-2010, 11:15 AM
  5. Replies: 2
    Last Post: 07-18-2008, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •