This lesson describes the last preliminary step in developing the Wish List application, that of creating a sample database with test data. To complete the steps in this tutorial, you will need a database in which to store data of wishers. With the NetBeans IDE you can perform all these activities through the IDE interface.
Before starting, configure your development environment as described in the Tutorial Requirements section.
Creating the User of the Database
Before you create a database you need to create its User who will be granted the right to perform any operations on the database. Creating a database User involves:
Start the IDE, switch to the Services window, and expand the Databases node.
To connect to the MySQL database server, navigate to the MySQL Server node and from the context menu choose Connect. The MySQL Server Properties dialog box appears.
Fill in the fields:
In the Server host name field, type localhost.
In the Server port number field, type 3306.
In the Administrator user name field, type root.
In the Administrator password field, type the root user's password that you set during the installation of the MySQL server.
Switch on the Save password checkbox and click OK.
The NetBeans IDE connects to the MySQL server, checks for the databases available through the server, detects the system mysql database, and adds the corresponding new node mysql to the Databases tree.
To execute a user creation SQL command, you need to be connected to a database. Because only the MySQL system is available, you need to connect to it. To connect to the system database, navigate to the mysql node and from the context menu choose Connect. The New Database Connection dialog box appears. The User Name field is by default filled in with root. In the Password field, enter the root user's password.
The New Database Connection dialog box shows the message "Connection established.". Click OK. A new node jdbc:mysql://localhost:3306/mysql is added to the Databases tree.
Navigate to the jdbc:mysql://localhost:3306/mysql node and from the context menu choose Execute Command.
An SQL Command window opens. In the SQL Command window, use syntax similar to the following statement:
CREATE USER 'phpuser'@'localhost'
IDENTIFIED BY '!phpuser'
From the context menu, choose Run Statement. If the command is executed successfully, the Status bar shows the message: "SQL Statement(s) executed successfully". If another message is displayed, check the syntax and follow the message hints.
Creating the Sample Database
To create the database:
Navigate to the MySQL Server at localhost:3306 node and from the context menu choose Create Database. The Create MySQL Database dialog box appears. Fill in the fields:
In the Database Name field, enter wishlist.
Switch on the Grant full access to user checkbox and from the drop down list select Click OK.
The New Database Connection dialog box appears.
Establishing Connection to the Sample Database
In the New Database Connection dialog box, fill in the fields:
From the Name drop down list, choose MySQL (Connector/J driver)
In the Database URL edit box, specify the host, the port, and the database as follows:
jdbc:mysql://localhost:3306/wishlist
In the User Name and Password edit boxes, enter the name and the password specified in section Creating the Owner (User) of the Database (in our example phpuser and !phpuser respectively). Click OK. The corresponding new connection node is displayed in the Databases tree.
(Optional) Now you can delete the default mysql connection by navigating to the jdbc:mysql://localhost:3306/ node and choosing Disconnect and then Delete from the context menu.
Designing the Structure of the Sample Database
To arrange and store all the necessary data you need two tables:
A wishers table for storing names and passwords of registered users
A wishes table for storing descriptions of wishes
The wishers table contains three fields:
id - the unique ID of a wisher. This field is used as the Primary Key
name
password
The wishes table contains four fields:
id - the unique ID of a wish. The field is used as the Primary Key
wisher_id - the ID of the wisher to whom the wish belongs. The field is used as the Foreign Key.
description
due_date - the date by when the wish is requested
The tables are related through the wisher's ID. All the fields are mandatory except due_date in wishes.
Creating the Tables
To connect to the database, on the jdbc:mysql://localhost:3306/wishlist connection, click the right mouse button and choose Connect from the context menu. Note: If the menu item is disabled, you are already connected. Continue with step 2.
From the same context menu, choose Execute Command. An empty SQL Command window opens.
To create the wishers table,
Type the following SQL query:
CREATE TABLE wishers(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(50) NOT NULL UNIQUE,
password CHAR(50) NOT NULL )
Note: You can get a unique auto generated number from MySQL by specifying the AUTO_INCREMENT property for a field. MySQL will generate a unique number by incrementing the last number of the table and will automatically add to the auto incremented field. In our example the ID field is auto incremented.
Click the right mouse button on the query and then choose Run Statement from the context menu.
To create the wishes table:
Type the following SQL query:
CREATE TABLE wishes(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
wisher_id INT NOT NULL,
description CHAR(255) NOT NULL,
due_date DATE,
FOREIGN KEY (wisher_id) REFERENCES wishers(id) )
Click the right mouse button on the query and then choose Run Statement from the context menu.
To verify that the new tables are added to the database, switch to the Services window and then navigate to the jdbc:mysql://localhost:3306/wishlist connection node.
Click the right mouse button and choose Refresh. The nodes wishers and wishes appear in the tree.
Entering the Test Data
To test your application you will need some data in the database. The example below shows how to add two wishers and four wishes.
On the jdbc:mysql://localhost:3306/wishlist connection, click the right mouse button and choose Execute Command. An empty SQL Command window opens.
To add a wisher, use syntax similar to the example below:
INSERT INTO wishers (name, password)
VALUES ('Tom', 'tomcat');
Click the right mouse button on the query and from the context menu choose Run Statement. Note: The statement does not contain a value for the id field. The values in entered automatically because the field type is specified as AUTO_INCREMENT.
Enter another test wisher:
INSERT INTO wishers (name, password)
VALUES ('Jerry', 'jerrymouse');
To add the wishes, use syntax similar to the example below:
INSERT INTO wishes (wisher_id, description, due_date)
VALUES (1, 'Sausage', 080401); INSERT INTO wishes (wisher_id, description)
VALUES (1, 'Icecream'); INSERT INTO wishes (wisher_id, description, due_date)
VALUES (2, 'Cheese', 080501); INSERT INTO wishes (wisher_id, description)
VALUES (2, 'Candle');
Select the queries, click the right mouse button on each query and from the context menu choose Run Selection. Note: You can also execute the queries one after another as described in item 2.
To view the test data, click the right mouse button on the relevant table and from the context menu choose View Data.
