Database Connection Pooling in Tomcat using Eclipse

tomcat-connection-poolingDatabase Connection Pooling is a great technique used by lot of application servers to optimize the performance. Database Connection creation is a costly task thus it impacts the performance of application. Hence lot of application server creates a database connection pool which are pre initiated db connections that can be leverage to increase performance.

Apache Tomcat also provide a way of creating DB Connection Pool. Let us see an example to implement DB Connection Pooling in Apache Tomcat server. We will create a sample web application with a servlet that will get the db connection from tomcat db connection pool and fetch the data using a query. We will use Eclipse as our development environment. This is not a prerequisite i.e. you may want to use any IDE to create this example.

Step 1: Create Dynamic Web Project in Eclipse

Create a Dynamic Web Project in Eclipse by selecting:
File -> New -> Project… ->Dynamic Web Project.
dynamic-project-eclipse

Step 2: Create context.xml

Apache Tomcat allow the applications to define the resource used by the web application in a file called context.xml (from Tomcat 5.x version onwards). We will create a file context.xml under META-INF directory.
db-connection-pooling-eclipse
Copy following content in the context.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<Context>
	<!-- Specify a JDBC datasource -->
	<Resource name="jdbc/testdb" auth="Container"
		type="javax.sql.DataSource" username="DB_USERNAME" password="DB_PASSWORD"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@xxx:1525:dbname"
		maxActive="10" maxIdle="4" />

</Context>

In above code snippet, we have specify a database connection pool. The name of the resource is jdbc/testdb. We will use this name in our application to get the data connection. Also we specify db username and password and connection URL of database. Note that I am using Oracle as the database for this example. You may want to change this Driver class with any of other DB Providers (like MySQL Driver Class).

Step 3: Create Test Servlet and WEB xml entry

Create a file called TestServlet.java. I have created this file under package: net.viralpatel.servlet. Copy following code into it.

package net.viralpatel.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

public class TestServlet extends HttpServlet {
	
	private DataSource dataSource;
	private Connection connection;
	private Statement statement;
	
	public void init() throws ServletException {
		try {
			// Get DataSource
			Context initContext  = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			dataSource = (DataSource)envContext.lookup("jdbc/testdb");

			
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		
		ResultSet resultSet = null;
		try {
			// Get Connection and Statement
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			String query = "SELECT * FROM STUDENT";
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				System.out.println(resultSet.getString(1) + resultSet.getString(2) + resultSet.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try { if(null!=resultSet)resultSet.close();} catch (SQLException e) 
			{e.printStackTrace();}
			try { if(null!=statement)statement.close();} catch (SQLException e) 
			{e.printStackTrace();}
			try { if(null!=connection)connection.close();} catch (SQLException e) 
			{e.printStackTrace();}
		}
	}
}

In the above code we initiated the datasource using InitialContext lookup:

Context initContext  = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
dataSource = (DataSource)envContext.lookup("jdbc/testdb");

Create test servlet mapping in the web.xml file (deployment descriptor) of the web application. The web.xml file will look like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
	xmlns="http://java.sun.com/xml/ns/j2ee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	<display-name>TomcatConnectionPooling</display-name>
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>

	<servlet>
		<servlet-name>TestServlet</servlet-name>
		<servlet-class>
			net.viralpatel.servlet.TestServlet
		</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>TestServlet</servlet-name>
		<url-pattern>/servlet/test</url-pattern>
	</servlet-mapping>
</web-app>

Now Run the web application in Tomcat using Eclipse (Alt + Shift + X, R). You will be able to see the result of the query executed.
db-connection-run-project-eclipse
Thus this way we can create a database pool in Tomcat and get the connections from it.



64 Comments

  • Ankur 30 December, 2013, 23:25

    where is index.jsp file…….

  • Ramya 23 February, 2014, 12:22

    I am not able to get a connection from my pool. When i set the maxactive to 1, i see 14 connections created in oracle under the machine name. While using ds.getconnection(), i don’t get more than one connection. I have added the pooling connection details to tomcat’s context.xml. in th is way the connection pool is available to all applications.

    My issues is i see connections getting created, but when calling getconnection(), i don’t see any connection being given from the pool.

  • Siddu 12 March, 2014, 20:46

    How to implement connection pooling in core java application? for stand alone application.

  • ANJU CHAUDHARY 25 March, 2014, 15:38

    I am not able for understand why you used context.xml file under meta -INF, and programing of step 4 pls tell me in detail of all process

  • Dheeraj Remella 23 April, 2014, 20:41

    Thanks for the article, Viral.

  • Arpit 22 May, 2014, 0:43

    Hi Viral,

    First I would like to thankyou for explaining java concepts in very simple way.
    I am a regular follow of your blog and learnt alot from you. You have given a simple example i was looking for connection pooling.

    The whole idea of connection pooling is that when application startsup a pool of connection is created. When we want to interact with the database we get a connection from pool and upon completing we return that connection to the pool i.e. we never close the connection.

    Here in your are closing the connection.

    Please expalin.

    Awaiting you reply…!

    Thanks & Regards,
    Arpit

    • raj 3 July, 2014, 12:58

      Its not closing database connection, while invoke the close. It return to pool connection

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current day month ye@r *