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

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database. (Wikipedia)

In this tutorial, we shall create a connection pool to a MySQL database in Glassfish web server, then create a simple web application that makes use of the connection pool.

Requirements

1. NetBeans IDE (This tutorial uses NetBeans 7)
2. Glassfish Web Server (This tutorial uses Glassfish 3.1 that is bundled within NetBeans)
3. MySQL database
4. 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 configure the connection pool in Glassfish

Copy the MySQL connector jar file into /lib folder

Start Glassfish server (or restart it if it was already running). Then we navigate to the admin console. On the home page, click on Create New JDBC Connection Pool

Give your connection pool a name (I called mine Tutorial). Select the Resource Type as javax.sql.DataSource and the Database Driver Vendor as MySQL then click Next

On the next page, scroll down to the section labeled “Additional Properties (199)”

From here, we set the values of the MySQL database connection for User, Password and URL. Note that you need to change the value for URL and not Url (case sensitive). For the URL, type in a JDBC connection value. I set mine as jdbc:mysql://localhost:3306/connpool.

Click finish and you should be able to see the connection you have just created on the JDBC Connection Pools list:

We now test whether this connection is working.

Click on Tutorial and it should open such an interface:

Click on the button labelled “Ping”. If everything is OK, you should get a “Ping Succeeded” message:

Once succeeded, scroll to the bottom of the page and click to select the check-box and enable Non Transactional Connections:

Click Save. That is it for that section.

Next we create a JDBC Resource.

On the left pane, click on Resources → JDBC → JDBC Resources

Click on the “New...” button to add a new resource. In the resulting form, type in TutorialJDBCResource for JNDI Name. For the Pool Name, select Tutorial. Leave the rest as default and click OK

You should now see your resource as having been created.

We now create our web application that makes use of the connection pool.

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

Click Next and give the project the name tutorial. Click Next:

Choose the server as Glassfish Server 3.1 (The one we just created the connection pool in) 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 on the IDE.

Edit index.jsp:

Add this code just after the initial coments but before <%@page contentType=...

<%@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 ctx = new InitialContext();
            //The JDBC Data source that we just created
            DataSource ds = (DataSource) ctx.lookup("TutorialJDBCResource");
            Connection connection = ds.getConnection();

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

            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:

To see that the jsp is using my connection pool, I ran in the MySQL terminal:

mysql> show full processlist;

+-----+--------+-----------------+----------+---------+------+-------+-----------------------+
| Id  | User   | Host            | db       | Command | Time | State | Info                  |
+-----+--------+-----------------+----------+---------+------+-------+-----------------------+
| 197 | arthur | localhost:54907 | connpool | Sleep   |  138 |       | NULL                  |
| 198 | arthur | localhost:54908 | connpool | Sleep   |  138 |       | NULL                  |
| 199 | arthur | localhost:54909 | connpool | Sleep   |  138 |       | NULL                  |
| 200 | arthur | localhost:54910 | connpool | Sleep   |  138 |       | NULL                  |
| 201 | arthur | localhost:54911 | connpool | Sleep   |  138 |       | NULL                  |
| 202 | arthur | localhost:54912 | connpool | Sleep   |  138 |       | NULL                  |
| 203 | arthur | localhost:54913 | connpool | Sleep   |  138 |       | NULL                  |
| 204 | arthur | localhost:54914 | connpool | Sleep   |  138 |       | NULL                  |
| 205 | arthur | localhost       | NULL     | Query   |    0 | NULL  | show full processlist |
+-----+--------+-----------------+----------+---------+------+-------+-----------------------+

9 rows in set (0.00 sec)
mysql> 

As you can see, the database in use is connpool, which is the connection pool that we used. It has 8 active connections, which is the minimum that Glassfish offered when we were creating the connection pool:

That's it folks!