DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Connection Pooling With BoneCP, DBCP, and C3PO [Video Tutorials]
  • JDBC Tutorial Part 3: Using Database Connection Pools
  • Connecting Jakarta EE Applications to MySQL Databases Using JPA and Apache DeltaSpike [Video]

Trending

  • TypeScript: Useful Features
  • Hugging Face Is the New GitHub for LLMs
  • How TIBCO Is Evolving Its Platform To Embrace Developers and Simplify Cloud Integration
  • AWS Amplify: A Comprehensive Guide
  1. DZone
  2. Data Engineering
  3. Databases
  4. Connection Pooling in a Java Web Application with Glassfish and NetBeans IDE

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.

Arthur Buliva user avatar by
Arthur Buliva
·
Sep. 18, 11 · Interview
Like (0)
Save
Tweet
Share
85.07K Views

Join the DZone community and get the full member experience.

Join For Free

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!

Database connection Integrated development environment Web application GlassFish Connection pool NetBeans Java (programming language) MySQL

Opinions expressed by DZone contributors are their own.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Connection Pooling With BoneCP, DBCP, and C3PO [Video Tutorials]
  • JDBC Tutorial Part 3: Using Database Connection Pools
  • Connecting Jakarta EE Applications to MySQL Databases Using JPA and Apache DeltaSpike [Video]

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: