< Install, test and Use NetBeans 6.x.x | Main | NetBeans & Java Desktop GUI 1 >

 


 

 

 

NetBeans 6.x.x and MySQL 5.x.x:

Connection String

and Database Manipulation Exercise

 

 

 

 

 

Contents:

  1. Registering the Database in NetBeans IDE

  2. Adding the Driver to the IDE (NetBeans 5.x)

  3. Creating a MySQL Database and a Connection

  4. Creating MySQL Database Tables

  5. Using the Create Table Dialog

  6. Using the SQL Editor

  7. Working with Table Data

  8. Running an MySQL SQL Script

 

The machine specifications used in 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

  • NetBeans IDE 6.x.x

  • MySQL 5.x.x

  • A sample MySQL script: ifpwafcad.sql

 

Previously we already play around with MySQL database. We also play around with SQL. In this tutorial we are trying to access our databases through Java.

 

Registering the Database in NetBeans IDE

 

Now that you have the database server installed and configured, and have created a new database, you can register the MySQL server in NetBeans IDE. Begin by examining the functionality offered by the Database explorer located in the IDE's Runtime window (Ctrl+5). The Database explorer is represented by the Databases node (database node icon) in the Services window. From this interface you can connect to databases, view current connections, add database drivers, as well as create, browse or edit database structures.

 

 

You can use the Database explorer to register MySQL in the IDE. There are two simple steps that need to be performed:

 

Adding the Driver to the IDE (NetBeans 5.x)

 

In order to allow NetBeans IDE to communicate with a database, you need to employ a Java-based driver. Generally speaking, drivers in NetBeans IDE use the JDBC (Java Database Connectivity) API to communicate with databases supporting SQL. The JDBC API is contained in the java.sql Java package. A driver therefore serves as an interface that converts JDBC calls directly or indirectly into a specific database protocol.

In this tutorial, you are using the MySQL Connector/J driver, which is a pure Java implementation of the JDBC API, and communicates directly with the MySQL server using the MySQL protocol. To add the driver to the IDE, if needed (for NetBeans 6.x.x, no need to do this because the driver has been bundled together with the installation file):

 

  1. If you have just downloaded the driver, first extract it to a location on your computer. Set the root directory to: C:\mysql-connector-java-5.0.5.

  2. In the IDE, in the Database explorer from the Runtime window (Ctrl+5) expand the Databases node and right-click the Drivers node. Choose New Driver. The New JDBC Driver dialog opens.

  3. Click the Add button in the top right corner. Navigate to the driver's root directory and select the driver's jar file (e.g. mysql-connector-java-5.0.5-bin.jar). Click Open. The New JDBC Driver dialog should look like this:

 

 

  1. Click OK. In the Runtime window expand the Databases > Drivers nodes and note that a new MySQL driver node is displayed:

 

 

Note: While you just made the database driver available to the IDE, you have not yet made it available to any specific application. At this stage, you can use the IDE to access and modify the database, but cannot do so from an application yet.

 

For NetBeans 6.0 the MySQL (Connector/J driver) already bundled into the installation. You can view it in the Services windows as shown below. You may need to open a project (any project) to make it visible.

 

 

Creating a Database and a Connection

 

