Re: Debugging my Connector/J based Tomcat Servlet

----- "Niklas Saers" <[email protected]> wrote:

> Hi again Roland, and thanks for your quick feedback,
> 
> On Jul 10, 2009, at 3:37 PM, Ronald Klop wrote:
> > Maybe a silly question (I don't know your skills), but do you close 
> 
> > all the ResultSets, Statements and Connections properly in your  
> > application after use? Or if you are using pooling is you pool  
> > closing everything right?
> 
> All ResultSets are sent a .close() after they have been used.
> 
> I only use PreparedStatements, and they are committed after execution 
> 
> of updates and inserts.
> 
> The connection is initialized in the servlets init() function from the
>  
> JDBC resource of type javax.sql.DataSource in the webapp config-file 
> 
> that defines the webapps context. It is closed in the destroy()  
> function.

That's not the best practice.  The servlet lifecycle methods init and destroy don't happen before and after every request and connection data (ResultSet, etc.) is tied to the connection.  If you are holding the same connection open for the entire lifetime of the servlet, across many requests, you will still have that data lingering.  What you want is a construct like this:

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {
    conn = ...;

    ps = ...;

    rs = ...;
} finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            ...;
        }
    }

    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) {
            ...;
        }
    }

    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            ...;
        }
    }
}

Since this is really boilerplate code, you're better off doing this with an aspect, a servlet Filter, or Spring.  Also, since you would be getting a new connection for every request, you would want to use some sort of pooling (e.g., commons-dbcp, c3p0, or the connection pooling method of whatever container you use).

-- 
Christopher G. Stach II



-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]