Question:
The Java servlet gets a Connection with the SQL database on every call (Oracle oracle.jdbc.driver.OracleDriver using DriverManager.getConnection() ), then creates a Statement and ResultSet . Everything is in local variables. Obviously (they write about this everywhere) that Statement and ResultSet must be closed.
Question: will there be a resource leak if the ResultSet or Statement not closed ?
UPD (in response to answers).
So, Oracle XE development, respectively, the version is free, limited (but so much!)
First of all, I closed Statement and ResultSet in normal mode, but for Exception(s) limited myself to closing Connection in try { ... } finally { ... }
Therefore, for testing, I first threw out Statement and ResultSet close() and in a continuous loop on 77 connection everything fell, but fell due to ORA-12519 . Can't OPEN a new connection !!! Those times, I'm closing them! Returned all close() – same thing. After all, I didn’t run queries in a continuous loop, I couldn’t imagine such a prank!
Trying a loop with one connection (when closing Statement and ResultSet ) everything works (1,000,000 loops). If not closed, it first falls by ResultSet , then by Statement .
I read about ORA-12519 on the network, increased resources ( alter system set processes=150 scope=spfile; it helped someone), the drop was pushed back to 230 connections and that's it.
Then I subtracted it, it turned out that the fact is that the number of connections per unit of time is limited . Did 100 per second – it goes, then falls. Now it goes from 50 per second (cycle 100000 after every hundred open-closed sleep(2000) ) – YES – has passed.
So thanks everyone !!!
UPD-2
I was looking for the optimal latency (when trying to open a new connection again) it turned out to be 350 milliseconds – for 1000 cycles I have a total latency in the worst case of 6.3 s. For some reason she floats, there is no repeatability. For other delays (50ms to 3s), the overall time is worse.
Does anyone have any ideas on how to properly organize such measurements?
UPD-3
Oracle is a mystery. Tried at work with "combat" 11.g Enterprise Edition on the server.
It is performed every 10 times slower, so I drove up to 10,000 cycles. No glitches! It doesn't matter if I close the Statement and ResultSet or not. Everything works here ( without any sleep() ), although the driver in the program is the same from XE Development.
Does anyone have any idea??? How to properly program the interaction with the database?
Answer:
The JDBC specification recommends that you always manually close everything. However, it is argued that
all Statement objects will be closed when the connection that created them is closed
and what
closing a Statement object will close and invalidate any instances of ResultSet produced by that Statement object
those. closing a connection should close all spawned objects hierarchically. However, the authors, apparently, decided to play it safe and protect you from cases when the "should" in the driver implementation flows into "we will try" – for technical reasons or due to inattention. Apparently, a specification is a specification, but in practice "anything can be" (no matter how ridiculous it may sound) – so it's better to close it yourself.
Personally, it does not seem that in the driver of Orakla it will still be OK with automatic closing, but when switching to another database there may be surprises.