You can now set up a connection to the database through the appropriate driver. Make sure your database service is running prior to continuing. You can do so by performing the following simple test:

 

  1. Open a Windows command shell (from the Start menu, choose Run and type cmd in the text field). A command line window displays.

  2. At the prompt, type sc query your_MySQL_service_instance_name (depending on your MySQL service name and in this case it is MySQL. You can check it through Windows Services: Start > Control Panel > Administrative Tools > Services and press Enter. The Windows sc command allows you to query the state of services. The output should indicate that the current state of the MySQL service is RUNNING:

 

 

If the service is STOPPED, you can start it by either typing sc start MySQL_instance _service_name at the command line prompt, or using the Windows Services GUI (Start > Control Panel > Administrative Tools > Services > Select MySQL service name > Right click mouse >  Start/Restart the service). Let create a database using the MySQL Command Line Client (console), click Start > All Programs > MySQL > MySQL 5.0 > MySQL Command Line Client.

 

 

  1. Create myhotel database by executing the following MySQL statement.

 

mysql> CREATE DATABASE myhotel;

 

 

  1. Verify the myhotel database creation by using show databases; statement.

 

 

Next, in NetBeans IDE, create a connection to myhotel database. Open NetBeans IDE.

 

  1. In the Runtime window (Ctrl+5) choose Connect Using from the right-click menu of the driver you just added. The New Database Connection dialog opens.

 

 

 

  1. In the Basic Setting tab, enter the Database's URL in the corresponding text field. The URL is used to identify the type and location of a database server. In this example, you need to specify the host name (i.e. the location of the server), the port number on which the database communicates, and the name of the database instance being used. In this case you can enter:

 

jdbc:mysql://localhost:3306/myhotel

 

  1. For User Name and Password, enter root, and the password that you use to access the MySQL database for root respectively:

 

 

  1. Click OK and it goes to the Advanced page with a confirmation that a connection established (else the message should be replaced by an error message), and then clicks OK again to exit the dialog. A new Connection node displays in the Runtime window's Database explorer under the Databases node:

 

 

 

You are now connected to myhotel database from the IDE. Note that the new connection node icon appears whole (connection node icon) when you are connected to a database. Likewise, it appears broken (broken connection node icon) when there is no connection. At later stages, when working with databases through the Database explorer, you may need to manually connect to a database. You can do so by right-clicking the broken database connection node and choosing Connect menu.

 

Creating Database Tables

 

Now that you have connected to the database, you can begin exploring how to create tables, populate them with data, and modify data maintained in tables. This allows you to take a closer look at the functionality offered by the Database explorer, as well as NetBeans IDE's support for SQL files. Keep in mind that in this exercise we are using the MySQL Command Line Client tool and the NetBeans IDE on and off, so make sure both are opened until we finish this exercise.

In this case, the myhotel database that we are using already contains some tables from the previous exercise (get the SQL script if you want, however these tables will not be used in this exercise except, we will create a Booking table later). Your myhotel database should be empty and we will add new table.

 

 

In NetBeans IDE it is possible to add a database table by either using the Create Table dialog, or by inputting an SQL query and running it directly from the SQL editor. Here you can explore both methods:

 

  1. Using the Create Table Dialog

  2. Using the SQL Editor

 

Using the Create Table Dialog

 

  1. In the Database explorer, expand the myhotel connection node (connection node icon) and note that there are three subfolders: Tables, Views and Procedures. Right-click the Tables node and choose Create Table. The Create Table dialog opens.

 

 

  1. In the Table Name text field, type Subject.

  2. In the first row displayed, select the Key check box. You are specifying the primary key for your table. All tables found in relational databases must contain a primary key. Note that when you select the Key check box, the Index and Unique check boxes are also automatically selected and the Null check box is deselected. This is because primary keys are used to identify a unique row in the database, and by default form the table index. Because all rows need to be identified, primary keys cannot contain a Null value.

  3. For Column Name, enter id. For Data Type, select SMALLINT from the drop-down list. Then click the Add Column button.

 

 

 

  1. Repeat this procedure by specifying all remaining fields, as shown in the following table. In this case, select the Subject table, right click and select Add column.

 

Key

Index

Null

Unique

Column name

Data type

Size

[checked]

[checked]

 -

[checked]

id

SMALLINT

0

 -

 -

[checked]

 -

name

VARCHAR

50

 -

 -

[checked]

 -

description

VARCHAR

500

 -

 -

[checked]

 -

counselor_idfk

SMALLINT

0

 

 

 

You are creating a table named Subject that will hold the following data for each record.

 

 

The IDE generates the Subject table in the database, and you see a new Subject table node (table node icon) display under Tables in the Database explorer. Beneath the table node there are the columns (fields) you created, with the primary key ( primary key node icon).

 

 

Using the SQL Editor

 

  1. In the Database explorer, right-click the Tables node and choose Execute Command. A blank canvas opens in the SQL Editor in the main window.

 

 

 

  1. In the SQL Editor, type in the following SQL script for creating a table. This is a table definition for the Counselor table you are about to create (again, take note that we can also run this SQL script using the MySQL Command Line Client tool):

 

-- creating a table

CREATE TABLE Counselor (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    first_name VARCHAR (50),

    nick_name VARCHAR (50),

    last_name VARCHAR (50),

    telephone VARCHAR (25),

    email VARCHAR (50),

    member_since DATE DEFAULT '0000-00-00',

    PRIMARY KEY (id)

)type=innodb;

 

 

Queries formed in the SQL Editor are parsed in Structured Query Language (SQL). SQL adheres to strict syntax rules which you should be familiar with when working in the IDE's Editor. Upon running a query, feedback from the SQL engine is generated in the Output window indicating whether execution was successful or not.

 

  1. Click the Run SQL (run SQL button) button in the task bar at the top (or, press Ctrl+Shift+E to execute the query). You should receive the following message in the Output window:

 

 

  1. To verify changes, right-click the Tables node in the Database explorer and choose Refresh. The Refresh option updates the Database explorer's UI component to the current status of the specified database. This step is necessary when running queries from the SQL Editor in NetBeans IDE. Note that the new Counselor table node (table node icon) now displays under Tables in the Database explorer.

 

 

Working with Table Data

 

In order to work with table data, you can make use of the SQL Editor in NetBeans IDE. By running SQL queries against a database, you can add, modify and delete data maintained in database structures. To add a new record (row) to the Counselor table, do the following:

 

  1. Choose Execute Command from the Tables node in the Database explorer. A blank canvas opens in the SQL Editor in the main window.

 

 

  1. In the SQL Editor, type in the following query:

 

INSERT INTO Counselor 
VALUES (1, 'Ricky', '"The Dragon"', 'Steamboat','334 612-5678',
 'r_steamboat@ifpwafcad.com', '1996-01-01')

 

 

  1. Click the Run SQL (run SQL button) button in the task bar at the top, or press Ctrl+Shift+E to execute the query. You should receive a message in the Output window indicating that the query was executed successfully.

 

 

  1. To verify that the new record has been added to the Counselor table, in the Database explorer, right-click the Counselor table node and choose View Data. A new SQL Editor pane opens in the main window. When you choose View Data, a query to select all the data from the table is automatically generated in the upper region of the SQL Editor. The results of the statement are displayed in a table view in the lower region. In this example, the Counselor table displays. Note that a new row has been added with the data you just supplied from the SQL query:

 

 

 

Running an SQL Script

 

Another way to manage table data in NetBeans IDE is by running an external SQL script directly in the IDE. If you have created an SQL script (.sql) elsewhere, you can simply open it in NetBeans IDE and run it in the SQL Editor.

For demonstrative purposes, download ifpwafcad.sql and save it to a location on your computer.

DROP TABLE IF EXISTS Counselor;

DROP TABLE IF EXISTS Subject;

 

CREATE TABLE Counselor (

       counselor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

       first_name VARCHAR (50),

       nick_name VARCHAR (50),

       last_name VARCHAR (50),

       telephone VARCHAR (25),

       email VARCHAR (50),

       member_since DATE DEFAULT '0000-00-00',

       PRIMARY KEY (counselor_id)

);

 

CREATE TABLE Subject (

      subject_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

      name VARCHAR (50),

      description TEXT,

      counselor_idfk SMALLINT UNSIGNED,

      PRIMARY KEY (subject_id)

);

 

INSERT INTO Counselor

       VALUES     (null, 'Jake', '"The Snake"', 'Roberts', '412 565-5656', 'snake@ifpwafcad.com', '2003-09-10'),

                  (null, 'Andre', '"The Giant"', '', '606 443-4567', 'bobo@ifpwafcad.com', '2001-01-12'),

                  (null, 'Brutus', '"The Barber"', 'Beefcake', '555 555-4432', 'beefcake@ifpwafcad.com', '2005-03-08'),

                  (null, 'Randy', '"Macho Man"', 'Savage', '555 317-4444', 'machoman@hotmail.com', '2000-11-10'),

                  (null, 'Ricky', '"The Dragon"', 'Steamboat','334 612-5678', 'r_steamboat@ifpwafcad.com', '1996-01-01'),

                  (null, 'George', '"The Animal"', 'Steele', '412 565-5656', 'george@ifpwafcad.com', Now()),

                  (null, 'Koko', '"The Birdman"', 'B. Ware', '553 499-8162', 'birdman@ifpwafcad.com', '1999-12-03'),

                  (null, 'Greg', '"The Hammer"', 'Valentine', '617 889-5545', 'valentino@ifpwafcad.com', '1998-05-07'),

                  (null, 'Bobby', '"The Brain"', 'Heenan', '777 513-3333', 'b_heenan@ifpwafcad.com', '2002-07-09');

 

INSERT INTO Subject

      VALUES  (null, 'Financial Consultancy', 'Investment advice and financial planning guidance, helping you to maximize your net worth through proper asset allocation. This includes the stocks, bonds, mutual funds, insurance products, and gambling strategies proven to work.', '9'),

            (null, 'Existential Psychotherapy', 'Often wonder what the purpose of life is? After learning the basics of Existential Psychotherapy, you''ll understand why you''re happy when you''re feeling happy, and why you''re not feeling happy when you''re not happy, allowing you to transcend to a state of pure bliss.', '7'),

              (null, 'Temper Management', 'Are your angry outbursts affecting your relationships with loved-ones? Do tantrums at work hinder your ability to perform? Temper management helps you to channel your anger into positive, life-changing productivity.', '4'),

            (null, 'Past-Life Regression', 'Past-Life Regression is a journey of the soul, backward and forward through time, like a yo-yo.', '2'),         

                  (null, 'Marriage Guidance', 'Even if you share a solid, caring and mutually beneficial relationship with your spouse, you may both still need urgent counseling. There''s only one way to find out. Contact us now!', '1'),

                  (null, 'Crisis Management', 'Whether you''re a fireman, executive CEO, or housewife, applying crisis management techniques at the right moment can be life-saving for you as well as all those around you.', '3'),

                  (null, 'Dream Analysis', 'Dream Analysis will allow you to delve into the depths of your subconscious. Your counselor will put you through a rigorous, disciplined training program, allowing you to remain in a waking state while dreaming. By the end, you''ll be able to analyze your dreams while you are having them!', '8'),           

                  (null, 'Hypnosis', 'Contrary to popular belief, hypnosis can be a powerful and effective form of counseling.', '6'),

            (null, 'Reiki', 'Need a massage but are afraid to let a stranger touch your body? Reiki could be the perfect solution for you.', '5');

This script creates two tables similar to what you just created above (Counselor and Subject), and immediately populates them with data. To run this script on myhotel database:

 

  1. Choose File > Open File (Ctrl+O) from the IDE's main menu. In the file browser navigate to the location where you previously saved ifpwafcad.sql and click Open. The script automatically opens in the SQL Editor.

 

 

 

 

  1. Make sure your connection to myhotel is selected from the Connection drop-down box in the tool bar at the top of the Editor. You not to worry about the same table names that this SQL use because we include the SQL check and drop statements for those tables if they are exist in the first place as can be seen for the first two statements:

 

 

  1. Click the Run SQL (run SQL button) button in the SQL Editor's task bar. The script is executed against the selected database, and any feedback is generated in the Output window.

  2. To verify changes, right-click the myhotel connection node in the Runtime window and choose Refresh. The Refresh option updates the Database explorer's UI component to the current status of the specified database. Note that the two new tables from the SQL script now display as a table nodes under myhotel in the Database explorer.

 

 

  1. To view the data contained in the new tables, choose View Data from the right-click menu of the selected table node. In this manner, you can compare the tabular data with the data contained in the SQL script to see that they match.

 

 

 

 

In the next exercise let create and populate some data into the Booking table. Use the following SQL script.

 

 

 

 

DROP TABLE IF EXISTS Booking;

CREATE TABLE Booking;

 

INSERT INTO Booking VALUES('J213','G333','2003-03-04','2003-03-10','5');

INSERT INTO Booking VALUES('L421','G576','2003-04-07','2003-04-12','6');

INSERT INTO Booking VALUES('P201','G323','2003-05-12','2003-05-21','7');

INSERT INTO Booking VALUES('J201','G211','2003-06-01','2003-06-07','8');

INSERT INTO Booking VALUES('H411','G230','2003-07-27','2003-08-01','9');

INSERT INTO Booking VALUES('P710','G218','2003-08-09','2003-09-01','10');

 

 

  1. Click the Run SQL (run SQL button) button in the SQL Editor's task bar. If there is no error, view the data.

 

 

As a conclusion, what you have done using the MySQL Command Line Client console previously can be done using NetBeans SQL Editor in more interactive way. At this stage we are supposed to be able to create and manipulate MySQL database and its objects using NetBeans 6.x.x.

 

 

 

 

 

 

 

 


 

< Install, Test and Use NetBeans 6.x.x | Main | NetBeans & Java Desktop GUI 1 >