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:
- Create the tables in MySQL server.
- 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', 'firstname.lastname@example.org'); 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.
- Be sure that you Glassfish server is stopped before going on.
- 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
- 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.
- Copy this jar file to $glassfish_install_folder\glassfish\lib
- Start Glassfish and go to the admin console, usually located at http://localhost:4848
- 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:
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:
- 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.
- 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
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.