Connection Pooling in a Java Web Application with Tomcat and NetBeans IDE

After my article Connection Pooling in a Java Web Application with Glassfish and NetBeans IDE, here are the instructions for Tomcat.

Requirements

• NetBeans IDE (this tutorial uses NetBeans 7)
• Tomcat (this tutorial uses Tomcat 7 that is bundled within NetBeans)
• MySQL database
• MySQL Java Driver

Steps

Assuming your MySQL database is ready, connect to it and create a database. Lets call it connpool:

mysql> create database connpool;

Now we create and populate the table from which we will fetch the data:

mysql> use connpool; 
mysql> create table data(id int(5) not null unique auto_increment, name varchar(255) not null);
mysql> insert into data(name) values("Fred Flintstone"), ("Pink Panther"), ("Wayne Cramp"), ("Johnny Bravo"), ("Spongebob Squarepants"); 

That is it for the database part.

We now create our web application.

In NetBeans IDE, click File → New Project... Select Java Web → Web Application:



Click Next and give the project the name TomPool. Click Next



Choose the server as Tomcat and, since we are not going to use any frameworks, click Finish.



The project will be created and the start page, index.jsp, opened for us in the IDE.

Now we create the connection pooling parameters. In the Projects window, expand configuration files and open "context.xml". You will see that the IDE has added this code for us:

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/TomPool"/>

Delete the last line:

<Context antiJARLocking="true" path="/TomPool"/>

and then add the following to the context.xml file. I have explained the sections along the way. Make sure you edit your MySQL username and password appropriately:

<Context  antiJARLocking="true" path="/tompool">
    <!-- maxActive: Maximum number of database connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle database connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a database connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL username and password for database connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL database.
         -->

    <Resource name="connpool" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000"
            username="arthur" password="password" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/connpool"/>

</Context>

Next, expand the Web Pages node, right-click WEB-INF → New → Other → XML → XML Document.

Click Next and type web for the File Name. Click next and choose Well-Formed Document then Finish. You will now have the file "web.xml":





Delete everything in the file and paste this code:

<?xml version="1.0" encoding="UTF-8"?>
<web-app 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"
         version="2.4">
    <description>MySQL Test App</description>
    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>connpool</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

That is it for the connection pool. We now edit our code to make use of it.

Edit index.jsp by adding this code just after the initial coments but before <%@page contentType=...

<%@page import="javax.naming.Context"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Connection"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>

Edit the <body> section of the page:

     <body>
        <h1>Data in my Connection Pooled Database</h1>
        <br>
        <%
            InitialContext initialContext = new InitialContext();
            Context context = (Context) initialContext.lookup("java:comp/env");
            //The JDBC Data source that we just created
            DataSource ds = (DataSource) context.lookup("connpool");
            Connection connection = ds.getConnection();

            if (connection == null)
            {
                throw new SQLException("Error establishing connection!");
            }
            String query = "SELECT name FROM variable";

            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet rs = statement.executeQuery();

            while (rs.next())
            {
                out.print(rs.getString("name") + "< br >");
            }
        %>
    </body>

Now, we test the connection pool by running the application:

If you want to have the one connection pool used in multiple applications, you need to edit the following two files:

1. <tomcat_install_folder>/conf/web.xml

Just before the closing </web-app> tag, add the code

<resource-ref>
        <description>DB Connection</description>
        <res-ref-name>connpool</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
</resource-ref>

2. <tomcat_install_folder>/conf/context.xml

Just before the closing </Context> tag, add the code

<Resource name="connpool" auth="Container" type="javax.sql.DataSource"
              maxActive="100" maxIdle="30" maxWait="10000"
              username="arthur" password="password" driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/connpool"/>

Now you can use the pool without editing XML files in each of your applications. Just use the sample code as given in index.jsp

That's it folks!