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?
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.
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?
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.
[ January 29, 2003: Message edited by: Peter den Haan ]
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.
[ January 30, 2003: Message edited by: Peter den Haan ]
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)?
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.