MySQL Database and Java Desktop GUI Part 1 - UniMAP Portal

MySQL Database and Java Desktop GUI Part 1
http://www.javaguicodexample.com/javadesktopguidatabaseappsdev.html
| | |
| |Machine specification used for this task |
| |Pre-requirement |
| |Creating a Database |
| |Making a Connection to a Database |
| |Creating a Table |
| |Inserting a Sample Data |
| |Creating a New Java Desktop Project |
| |Running the Project for the First Time |
| |Customizing the GUI |
| |Re-run the Project and Testing Its Functionality |
| |In this series of three parts, we will learn how to build Java |
| |desktop GUI application that connected to MySQL database. This |
| |tutorial is quite long containing a lot of screen snapshots to |
| |make it as details as possible. The original tutorial can be found|
| |at netbeans.org. All credits must go to the original authors. |
| | |
| |Machine specification used for this task |
| |Intel Pentium Core 2 Duo, 2.2 GHz, |
| |Windows XP Pro SP2 + periodical patches + periodical updates. |
| |2 GB DDR2 RAM |
| |160 GB SATA HDD |
| |17" SyncMaster 713N monitor. |
| |Pre-requirement: NetBeans 6.x.x and MySQL 5.x.x |
| | |


Creating a Database

Firstly let create a database using MySQL Command Line Client console. We
cannot find how to create a MySQL database using NetBeans. However there
are wizard to create Java database for Derby database. After finishing the
database creation, exit and open NetBeans.

[pic]

SQL statement to create database is:

CREATE DATABASE tid5013studentrecord;










Change the database name accordingly if you want.
[pic]









When the NetBeans IDE launched, go to the Services pane, expand the
Databases folder and expand the Drivers sub-folder.

[pic]

Making a Connection to a Database
Well, NetBeans 6.0 provides drivers for JDBC-ODBC (Windows driver for
Microsoft Access/MSSQL), MySQL, Java and PostgreSQL database. This means
that no need for us to install third party driver for these databases as
version 5.5. Thanks to NetBEans and in the future there will be more
drivers for other dominant databases used in the market such as Oracle,
Sybase etc. A new driver can be added to NetBeans using the following
steps.
[pic]

And fill in the needed information in the following Figure. Get the third
party JDBC driver for various databases at sun.com.
[pic]
Next, select MySQL (Connector/J driver) and right-click mouse button.
Select Connect Using... context menu.
[pic]

The New Database Connection wizard launched. Use the following connection
string. Key in the MySQL username as root and its password that you use to
access your MySQL database.

jdbc:mysql://localhost:3306/tid5013studentrecord

The format for the connection string for this driver is:

jdbc:mysql://:/

Change the database name accordingly if needed. Click the OK button.

Using root user is not a good practice. We need to create MySQL users and
assign appropriate level of permissions and rights to those users and use
them in different level of database accesses such as users for update,
backup and many more. Left the root as the highest administrator similar to
Windows (Administrator) and Linux (root) system access. Click the Remember
password tick box if you want the password to be remembered so next time no
need to key-in the password any more. Click OK.

[pic]

If your connection is successful it will be notified (Connection
established) in the following frame and same as if it is fail. Click the OK
button.

[pic]
Now we are connected to the MySQL database through NetBeans. You can see
icon for the established connection as shown below.
[pic]
Creating a Table
Next step is to create table and then populate it with sample data. The
following table describes our sample table structure. (The red record has
some issue to be resolved later. There are some incompatibilities between
java.util.date and java.sql.date here. Quick solution is to use int or
String type for the stud_date_of_birth or we can separate the year, month
and day into different fields/columns).
|The structure for a studentrecord table |
|Column name |Data type |Constraints|
|student_id |VARCHAR(7) |NOT NULL |
| | |PRIMARY KEY|
|stud_first_name |VARCHAR(15) |NOT NULL |
|stud_last_name |VARCHAR(15) |NOT NULL |
|stud_date_of_birt|DATE |NOT NULL |
|h | | |
|stud_address |VARCHAR(50) |NOT NULL |
|stud_program |VARCHAR(20) |NOT NULL |
|stud_marital_stat|INT(2) |NOT NULL |
|us | | |
|stud_country |VARCHAR(20) |NOT NULL |


