Java: Database connection pooling in Apache Tomcat

Database connection pooling is not a new concept.

Typically, one would want to use this idea into their application when handling database connections.

The old style font of making database connections from scratch, maintaining them and shutting them upon finishing the request may be a very expensive process and takes an important load on the server itself.

 

Apache Tomcat supports connection pooling.

One advantage of pooling is that it reduces the garbage collection load.

Using connections objects that are in the pool, we do not need to worry about memory leaks of sort.

Once we request a connection and shut it, the connection object are going to be sent back to the pool and await other requests.

Hence, we are literally getting to use existing connection objects.

If the pool has ran out of connection objects, it’ll create a replacement one and adds it up to the entire number of connections inside the pool.

To enable connection pooling in java, you only need to add this tag in the context.xml in the conf/ folder of apache tomcat’s working folder.

<Resource name=”jdbc/dbname”
auth=”Container”
type=”javax.sql.DataSource”
factory=”org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory”
username=”micmic”
password=”micmic”
driverClassName=”com.mysql.jdbc.Driver”
url=”jdbc:mysql://localhost:3306/dbname”
maxWait=”1000″
removeAbandoned=”true”
maxActive=”30″
maxIdle=”10″
removeAbandonedTimeout=”60″
logAbandoned=”true”/>

This tag must be inside the <context> tag.

To use this connection, you can have a method that looks similar to this

public static Connection getConnection() throws Exception {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup(“java:/comp/env”);
DataSource ds = (DataSource)envContext.lookup(“jdbc/dbname”);
ds.getConnection();
}

And use that same method to get a connection object from the pool:

Connection con = getConnection();

 

When you close this connection object after use, it’ll not close the thing and garbage collect.

Rather, it puts it back in the pool for the next use.

Please note that this sample code uses the MySQL Database. If you use other databases, check the appropriate driver class for the JDBC driver and make sure that you include the JDBC driver library in Apache Tomcat’s lib/ folder.