Apache TOMCAT - Database Connection Pooling

In Apache TOMCAT you can do Database Connection Pooling where in the server will manage the no of the connections with the Database. In this article we will learn how we can achieve Database Connection Pooling using Apache TOMCAT.

NOTE:
Here i am showing MySql as my database, developers can achieve the same using any other Database Engine they just have to change the Database Connection String. Also i am creating an Web Application Context.

Database Connection Pooling is a 2 Step Process:
1) Adding Database Resource in Web Application Context
2) Getting / Reading the JDBC Connection in Java

Adding Database Resource in Web Application Context:
I assume that you know how to create new Application Context in Apache TOMCAT, still don’t know than you can go through this article . Here we need to add an additional tag called “resource” inside context tag.

	<context path="/test" docBase="/home/hitesh/test/web" reloadable="true" >          
          <logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_test_log." suffix=".txt" timestamp="true"/>          
          <resource name="jdbc/test" auth="Container" type="javax.sql.DataSource" username="test" password="test" driverClassName="org.gjt.mm.mysql.Driver" url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true" maxActive="100" maxIdle="30"/>     
     </context>

Here if you see i have added an resource tag containing all the essential information required for connecting to the database. Developer has to change the driverClassName and url incase they are using other Database Engine.

Getting / Reading the JDBC Connection in Java:

package com.hiteshagrawal;
 
import javax.naming.*;
import java.sql.*;
import javax.sql.*;
import java.io.*;
 
public final class ConnectionManager{
        private DataSource ds = null;
 
        public ConnectionManager getDatabaseInstance(){
           Context ctx = null;
           String datasource = null;
           java.io.InputStream is = null;
 
           if (null != ds) {
               return ds;
           }
 
           try {
               ctx = new InitialContext();
               if (null == ctx) {
                    throw new Exception("Context is null. Error in connecting to the datasource.");
               }
 
	       is = DBConnection.class.getResourceAsStream("jdbc");
	       if (is == null) {
		 ds = (DataSource) ctx.lookup("java:comp/env/jdbc/test");
	      } else {
		datasource = new String(convertStreamToString(is)).trim();
		ds = (DataSource) ctx.lookup(datasource);
	     }
          } catch (Exception e) {
	     e.printStackTrace();
          }
          return ds;	
      }
 
      public Connection getDatabaseConnection(){
	Connection con = null;
	try{
	      con =  ds.getConnection();
	}catch(Exception e){
	        e.printStackTrace();
	}
	return con;
     }	
 
     private String convertStreamToString(InputStream is) {
	BufferedReader reader = new BufferedReader(new InputStreamReader(is));
	StringBuilder sb = new StringBuilder();
 	String line = null;
	try {
	     while ((line = reader.readLine()) != null) {
	         sb.append(line + "\n");
	     }  
        } catch (IOException e) {
	     e.printStackTrace();
	} finally {
	    try {
	      is.close();
	   } catch (IOException e) {
	      e.printStackTrace();
	   }
	}
	return sb.toString();
     }
}

Code Explanation:
getDatabaseInstance() function will lookup for the resource name that you have defined in your Web Application Context. You can also store the same in properties file or inside WEB-INF Folder.
getDatabaseConnection() function is responsible for getting new database connection from the pool.
convertStreamToString() function will convert the InputStream to String.