Difference between revisions of "EDT:Tutorial: RUI With DataBase Lesson 3"

From Eclipsepedia

Jump to: navigation, search
Line 85: Line 85:
  
 
'''Notes:'''  
 
'''Notes:'''  
 +
 
*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.  
 
*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.  
 
*The names of Derby tables and columns are always in uppercase regardless of the case of names that are in the CREATE TABLE statement.   
 
*The names of Derby tables and columns are always in uppercase regardless of the case of names that are in the CREATE TABLE statement.   
 +
 +
== Use the EGL SQL retrieve feature to create a Record part ==
 +
 +
You can automatically retrieve the fields for a Record
 +
part that corresponds to the '''PAYMENT''' table in the '''PaymentDB''' database.
 +
The column names are the basis of the field names in the Record part.
 +
 +
To create the Record part:
 +
 +
<ol><li>From the file menu, left clink on '''New''' and select the '''EGL > Record''' wizard.
 +
<li>On the '''EGL Record''' window, enter '''Source folder''', '''Package''', and '''Name''' as shown below, select the template '''Records from SQL Database''', and click on '''Next'''<br />[[Image:EDT Tutorial edt richui sql02 paymentRec record.jpg|EGL Record paymentRec]]
 +
<li>On the '''Database Tables''' window, select the '''Derby''' database connection and the '''PAYMENT''', check '''Qualify tables names with schema''' and '''Save data source configuration to the deployment descriptor''', and click on '''Finish'''.<br />[[Image:EDT Tutorial edt richui sql02 paymentRec record.jpg|EGL Record paymentRec]]
 +
</ol>
 +
 +
EGL creates the records directory
 +
and the paymentRecords.egl file and then opens
 +
the file in the EGL editor.  The file contents are:
 +
  package records;
 +
  record paymentRec  { @Table {name="PAYMENT"} }
 +
    paymentId int {@Id, @GeneratedValue, @Column { name="PAYMENT_ID" } };
 +
    category int { @Column { name="CATEGORY" } } ;
 +
    description string? { @Column { name="DESCRIPTION" } } ;
 +
    amount decimal(10,2) { @Column { name="AMOUNT" } } ;
 +
    fixedPayment boolean { @Column { name="FIXED_PAYMENT" } } ;
 +
    dueDate date { @Column { name="DUE_DATE" } } ;
 +
    payeeName string { @Column { name="PAYEE_NAME" } } ;
 +
    payeeAddress1 string { @Column { name="PAYEE_ADDRESS1" } } ;
 +
    payeeAddress2 string { @Column { name="PAYEE_ADDRESS2" } } ;
 +
  end
 +
 +
'''Notes:'''
 +
*Your use of a Record part has the '''@Table''' annotation means that, in the following case, the EDT Java generator will create code that is appropriate for SQL I/O.  For example, you code an I/O statement such as '''add''',
 +
as in this example:
 +
  mySQLRecord paymentRec ; // variable mySQLRecord is of type paymentRec
 +
  add mySQLRecord; // generator creates INSERT SQL statement for add
 +
*The EDT Java generator uses several annotations such as '''@Table''', '''@ID''', '''@GeneratedValue''', and '''@Column''' from the Record part to generate the appropriate Java for the SQL operation.
 +
 +
To complete the record definition:
 +
#Change the type for '''fixedPayment''' from '''smallInt''' to '''boolean'''.  This change will be useful during a later drag-and-drop operation when definining the user interface.
 +
#Save (Ctrl-S) and close the file containing the record definition.
  
 
== Lesson checkpoint  ==
 
== Lesson checkpoint  ==
Line 92: Line 133:
 
In this lesson, you completed the following tasks:  
 
In this lesson, you completed the following tasks:  
  
*Created an Derby database connection  
+
*Created a Derby database connection  
*Created a database named <tt>sample</tt>
+
*Created a database named '''sample'''
*Created a database table named <tt>PAYMENT</tt>
+
*Created a database table named '''PAYMENT'''
 +
*Created a Record part named '''paymentRec'''
  
In the next lesson, you start writing application code.  
+
In the next lesson, you develop some of the Rich UI application
 +
