atabase 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.
File -> New -> Project… ->Dynamic Web Project.
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).
In the above code we initiated the datasource using InitialContext lookup:
Create test servlet mapping in the web.xml file (deployment descriptor) of the web application. The web.xml file will look like:
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.
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 filecontext.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.vaani.servlet. Copy following code into it.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>
com.vaani.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.
