Database Connection Pooling in Tomcat using Eclipse
- By Viral Patel on September 22, 2009
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: 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.
Thus this way we can create a database pool in Tomcat and get the connections from it.
Get our Articles via Email. Enter your email address.
Great article, exactly what I was looking for! Thanks!
excellent work
where do you add the index.jsp?
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
Excellent job … “Programming made easy” …
– Sreedhar Siliveri
to solve problem with “cannot load class”:
http://forums.sun.com/thread.jspa?threadID=5381419
context.xml
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
there is no instruction for index.jsp.. please let us know how to create it
Very good explanation of connection pooling with example..Thanks !
its very easy to understand for learners new to this topic .thank u very much.
Thanks,
I spent hours trying to make it works.
With this one – it took me minutes.
it show
rg.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class ‘com.mysql.jdbc.Driver’
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at net.viralpatel.servlet.TestServlet.doGet(TestServlet.java:43)
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:859)
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(Thread.java:595)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1680)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1526)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420)
… 17 more