This really belongs in a JDBC mailing list, not the JSP mailing list.
See below for answers, however.
Daniel Rvnnqvist wrote:
> I have a question about database connections. Should I or shouldn't I close
> all resultsets, statements after I've used them ? I've read that it's very
> important to do this because it may slow down your server if you don't close
> them and let the GC handle it (or timeout). But if I do this, close all my
> resultsets and statements, the server slow down and eventually hangs. If I
> don't it runs fine most of the time and just slows down one in hundredth of
> the times it did before.
> I recently changed from JDBC-ODBC bridge to another driver and then it works
> smoother but it still hangs from time to time.
>
> Could someone give me some hints about the following things:
> - When should I close my resultsets, statements and connections in
> comparrison to commit (I use auto commit off) and each other ?
You should commit/rollback after your transaction's statements have
closed. A try/finally block facilitates this:
boolean completedOK = false;
Connection dbc = null;
Statement st = null;
try {
dbc =
ConnectionPool.checkout(ConnectionPool.MANUAL_COMMIT_MODE);
st = dbc.createStatement();
ResultSet rs = st.executeQuery(...);
while( rs.next() ) {
// process rows
}
rs.close();
st.executeUpdate(....);
completedOK = true;
}
finally {
if( st != null ) try {st.close()} catch(SQLException ignore) { }
if( dbc != null ) {
if( completedOK ) {
try {dbc.commit();} catch(SQLExeption ignore) { }
} else {
try {dbc.rollback();} catch(SQLException ignore) { }
}
ConnectionPool.checkin(dbc);
}
}
Since the finally block is mostly boilerplate code, you could implement
this as a static utility method.
> - Would a connection pool servlet solve this problem ? I've read that
> connection pooling is buildt in in JDBC 2.0.
A connection pool is essential when running in manual-commit mode to
maintain high performance (eliminating Connection establishment
overhead). One can write a simple one in about a hundred lines. Simply
implement a static method to check out and another one to check in. In
our homegrown connection pool, we perform a .rollback() on any
Connections checked back in to eliminate "dangling" transactions.
> - What about thread safetyness and databases ?
Section A.1.6 in [Hamilton 1997], which is as close exists to a JDBC 1.x
spec says that "All operations on java.sql objects are required to be
multithread safe... a statement execution in one thread should not block
an execution in another thread ... two Statement objects on the same
connection can be executed concurrently [...] from the perspective of
the developer. Some drivers will provide this full concurrency. Others
may execute one statement and wait until it completes before sending the
next one.
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
