HOME / Docs & Support / NetBeans 6.0 / Basic IDE Documentation

Working with the Java DB (Derby) Database

This document demonstrates how to set up a connection to Sun's Java DB database in NetBeans IDE. Once a connection is made, you can begin working with the database in the IDE, allowing you to create tables, populate them with data, run SQL statements and queries, and more.

The Java DB database is Sun's supported distribution of Apache Derby. Java DB is a fully transactional, secure, standards-based database server, written entirely in Java, and fully supports SQL, JDBC API, and Java EE technology. The Java DB database is packaged with the GlassFish V2 application server, and is now included in JDK 6 as well. For more information on Java DB database, consult the official documentation.

Contents

Content on this page applies to NetBeans IDE 6.0

Configuring the Database
Registering the Database in NetBeans IDE
Starting the Server and Creating a Database
Connecting to the Database
Creating Tables
Adding Table Data
Deleting Tables
Using an External SQL Script
Recreating Tables from a Different Database
See Also

To follow this tutorial, you need the following software and resources.

Software or Resource	Version Required
NetBeans IDE	version 6.0
Java Developer Kit (JDK)	version 6 or version 5
Java DB	version 10.3.x

Note: Java DB is included in JDK 6. If you are installing the Web and Java EE version (or full installation) of NetBeans IDE, you can optionally install the GlassFish V2 application server, which also includes Java DB. Alternatively, if you are working with the Sun Java System Application Server, note that the Java DB database is included.

Configuring the Database

If you have the GlassFish Server registered in your NetBeans IDE installation, Java DB will already be registered for you. You can skip ahead to Starting the Server and Creating a Database. If you downloaded GlassFish (or Sun Java System Application Server) separately, and need help registering it in NetBeans IDE, see Registering a Sun Java System Application Server/GlassFish Instance in the IDE's Help Contents (F1). If you just downloaded Java DB on its own, do the following:

Run the self-extracting file. A folder named 'javadb' will be created in the same location as the file. If you just downloaded Java DB and want to have the database server reside in a different location than where it was extracted to, you should relocate it now.
In the Java DB root directory (javadb), create a new folder named 'databases'. You will later use this folder to contain individual instances of the database server. Note that this folder does not need to reside in the database root directory.

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

The demo subdirectory contains the demonstration programs.
The bin subdirectory contains the scripts for executing utilities and setting up the environment.
The javadoc subdirectory contains the API documentation that was generated from source code comments.
The docs subdirectory contains the Java DB documentation.
The lib subdirectory contains the Java DB jar files.
The frameworks subdirectory contains older style scripts for executing utilities and setting up the environment. These are generally provided for backward compatibility. In later releases, they may be deprecated in favor of the scripts in the bin directory.

Registering the Database in NetBeans IDE

Now that the database is configured, you can register it in the IDE:

In NetBeans IDE choose Tools > Java DB Database > Settings from the main menu. The Java DB Settings dialog opens.
For the Java DB Installation text field, enter the path to the Java DB root directory (javadb) which you specified in the previous step.
For Database Location, set the path to the newly created 'databases' folder. The screen capture below displays example settings for the Java DB server on a Unix-based system. When you are finished, click OK.

Starting the Server and Creating a Database

The Java DB Database menu option displays under Tools in the main menu. This menu item allows you to start and stop the database server, create a new database instance, as well as register database servers in the IDE (as demonstrated in the previous step). To start the database server:

Choose Tools > Java DB Database > Start Java DB Server. Note the following output in the Output window, indicating that the server has been started:
Choose Tools > Java DB Database > Create Database. The Create Java DB Database dialog opens.
For the Database Name text field, type contact. Also set User Name and Password to nbuser. Note that Database Location is set based on your input from step 3 of Registering the Database in NetBeans IDE above. If you are using Java DB from GlassFish, the location will be set elsewhere (e.g. ${HOME}\.netbeans-derby). Click OK. The screenshot below is provided as a sample:

Connecting to the Database

So far, you have successfully started the the database server and created a database instance named contact in the IDE. NetBeans IDE's Database Explorer, available from the Services window, provides functionality for common tasks on database structures. This includes:

creating, deleting, modifying tables
populating tables with data
viewing tabular data
executing SQL statements and queries

In order to begin working with the contact database, you need to create an exclusive connection to it. To connect to contact:

Expand the Database Explorer in the Services window (Ctrl-5) and locate the new database:

