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 .
How to install netbean
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 Raja...
The code for creating this tables is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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.
- 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 Glass fish 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:
URL: jdbc:mysql://localhost:3306/Raja
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 encrypted. You should see your screen
like similar to this two images:
Click Finish to save the values. You have now a connection pool called MyDatabase.
- 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.If you have any confusion then comment below.i feel very happy after helping others.So that comment below if you have any question.
