Java EE6 & Wicket - Article #2 – Creating A Backing Datastore And Schema Using MySQL

Welcome to the 2nd in a series of detailed articles on Java EE6 & Wicket. If you haven’t already, please read the following previous articles before continuing.

1. Java EE6 & Wicket
2. Java EE6 & Wicket - Article #1 - Requirements, Architecture & Resources

In this, my 2nd article in this series, we will discuss and demonstrate creating the backing datastore that our GuestBook Web application will use. To accomplish this, we will rely on NetBeans which has excellent built in support for working with MySQL. If you haven’t already created a connection to MySQL in NetBeans you will need to do so before continuing. If you’d like to view an excellent tutorial that demonstrates connecting to MySQL I recommend you read Connecting to a MySQL Database.

Create A New Database

Now that you have connected to MySQL in NetBeans we can get started. If it isn’t already, fire up NetBeans and open the Services tab.

We want to create a MySQL database that will be dedicated to the GuestBook Web application that we will build together in later articles. NetBeans MySQL support makes creating a new database trivial as the following steps demonstrates.

1. In Services, expand the Databases item, right click on the MySQL entry and then select Create Database.
2. In the Create MySQL Database window enter guestbook in the New Database Name field. It should look exactly
   like the following image. Now click OK.
   
   

When we clicked OK NetBeans created a new database called guestbook. It also created a new jdbc connection for the guestbook database and added it as an item listed under Databases as the image below shows.

Create A New Table With Test Data

Now that we have created the guestbook database and jdbc connection we can create a table which we will call guest and which will serve to store the information that our GuestBook Web application visitors will provide. We also want to load the table with some test data which we will use to insure that our database is working properly and also later on when we test our GuestBook Web application.

To make this step easier for you, I’ve created a SQL script that we will run to generate the table and load it with test data. This is the script that we will run:

CREATE  TABLE `guestbook`.`guest` (

  `id` INT NOT NULL AUTO_INCREMENT ,

  `firstName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ,

  `lastName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ,

  PRIMARY KEY (`id`) );
 
INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('James', 'Taylor');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('Eric', 'Clapton');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('Bill', 'Gates');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('Steven', 'Jobs');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('John', 'Glenn');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('John', 'Kennedy');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('Glenn', 'Ford');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('Al', 'Pacino');

INSERT INTO `guestbook`.`guest` (`firstName`, `lastName`) VALUES ('President', 'Obama');

The above script creates a table by defining its schema in the CREATE TABLE command. The schema declares 3 columns - id, firstName and lastName. The id column is defined as an auto incrementing column whose values will serve as the table’s primary key. The first and lastName columns are used to store the first and last names of the visitors to our GuestBook Web application. They are both defined as being required and able to store up to 45 characters.

The interesting part of the firstName and lastName column definitions are our use of CHARACTER SET 'utf8' and COLLATE 'utf8_general_ci' to define the character sets and collation for these columns:

• By using CHARACTER SET 'utf8' in the column definitions we are instructing MySQL to use the utf8 character set for the characters stored in these columns. This will allow us to store the names of our GuestBook Web application’s visitors from all over the world whose names might require 3 bytes per character.
• By using COLLATE 'utf8_general_ci' in the column definitions we are instructing MySQL to use case insensitive collation on these columns. The benefit of this is that when we query on the guest table we will be able to order the result set on firtName and lastName and MySQL will order the result set using case insensitive collation on these columns.

The remaining statements in the script, the INSERT statements, insert rows into the table whose data will be used to test the GuestBook Web application.

NetBeans has built in support for running scripts against an already created database. Please follow these steps to run the above script against the guestbook database:

1. In NetBeans Services right click on the jdbc connection that was created when we created the guestbook database and select Execute Command to open a SQL Command window in the editor.
2. Copy the script from above and paste it into the SQL Command editor window and then click on the Run SQL icon located on the window’s button bar. Now fully expand the the guestbook jdbc connection in the Services pane and you should see the following:
    

We can see that NetBeans successfully applied our SQL script to the guestbook database creating the guest table which contains id, firstName and lastName columns. We can now issue a query against the table to retrieve the list of names we loaded the table with:

1. In the Services window, right click on the guest table’s entry and select Execute Command.
2. In the newly opened SQL Command window, type SELECT * FROM guestbook.guest ORDER BY firstName, lastName and then click on the command window’s Run SQL icon located in its icon bar to run the query against the guest table. When the query completes the result set will be displayed listing the 9 rows of data that we loaded into the guest table:
   

With our database preparation out of the way the next article will focus on the fun stuff – generating an ear-less dynamic web application, adding the Wicket framework to it and deploying it to GlassFish. Stay tuned!