Computing @ 40's

Trying to overcome the obsolescence

How to setup a JDBC connection in Glassfish

Following the series of articles I am writing for newcomers to Java EE world and 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. If you want some details about software that I am using check my development environment.

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 two tables in this database: USERS (Users table) and USRGP (User groups). The database is called booreg

The code for creating this tables is:


delimiter ';'

CREATE DATABASE `booreg`;

CREATE  TABLE booreg.USERS
(
    USID VARCHAR(30)  NOT NULL ,
    USNM VARCHAR(120) NULL ,
    PASS VARCHAR(45)  NULL ,
    EMAL VARCHAR(55)  NULL ,

    PRIMARY KEY (USID)
);

CREATE TABLE booreg.USRGP
(
    USGP VARCHAR(30) NOT NULL ,
    USID VARCHAR(30) NULL ,

    PRIMARY KEY (USGP),
    INDEX USRGPFK00_idx (USID ASC) ,

    CONSTRAINT USRGPFK00 FOREIGN KEY (USID) REFERENCES booreg.users (USID)
);

INSERT INTO booreg.USERS VALUES ('dgisbert', 'Dani Gisbert', 'mypassword', 'daniel.gisbert@notmyemail.com');
INSERT INTO booreg.USRGP VALUES ('allUsers', 'dgisbert');

COMMIT;

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: MyDatabae
    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/booreg
    user: set the user you want to access this database. Notice that all connection will use the same user.
    password: write the password of your user. Notice that password is stored unencrypted.You should see your screen like similar to this two images:

    JDBC connection pool configuration - I

    JDBC connection pool configuration - II
    Click Finish to save the values. You have now a connection pool called MyDatabase.

  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 propertiesJNDI Name: MyDatabase
    Pool Name: MyDatabase

Configuring JDBC Resource

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

Please any comment will be welcomed.

Cardedeu, 10/06/2013.

114 thoughts on “How to setup a JDBC connection in Glassfish

  1. i am using jsf for development. but my netbeans is not connecting with mysql giving deploying error on build, is this actualy related with the confirguration u provided in your post???.

  2. Dani plz help, I dont what to do. I do everything in the tutorial, I’m using Glassfish 4.1.1 and SQL 2008, jdbc 4.0

    1.- I can’t create a new PoolConnection
    2.- I’m working on the Derby Pool, but i don’t know what drivers I have to use or resource type or datasource.

    When I put on resource type: “java.Sql.Driver” i get: “Ping Connection Pool failed for DerbyPool. com.microsoft.sqlserver.jdbc.SQLServerDriver cannot be cast to javax.sql.DataSource Please check the server.log for more details.”

    When I put on resource type: “javax.sql…(any other)” I get: “Ping Connection Pool failed for DerbyPool. Class name is wrong or classpath is not set for : com.microsoft.sqlserver.jdbc.SqlServerDataSource Please check the server.log for more details.”

    3.- I’m no using user and password, only windows authentication.

    Plz help me Dani.

    Greetings,
    Rostan
    Ecuador

  3. I used root and no password. My ping didn’t work. I suppose empty passwords aren’t allowed?

  4. I have the same problems that users have written before me, and I discovered , after some research , that the new version of Glassfish is no longer able to do these things , especially if you use NetBeans as the IDE . I’m trying to fix it , or using a sample provided by Glassfish and editing it at my leisure database , or the second attempt I’ll do will be to replace the current version with a previous Glassfish . She has suggestions about that?

  5. thinks a lot

  6. why?

    Error An error has occurred

    Ping Connection Pool failed for MySQLPool. Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource Please check the server.log for more details.

  7. Using the latest builds from yesterday which I had to do since the stable 4.1 build had a java.lang error when trying to add the resources. The nightly build seems to fix this issue at: http://download.oracle.com/glassfish/5.0/nightly/

    I had to put the .jar file in the glassfish/domains/domain1/lib/ext folder before it worked. If you are using the microsoft driver, you need to use sqljdbc4.jar file only. Adding the other file from microsoft errors out completely. I guess there is no error handling or something. The nightly builds does not have the error logs turned on by default for some reason. I had to turn this on to see the errors.

    If you are trying to do ADF and adding the Oracle ADF Essentials, you have to add it in the “ext” folder also. Atleast that is where it worked for me as I am exploring this platform as a newbie…

  8. Hi,

    I am getting the following error while trying to create JDBC Connection Pool for MySQL 5.7 by using Glassfish-4.1.1. I am using JDK 8u-102 with this version of Glassfish.

    HTTP Status 500 – Internal Server Error
    type Exception report
    messageInternal Server Error
    descriptionThe server encountered an internal error that prevented it from fulfilling this request.
    exception
    java.lang.IllegalStateException: getOutputStream() has already been called for this response

    Can I please get some help on that?

    Thx,
    TR

    • Hi.

      Many posts advice of having problems with JDBC connection pools with Glassfish 4.x

      It seems that, on the last years, Glassfish is becoming a secondary project for Oracle and they don’t pay enough attention to it. Instead of using Glassfish, I recommend you using Payara, that is a fork from Glassfish, well maintained and without that horrible bugs.

      • Thanks for your advice Dani.

        Actually I am taking a course on Web Services and the instructor has that setup which I am trying to build on my personal machine for practice.

        Thx,
        TR

  9. Pingback: 2. How to construct a road (setup a connection between a Java Application and a database in GlassFish 4.1.0?) – crackingcorejava

  10. My goodness, I have had a helluva time getting to this point but I have finally one it. I downloaded Payara fish server as someone advised and made sure the connector driver was loaded into the correct directory and bingo. I am SOOOOO pleased. Thank you Dani for posting this up. I now have a wonderful set up for java programming.

  11. Sometimes Glassfish 4 does not allow JDBC Resources creation using the GUI (apparently is a bug we have to deal with)

    The solution is as follows (example for mysql):

    go to: $glassfish_install_dir/glassfish/bin:

    1) CREATING CONNECTION POOL

    ./asadmin create-jdbc-connection-pool –datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource –restype javax.sql.DataSource –property user=myuser:password=mypasword:url=”jdbc\:mysql\:\/\/my_host\:my_port\/my_db_schema” myjdbc_mysql-pool

    2) CREATING JDBC RESOURCE

    ./asadmin create-jdbc-resource

    Enter admin user name> YOUR_GLASSFISH_ADMIN_USER
    Enter admin password for user “YOUR_USER”> YOUR_GLASSFISH_ADMIN_PASSWORD

    Enter the value for the connectionpoolid option> myjdbc_mysql-pool (the name you assigned at the end of pool creation command)
    Enter the value for the jndi_name operand> jdbc/mysql-resource (the name you want to assign to hsbc resource)

    3) ADDING DRIVER TO CONNECTION POOL:
    ./asadmin set domain.resources.jdbc-connection-pool.myjdbc_mysql-pool.driver-classname=com.mysql.jdbc.Driver

    4) PING CONNECTION POOL:
    ./asadmin ping-connection-pool myjdbc_mysql-pool (again, the name you assigned at the end of pool creation command)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s