Welcome!

Blog Feed Post

Configuring a MySQL JDBC Connection Pool and JDBC Resource for GlassFish using NetBeans

This tutorial assumes that you have installed the Java EE version of NetBeans 8.02. If you are using Windows then it is further assumed that you have replaced the default GlassFish server instance in NetBeans with a new instance with its own folder for the domain. See my previous article “Creating a New Instance of GlassFish in NetBeans IDE” On an Apple Mac and likely on Linux it is not necessary to do this.

The other day I presented to my students the steps necessary to make GlassFish responsible for JDBC connections. As I went through the steps I realized that I needed to record the steps for my students to reference. Here then are these steps.

Step 1: Add the MySQL driver to the GlassFish Domain

Before we even start NetBeans we must do a bit of preliminary work. GlassFish does not include the MySQL driver or any other driver in its distribution. One exception is that it does include a complete implementation of the Derby database and Derby is the default data source for GlassFish.

Go to the MySql Connector/J download site at http://dev.mysql.com/downloads/connector/j/ and download the latest version. I recommend downloading the Platform Independent version. If your OS is Windows download the ZIP archive otherwise download the TAR archive. You are looking for the driver file named mysql-connector-java-5.1.34-bin.jar in the archive.

The driver file must now be copied to the lib folder in the directory where you placed your domain. On my system the folder is located at C:\Users\Ken\personal_domain\lib. If GlassFish is already running then you will have to restart it so that it picks up the new library.

Step 2: Create a Database Connection in NetBeans

One feature I have always liked in NetBeans is that it has an interface for working with databases. All that is required is that you create a connection to the database. It also has additional features for managing a MySQL server but we won’t need those. If you have not already started your MySQL DBMS then do that now. I also assume that the database you wish to connect to already exists.

Go to the Services tab and right mouse click on New Connection.

Image0120

In the next dialog you must choose the database driver you wish to use. It defaults to Java DB (Embedded). Pull down the combo box labeled Driver: and select MySQL (Connector/J driver).

Image0220

Click on Next and you will now see the Customize Connection dialog. Here you can enter the details of the connection. On my system the server is localhost and the database name is Aquarium. Here is what my dialog looks like.

Image0320

Notice the Test Connection button. I have clicked on mine and so I have the message Connection Succeeded. Click on Next.

Image0420

There is nothing to do on this dialog so click on Next.

Image0520

On this last dialog you have the option of assigning a name to the connection. By default it uses the URL but I prefer a more meaningful name. I have used AquariumMySQL.

Image0620

Click on Finish and the connection will appear under Databases.

Image0720

If the icon next to AquariumMySQL has what looks like a crack in it similar to the jdbc:derby connection then this means that a connection to the database could not be made. Verify that the database is running and is accessible. If it is then delete the connection and start over.

Step 3: Create a Web Application Project in NetBeans

If you have not already done so create a New Project in NetBeans. My preference is to create a New Project in the Maven category of a Web Application project. You could also create a New Project of the Java Web category of a Web Application project.

Image0820

Click on Next. In this next dialog you can give the project a name and a location in your file system. The Artifact Id, Group Id and Version are used by Maven.

Image11a20

The final dialog lets you select the application server that your application will use and the version of Java EE that your code must be compliant with.

Image11b20

Here is my project ready for the next step.

Image0920

Step 4: Create the GlassFish JDBC Resource

For GlassFish to manage your database connection you need to set up two resources, a JDBC Connection Pool and a JDBC Resource. You can create both in one step by creating a GlassFish JDBC Resource because you can create the Connection Pool as part of the operation.

Right mouse click on the project name and select New and then Other …

Image1020

Scroll down the Categories list and select GlassFish. In the File Types list select JDBC Resource.

Image1120

Click on Next. The next dialog is the General Attributes. Click on the radio button for Create New JDBC Connection Pool. In the text field JNDI Name enter a name that is unique for the project. JNDI names for connection resources always begin with jdbc/ followed by a name that starts with a lower case letter. I have used jdbc/myAquarium.

Image1220

Click on Next. There is nothing for us to enter on the Properties dialog.

Image1320

Click on Next. On the Choose Database Connection dialog we will give our connection pool a name and select the database connection we created in Step 2. Notice that in the list of available connections you are shown the connection URL and not the name you assigned to it.

Image1420

Click on Next. On the Add Connection Pool Properties dialog you will see the connection URL and the user name and password. We do need to make one change. The resource type shows javax.sql.DataSource and we must change it to javax.sql.ConnectionPoolDataSource.

Image1520

Click on Next. There is nothing we need to change on Add Connection Pool Optional Properties so click on Finish.

A new folder has appeared in the Projects view named Other Sources. It contains a sub folder named setup. In this folder is the file glassfish-resources.xml.

Image1620

