MySQL Database and Java Desktop GUI Part 1
|
|
Contents:
1. Machine specification used for this task
2. Pre-requirement
3. Creating a Database
4. Making a Connection to a Database
5. Creating a Table
6. Inserting a Sample Data
7. Creating a New Java Desktop Project
8. Running the Project for the First Time
9. Customizing the GUI
10.
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
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.
SQL statement to create
database is:
CREATE DATABASE tid5013studentrecord;
Change the database name
accordingly if you want.
When the NetBeans IDE
launched, go to the Services pane, expand the Databases folder
and expand the Drivers sub-folder.
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.
And fill in the needed
information in the following Figure. Get the third party JDBC driver for
various databases at sun.com.
Next, select MySQL
(Connector/J driver) and right-click mouse button. Select Connect
Using… context menu.
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://<hostname>:<database_access_port_number>/<database_name>
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.
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.
Now we are connected to
the MySQL database through NetBeans. You can see icon for the established
connection as shown below.
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_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
|
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.
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
Then, run/execute the
script by clicking the run/execute 
icon.
icon.
 |
|
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.
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.
Verify our data/values
insertion by using the following SQL statement.
SELECT * FROM studentrecord;
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');
INSERT INTO
studentrecord VALUES(
'78998','Satkorn','Chengmo','1968-01-26',
'34 Uptown Street #4,
Tech Park, 78100 Bangkok','MSc. IT',
'2','Thailand');
Next step is to create a
GUI for our database access and manipulation. The following screen snapshots
are self-explanatory.
Creating a New Java
Desktop Project
Well, click File >
select New Project.
Select Java in
the Categories: and Java Desktop Application in
the Projects: pane. Click Next.
Put the project name
as StudentRecordApp and change the location if needed else
just leave as it is. Select the Database Application in
the Choose Application Shell pane. Click Next.
Select our previously
created database connection in the Database Connection text
field. We only have one table here, so no need to select any as shown in
the Database Table: field. In this case we include all the
table columns. You can exclude some columns if needed. Click Next button.
By default the first
radio button has been selected. Just leave as it is. Click Finish button
and wait.
Here you are! Our
database GUI template! This GUI can be built and run/executed as other Java
project.
You can view the GUI by
clicking the Preview Design icon (
).
).
Running the Project for
the First Time
Next let see our real
GUI in action. You can build first and then run. In this case we directly run
the project (in the process it will be built as other Java project). Select
the StudentRecordAppproject folder, right-click mouse and
select Run menu.
Here is the Real GUI.
Try the File menu and its sub-menu. In this case not all the
record was displayed. Only the first three and this is our job to find the
cause and it is a normal process in programming! Select any record and test
the New (click New button and key-in new data
and click Save to update new data), Delete (select
a record to be deleted and press the Deletebutton) and Refresh (refresh
the connection and update the data) buttons. All controls supposed to be
working else we need to find the reasons and resolve it.
Customizing the GUI
Close this application.
Let go back to the design page. Select the Stud Date Of Birth text field. Go to
the Properties sheet and click the Binding.
Click the ellipses (…)
at the end of the text field.
Try selecting the date
int type in the Binding Expression: field, expand
the studDateOfBirth java.util.Date and select date int
and then click OK.
Re-run the Project and
Testing Its Functionality
Re-run this project
again.
Select any row of the
table. All the record displayed but the Stud Date Of Birth just display the day
only. However this program can be said 97% working.
If we want to add other
component either to bind to the database table or not it is depend on our
creativities now.
-----------------------------------------------------------------------------------------------------------------
Close
the application and back to the design. Select, drag and drop the position of
the table grid to the bottom of the frame as shown below.
Re-run
this project again and see the result!
Regarding
the Date Of Birth (DOB), well we think here is the reason. Searching in the
Internet, the reason is the java.util.Date is not compatible
with the java.sql.Date. Older java.util.Datepackage
that contains date only already deprecated. The new version of this java.util.date contains
date and time as well, while the java.sql.util only contains
the date. Coding wise, we need to parse or convert to the java.sql.Date format.
The validation and conversion features already available in the Java Server
Faces (JSF) web development.
As
said before we can fix this problem immediately. Easy solution is to use
a String type for the stud_date_of_birth replacing the DATE
data type. From this simple issue, that is why the database design stage is
very important.
|
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_birth
|
VARCHAR(10)
|
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
|
Or we can separate the
year, month and day into different fields/columns. (We have tried this, also
failed).
|
…
|
…
|
…
|
|
stud_last_name
|
VARCHAR(15)
|
NOT NULL
|
|
stud_dob_day
|
INT(2)
|
NOT NULL
|
|
stud_dob_month
|
INT(2)
|
NOT NULL
|
|
stud_dob_year
|
INT(4)
|
NOT NULL
|
|
stud_address
|
VARCHAR(50)
|
NOT NULL
|
|
…
|
…
|
…
|
And
for this case we need to create (I mean, NetBeans create for us and we
re-arrange it in the frame) different fields for each column and we need to
re-arrange the day, month and year in the standard format in the GUI such as:
You
can try both solution and let see the outputs however we will solve this issue
in the next tutorial, replacing the DATA type to String and we will show the
steps on how to edit a table while designing the GUI interface.
Continue
to the next stage: Adding more functionalities and features.
0 comments:
Post a Comment