And the following is the SQL script for our table creation.
CREATE TABLE IF NOT EXISTS studentrecord (
student_id VARCHAR(7),
stud_first_name VARCHAR(15) NOT NULL,
stud_last_name VARCHAR(15) NOT NULL,
stud_date_of_birth DATE NOT NULL,
stud_address VARCHAR(50) NOT NULL,
stud_program VARCHAR(20) NOT NULL,
stud_marital_status INT(2) NOT NULL,
stud_country VARCHAR(20) NOT NULL,
PRIMARY KEY (student_id)
) ENGINE=innodb;

To execute this SQL script, select the previously established connection
(our MySQL database), right-click mouse and select Execute Command...
context menu.
[pic]








Type or copy-paste the SQL script into the SQL Command editor as shown
below. Make sure you have chosen a proper database as blue highlighted in
the Connection: field
[pic]

Then, run/execute the script by clicking the run/execute [pic]icon.

|[pic] | |



Verify the studentrecord database creation by using the following command
in new SQL Command editor. You can also combine and execute/run this code
together with the previous SQL script.

DESC studentrecord;

The following figure shows that our table has been successfully created.
Congrats!!!
Next step is to populate the studentrecord with sample data.

[pic]







Inserting a Sample Data

Use the following sample SQL scrip to insert sample data into out table.
You can try creating your own sample data. Launch and use new SQL Command
editor or you can overwrite the previous SQL script. Execute/run the script
as done previously.
INSERT INTO studentrecord VALUES(
'88889','Albukori','Zaman Khan','1969-07-08',
'4-5, Dead Wood Street 5, 12000 Sintok, Kedah','MSc. IT',
'1','Malaysia');
INSERT INTO studentrecord VALUES('87990','Haslina','Mahathir','1970-11-
12','345, New Smart Village, 17100 Nilai, N. Sembilan','MSc.
ICT','2','Malaysia');

INSERT INTO studentrecord VALUES(
'79678','Mohammed','Fajr','1975-04-20',
'Pearl Apt, Level 10, Al-Hijr, 45200 Abu Dhabi','MSc. Expert System',
'2','UEA');
INSERT INTO studentrecord VALUES(
'88799','Mustar','Mohd Dali','1979-06-24',
'345, Side Village, Kerian, 12300 Jawa Barat','MSc. MultiMedia',
'1','Indonesia');

INSERT INTO studentrecord VALUES(
'78998','Satkorn','Chengmo','1968-01-26',
'34 Uptown Street #4, Tech Park, 78100 Bangkok','MSc. IT',
'2','Thailand');

Any success or fail will be notified in the Output window at the bottom of
the NetBeans IDE. So don't worry. A screen snapshot is shown below. Notice
the different default colors used in the SQL script. Keywords, values and
table name are in different colors and together with the report in the
Output window, this makes our tasks in troubleshooting easier.
[pic]
Verify our data/values insertion by using the following SQL statement.
SELECT * FROM studentrecord;

[pic]

A complete MySQL script for this exercise is given below.
-- create a table
CREATE TABLE IF NOT EXISTS studentrecord (
student_id VARCHAR(7),
stud_first_name VARCHAR(15) NOT NULL,
stud_last_name VARCHAR(15) NOT NULL,
stud_date_of_birth DATE NOT NULL,
stud_address VARCHAR(50) NOT NULL,
stud_program VARCHAR(20) NOT NULL,
stud_marital_status INT(2) NOT NULL,
stud_country VARCHAR(20) NOT NULL,
PRIMARY KEY (student_id)
) ENGINE=innodb;

-- insert a sample data
INSERT INTO studentrecord VALUES(
'88889','Albukori','Zaman Khan','1969-07-08',
'4-5, Dead Wood Street 5, 12000 Sintok, Kedah','MSc. IT',
'1','Malaysia');
INSERT INTO studentrecord VALUES(
'87990','Haslina','Mahathir','1970-11-12',
'345, New Smart Village, 17100 Nilai, N. Sembilan','MSc. ICT',
'2','Malaysia');
INSERT INTO studentrecord VALUES(
'79678','Mohammed','Fajr','1975-04-20',
'Pearl Apt, Level 10, Al-Hijr, 45200 Abu Dhabi','MSc. Expert System',
'2','UEA');

INSERT INTO studentrecord VALUES(
'88799','Mustar','Mohd Dali','1979-06-24',
'345, Side Village, Kerian, 12300 Jawa Barat','MSc. MultiMedia',
'1','Indonesia');