The glassfish-resources.xml file will contain the following. I have reformatted the file for easier viewing.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN" "http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
<resources>
    <jdbc-resource enabled="true" jndi-name="jdbc/myAquarium" object-type="user" pool-name="aquariumPool">
        <description/>
    </jdbc-resource>
    <jdbc-connection-pool allow-non-component-callers="false" 
                        associate-with-thread="false" 
                        connection-creation-retry-attempts="0" 
                        connection-creation-retry-interval-in-seconds="10" 
                        connection-leak-reclaim="false" 
                        connection-leak-timeout-in-seconds="0" 
                        connection-validation-method="auto-commit" 
                        datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" 
                        fail-all-connections="false" idle-timeout-in-seconds="300" 
                        is-connection-validation-required="false" is-isolation-level-guaranteed="true" 
                        lazy-connection-association="false" 
                        lazy-connection-enlistment="false" 
                        match-connections="false" 
                        max-connection-usage-count="0" 
                        max-pool-size="32" 
                        max-wait-time-in-millis="60000" 
                        name="aquariumPool" 
                        non-transactional-connections="false" 
                        pool-resize-quantity="2" 
                        res-type="javax.sql.ConnectionPoolDataSource" 
                        statement-timeout-in-seconds="-1" 
                        steady-pool-size="8" 
                        validate-atmost-once-period-in-seconds="0" 
                        wrap-jdbc-objects="false">
        <property name="URL" value="jdbc:mysql://localhost:3306/Aquarium?zeroDateTimeBehavior=convertToNull"/>
        <property name="User" value="fish"/>
        <property name="Password" value="fish"/>
    </jdbc-connection-pool>
</resources>
glassfish-resources.xml

Step 4: Configure GlassFish with glassfish-resources.xml

The glassfish-resources.xml file when included in the application’s WAR file should configure the resource and pool for the application. We can also use this file to configure GlassFish directly and therefore verify that it is the correct configuration.

Go to the Services tab and select Servers and then right mouse click on GlassFish. If GlassFish is not running then click on Start. You can tell if GlassFish is running based on whether Start is black (not started) or grey (started).

With the server started click on View Domain Admin Console.

Image1720

Your web browser will now open and show you the GlassFish console. If you assigned a user name and password to the server you will have to enter this information before you see the console.

Image1820

In the Common Tasks tree select Resources. You should now see in the panel adjacent to the tree the following.

Image1920

Click on Add Resources. You should now see:

Image2020

In the Location click on Choose File and locate your glassfish-resources.xml file. Mine is found at D:\NetBeansProjects\GlassFishTutorial\src\main\setup. You should now see:

Image2120

Click on OK. If everything has gone well you should see:

Image2220

The final task in this step is to test if the connection works. In the Common Tasks tree select Resources, JDBC, JDBC Connection Pools and aquariumPool.

Image2320

Click on Ping. You should see:

Image2520

If the Ping was not successful review the steps.

The resources are now visible in NetBeans.

Image2620

You are ready to code!

Read the original blog entry...

More Stories By Ken Fogel

In 1980 I bought for myself the most wonderful toy of the day, the Apple ][+. Obsession followed quickly and by 1983 I was writing software for small and medium sized businesses in Montreal for both the Apple and the IBM PC under the company name Omnibus Systems. In the evenings I taught continuing education courses that demystified the computer to the first generation of workers who found themselves with their typewriter on the scrap heap and a PC with WordStar taking its place.

In 1990 I was invited to join the faculty at Dawson College in the Computer Science Technology program. When I joined the program the primary language was COBOL and my responsibility was to teach small systems languages such as BASIC and C/C++.

Today I am now the chairperson and program coordinator of the Computer Science Technology program at Dawson. The program's primary language is Java and the focus is on enterprise programming.

I like to write about the every day problems my students and I face in using various languages and platforms to get the job done. And from time to time I stray from the path and write about what I plan to do, what I actually get around to doing, and what I imagine I am doing.

@omniprof

Latest Stories
It is ironic, but perhaps not unexpected, that many organizations who want the benefits of using an Agile approach to deliver software use a waterfall approach to adopting Agile practices: they form plans, they set milestones, and they measure progress by how many teams they have engaged. Old habits die hard, but like most waterfall software projects, most waterfall-style Agile adoption efforts fail to produce the results desired. The problem is that to get the results they want, they have to ch...
Cloud Expo, Inc. has announced today that Andi Mann and Aruna Ravichandran have been named Co-Chairs of @DevOpsSummit at Cloud Expo Silicon Valley which will take place Oct. 31-Nov. 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. "DevOps is at the intersection of technology and business-optimizing tools, organizations and processes to bring measurable improvements in productivity and profitability," said Aruna Ravichandran, vice president, DevOps product and solutions marketing...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), provided an overview of various initiatives to certify the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldwide re...
Wooed by the promise of faster innovation, lower TCO, and greater agility, businesses of every shape and size have embraced the cloud at every layer of the IT stack – from apps to file sharing to infrastructure. The typical organization currently uses more than a dozen sanctioned cloud apps and will shift more than half of all workloads to the cloud by 2018. Such cloud investments have delivered measurable benefits. But they’ve also resulted in some unintended side-effects: complexity and risk. ...
"Loom is applying artificial intelligence and machine learning into the entire log analysis process, from start to finish and at the end you will get a human touch,” explained Sabo Taylor Diab, Vice President, Marketing at Loom Systems, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We are a monitoring company. We work with Salesforce, BBC, and quite a few other big logos. We basically provide monitoring for them, structure for their cloud services and we fit into the DevOps world" explained David Gildeh, Co-founder and CEO of Outlyer, in this SYS-CON.tv interview at DevOps Summit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 21st Int\ernational Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their ...
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, Doug Vanderweide, an instructor at Linux Academy, discussed why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers wit...
DevOps at Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to w...
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...