How to set up a JDBC connection in Glassfish

Today I will make an introductory work that will allow us to work with databases. In this case  we will create a little database with MySQL and I will setup a JDBC connection to use it later from a program.

I assume that you have installed Glassfish and MySQL.

Our objectives will be:

  1. Create the tables in MySQL server.
  2. Set up a JDBC connection to a MySQL server running on our local machine.

Creating tables in MySQL.

I assume you have basic knowledge about installing MySQL, accessing the database and run SQL statements against it. So, I will directly create a database and assign permissions.

Database : testdb
Username : testuser
Password : pa$$word

Setting up a JDBC connection in Glassfish.

This is one of that kind of things that can be a challenge for beginners but, at last, is really simple, easy and fast to do. Follow these steps.

  1. Be sure that you Glassfish server is stopped before going on.
  2. Download MySQL JDBC driver from Mysql.com. You can find it here. JDBC driver is called MySQL Connector/J. At the moment I write this article current version is 5.1.25
  3. Unzip and extract all files from the file. Locate the jar containing the JDBC driver. Currently this file is called mysql-connector-java-5.1.25-bin.jar and is located at the root of the folder you have just unzipped.
  4. Copy this jar file to $glassfish_install_folder\glassfish\lib
  5. Start Glassfish and go to the admin console, usually located at http://localhost:4848
  6. At left side on your console you will see a tree, and one node called Resources. Open Resources\JDBC\JDBC Connection Pools. Create a connection pool with the following properties:Pool name: Database Pool
    Resource type: java.sql.Driver (you can choose any other but by now is the simplest option).
    Database Driver Vendor: MySQL. Click on next. Because you choose database driver vendor MySQL you will have already specified the driver classname (com.mysql.jdbc.Driver).
    Initial and Minimum Pool Size Set a zero value on this parameter. You don’t need initially 8 connections to the database in your development machine.Set the next additional properties:

    URL: jdbc:mysql://localhost:3306/testdb
    user: testuser ( set the user you want to access this database. Notice that all connection will use the same user.)
    password: pa$$word (write the password of your user. Notice that password is stored unencrypted.)

    You should see your screen like similar to this.

    JDBC connection pool configuration - I



    Click Finish to save the values. You have now a connection pool called Database Pool

  7. Enter again into the connection pool. You will see now a Ping button to test if you have done well the previous steps. You should see a message saying Ping succeeded.JDBC connection pool configuration - III
  8. Now we will create the JDBC resource that will give access to our connection pool from our programs. Go to Resources → JDBC → JDBC Resources and create a new one, with these properties JNDI Name: My Database
    Pool Name: Database Pool

Configuring JDBC Resource

And that’s all!! Simple, isn’t it ?  Now you can access the tables by a JNDI lookup inside you application, or using JPA, but this will be explained in another chapter.