Right-click the database connection node (jdbc:derby://localhost:1527/contact_database[nbuser on NBUSER]) and choose Connect.
In the Connect dialog that displays, enter the password (nbuser) then click OK. Note that the connection node icon now appears whole (), signifying that the connection was successful. Likewise, it appears broken () when there is no connection.

Creating Tables

The contact database that you just created is currently empty. It does not yet contain any tables or data. In NetBeans IDE you can add a database table by either using the Create Table dialog, or by inputting an SQL statement and running it directly from the SQL Editor. You can explore both methods:

Using the Create Table Dialog
Using the SQL Editor

Using the Create Table Dialog

Expand the contact 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 FRIENDS.
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 are used as the table index. Because all rows must be identified, primary keys cannot contain a Null value.
For Column Name, enter id. For Data Type, select INTEGER from the drop-down list. Then click the Add Column button.

Repeat this procedure now by specifying fields as shown in the table below:

Key	Index	Null	Unique	Column name	Data type	Size
[checked]	[checked]		[checked]	id	INTEGER	0
		[checked]		firstName	VARCHAR	20
		[checked]		lastName	VARCHAR	20
		[checked]		nickName	VARCHAR	30
		[checked]		friendSince	DATE	0
		[checked]		email	VARCHAR	60

You are creating a table named FRIENDS that holds the following data for each contact record:

First Name
Last Name
Nick Name
Friend Since Date
Email Address

Create Table dialog with selected fields for contacts table

When you are sure that your Create Table dialog contains the same specifications as those shown above, click OK. The IDE generates the FRIENDS table in the database, and you can see a new FRIENDS table node () display under Tables in the Database Explorer. Beneath the table node the columns (fields) are listed, starting with the primary key ():

Using the SQL Editor:

In the Database Explorer, either right-click the contact connection node or the Tables node beneath it and choose Execute Command. A blank canvas opens in the SQL Editor in the main window.
Enter the following query in the SQL Editor. This is a table definition for the COLLEAGUES table you are about to create:
```
CREATE TABLE "COLLEAGUES" (
    "ID" INTEGER not null primary key,
    "FIRSTNAME" VARCHAR(30),
    "LASTNAME" VARCHAR(30),
    "TITLE" VARCHAR(10),
    "DEPARTMENT" VARCHAR(20),
    "EMAIL" VARCHAR(60)
);
```
Note: Statements and queries formed in the SQL Editor are parsed in Structured Query Language. SQL adheres to strict syntax rules which you should be familiar with when working in the IDE's editor. SQL syntax can also differ depending on the database management system. See the JavaDB Reference Manual for specific guidelines.
Click the Run SQL () button in the task bar at the top of the editor (Ctrl-Shift-E) to execute the query. In the Output window (Ctrl-4), a message displays indicating that the statement was successfully executed:
To verify changes, right-click the contact connection node in the Database Explorer and choose Refresh. This updates the Runtime 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 COLLEAGUES table node () now displays under Tables in the Services window.

Adding Table Data

Now that you have created one or more tables in the contact database, you can start populating it with data. In order to add a complete record (row) to the FRIENDS table, create a statement that supplies a value for every field present in the table schema. You can use the SQL Editor to formulate a simple statement that adds a new record:

Right-click the Tables node in the Database Explorer and choose Execute command. A blank canvas opens in the SQL Editor in the main window.

In the SQL Editor, enter the following statement:

INSERT INTO "NBUSER"."FRIENDS" VALUES (1,'Theodore','Bagwell','T-Bag','2004-12-25','tbag@foxriver.com')

Right-click inside the SQL Editor and choose Run Statement. The Output window displays a message indicating that the statement was successfully executed.
To verify that the new record has been added to the FRIENDS table, right-click the FRIENDS table node in the Services window and choose View Data. The SQL Editor opens again 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 pane of the SQL Editor. The results of the statement are displayed in the lower pane of the SQL Editor. In this case, the FRIENDS table displays in the lower pane. Note that a new row has been added with the data you just supplied from the SQL statement:

Deleting Tables

In the following step, you use an external SQL script to create a new COLLEAGUES table. However, you just created a COLLEAGUES table in Using the SQL Editor above. In order to make it clear that the SQL script indeed creates a new table, you can delete the already created COLLEAGUES table now. To delete a database table:

Right-click the table node in the Database Explorer and choose delete.
In the Confirm Object Deletion dialog, click Yes. Note that the table node is immediately removed from the Database Explorer.

Using an External SQL Script

Issuing commands from an external SQL script is a popular way to manage your database. You may have already created an SQL script elsewhere, and want to import it into NetBeans IDE to run it on a specified database.

For demonstrative purposes, download colleagues.sql and save it to a location on your computer. This script creates a new table named COLLEAGUES and populates it with data. To run this script on the contact database:

Choose File > Open File from the IDE's main menu. In the file browser navigate to the location of the saved colleagues.sql file and click Open. The script automatically opens in the SQL Editor.
Make sure your connection to contact 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 contact connection node in the Services window and choose Refresh. This updates the Runtime UI component to the current status of the specified database. Note that the new COLLEAGUES table from the SQL script now displays as a table node under contact in the Services window.
To view the data contained in the new tables, choose View Data from the right-click menu of a table as described above. In this manner, you can also compare the tabular data with the data contained in the SQL script to see that they match.

Recreating Tables from a Different Database

If you have a table from another database which you would like to recreate in the database you are working in from NetBeans IDE, the IDE offers a handy tool for this. You first need to have the second database registered in the IDE, similar to what was described at the beginning of this tutorial. For the purposes of this tutorial, use the sample database that comes packaged with Java DB when you install the GlassFish application server with NetBeans IDE. This process is essentially carried out in two parts: You first 'grab' the table definition of the selected table, then you can recreate the table in your chosen database:

Connect to the sample database by right-clicking the connection node in the Database Explorer and choosing Connect (password is app).
Expand the Tables node. Right-click the CUSTOMER table node and choose Grab Structure:
In the Grab Table dialog that opens, specify a location on your computer to save the grab file that will be created. Click Save. The grab file records the table definition of the selected table.
In the Databases Explorer, right-click the Tables node beneath the contact database connection and choose Recreate Table.
In the Recreate Table dialog that opens, navigate to the location where you saved the CUSTOMER grab file, then click Open. The Name the Table dialog opens:
At this point you can change the table name or edit the table definition. Otherwise, click OK to immediately create the table in the contact database. A new CUSTOMER table node displays beneath the contact connection node: