EDT:Tutorial: RUI With Database Lesson 2

Lesson 2: Set up the database

This tutorial assumes you have the ability to connect to a relational database of one of the following kinds: Derby, Cloudscape, DB2® UDB, Informix®, Oracle, or SQL Server.

To run the tutorial you will need the following:

• Connection URL for the database, for example:  "jdbc:derby:C:\\EDT\\derby\\SAMPLE;create=true"
• Payment table defined in the database using the following SQL script:

 CREATE TABLE PAYMENT(
        PAYMENT_ID INT PRIMARY KEY NOT NULL 
           GENERATED ALWAYS AS IDENTITY
           (START WITH 1, INCREMENT BY 1),
        CATEGORY INT,
        DESCRIPTION CHAR(30),
        AMOUNT DECIMAL(10,2),
        FIXED_PAYMENT SMALLINT,
        DUE_DATE DATE,
        PAYEE_NAME CHAR(30),
        PAYEE_ADDRESS1 CHAR(30),
        PAYEE_ADDRESS2 CHAR(30));

If you have done this already, you may proceed to the next step.   Otherwise follow these instructions to

1. Download and install Derby
2. Define the database connection 
3. Create the Payment table in the database

Download and Install Derby

Follow the Derby instructions for downloading and installing the Derby Eclipse plugins. These instructions can be found in link Derby 10 Core Plug-in for Eclipse.   Verify the installation by following the directions in link Using the 10 Core and 1.1 UI Derby plug-ins.

Define the Database Connection

To set up the Derby database for your application, use the Database Development perspective, which is a workbench perspective and different from the EGL Data view and define a new Database Connection.

1. In the top menu of the Eclipse workbench, select Window -> Perspective -> Database Development.
   
2. In the Data Source Explorer view, click Database Connections and click New.
3. In the Connection Profile window, complete these steps:

1. 1. Under Connection Profile Types, click Derby.
   2. In the Name field, type the following string:

Derby Database Connection

1. 1. Click Next.
   2. In the Specify a Driver and Connection Details window, specify the following information:
   3. From the Drivers list, select Derby Embedded JDBC Driver 10.1 Default.
      
   4. For the Database location field, enter a simple path:
2. C:\databases\PaymentDB
3. The final element in the path is the name of a folder that does not yet exist.
   
   1. Specify generic login information:
      * In the User name field, enter admin
      * In the Password field, also enter admin
      
4. Select the Create database (if required) check box.
   
5. Select the Save password check box. When you work with live data, you might prefer not to select this option, but it simplifies the tutorial.
   
6. Make sure that Connect when the wizard completes is selected and that Connect every time the workbench is started is cleared.
   7. Click Test Connection. You should see a message that says “Ping succeeded!” Click OK to close the message window. If the test failed, get more information by clicking Details on the failure message.
   8. Click Finish.
   1. In the Preferences window, make sure that Derby Database Connection is highlighted, then click OK.
      

To connect to the database:

  1. Change to the Data perspective as follows:
        1. Click the Open Perspective button, which is located by default in the right side of the navigation bar.
           The Open Perspective button
        2. If the Data perspective is not shown on the menu, click Other.
        3. If you still do not see the Data perspective, select Show All at the bottom of the wizard. Click Data and then click OK.
           The Data perspective in the menu of perspectives.
  2. Locate the Data Source Explorer view, by default in the lower left corner of the workbench; and under Database Connections, right-click Derby Database Connection. Click the Connect option. The option was enabled because you set the following check boxes when you created the connection: Create database (if required) and Connect when the wizard completes.

Create a table

While in the Data perspective, you can write an SQL script to create a table in the database.

  1. In the Data Source Explorer view, expand Derby Database Connection. Right-click the PaymentDB database name and click New SQL Script.
     The menu for the new database
     A new script file opens in the editor.
  2. Copy the following SQL code into the script file:

     CREATE TABLE PAYMENT(
        PAYMENT_ID INT PRIMARY KEY NOT NULL 
           GENERATED ALWAYS AS IDENTITY
           (START WITH 1, INCREMENT BY 1),
        CATEGORY INT,
        DESCRIPTION CHAR(30),
        AMOUNT DECIMAL(10,2),
        FIXED_PAYMENT SMALLINT,
        DUE_DATE DATE,
        PAYEE_NAME CHAR(30),
        PAYEE_ADDRESS1 CHAR(30),
        PAYEE_ADDRESS2 CHAR(30));

     In the next step, you run this code to create a table named PAYMENT.
     Note:
        1. The PAYMENT_ID column is an identity column, which means that Derby will place a unique value into that column whenever the user creates a record. Each value is one more than the last.
        2. The names of Derby tables and columns are always in uppercase regardless of the case of names that are in the CREATE TABLE statement.
  3. Right-click anywhere in the background of the editor pane, and then click Run SQL. The SQL Results view, which is by default at the bottom center of the workbench, should show the “create table” operation and a status of “Succeeded”. You can now expand the PaymentDB entry in the Data Source Explorer and see the columns for the new table:
     The column names are under Schemas/APP/Tables/PAYMENT/Columns.
  4. Close the script file. You do not need to save the file, as you will not need it again.
  5. You cannot access the database from EGL source code while the Data view is using the connection. Right-click Derby database connection and click Disconnect.

Lesson checkpoint In this lesson, you completed the following tasks:

   * Created an EGL database connection
   * Created a database named PaymentDB
   * Created a database table named PAYMENT

In the next lesson, you start writing application code.

Next
Previous
Up