Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

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

m (Use the EGL SQL retrieve feature to create a Record type)
 
(12 intermediate revisions by 5 users not shown)
Line 1: Line 1:
[[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]
+
[[EDT:Tutorial: Access a database with EGL Rich UI|Access a database with EGL Rich UI]]  
  
{| style="float: right"
+
{| style="float: right;"
|[[EDT:Tutorial: RUI With DataBase Lesson 2|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 4|Next >]]
+
|-
 +
| [[EDT:Tutorial: RUI With DataBase Lesson 2|< Previous]] | [[EDT:Tutorial: RUI With DataBase Lesson 4|Next >]]
 
|}
 
|}
 +
 
= Lesson 3: Connect to a new Derby database  =
 
= Lesson 3: Connect to a new Derby database  =
  
Line 23: Line 25:
 
#Download the Derby Eclipse plugins into your Eclipse IDE as directed by the Derby site. The plugins for the [http://db.apache.org/derby/releases/release-10.8.2.2.cgi Apache Derby 10.8.2.2 Release] work well.   There will be two plugin zip files for the release:  
 
#Download the Derby Eclipse plugins into your Eclipse IDE as directed by the Derby site. The plugins for the [http://db.apache.org/derby/releases/release-10.8.2.2.cgi 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_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.
+
#*derby_ui_doc_plugin - provides an Apache Derby Nature in Eclipse for easy database application development.  
 
#Extract the plugins to the Eclipse home directory. As a result, the eclipse/plugins directory received these folders:  
 
#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.core_10.8.2  
 
#*org.apache.derby.plugin.doc_1.1.3  
 
#*org.apache.derby.plugin.doc_1.1.3  
 
#*org.apache.derby.ui_1.1.3
 
#*org.apache.derby.ui_1.1.3
 +
#Restart your Eclipse IDE to activate the Derby plugins
  
 
== Add Derby Nature To Service Project  ==
 
== Add Derby Nature To Service Project  ==
  
To add the Derby nature to the '''ServiceProject''':  
+
To add the Derby nature to the '''PaymentService''' project:  
  
#Right click on '''ServiceProject''' in the project explorer  
+
#Right click on '''PaymentService''' in the project explorer  
#On the dropdown menu, left click on '''Apache Derby > Add Apache Derby Nature'''
+
#On the dropdown menu, click '''Apache Derby > Add Apache Derby Nature'''
  
 
Once the Derby nature is added, the ''''Apache Derby''' menu will show the following selections:.[[Image:EDT Tutorial edt richui sql02 derby menu.jpg|Apache Derby Menu for projects with Derby nature]]  
 
Once the Derby nature is added, the ''''Apache Derby''' menu will show the following selections:.[[Image:EDT Tutorial edt richui sql02 derby menu.jpg|Apache Derby Menu for projects with Derby nature]]  
Line 40: Line 43:
 
== Start Derby Server  ==
 
== Start Derby Server  ==
  
To start the Derby server, left click on selection '''Start Derby Network Server''' from the Derby menu.  
+
To start the Derby server,  click '''Start Derby Network Server''' from the Derby menu.  
  
 
You should see messages like this in the '''Console''' view: &lt;blockquoteWed Nov 23 09:07:39 EST 2011&nbsp;: Security manager installed using the Basic server security policy.<br>Make a note of the sever port for future use.  
 
You should see messages like this in the '''Console''' view: &lt;blockquoteWed Nov 23 09:07:39 EST 2011&nbsp;: Security manager installed using the Basic server security policy.<br>Make a note of the sever port for future use.  
Line 46: Line 49:
 
== Create Database Connection  ==
 
== Create Database Connection  ==
  
#From the file menu, left clink on '''New''' and select the '''Connection &gt; Connection Profile'''.  
+
#Click '''File &gt; New &gt; Other''', expand '''Connection Profiles''', and double-click '''Connection Profile'''.  
 
#On '''Connection Profile''' window, select '''Derby''' as profile type and enter a name and description for your server.<br>[[Image:EDT Tutorial edt richui sql02 new connection.jpg|Connection profile for Derby server]]  
 
#On '''Connection Profile''' window, select '''Derby''' as profile type and enter a name and description for your server.<br>[[Image:EDT Tutorial edt richui sql02 new connection.jpg|Connection profile for Derby server]]  
#Left click on '''Next'''.  
+
#Click '''Next'''.  
 
#On '''Driver and Connection Details''' window, select '''Derby''' as profile type and enter a name and description for your server.[[Image:EDT Tutorial edt richui sql02 new connection details.jpg|Connection profile details for Derby server]]  
 
#On '''Driver and Connection Details''' window, select '''Derby''' as profile type and enter a name and description for your server.[[Image:EDT Tutorial edt richui sql02 new connection details.jpg|Connection profile details for Derby server]]  
#Left click on '''Test Connection'''. Make sure you get a successful response.  
+
#Click '''Test Connection'''. Make sure you get a successful response.  
#Left click on '''Next''' to see a summary of the connection information.  
+
#Click '''Next''' to see a summary of the connection information.  
#Make a note of the server URL for future use and left click on '''Finish'''.
+
#Make a note of the server URL for future use and click '''Finish'''.
  
 
== Create Payment Table  ==
 
== Create Payment Table  ==
Line 59: Line 62:
  
 
   CREATE TABLE PAYMENT (  
 
   CREATE TABLE PAYMENT (  
    PAYMENT_ID INT  
+
  PAYMENT_ID INT  
      PRIMARY KEY NOT NULL  
+
    PRIMARY KEY NOT NULL  
      GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+
    GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    CATEGORY INT,  
+
  CATEGORY INT,  
    DESCRIPTION CHAR(30),  
+
  DESCRIPTION CHAR(30),  
    AMOUNT DECIMAL(10,2),  
+
  AMOUNT DECIMAL(10,2),  
    FIXED_PAYMENT SMALLINT,  
+
  FIXED_PAYMENT SMALLINT,  
    DUE_DATE DATE,  
+
  DUE_DATE DATE,  
    PAYEE_NAME CHAR(30),  
+
  PAYEE_NAME CHAR(30),  
    PAYEE_ADDRESS1 CHAR(30),
+
  PAYEE_ADDRESS1 CHAR(30),
    PAYEE_ADDRESS2 CHAR(30));
+
  PAYEE_ADDRESS2 CHAR(30));
  
<br>
+
<br>  
  
#From the file menu,left clink on '''New''' and select the '''SQL Development &gt; SQL File''' wizard.  
+
#Click '''File &gt; New &gt; Other''', expand '''SQL Development''', and double-click '''SQL File'''.&nbsp;
#On the '''Create SQL File''' window, enter SQL file attributes as shown in the image below.[[Image:EDT Tutorial edt richui sql02 create payment table SQL file.jpg|CreatePaymentTable SQL file attributes]]  
+
#On the '''Create SQL File''' window, select the '''PaymentService''' project and enter other SQL file attributes as shown in the image below.[[Image:EDT Tutorial edt richui sql02 create payment table SQL file.jpg|CreatePaymentTable SQL file attributes]]  
#Left click on '''Finish'''  
+
#Click '''Finish'''  
 
#Copy and paste this SQL statement into the SQL file editor window[[Image:EDT Tutorial edt richui sql02 create payment table SQL file contents.jpg|CreatePaymentTable SQL file contents]]  
 
#Copy and paste this SQL statement into the SQL file editor window[[Image:EDT Tutorial edt richui sql02 create payment table SQL file contents.jpg|CreatePaymentTable SQL file contents]]  
 
#Close and save the SQL file.  
 
#Close and save the SQL file.  
Line 83: Line 86:
  
 
*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.
 +
*If the table PAYMENT is not created successfully, please create a new .sql and execute 'DROP TABLE PAYMENT', then execute creation table .sql again.
  
 
== Use the EGL SQL retrieve feature to create a Record type  ==
 
== Use the EGL SQL retrieve feature to create a Record type  ==
Line 91: Line 95:
 
To create the Record type:  
 
To create the Record type:  
  
#From the file menu, left clink on '''New''' and select the '''EGL &gt; Record''' wizard.  
+
#Click '''File &gt; New &gt; Other''', expand '''EGL''', and double-click '''Record'''.  
#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]]  
+
#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'''. Note that the record is being defined in the PaymentShared project since it is used both by the RUI handler and the service.<br>[[Image:EDT Tutorial edt richui sql02 shared paymentRec record.jpg|EGL Record paymentRec]]  
 
#On the '''Database Tables''' window, select the '''Derby''' database connection and the '''PAYMENT''' table, 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 database tables.jpg|EGL database tables selection]]
 
#On the '''Database Tables''' window, select the '''Derby''' database connection and the '''PAYMENT''' table, 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 database tables.jpg|EGL database tables selection]]
  
Line 109: Line 113:
 
       PAYEE_ADDRESS2 string?;
 
       PAYEE_ADDRESS2 string?;
 
   end
 
   end
 +
'''Notes:'''
 +
In EDT 8.0, the content might be as following codes snippet:
 +
    package records;
 +
    record paymentRec
 +
        itemName string;
 +
    end
  
 
In our application we want to change the record definition to give information to the EGL wizards and generators. Therefore replace the record definition above with this definition using copy and paste:  
 
In our application we want to change the record definition to give information to the EGL wizards and generators. Therefore replace the record definition above with this definition using copy and paste:  
Line 128: Line 138:
 
'''Notes:'''  
 
'''Notes:'''  
  
<br>
+
<br>  
  
 
*To generate the Java code that is appropriate for the SQL operation, the EDT Java generator uses the '''Table''', '''ID''', '''GeneratedValue''', and '''Column''' annotations.&nbsp; For example, the following code declares a variable&nbsp;and uses it&nbsp;in an&nbsp;'''add''' statement:
 
*To generate the Java code that is appropriate for the SQL operation, the EDT Java generator uses the '''Table''', '''ID''', '''GeneratedValue''', and '''Column''' annotations.&nbsp; For example, the following code declares a variable&nbsp;and uses it&nbsp;in an&nbsp;'''add''' statement:
Line 134: Line 144:
 
&nbsp;  
 
&nbsp;  
 
<pre>      mySQLRecord paymentRec;
 
<pre>      mySQLRecord paymentRec;
       add mySQLRecord;</pre>
+
       add mySQLRecord;</pre>  
<br>
+
<br>  
  
 
:The generated code includes an SQL INSERT statement.<br>
 
:The generated code includes an SQL INSERT statement.<br>
  
*The type for '''fixedPayment''' has been changed from smallInt to boolean. This change will be useful for defining the user interface during a later drag-and-drop operation.
+
*The type for '''fixedPayment''' has been changed from smallInt to boolean. This change will be useful for defining the user interface during a later drag-and-drop operation.
 +
*The type for '''fixedPayment''' has been changed from '''smallInt''' to '''boolean'''. The EGL runtime knows that by convention the database uses smallInt for boolean values.
  
<br>
+
<br>  
  
<br>
+
<br>  
  
 
To complete the record definition, save (Ctrl-S) and close the file containing that definition.
 
To complete the record definition, save (Ctrl-S) and close the file containing that definition.
Line 158: Line 169:
 
In the next lesson, you develop some of the Rich UI application and view your prototype code in action.  
 
In the next lesson, you develop some of the Rich UI application and view your prototype code in action.  
  
{| style="float: right" class="FCK__ShowTableBorders"
+
{| class="FCK__ShowTableBorders" style="float: right;"
 
|-
 
|-
 
| [[EDT:Tutorial: RUI With DataBase Lesson 2|&lt; Previous]] &#124; [[EDT:Tutorial: RUI With DataBase Lesson 4|Next &gt;]]
 
| [[EDT:Tutorial: RUI With DataBase Lesson 2|&lt; Previous]] &#124; [[EDT:Tutorial: RUI With DataBase Lesson 4|Next &gt;]]

Latest revision as of 03:58, 12 March 2012

Access a database with EGL Rich UI

< Previous | Next >

Lesson 3: 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.
  6. Create an EGL record that represents a row in the table

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
  3. Restart your Eclipse IDE to activate the Derby plugins

Add Derby Nature To Service Project

To add the Derby nature to the PaymentService project:

  1. Right click on PaymentService in the project explorer
  2. On the dropdown menu, click 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,  click Start Derby Network Server from the Derby menu.

You should see messages like this in the Console view: <blockquoteWed Nov 23 09:07:39 EST 2011 : Security manager installed using the Basic server security policy.
Make a note of the sever port for future use.

Create Database Connection

  1. Click File > New > Other, expand Connection Profiles, and double-click 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. Click 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. Click Test Connection. Make sure you get a successful response.
  6. Click Next to see a summary of the connection information.
  7. Make a note of the server URL for future use and click 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. Click File > New > Other, expand SQL Development, and double-click SQL File
  2. On the Create SQL File window, select the PaymentService project and enter other SQL file attributes as shown in the image below.CreatePaymentTable SQL file attributes
  3. Click 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.
  • If the table PAYMENT is not created successfully, please create a new .sql and execute 'DROP TABLE PAYMENT', then execute creation table .sql again.

Use the EGL SQL retrieve feature to create a Record type

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

To create the Record type:

  1. Click File > New > Other, expand EGL, and double-click Record.
  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. Note that the record is being defined in the PaymentShared project since it is used both by the RUI handler and the service.
    EGL Record paymentRec
  3. On the Database Tables window, select the Derby database connection and the PAYMENT table, check Qualify tables names with schema and Save data source configuration to the deployment descriptor, and click on Finish.
    EGL database tables selection

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 PAYMENT type Entity{@table{name = "EUNIT.PAYMENT"}}
     PAYMENT_ID int{@id};
     CATEGORY int?;
     DESCRIPTION string?;
     AMOUNT decimal(10, 2)?;
     FIXED_PAYMENT smallInt?;
     DUE_DATE date?;
     PAYEE_NAME string?;
     PAYEE_ADDRESS1 string?;
     PAYEE_ADDRESS2 string?;
  end

Notes: In EDT 8.0, the content might be as following codes snippet:

   package records;
   record paymentRec
       itemName string;
   end

In our application we want to change the record definition to give information to the EGL wizards and generators. Therefore replace the record definition above with this definition using copy and paste:

  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:


  • To generate the Java code that is appropriate for the SQL operation, the EDT Java generator uses the Table, ID, GeneratedValue, and Column annotations.  For example, the following code declares a variable and uses it in an add statement:

 

      mySQLRecord paymentRec;
      add mySQLRecord;


The generated code includes an SQL INSERT statement.
  • The type for fixedPayment has been changed from smallInt to boolean. This change will be useful for defining the user interface during a later drag-and-drop operation.
  • The type for fixedPayment has been changed from smallInt to boolean. The EGL runtime knows that by convention the database uses smallInt for boolean values.



To complete the record definition, save (Ctrl-S) and close the file containing that definition.

Lesson checkpoint

In this lesson, you completed the following tasks:

  • Created an 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 >

Copyright © Eclipse Foundation, Inc. All Rights Reserved.