Products Docs & Support Community

Working with the Java DB (Derby) Database in NetBeans IDE

Contributed and maintained by and .

This document demonstrates how to set up a connection to Sun's Java DB (which is based on the Apache Derby 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 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 Sun Java System Application Server 9.0, Platform Edition, and is now included in JDK 6 as well.


Expected duration: 25 minutes


The following topics are covered below:


Getting the Software

Before you start this tutorial, you must make sure you have the following software installed on your computer:

  1. NetBeans IDE 5.5 (download)
  2. Java SE Development Kit (JDK™) version 5.0 or higher (download)
  3. Java DB (download)

Note: If you are downloading JDK 6, the Java DB database will be included in your installation. You can also download the Sun Java System Application Server, which includes Java DB. When registering the application server in NetBeans IDE, Java DB will be automatically registered as well. Alternatively, consider downloading the Java EE 5 Tools Bundle, which includes both NetBeans IDE and Sun Java System Application Server.


Configuring the Database

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

  1. 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.
  2. In the Java DB root directory (javadb), create a new folder named 'database'. 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:

  1. In NetBeans IDE select Tools > Options from the main menu. Click the Advanced Options button in the lower left corner of the Options window. The Advanced Options window opens.
  2. In the Advanced Options window, expand the IDE Configuration > Server and External Tools Settings nodes. Select the Java DB Database node.
  3. Now, under Properties in the right panel of the Advanced Options window, click the ellipsis () button for Java DB Location and set the path to where you extracted the database archive (e.g. C:\javadb).
  4. For Database Location, set the path to the newly created 'database' folder. The screenshot below displays example settings for the Java DB server. When you are finished, click Close.


Starting the Server and Creating a Database

Once your database is registered with the IDE, the Java DB Database menu item appears under Tools in the main menu. This menu item allows you to start and stop the database server, as well as create a new database. To start the database server:

  1. Choose Tools > Java DB Database > Start Java DB Server. You will see the following in the Output window, indicating the server has been started:



  2. Choose Tools > Java DB Database > Create Java DB Database. The Create Java DB Database dialog opens.
  3. For the Database Name text field, type contact_database. 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 the Application Server, 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 in NetBeans IDE. However, in order to start working with the new database in the IDE, you need to create a connection. To connect to contact_database:

  1. Switch to the Runtime window (Ctrl+5) and expand the Databases node to see the new database:



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

Creating Tables

Because you just created contact_database, it obviously 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 inputing an SQL query and running it directly from the SQL editor. You can explore both methods:

Using the Create Table dialog:

  1. Expand the contact_database 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.
  2. In the Table Name text field, type CONTACTS.
  3. 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.
  4. For Column Name, enter ID. For Data Type, select INTEGER from the drop-down list. Then click the Add Column button.
  5. 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]   FIRST_NAME VARCHAR 50
        [checked]   LAST_NAME VARCHAR 50
        [checked]   TITLE VARCHAR 50
        [checked]   NICKNAME VARCHAR 50
        [checked]   DISPLAY_FORMAT SMALLINT 0
        [checked]   MAIL_FORMAT SMALLINT 0
        [checked]   EMAIL_ADDRESS VARCHAR 500
  6. You are creating a table named CONTACTS that will hold the following data for each contact record:

    • First Name
    • Last Name
    • Title
    • Nickname
    • Display Format
    • Mail Format
    • Email Address

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



Using the SQL Editor:

  1. Either right-click the contact_database connection node or the Tables node beneath it and choose Execute Command. A blank canvas opens in the SQL Editor in the main window.
  2. Enter the following query in the SQL Editor. This is a table definition for the CONTACTS table you are about to create: 
    CREATE TABLE "CONTACTS" (
    "ID" INTEGER not null primary key,
    "FIRST_NAME" VARCHAR(50),
    "LAST_NAME" VARCHAR(50),
    "TITLE" VARCHAR(50),
    "NICKNAME" VARCHAR(50),
    "DISPLAY_FORMAT" SMALLINT,
    "MAIL_FORMAT" SMALLINT,
    "EMAIL_ADDRESS" VARCHAR(500)
);

    Note: 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. Upon running a query, feedback from the SQL engine is generated in the Output window indicating whether execution was successful or not.

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



  4. To verify changes, right-click the contact_database node in the Runtime window 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 CONTACTS table node () now displays under Tables in the Runtime window.

Adding Table Data

Now that you have created your first table in the contact_database, you can start populating it with data. In order to add a complete record (row) to the CONTACTS table, you need to supply a value for every field present in the table. You can use the SQL Editor to formulate a simple query to add a new contact record:

  1. Choose Execute Command from the CONTACTS table node in the Runtime window. A blank canvas opens in the SQL Editor in the main window.
  2. In the SQL Editor, enter the following query: 
    insert into "NBUSER"."CONTACTS" values (1,'Monty','Python','Mr.','Bruiser',3,10,'....')
  3. Click the Run SQL () button in the task bar at the top of the editor (or press Ctrl+Shift+E) to execute the query. You should receive a message in the Output window indicating that the query was successfully executed.
  4. To verify that the new record has been added to the CONTACTS table, in the Runtime window right-click the CONTACTS table node 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 CONTACTS table displays in the lower pane. Note that a new row has been added with the data you just supplied from the SQL query:


Using an 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 friends.sql and save it to a location on your computer. This script creates a new table named Friends and populates it with data. To run this script on our contact_database:

  1. Choose File > Open File (Ctrl+O) from the IDE's main menu. In the file browser navigate to the location of the saved friends.sql file and click Open. The script automatically opens in the SQL Editor.
  2. Make sure your connection to contact_database is selected from the Connection drop-down box in the tool bar at the top of the editor:



  3. 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.
  4. To verify changes, right-click the contact_database connection node in the Runtime window and choose Refresh. This updates the Runtime UI component to the current status of the specified database. Note that the new Friends table from the SQL script now displays as a table node under contact_database in the Runtime window.
  5. 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 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. Use the sample database that comes packaged with Java DB when you download the Sun Java System Application Server. This process is essentially carried out in two steps: You must first grab the table definition of the selected table, then you can recreate the table in your chosen database.

Grabbing a Table Definition:

  1. Connect to the sample database by right-clicking the connection node in the Runtime window and choosing Connect (password is app).
  2. Expand the Tables node. Right-click the CUSTOMER table and choose Grab Structure:



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

Recreating the Table:

  1. In the Runtime window, right-click the Tables node from contact_database and choose Recreate Table.
  2. In the Recreate Table dialog that opens, navigate to the location where you saved the CUSTOMER grab file above, then click Open. The Name the Table dialog opens:



  3. At this point you can change the table name or edit the table definition. It is not necessary to make any changes, so click OK to create the table and you will see the new CUSTOMER table node displayed below Tables under the contact_database connection node in the Runtime window:




Next Steps

This concludes the Working with the Java DB (Derby) Database in NetBeans IDE tutorial. This tutorial demonstrated how to set up a connection to the Java DB database in NetBeans IDE. It then demonstrated how to create, view and modify tables, as well as run SQL queries on tabular data while using NetBeans IDE.

For related and more advanced tutorials, see the following resources:



top