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:
- 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 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.
- 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: 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.
- 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 properties JNDI Name: My Database
Pool Name: Database Pool
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.