and view your prototype code in action.  
  
 
{| style="float: right"
 
{| style="float: right"

Revision as of 16:23, 23 November 2011

Access a database with EGL Rich UI

< Previous | Next >

Contents

Lesson 4: Connect to a new Derby database

Use the Derby open source database manager to handle the data store for the application.

This tutorial uses the open source Derby database. In this chapter, you connect to a Derby database and create the table to be accessed. Alternatively, you can connect to a Cloudscape, DB2® UDB, or SQL Server database. In any case, create the table described in this lesson.

Follow these steps to set up the Derby database:

  1. Install Derby plugins in the Eclipse IDE
  2. Add Derby nature to the Service Project
  3. Start the Derby server
  4. Create an SQL database connection .
  5. Create a script in an SQL file to create a table within the database..

Install Derby Plugins

  1. Download the Derby Eclipse plugins into your Eclipse IDE as directed by the Derby site. The plugins for the Apache Derby 10.8.2.2 Release work well.   There will be two plugin zip files for the release:
    • derby_core_plugin - provides the Derby jar files to other plugins in Eclipse.
    • derby_ui_doc_plugin - provides an Apache Derby Nature in Eclipse for easy database application development.
  2. Extract the plugins to the Eclipse home directory. As a result, the eclipse/plugins directory received these folders:
    • org.apache.derby.core_10.8.2
    • org.apache.derby.plugin.doc_1.1.3
    • org.apache.derby.ui_1.1.3

Add Derby Nature To Service Project

To add the Derby nature to the ServiceProject:

  1. Right click on ServiceProject in the project explorer
  2. On the dropdown menu, left click on Apache Derby > Add Apache Derby Nature

Once the Derby nature is added, the 'Apache Derby menu will show the following selections:.Apache Derby Menu for projects with Derby nature

Start Derby Server

To start the Derby server, left click on selection Start Derby Network Server from the Derby menu.

You should see messages like this in the Console view:

Wed Nov 23 09:07:39 EST 2011 : Security manager installed using the Basic server security policy.
Wed Nov 23 09:07:40 EST 2011 : Apache Derby Network Server - 10.8.2.2 - (1181258) started and ready to accept connections on port 1527

Make a note of the sever port for future use.

Create Database Connection

  1. From the file menu, left clink on New and select the Connection > Connection Profile.
  2. On Connection Profile window, select Derby as profile type and enter a name and description for your server.
    Connection profile for Derby server
  3. Left click on Next.
  4. On Driver and Connection Details window, select Derby as profile type and enter a name and description for your server.Connection profile details for Derby server
  5. Left click on Test Connection. Make sure you get a successful response.
  6. Left click on Next to see a summary of the connection information.
  7. Make a note of the server URL for future use and left click on Finish.

Create Payment Table

Create the Payment table to be used in the application using this create SQL statement::

 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));

  1. From the file menu,left clink on New and select the SQL Development > SQL File wizard.
  2. On the Create SQL File window, enter SQL file attributes as shown in the image below.CreatePaymentTable SQL file attributes
  3. Left click on Finish
  4. Copy and paste this SQL statement into the SQL file editor windowCreatePaymentTable SQL file contents
  5. Close and save the SQL file.
  6. From the Project Explorer view, right click on file CreatePaymentTable.sql in the ServiceProject and select Execute SQL files from the menu. Look for results in the SQL Results view after the file runs.>.CreatePaymentTable SQL file results

Notes:

  • 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.
  • The names of Derby tables and columns are always in uppercase regardless of the case of names that are in the CREATE TABLE statement.

Use the EGL SQL retrieve feature to create a Record part

You can automatically retrieve the fields for a Record part that corresponds to the PAYMENT table in the PaymentDB database. The column names are the basis of the field names in the Record part.

To create the Record part:

  1. From the file menu, left clink on New and select the EGL > Record wizard.
  2. On the EGL Record window, enter Source folder, Package, and Name as shown below, select the template Records from SQL Database, and click on Next
    EGL Record paymentRec
  3. On the Database Tables window, select the Derby database connection and the PAYMENT, check Qualify tables names with schema and Save data source configuration to the deployment descriptor, and click on Finish.
    EGL Record paymentRec

EGL creates the records directory and the paymentRecords.egl file and then opens the file in the EGL editor. The file contents are:

 package records;
 record paymentRec  { @Table {name="PAYMENT"} } 
    paymentId int {@Id, @GeneratedValue, @Column { name="PAYMENT_ID" } };
    category int { @Column { name="CATEGORY" } } ;
    description string? { @Column { name="DESCRIPTION" } } ;	
    amount decimal(10,2) { @Column { name="AMOUNT" } } ;
    fixedPayment boolean { @Column { name="FIXED_PAYMENT" } } ;
    dueDate date { @Column { name="DUE_DATE" } } ;		
    payeeName string { @Column { name="PAYEE_NAME" } } ;
    payeeAddress1 string { @Column { name="PAYEE_ADDRESS1" } } ;
    payeeAddress2 string { @Column { name="PAYEE_ADDRESS2" } } ;
  end 

Notes:

  • Your use of a Record part has the @Table annotation means that, in the following case, the EDT Java generator will create code that is appropriate for SQL I/O. For example, you code an I/O statement such as add,

as in this example:

 mySQLRecord paymentRec ; // variable mySQLRecord is of type paymentRec
 add mySQLRecord; // generator creates INSERT SQL statement for add
  • The EDT Java generator uses several annotations such as @Table, @ID, @GeneratedValue, and @Column from the Record part to generate the appropriate Java for the SQL operation.

To complete the record definition:

  1. Change the type for fixedPayment from smallInt to boolean. This change will be useful during a later drag-and-drop operation when definining the user interface.
  2. Save (Ctrl-S) and close the file containing the record definition.

Lesson checkpoint

In this lesson, you completed the following tasks:

  • Created a Derby database connection
  • Created a database named sample
  • Created a database table named PAYMENT
  • Created a Record part named paymentRec

In the next lesson, you develop some of the Rich UI application and view your prototype code in action.

< Previous | Next >