testwww: Connecting to a MySQL Database in NetBeans IDE 5.5

This document demonstrates how to configure MySQL on your system and set up a connection to the database from NetBeans IDE. Once connected, you can begin working with MySQL in the IDE's Database explorer by creating new tables, populating tables with data, and running SQL queries on database structures and content. This tutorial is designed for beginners with a basic understanding of database management and application development, who want to apply their knowledge in working with MySQL in NetBeans IDE.

MySQL is a popular Open Source database management system commonly used in web applications due to its speed, flexibility and reliability. MySQL employs SQL, or Structured Query Language, for accessing and processing data contained in databases.

Note: This document uses the NetBeans IDE 5.5 Release. If you are using NetBeans IDE 6.5, see Connecting to a MySQL Database.

Getting the Software

Before you begin, make sure you have the following software installed on your computer:

Installing and Configuring the Database

If you already have the MySQL database set up and running on your machine, please skip ahead to Registering the Database in NetBeans IDE. By way of example, this tutorial demonstrates how to run the MySQL Community Server on Windows XP. To get the MySQL server running:

Before continuing further, it is important to understand the components found in MySQL's root directory:

Creating a Database Instance

Now that the MySQL database server is installed and configured, you can create a database instance which you will later connect to in NetBeans IDE. To create a new database instance:

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 (

). 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

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 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.
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.
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:
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. This is covered in the follow-up tutorial Creating a Simple Web Application in NetBeans IDE using MySQL.

Creating a Database 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:

Open a Windows command shell (from the Start menu, choose Run and type cmd in the text field). A command line window displays.
At the prompt, type sc query MySQL 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 at the command line prompt, or using the Windows Services GUI (Start > Control Panel > Administrative Tools > Services).

Now, in NetBeans IDE, create a connection to MyNewDatabase:

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.
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/MyNewDatabase.
For User Name and Password, enter root, and nbuser respectively:
Click OK, then click 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 MyNewDatabase in the IDE. Note that the new connection node icon appears whole () 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.

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. As an example, you can prepare the database for use in the web application developed in the follow-up tutorial Creating a Simple Web Application in NetBeans IDE using MySQL.

The MyNewDatabase database that you are using is currently empty. 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:

Using the Create Table Dialog

In the Database explorer, expand the MyNewDatabase connection node () 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.
In the Table Name text field, type Subject.
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.
For Column Name, enter id. For Data Type, select SMALLINT from the drop-down list. Then click the Add Column button.

Repeat this procedure by specifying all remaining fields, as shown in the table below:

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:
- Name
- Description
- Counselor ID
When you are sure that your Create Table dialog contains the same specifications as those shown above, click OK. The IDE generates the Subject table in the database, and you see a new Subject table node () display under Tables in the Database explorer. Beneath the table node there are the columns (fields) you created, starting with the primary key ( ):

Using the SQL Editor

In the Database explorer, right-click the Tables node beneath the MyNewDatabase connection node and choose Execute Command. A blank canvas opens in the SQL Editor in the main window.
In the SQL Editor, type in the following query. This is a table definition for the Counselor table you are about to create:
```
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)
);                      
                    
```
Note: 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.
Click the Run SQL () 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:
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 () 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 on 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:

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 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. 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 MyNewDatabase:

Next Steps

This concludes the Connecting to a MySQL Database in NetBeans IDE tutorial. This document demonstrated how to quickly configure MySQL on your system and set up a connection to the database from NetBeans IDE. It also described how to work with MySQL in the IDE's Database explorer by creating new tables, populating tables with data, and running SQL queries.