• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement Resource leak

 
Nojin Kokkatt
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
     

I know the safe pattern in Java is to close ResultSet, PreparedStatement, and Connection in order in a finally block.But I want to know what happened when we set null for ResultSet and PreparedStatement object in between two query execution. I highlighted the code that I set as null. Is this cause any resource leakage ?

 
Stephan van Hulst
Bartender
Pie
Posts: 6503
83
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to CodeRanch!

Yes. Statements and result sets should be closed as soon as you're done with them. By nulling them out before closing them, you won't be able to create new statements until the garbage collector collects the old ones.

The leak is not permanent, but it's bad anyway.
 
Nojin Kokkatt
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Stephan for your quick response . But I have one more doubt , if the connection parameter close  is it automatically close the Statement and result set ?
 
Ganesh Patekar
Bartender
Posts: 696
23
Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Oracle Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nojin Kokkatt wrote: if the connection parameter close  is it automatically close the Statement and result set ?

Java 8 SE Doc wrote:void close() throws SQLException
Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
  • Yes it closes Statement and ResultSet automatically but it is recommended to close them explicitly because when you close them immediately it makes them eligible for GC so saves memory.
  • Some implementation just returns Connection to Connection pool on close() method so sometime resources may not be freed so better close it explicitly.
  • Closing Statement object also closes ResultSet object
  • Java 8 SE Doc wrote:Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed.
  • It is good practice to close them in this order ResultSet, Statement and Connection using try catch and finally block.
  • If you are using Java 7 or later then you can use try with resources to create Connection, Statement and ResultSet objects in this order so they are always closed in reverse order i.e. ResultSet, Statement and Connection.
  • Example:
     
    Dave Tolls
    Ranch Hand
    Posts: 2112
    16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Stephan van Hulst wrote:By nulling them out before closing them, you won't be able to create new statements until the garbage collector collects the old ones.


    I can't think of anything about a connection that would prevent you from creating a new Statement just because you've null'd a previous reference.

    Stephan van Hulst wrote:
    The leak is not permanent, but it's bad anyway.


    And when you've had to track down your first ORA-1000 (Max open cursors exceeded), or your database's equivalent, you'll know why it's bad.
    They're a pain to solve, especially these days when they're most likely encountered with old code.
     
    Stephan van Hulst
    Bartender
    Pie
    Posts: 6503
    83
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Ganesh Patekar wrote:Yes it closes Statement and ResultSet automatically but it is recommended to close them explicitly because when you close them immediately it makes them eligible for GC so saves memory.

    As far as I know, closing a connection does not close statements or result sets. Closing a statement does close its result sets.

    I believe this is the reason why one could sometimes end up with an "Maximum open cursors exceeded": People close the connections nicely, but they forget about the result sets.

    Dave Tolls wrote:I can't think of anything about a connection that would prevent you from creating a new Statement just because you've null'd a previous reference.

    Maybe I'm misremembering, but I was under the impression that it's possible to have a maximum amount of open statements you could have per connection. It could be that I'm confusing it with result sets.

    And when you've had to track down your first ORA-1000 (Max open cursors exceeded), or your database's equivalent, you'll know why it's bad.

    I think this is what I was referring to.
     
    Dave Tolls
    Ranch Hand
    Posts: 2112
    16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Stephan van Hulst wrote:[
    I think this is what I was referring to.


    That limit (and it does include Statements) is in the 100s by default, and usually 1000s for a properly set up system.
    So yes, nulling will (eventually) cause it to run out.

    It's why it can be hard to trace as it can hide until live, and where the error occurs is not necessarily the culprit.
     
    Ganesh Patekar
    Bartender
    Posts: 696
    23
    Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Oracle Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Stephan van Hulst wrote:As far as I know, closing a connection does not close statements or result sets.
    but I found this here in Java 8 doc link which says
    Java 8 SE Doc wrote:void close() throws SQLException
    Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
    What precisely do they mean by this? may be I misunderstood that
     
    Paul Clapham
    Sheriff
    Posts: 21586
    33
    Eclipse IDE Firefox Browser MySQL Database
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Yes, I saw that and I couldn't guess what "JDBC resources" meant in the context of a JDBC connection.

    But here's a suggestion: the Statement interface includes an "isClosed" method. So why don't you build a little test which creates a Connection and then a Statement. Close the Connection and then see if the Statement was closed by that action or not. And let us know what you find out.
     
    Ganesh Patekar
    Bartender
    Posts: 696
    23
    Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Oracle Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Apology, didn't notice your reply early. Yes that was really a good idea. Here it is what I tried, please correct me If I go wrong somewhere.
    I know too large lines, cause of printing proper messages please hide categories to see properly.
    Output:
    Connection is established successfully!
    // Records from table
    100
    Ganesh
    25
    101
    Robin
    40
    // upto here
    Connection is closed successfully!
    Statement is also closed as soon as Connection is closed
    ResultSet is also closed as soon as Connection is closed
    conclusion:
  • Output proves that closing Connection closes Statement and ResultSet for sure.
  • So perhaps ResultSet and Statement are JDBC resources according to doc.
  •  
    Stephan van Hulst
    Bartender
    Pie
    Posts: 6503
    83
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Ganesh Patekar wrote:conclusion:
  • Output proves that closing Connection closes Statement and ResultSet for sure.
  • So perhaps ResultSet and Statement are JDBC resources according to doc.

  • It may very well depend on the database driver though. To be certain, I think it's good practice to always properly close your statements and result sets separately from the connection.
     
    Ganesh Patekar
    Bartender
    Posts: 696
    23
    Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Oracle Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Stephan van Hulst wrote:It may very well depend on the database driver though. To be certain, I think it's good practice to always properly close your statements and result sets separately from the connection.
    Yes some implementation returns Connection to Connection pool on close() method. Completely Agreed
     
    Stephan van Hulst
    Bartender
    Pie
    Posts: 6503
    83
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    No, I'm talking about the relation between connections and statements. It could be that some drivers don't close statements if you close the connection, even if the connection is not merely returned to the pool.
     
    Ganesh Patekar
    Bartender
    Posts: 696
    23
    Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Oracle Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Stephan van Hulst wrote:No, I'm talking about the relation between connections and statements. It could be that some drivers don't close statements if you close the connection, even if the connection is not merely returned to the pool.
    Ohh, I see. Thank you for clearing my confusion
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic