当前页面: 开发资料首页 → Netbeans 专题 → Working with the Java DB (Derby) Database in NetBeans 5.5

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

摘要: This document demonstrates how to quickly set up a connection to Sun's Java DB (which is based on the Apache Derby database) in NetBeans. Once a connection is made, you can easily begin working with the database in the NetBeans 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 bundled with the Sun Java System Application Server 9.0, Platform Edition, and is now included in JDK 6 as well.

The following topics are covered below:

• Getting the Software
• Configuring the Database
• Registering the Database in NetBeans
• Starting the Server and Creating a Database
• Connecting to the Database
• Creating Tables
• Adding Table Data
• Using an SQL Script
• Recreating Tables from a Different Database
• Next Steps

This tutorial can be completed in 25 minutes.

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, Java DB will be automatically registered as well. Alternatively, you can download the Java EE 5 Tools Bundle, which includes both the NetBeans IDE and Sun Java System Application Server.

Configuring the Database

If you have the Sun Java System Application Server registered in NetBeans, 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, search the IDE help (F1) for Registering a Sun Java System Application Server Instance. 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 have 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'. We 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, but for our purposes this will be fine.

Before moving on, let's have a brief look at Java DB's subdirectory:

• 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

Now that we have the database configured, let's register it in the IDE:

1. In the NetBeans IDE select Tools > Options (NetBeans > Preferences on Macintosh) 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 enables you to easily 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'll see the following in the Output window, indicating the server has been started:
   
   
   
   
2. Now choose Tools > Java DB Database > Create Java DB Database.... The Create Java DB Database dialog opens.
3. For the Database Name text field, type in 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 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, we have successfully started the the database server and created a database in NetBeans. However, we still need to connect to the new database before we can start working with it in the IDE. To connect to the contact_database database:

1. Switch to the Runtime window (Ctrl+5) and expand the Databases node to see your 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

Obviously, because we just created the contact_database database, it doesn't yet contain any tables or data. In NetBeans 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. Let's 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 in 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 screenshot below:
   
   
   
   
6. You are basically 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 addresses
   
   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 there are the columns (fields) we created, 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. In the SQL Editor, type in the following query. 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_ADDRESSES" VARCHAR(1000)
       )

   
   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 (Alternately, press Ctrl+Shift+E) to execute the query. You should receive the following message in the Output window:
   
   
   
   
4. To verify changes, again 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 is a necessary step when running queries from the SQL Editor in NetBeans. Note that the new Contacts table node () now displays under Tables in the Runtime window.

Adding Table Data

Now that we've created our first table in the contact_database, we can start populating it with data. In order to add a complete record (row) to our Contacts table, we need to supply a value for every field present in the table. Let's begin by opening the SQL Editor, then entering a simple query to add a 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, type in 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 (Alternately, press Ctrl+Shift+E) to execute the query. You should receive a message in the Output window indicating that the query was executed successfully.
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 our case, the Contacts table displays in the lower pane. Note that a new row has been added with the data we 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 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 where you previously saved friends.sql 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'd like to quickly recreate in the database you're working in in NetBeans, 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 demonstrative purposes, we'll use the sample database that comes bundled 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. For our purposes, just click OK to create the table and you'll 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 5.5 tutorial. This tutorial has demonstrated how to quickly set up a connection to the Java DB database in the NetBeans IDE. It then demonstrated how to create, view and modify tables, as well as run SQL queries on tabular data while using the NetBeans IDE.

For more advanced tutorials, see the following resources:

• Connecting a GUI to a Java DB Database with NetBeans IDE. A guide to the process of connecting an application GUI to a Java DB database.
• GUI Building in the NetBeans IDE. A quick guide demonstrating the process of creating graphical user interfaces with the NetBeans IDE.
• Matisse GUI Builder FAQ. A collection of useful tips and tricks for using NetBeans GUI Builder.
• NetBeans Support and Docs. A full list of articles, FAQs, and mailing lists for NetBeans IDE users.

top