Basic DB connection pool with Java and Tomcat 7

Question

I'm trying to create my first connection pool. I'm creating a Java web aplication with Tomcat 7 and a MySQL DB, and I'd like to create the simplest connection pool possible. I've taken a look at several tutorials but it's not really clear for me, so I'd like you to confirm if I'm doing well.

I've written the following class as a connection pool manager:

package dao.mysql;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class MySQLConnectionPool {

    private static DataSource datasource;
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/mydb";
    private static String username = "user";
    private static String password = "password";

    public MySQLConnectionPool() {
        datasource = new DataSource(configurePoolProperties(driver, url, username, password));
    }

    private PoolProperties configurePoolProperties(String driver, String url, String username, String password) {
        PoolProperties properties = new PoolProperties();
        properties.setDriverClassName(driver);
        properties.setUrl(url);
        properties.setUsername(username);
        properties.setPassword(password);
        return properties;
    } 

    public static synchronized Connection getConnection() {
        Connection connection = null;
        try {
            connection = datasource.getConnection();
        } catch (SQLException ex) {
            System.out.println("Error while getting a connection from the pool! \nSQL state:" + ex.getSQLState() + "\nMESSAGE" + ex.getMessage());
        }
        return connection;
    }
}

I'm not sure about the static properties nor the synchronized.

And I'm not sure about the "client" classes of the pool. I understand they have only to get a connection using

Connection con = MySQLConnectionPool.getConnection();

and finally close this connection using

con.close();

And that's it? And also, is there any simpler or better way to do this?

Thanks very much!

score 18 · Accepted Answer · answered Dec 09 '12 at 22:32

18

This is the wrong way to do it.

Tomcat already has a connection pool and you can configure and setup without any code through the context.xml in the conf directory.

Once it is defined there, all you need to do is to lookup the JNDI DataSource in your code. Hardcoding all that (and re-inventing the wheel) is a very bad idea.

To learn how to configure a JNDI DataSource check out the manual: http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html

The Tomcat manual also has an example on how to obtain a connection from the pool:

InitialContext cxt = new InitialContext();
DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/dsname" );

where dsname is the name you provided in the context.xml

answered Dec 09 '12 at 22:32

A DAO does not *manage* a DataSource, it simply *uses* it. You might also want to read the JavaEE tutorial: http://docs.oracle.com/javaee/6/tutorial/doc/bncjj.html – Dec 09 '12 at 22:45
If the connection pool is configurated as JNDI DataSource in Tomcat and the factory attribute of the Resource is `org.apache.tomcat.jdbc.pool.DataSourceFactory`, what is the DataSource type when accessing the datasource from a class java? I use `org.apache.tomcat.jdbc.pool.DataSource`, or it should be `javax.sql.DataSource` (the latter is the value of the `type` attribute of the Resource)? – Sefran2 Feb 07 '13 at 11:48

score 1 · Answer 2 · answered Dec 09 '12 at 22:32

1

Check out the JNDI Datasource HOW-TO and the Tomcat JDBC Connection Pool Tomcat documentation. Letting Tomcat do it is preferable especially since it avoids class loader leaks.

answered Dec 09 '12 at 22:32

Philippe Marschall

4,452
1
34
52

Basic DB connection pool with Java and Tomcat 7

2 Answers2

Linked