Database Connection Pooling in Tomcat using Eclipse
Database 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.

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. 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>

Code language: HTML, XML (xml)
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();}
  }
 }
}

Code language: Java (java)
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");

Code language: Java (java)
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>

Code language: HTML, XML (xml)
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. Thus this way we can create a database pool in Tomcat and get the connections from it.

View Comments

  • I'm getting this error in Eclipse:

    org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1136)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
    at TestServlet.doGet(TestServlet.java:42)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClassInternal(Unknown Source)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Unknown Source)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1130)
    ... 16 more

  • Resource name="jdbc/orclPool"
    auth="Container"
    type="oracle.jdbc.pool.OracleDataSource"
    url="jdbc:oracle:thin:@host:1521:orcl"
    factory="oracle.jdbc.pool.OracleDataSourceFactory"
    user="user"
    password="pass"
    connectionCachingEnabled="true"
    connectionCacheName="orclConnCash"
    connectionCacheProperties="{MinLimit=0, MaxLimit=5, InitialLimit=3, connectionWaitTimeout=10}"

  • I repeat your code but I don't know what is wrong..
    java.lang.NullPointerException
    paquete.TestServlet.doGet(TestServlet.java:40)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

    note The full stack trace of the root cause is available in the Apache Tomcat/5.5.23 logs.

    • Hi Mike,

      Did you managed to resolve nullpointer exception now?if so can you give me solution..iam facing same error..doGet throwing null

Leave a Comment
Share
Published by
Viral Patel
Tags: apache tomcat database connections eclipse tomcat
15 years ago

Recent Posts

  • Java

Java URL Encoder/Decoder Example

Java URL Encoder/Decoder Example - In this tutorial we will see how to URL encode/decode…

5 years ago
  • General

How to Show Multiple Examples in OpenAPI Spec

Show Multiple Examples in OpenAPI - OpenAPI (aka Swagger) Specifications has become a defecto standard…

5 years ago
  • General

How to Run Local WordPress using Docker

Local WordPress using Docker - Running a local WordPress development environment is crucial for testing…

5 years ago
  • Java

Create and Validate JWT Token in Java using JJWT

1. JWT Token Overview JSON Web Token (JWT) is an open standard defines a compact…

5 years ago
  • Spring Boot

Spring Boot GraphQL Subscription Realtime API

GraphQL Subscription provides a great way of building real-time API. In this tutorial we will…

5 years ago
  • Spring Boot

Spring Boot DynamoDB Integration Test using Testcontainers

1. Overview Spring Boot Webflux DynamoDB Integration tests - In this tutorial we will see…

5 years ago