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!

user330315 · Accepted Answer · 2012-12-09 22:32:46Z

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

A DAO does not manage a DataSource, it simply uses it. You might also want to read the JavaEE tutorial: docs.oracle.com/javaee/6/tutorial/doc/bncjj.html
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)?

Philippe Marschall · Accepted Answer · 2012-12-09 22:32:37Z

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.

Collectives™ on Stack Overflow

Basic DB connection pool with Java and Tomcat 7

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related