How to use connection pool with Tomcat 6 and MySQL?

Question

i'm building a web application and i want to use "Connection Pooling" because of the benefits that came with it. I read some tutorials but i really don't understand what i need to do.

If someone could give me a north, i appreciate.

I'm using JSP/Servlet, MySQL, Tomcat 6 and Netbeans 6.9.1.

Best regards, Valter Henrique.

Answer 1

Have you read http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html#MySQL_DBCP_Example? It shows you all the steps to access your database from a web app.

If you need to access the database from withing Java code (much better than from JSP), your code should look like this :

InitialContext initCtx = new InitialContext();
// getting the datasource declared in web.xml
DataSource dataSource = (DataSource) initCtx.lookup("java:comp/env/jdbc/TestDB");

// getting a connection from the dataSOurce/connection pool
Connection c = null;
try {
    c = dataSource.getConnection();
    // use c to get some data
}
finally {
    // always close the connection in a finally block in order to give it back to the pool
    if (c != null) {
        try {
            c.close();
        }
        catch (SQLException e) {
            // not much to do except perhaps log the exception
        }
    }
}

Also, note that you should also close the resultsets and statements used inside the try block. See http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html#Random_Connection_Closed_Exceptions for a more complete example.