Contents:
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 tableCREATE 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 dataINSERT 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 StudentRecordApp project 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 Delete
button) 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.Date package 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.
