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 --><Resourcename="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.
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.