Why close the connection, statement and resultset?

[Renata fonseca]

I would like to understand why the close() method of PreparedStatement, Connection (??) and ResultSet must be called every time I use it...
Let's supose I have to connect to the database from my Servlet (ie. when a JSP page call the servlet). Ok, I'll create a Connection, a PreparedStatement and ResultSet. After using it, will I have to call the close() method of these objects?
I think it's not necessary because after executing the servlet, the garbage collector will clear (destroy)automatically all object created. I'm not wrong, ok?

[Gregg Bolinger]

From what I understand, closing the Connetion Object also closes the Threaded Process on the DBMS. The DBMS will error check these threads most of the time and handle strays, but it is always better to close them when you are finished with them. It will help performance on the DBMS side.

[Peter den Haan]

Originally posted by Renata fonseca:
I would like to understand why the close() method of PreparedStatement, Connection (??) and ResultSet must be called every time I use it... [...] I think it's not necessary because after executing the servlet, the garbage collector will clear (destroy)automatically all object created. I'm not wrong, ok?

Yes, the garbage collector might eventually clean them up. Eventually. This may takea while, and if your application experiences a significant load the database will be brought to its knees for all the open connections that are waiting for the garbage collector to chance by.
And that's if you're not using a connection pool.
If you are using a connection pool, things get worse. Not closing your Connection will mean that it isn't returned to the pool. Your pool will empty itself in no time and, depending on how the pool handles things, the application will either act erratically with lots of delays and timeouts, or stop working altogether.
Should you close your connections but not always close your statements and cursors, then once again you are likely to eventually run out of database resources under load. Worse, in at least some implementations connections, statements and result sets keep strong references to each other which means they are never garbage collected if you're using a connection pool. Every failure to close is a resource leak which will eventually crash the system.
I'm speaking from practical experience. I've seen a public-facing production system collapse under moderate load because connections weren't always returned to the pool on fault conditions (and I'm relieved to say I didn't develop that system). I've seen an Oracle database run out of cursors after weeks of light load because the O/R mapper did not close statements and result sets in rare cases (I'm afraid I did develop that system). One of the most challenging and enjoyable jobs I did was the stats engine for the F.A. Premier League website. It is fully database-driven; you're querying an Oracle data warehouse (although there are two tiers of caching between you and the database). The system is scoped for 10 million page hits a month (30 million server hits) and was obviously heavily load tested. At loads like that, the slightest resource leak kills you almost instantly.
So you understand that from my point of view, yes, it's absolutely necessary. Even on small-scale, lightweight systems I think you should get into the habit of ensuring religiously that connections and statements are closed in every single code path, including exceptions. The finally clause is your best friend.
It may help to know that closing a statement also closes any open result set on that statement. So in many cases you can save yourself a line or two of code by not explicitly closing your ResultSet.
- Peter
[ January 29, 2003: Message edited by: Peter den Haan ]

[Renata fonseca]

THANKS!!!
it helped me a lot!
By the way, could you explain what's "connection pool"?

[Peter den Haan]

"Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. Accordingly, developers often wish to share a "pool" of open connections between all of the application's current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required. The application itself logs into the DBMS, and handles any user account issues internally." -- from the introduction to the Jakarta connection pool.
I can only add that a connection pool is a good idea for any thin-client database-driven application, not just for public-facing web sites. Most application servers have a connection pool built in; whenever you're using a DataSource, you are usually also using a connection pool.
- Peter
[ January 30, 2003: Message edited by: Peter den Haan ]

[Renata fonseca]

Thanks again.
So.. will I have to use Singleton pattern to create a Connection Pooling?
Do you know some image that represent the Connection Pooling graphically (by showing the database, pooling mannager, and so on)?

[Anonymous]

Originally posted by Peter den Haan:
Most application servers have a connection pool built in; whenever you're using a DataSource, you are usually also using a connection pool.

That one bit me a some time ago -- after implementing a pool, separate connection reaper threads, check alive methods and the whole shebang, I finally figured out that those connections (farmed out by the DataSource) were already pooled. Down the bitbucket with all that tedious typing, compiling and testing ... :roll:
The moral of the story: RTFM over and over again.
kind regards