0

It is said that when we close connection object, the preparedstatement, resultset everything gets closed. In existing project code I found that PreparedStatement is not closed explicitly and also it is instantiated in try block and is not accessible in finally block where we have closed the Connection object. So will the rule that closing connection object will close the preparedstatement as well still applies here?

Help will be appreciated :)

Note- I do know that closing Preparedstatement explicitly is good practice from clean up point of view.

We have code where preparedStatement is not closed explicitly. PreparedStatement is instantiated in try block and is not accessible in finally block. We are closing connection object in finally block. ideally we expect all resources to get closed once connection object is closed. But since preparedstatement is not even accessible in finally block, will the rule still remains same?

Divya
  • 3
  • 2
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jul 03 '23 at 18:53
  • Closing the connection closes all resources that have been allocated from it. This includes `Statement`, `PreparedStatement`, and `ResultSet`. This is clearly stated in the Javadoc. However you should use try-with-resources to ensure that the resources are closed ASAP rather than deferring it all until `Connection.close()`. This is kinder to the database server, to your local computer, and to your own JVM. – user207421 Jul 04 '23 at 01:01

1 Answers1

-2

I found that PreparedStatement is not closed explicitly and also it is instantiated in try block and is not accessible in finally block where we have closed the Connection object. So will the rule that closing connection object will close the preparedstatement as well still applies here?

The prepared statement in the database will be cleaned up no later than when the database connection with which it is associated is closed. This is a matter of the database's own resource management.

Just like any other object, the PreparedStatement instance in the Java VM will be subject to garbage collection when it ceases to be accessible from any live thread. This is ordinary Java memory management.

But whether the close() method of the PreparedStatement will ever be invoked under the circumstances you describe is a function of the JDBC driver providing the Connection and PreparedStatement implementations. It is possible, for example, that the Connection maintains references to the PreparedStatement objects associated with it, and ensures that their close() methods are invoked when it is itself closed. It is also possible that the PreparedStatement implementation has a finalizer or a reference-queue-based cleanup mechanism that ensures that its close() method is invoked. But without something along these lines, no, the close() method will not be invoked if the reference is lost or abandoned first.

We have code where preparedStatement is not closed explicitly. PreparedStatement is instantiated in try block and is not accessible in finally block. We are closing connection object in finally block. ideally we expect all resources to get closed once connection object is closed. But since preparedstatement is not even accessible in finally block, will the rule still remains same?

To the extent that what you describe is a rule that you can rely on, as opposed to one that you are obligated to obey, yes, you can still rely on it despite there not being a reference to the PreparedStatement accessible in the finally block where the connection is closed. Having an accessible reference matters only if you want to do something that actually uses that reference. That doesn't apply here because you are not explicitly closing the PS (or doing anything else with its reference) anyway.

Overall, you should not be much concerned about a resource leak in the situation described. But if there were one, then that would best be characterized as a bug in the JDBC driver, not in your code, though that distinction might be lost on your application's users.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
  • Hi @JohnBollinger , thank for your answer. I still have a query that if PreparedStatement itself is instantiated in Try block and not accessible in finally block will get closed and be accessible to the connection object that is getting closed in finally block? – Divya Jul 03 '23 at 19:28
  • 1
    Only a non-compliant JDBC driver would fail to release all database and JDBC resources on closing the connection. – user207421 Jul 04 '23 at 01:04
  • Yes, @user207421, I believe that's exactly what this answer says. – John Bollinger Jul 04 '23 at 01:59
  • @Divya, you seem to be restating the original question. This answer already responds to it. It does not matter in what scope the `PreparedStatement` was instantiated, or whether a reference to it is still reachable or is in scope at the point where the connection is closed. Closure of the connection can be relied upon to clean up all database resources that are still held at that point. It may or may not actually cause the PS's `close()` method to be invoked, but it will nevertheless transition that object into the closed state. – John Bollinger Jul 04 '23 at 02:11
  • Thanks for your valuable inputs. @JohnBollinger Actually our application uses Oracle weblogic servers and we are getting "Server overload issue" with all connections of datasource being used up. We get this error on random days andon 2 specific timings. We checked everything and are not sure exactly what can cause this. – Divya Jul 06 '23 at 06:26
  • @Divya, `PreparedStatement`s are associated with an existing connection. They do not have their own connection, nor do they cause the connection with which they are associated to be held open longer than it ordinarily would. That is: if you are running out of connections, then the details of the application's use of `PreparedStatement`s are not responsible. – John Bollinger Jul 06 '23 at 11:11
  • @Divya, more likely would be (i) the application is leaking connections by failing to close them when they are no longer needed, or (ii) under peak load, the application simply needs more simultaneous connections than the `DataSource` is configured to provide, or than the DB is configured to support. – John Bollinger Jul 06 '23 at 11:14
  • Thanks @JohnBollinger I have added more details about this issue in below Question link- https://stackoverflow.com/questions/76626426/weblogic-application-server-goes-in-overload-state-at-2-specific-timings Can you please tak a look once? – Divya Jul 06 '23 at 13:37
  • @JohnBollinger Also, we have checked from the application end for connection leaks, the conn object is closed everywhere. The weird part is it happens on random days but specific time. – Divya Jul 06 '23 at 13:53
  • @Divya, if connections are always closed as soon as they are no longer needed, yet you are still running out of connections, then it follows that in its current form, the application sometimes requires more connections than `DataSource` allows. Possibly that reflects a flaw in the application, but possibly it just needs more resources than you are allowing it to use. To distinguish between these cases, you should look into how the connections are being used at the time that the error occurs. – John Bollinger Jul 06 '23 at 14:00
  • @JohnBollinger Thanks. Yes, during overload issue, I did check the connections I could see "Active connection high count" "Active connections current count" both as 15. which is Max connection value set by us =15. – Divya Jul 06 '23 at 14:17
  • Yes, @Divya, you already said that. I said "look *how* the connections are being used". That is, what your application is doing with them. It might also be enlightening to check whether they are each being used by a different thread. – John Bollinger Jul 06 '23 at 14:23