I'd like to re-open this thread for discussion. IMO, following is what has been proposed so far:
1. Use connection pooling. 2. Use a singleton class as connection manager OR (if your app server supports it) 1. Configure JNDI datasource 2. Configure connection pool manager There have been repeated discussions on the best way to close connection to ensure all resources are freed up properly. There is also that issue with Oracle implicit cursors not being closed unless you explicitly close the statement. Instead of 'making sure' to close connection in the finally block (which I believe is a repitition of code anyway), can we not have a design strategy that does this automatically for us? Here's what I suggest (apart from the steps listed above): 1. Create a 'gateway' or 'adapter' class that interfaces with the connection manager (read: calls the getConnection(), freeConnection() and other DB interaction methods) with member variables for statement and resultset. 2. This 'gateway' or 'adapter' exposes methods to perform generic database methods (select, insert, update, delete) - you'd as it always use a standard gateway for all your entities to use instead of all entities talking to the database, thereby splattering around the persistence code all over the application. 3. The destructor of this gateway closes the statement as well as the resultset. So, you have a domain model (or table gateway, row gateway or whatever object-relational mapping you chose), your database code is at one single place, your connections are being managed and best, you don't have to write a single additional line to ensure you don't exceed the MAX_CURSORS (unless of course your methods take too long to complete and the load on your application is extremely high - in which case you'd have to increase the cursors anyway, no matter what you choose). One of the pitfalls I see of this design is higher object creation/destruction, but your statements/resultsets had to be closed anyway so you just have 4 additional bytes being used on the stack. Comments? Cheers, Manav. ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
