< NetBeans, Java DB Derby and GUI Desktop | Java & Friends | A Complete Java Desktop GUI & MySQL Database App 1 >

 


 

 

 

NetBeans 6.x.x and MySQL 5.x.x Database

 

 

 

 

Contents:

  1. Creating a Database Using MySQL

  2. Making a Connection to MySQL Database

  3. Creating a Table

  4. Inserting a Sample Data

  5. Related references

 

The machine specifications used in this task are:

  • 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 Database 5.x.x

 

Before we proceed to the next tutorial, upgrading our skill level, we would like to recap the NetBeans and MySQL database related thingy. We start learning to develop Java GUI application and then make a connection to a database using Java DB (Derby). Then we will try to change the database to MySQL while retaining other aspects of the Java GUI application.  During the process in completing the Java GUI application, we have learned a lot of things regarding the Java technologies and at the same time got familiar with NetBeans 6.x.x. We will try to developed Java application up to the web applications while experiencing all the related Java technologies. Stay tune!

 

Creating a Database

 

Firstly let create a new database by using the following SQL statement through MySQL Command Line Client Tool. In this exercise, our database name will be Mycompany. Take note that MySQL is not case sensitive. If you want to retain the case sensitiveness, try using the double quotes. We found that using the double quotes also not working all the times.

 

 

CREATE DATABASE Mycompany;

 

 

Making a Connection

 

For the next steps, we are using the NetBeans. Launch NetBeans. Click Services tab > expand Database node > expand Drivers node > select MySQL (Connector/J driver) > right click mouse > select Connect Using.

 

 

Use the following connection string and key in the username as root and its password. Click OK.

 

jdbc:mysql://localhost:3306/mycompany

 

 

If the connection to the database was established successfully, it will be confirmed as shown in the following Figure. Click OK.

 

 

Next, expand the connection node as shown below. You can see the Tables, Views and Procedures folders. These are common database objects that act as containers for other database objects that will be created later.

 

 

Creating a Table

 

Next, execute the following SQL script to create a table. Select the database connection node > right click mouse > select Execute Command to launch SQL query editor.

 

 

 

The following table summarized the table that we want to create. You can create the table shown in the Table using the following SQL script. Copy, paste and execute the following SQL script.

 

The structure for a Employees table

Column name

Data type

Constraints

userID

INT UNSIGNED

NOT NULL PRIMARY KEY

name

CHAR(10)

NOT NULL UNIQUE INDEX

lastName

VARCHAR(30)

 -

firstName

VARCHAR(30)

 -

office

CHAR(2)

NOT NULL

 

CREATE TABLE IF NOT EXISTS Employees(

   empl_id INT,

   empl_first_name VARCHAR(32) NOT NULL,

   empl_last_name VARCHAR(32) NOT NULL,

   empl_addr_street VARCHAR(32) NOT NULL,

   empl_addr_city VARCHAR(32) NOT NULL,

   empl_addr_zip VARCHAR(10) NOT NULL,

   empl_addr_state VARCHAR(2) NOT NULL,

   empl_dept_id VARCHAR(10) NOT NULL,

   empl_start_date DATE NOT NULL,

   empl_position VARCHAR(5) NOT NULL,

   empl_birth_date DATE NOT NULL,

   PRIMARY KEY (empl_id)

) ENGINE=innodb;

 

The following Figure shows the SQL script in the SQL query editor/SQL Command editor.

 

 

Make sure the connection to the correct database as shown in the following Connection: text field.

 

 

Next, click the Run SQL button () or Ctrl+Shift+E to execute the SQL script and notice the progress in the Output window at the bottom.

 

 

Next, expand the Tables > employees. MySQL is not case sensitive. If you want to retain the case sensitiveness, use double quotes.

 

 

The Employees table contains the following columns:

 

 

Verify our task by issuing the following SQL statement (describe a table).

 

DESC Employees;

 

 

The following Tables list MySQL numerical and character data types information. A complete information can be found in MySQL documentation and you can also download it for offline reading.

 

Numeric types in MySQL

TINYINT

Whole 7-bit numbers in the range -128 to 127.

SMALLINT

Whole 8-bit numbers in the range -32758 to 32,757.

MEDIUMINT

Whole 16-bit numbers in the range -8,388,608 to 8,388,607.

INT

Whole 32-bit numbers in the range -2,147,483,548 to 2,147,483,547.

BIGINT

Whole 64-bit numbers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

DECIMAL(p,s)

Decimal values with s as the scale and p as the precision.

DOUBLE(p,s)

Double-precision values with s as the scale and p as the precision.

FLOAT(p)

Floating point numbers with a precision of 8 or less.

 

Character types for MySQL

CHAR(n)

Fixed-length character type that holds exactly n characters. Shorter strings are padded with spaces to n characters.

NCHAR(n)

Same as CHAR, except for Unicode strings.

VARCHAR(n)

Variable-length strings that may store up to n characters. Any excess characters are discarded.

NVARCHAR(n)

Same as VARCHAR, except for Unicode strings.

 

Inserting a Sample Data

 

Next, let insert a record by running the following SQL statement.

INSERT INTO Employees VALUES(

'1234','Jodie','Foster','45th Street, Level 3','New York','32000',

'NY','IT','2008-07-04','Dir','1970-08-05');

 

Then, verify our task by issuing the following SQL statement.

 

SELECT * FROM Employees;

 

 

Related references:

 

  1. Java database technologies

  2. Java jdbc drivers

  3. NetBeans databases and drivers information

 

 

 

 

 

 

 

 

 


 

< NetBeans, Java DB Derby and GUI Desktop | Java & Friends | A Complete Java Desktop GUI & MySQL Database App 1 >