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







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;
02
03 import java.io.IOException;
04 import java.sql.Connection;
05 import java.sql.ResultSet;
06 import java.sql.SQLException;
07 import java.sql.Statement;
08
09 import javax.naming.Context;
10 import javax.naming.InitialContext;
11 import javax.naming.NamingException;
12 import javax.servlet.ServletException;
13 import javax.servlet.http.HttpServlet;
14 import javax.servlet.http.HttpServletRequest;
15 import javax.servlet.http.HttpServletResponse;
16 import javax.sql.DataSource;
17
18 public class TestServlet extends HttpServlet {
19
20 private DataSource dataSource;
21 private Connection connection;
22 private Statement statement;
23
24 public void init() throws ServletException {
25 try {
26 // Get DataSource
27 Context initContext = new InitialContext();
28 Context envContext = (Context)initContext.lookup("java:/comp/env");
29 dataSource = (DataSource)envContext.lookup("jdbc/testdb");
30
31 } catch (NamingException e) {
32 e.printStackTrace();
33 }
34 }
35
36 public void doGet(HttpServletRequest req, HttpServletResponse resp)
37 throws ServletException, IOException {
38
39 ResultSet resultSet = null;
40 try {
41 // Get Connection and Statement
42 connection = dataSource.getConnection();
43 statement = connection.createStatement();
44 String query = "SELECT * FROM STUDENT";
45 resultSet = statement.executeQuery(query);
46 while (resultSet.next()) {
47 System.out.println(resultSet.getString(1) + resultSet.getString(2) + resultSet.getString(3));
48 }
49 } catch (SQLException e) {
50 e.printStackTrace();
51 }finally {
52 try { if(null!=resultSet)resultSet.close();} catch (SQLException e)
53 {e.printStackTrace();}
54 try { if(null!=statement)statement.close();} catch (SQLException e)
55 {e.printStackTrace();}
56 try { if(null!=connection)connection.close();} catch (SQLException e)
57 {e.printStackTrace();}
58 }
59 }
60 }

note : copy the jar file in the %catalina%/lib folder