Connecting to a MySQL Database in NetBeans IDE
Contributed and maintained by
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.
Expected duration: 25 minutes
The following topics are covered below:
Getting the Software
Before you begin, make sure you have the following software installed
on your computer:
- NetBeans IDE 5.5 (download)
- Java SE Development Kit (JDK™) version 5.0 or higher
- MySQL database
- JDBC Driver for MySQL (download)
Note: As an alternative to downloading the IDE and JDK separately, consider
downloading the Java SE
Development Kit 6u1 with NetBeans IDE 5.5 Bundle.
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:
- Run the self-extracting file. The MySQL Setup Wizard opens to guide you
through the installation process. Accept all default settings.
- Upon completing installation, allow the MySQL wizard to
immediately configure the server by making sure the Configure
the MySQL Server Now checkbox is selected. This will allow an
instance of the server to be created, which will run as a Windows
service on your machine.
- In the MySQL Server Instance Configuration wizard, select Standard
Configuration. Click Next. When you arrive at the step allowing you
to set Windows options, select the Include Bin Directory in Windows
PATH checkbox. This will later allow you to perform a simple check to
make sure the MySQL service is up and running:
- Set the root password to: nbuser. Finally, click Execute to
allow the wizard to generate the server instance on your computer. If
you encounter any problems, refer to the MySQL Reference Manual included
in your installation or the
Before continuing further, it is important to understand the components found in
MySQL's root directory:
- The bin subdirectory contains the scripts for
executing utilities and setting up the environment.
- The data subdirectory contains all database
instances, including their data.
- The Docs subdirectory contains the MySQL Reference Manual.
- The share subdirectory contains localization
files, including character sets and language packages.
- The my.ini file is the configuration file that was
generated by the Configuration wizard, and contains information such as the
port being listened on, path to installation directory, path to database root,
and default character set.
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. You need to
have a database instance already created in order to connect to the MySQL database
server in NetBeans IDE. To create a new database instance:
- Open up the MySQL Command Line Client from your Start > All Programs >
MySQL menu. The Command Line client displays prompting you to enter your
- Enter the password specified above (nbuser), and a brief
welcome message displays, introducing you to the MySQL monitor.
- Type create database MyNewDatabase; at the prompt. You should receive
a Query OK message, indicating that the new database has been created.
- To verify that MyNewDatabase has indeed been created, type
show databases; at the prompt. A list is displayed, showing all
databases maintained by the server. Note that MyNewDatabase is listed:
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
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
- Creating a Database Connection
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
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
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
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:
- For User Name and Password, enter root, and
- 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 () 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
- Using the SQL Editor
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:
- You are creating a table named Subject
that will hold the following data for each record:
- 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
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:
- Choose Execute Command from the Counselor
table node in the Database explorer. A blank canvas opens in the SQL
Editor in the main window.
- In the SQL Editor, type in the following query:
INSERT INTO Counselor
VALUES (1, 'Ricky', '"The Dragon"', 'Steamboat','334 612-5678', '....', '01-01')
- Click the Run SQL () 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
- 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 elsewhere, you
can simply open it in NetBeans IDE and run it in the SQL Editor.
For demonstrative purposes, download
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:
- 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.
- Make sure your connection to MyNewDatabase is selected from
the Connection drop-down box in the tool bar at the top of the Editor:
- Click the Run SQL () 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.
- To verify changes, right-click the MyNewDatabase 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
MyNewDatabase in the Database explorer.
- To view the data contained in the new tables, choose View Data from the
right-click menu of a 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.
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.
For related and more advanced tutorials, see the following resources: