Post Reply 
 
Thread Rating:
  • 1 Votes - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Connection Pooling in Tomcat using Eclipse
02-13-2011, 03:32 PM
Post: #1
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>


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: com.tutorials4u.servlet. Copy following code into it.

package com.tutorials4u.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.

Thus this way we can create a database pool in Tomcat and get the connections from it.
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:



Send your comments, Suggestions or Queries regarding this site at info@tutorials4u.net

Copyright © 2010 Tutorials4u.net All Rights Reserved