Results 1 to 14 of 14
  1. #1
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default Will the connection.close() and statement.close() ever be called???

    I have a code and question on it..
    Java Code:
    import java.sql.*;
    
    public class ResultSet_lastrow {
    
        public static int last_row() {
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oracle9i", "rms", "stephen00.rms");
                statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    
                resultSet = statement.executeQuery("select * from rms_accountstore");
                resultSet.last();
                [COLOR="Red"]return resultSet.getRow();[/COLOR]
    
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            } finally {
                try {
                    statement.close();
                    connection.close();
                } catch (Exception exception) {
                    exception.printStackTrace();
                    System.exit(1);
                }
            }
            return 0;
        }
    
        public static void main(String[] args) {
            ResultSet_lastrow r = new ResultSet_lastrow();
            System.out.println(ResultSet_lastrow.last_row());
        }
    }
    The colored portion of the snippet returns a value.. I have a question that whether the connection.close() and statement.close() methods will ever be called???

    i mean the return statements pass the control to the calling methods then in that cases what will be the exact behavior of this program. please suggest
    The Quieter you become the more you are able to hear !

  2. #2
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    OR this code will be better !!

    Java Code:
    import java.sql.*;
    
    public class ResultSet_lastrow {
    
        public static int last_row() {
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oracle9i", "rms", "stephen00.rms");
                statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    
                resultSet = statement.executeQuery("select * from rms_accountstore");
                resultSet.last();
    
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            } finally {
                try {
                    statement.close();
                    connection.close();
                } catch (Exception exception) {
                    exception.printStackTrace();
                    System.exit(1);
                }
            }
            try {
                [COLOR="Red"]return resultSet.getRow();[/COLOR]
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
            return 0;
        }
    
        public static void main(String[] args) {
            ResultSet_lastrow r = new ResultSet_lastrow();
            System.out.println(ResultSet_lastrow.last_row());
        }
    }
    :rolleyes:
    The Quieter you become the more you are able to hear !

  3. #3
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    I believe it will close automatically with the return but surely this depends on the nature of the variables and objects used and won't always be the case (global vs local).

    If you are going to return first then the code beneath your return won't execute. (this is the case with the code in your first post).

    As a habit I always close before returning a value though just to be safe as there really is no harm in it.
    Last edited by porchrat; 04-08-2010 at 10:09 PM.

  4. #4
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    As a habit I always close before returning a value though just to be safe as there really is no harm in it.
    SO you go by the code i gave in my reply #2.

    but surely this depends on the nature of the variables and objects used and won't always be the case (global vs local)
    I didn't got this part. plz elaborate.thnx
    The Quieter you become the more you are able to hear !

  5. #5
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by Stephen Douglas View Post
    SO you go by the code i gave in my reply #2.
    Well yeah it is far better the code in post 1 will still close it because of the return, but the actual close() method is not going to be executed. It is just that when the method is returned from that particular local variable/object is removed from memory which is essentially "closed".

    I didn't got this part. plz elaborate.thnx
    Well say for example your Statement object were global (it isn't in your example, but it could be for some reason), at that point unless you actually closed it at some point then even after your method returned a result it would remain open.

    For this reason and to avoid the potential confusion I close it as soon as I am done getting the information required from the ResultSet.

    Also I often write code that requires the use of the information from one query in order to run another query. So I can have multiple Statement objects in the same method. To avoid trying to use a Statement before it is closed elsewhere I close it as soon as possible.
    Last edited by porchrat; 04-08-2010 at 10:26 PM.

  6. #6
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    Is there any way we can detect that the connection is still open?? :D
    The Quieter you become the more you are able to hear !

  7. #7
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    Well yeah it is far better the code in post 1 will still close it because of the return, but the actual close() method is not going to be executed. It is just that when the method is returned from that particular local variable/object is removed from memory which is essentially "closed".
    Well say for example your Statement object were global (it isn't in your example, but it could be for some reason), at that point unless you actually closed it at some point then even after your method returned a result it would remain open.
    Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

    And that in the case when these variables are global... the connection will not be closed !!
    The Quieter you become the more you are able to hear !

  8. #8
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by Stephen Douglas View Post
    Is there any way we can detect that the connection is still open?? :D
    Statement has an isClosed() method.

    Will return true if Statement object is closed and false if it is still open.

  9. #9
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by Stephen Douglas View Post
    Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

    And that in the case when these variables are global... the connection will not be closed !!
    Yea that is kind of the nature of java.

    Local is only valid within the context of the method that creates it and only within that particular invocation of the method. All local variables, objects etc. created during the execution of the method will be freed when the method returns. This includes the Statement object.

    Not so with global variables and objects. Once a global variable or object is influenced by a method the return of that method won't all of a sudden cause the global variable or object to be freed. So a Statement object created outside the method shouldn't close automatically just because that method returns.
    Last edited by porchrat; 04-08-2010 at 10:48 PM.

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

    Thumbs down

    Quote Originally Posted by Stephen Douglas View Post
    Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

    And that in the case when these variables are global... the connection will not be closed !!
    In your example they will be closed because you have them in a finally block, which is always called when execution leaves a try{} block.

    However, you cannot say that the connection or statement will be closed without these. That is a false assumption. Yes, there maybe some something in a finaliser to close them off, but there is no guarantee when that will be run, so you will be leaking resources.

    The short and simple rule is your JDBC code should have the format:

    Java Code:
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        // Open stuff, get results, turn those results into something useful.
    } catch (...whatever exceptions you want to catch...) {
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) { ...possibly log... }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException ex) { ...possibly log... }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) { ...possibly log... }
        }
    }
    The stuff in finally will possibly be a method in a utility class, since it's quite common code.

    Why all the try/catch in there? Because, if the ps.close() throws an exception you still want to be able to close the conn.

    Why close the resultset there? Because you should close all resources, and not rely on the implementation to do it for you.

    And finally, since you seem to be getting the number of rows in this case, might I suggest a simple "SELECT count(*) FROM rms_accountstore"? It will be significantly faster, and gets you the data you want.

  11. #11
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    which is always called when execution leaves a try{} block.
    Which means the concept which i adopted last night is actually wrong !! is it???????
    thnx for clarification.

    I am sorry i didn't get this part..
    but there is no guarantee when that will be run, so you will be leaking resources.
    please elaborate. thnx
    The Quieter you become the more you are able to hear !

  12. #12
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,436
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by Stephen Douglas View Post
    Which means the concept which i adopted last night is actually wrong !! is it???????
    thnx for clarification.

    I am sorry i didn't get this part.. please elaborate. thnx
    You can't just leave a ResultSet, nor a Statement, nor a Connection be and forget all about it; if the garbage collector runs (if it runs) the stuff will be closed eventually but there is no guarantee that the garbage collector ever runs. You should close those things if you don't need them anymore; first, before you close them you should get the data you want and after that close them (e.g. see the API documentation for the ResultSet: you can't get any data from it after it has been closed).

    kind regards,

    Jos

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

    Default

    Indeed, there's actually no guarantee that even after garbage collection has collected a connection or whatever that that will actually close the physical resource in the db. I wouldn't be surprised to find that some of the drivers out there do not have a finaliser for their resources.

    You should never expect that sort of behaviour...always close what you open, and always do it in a finally block. There may be the odd exception to this, but it's a good start point.

  14. #14
    Stephen Douglas's Avatar
    Stephen Douglas is offline Senior Member
    Join Date
    Mar 2010
    Posts
    137
    Rep Power
    0

    Default

    Got it ! :) thnx
    The Quieter you become the more you are able to hear !

Similar Threads

  1. Can't close JFrame
    By dunafrothint in forum AWT / Swing
    Replies: 5
    Last Post: 12-16-2009, 05:00 PM
  2. How to Close Combobox.
    By ocean in forum New To Java
    Replies: 4
    Last Post: 11-17-2009, 04:50 PM
  3. close to zero
    By nokicky in forum New To Java
    Replies: 6
    Last Post: 10-26-2009, 04:30 PM
  4. [SOLVED] Close Exe
    By smartsubroto in forum New To Java
    Replies: 11
    Last Post: 10-14-2008, 08:04 AM
  5. How to close JDBC Connection
    By Heather in forum JDBC
    Replies: 2
    Last Post: 07-15-2007, 01:07 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
  •