Over a million developers have joined DZone.

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

Create a connection pool to a MySQL database in Glassfish web server, then create a simple web application that makes use of it.

· Java Zone

Learn more about how the Java language, tools and frameworks have been the foundation of countless enterprise systems, brought to you in partnership with Salesforce.

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

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

Create New JDBC Connection Pool

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:

Connection Pools list

We now test whether this connection is working.

Click on Tutorial and it should open such an interface:

Test Connection

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

Ping Succeeded

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

Enable Non-Transactional

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

New JDBC Resource

You should now see your resource as having been created.

JDBC Resource

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:

NetBeans New Web Application

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

NetBeans New Web Application

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.

NetBeans New Web Application

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"%>

index.jsp

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:

Firefox

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:

Default Config

That's it folks!

Discover how the Force.com Web Services Connector (WSC) is a code-generation tool and runtime library for use with Force.com Web services, brought to you in partnership with Salesforce.

Topics:
netbeans

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}