Difference between revisions of "EDT:Working with a database"

From Eclipsepedia

Jump to: navigation, search
(Adding a row)
(Declaring variables)
 
(5 intermediate revisions by one user not shown)
Line 53: Line 53:
  
 
<source lang="java">
 
<source lang="java">
ds SQLDataSource?{@Resource {}};                       
+
ds SQLDataSource?{@Resource};                       
 
</source>
 
</source>
  
Line 75: Line 75:
 
</source>
 
</source>
  
Here is a statement variation that shows the SQL code you can customize:  
+
Here is a variation that shows the SQL code you can customize:  
 
<source lang="java">
 
<source lang="java">
 
   
 
   
Line 93: Line 93:
 
</source>
 
</source>
  
A variation new in version .8 returns a value that is generated by the database management system (DBMS). Consider the following custom type:<source lang="java">
+
In either case, the '''GeneratedValue''' annotation in the Record type has the following effect: the '''add''' statement places a DBMS-generated value into the PAYMENT_ID column of a new row and into the <code>paymentID</code> field of the record being added.
 
+
Record Customer type Entity { @table { name = "customer" } }
+
  id string{@id, @generatedvalue};
+
  name string?
+
end
+
</source>
+
 
+
The following code places the a value DBMS-generated value into the ID column of a new row and into the ID field of the record being added:<source lang="java">
+
 
+
myCustomer Customer{ name = "Jenny" };
+
add myCustomer;
+
</source>
+
  
 
= '''Getting a row'''  =
 
= '''Getting a row'''  =

Latest revision as of 21:27, 13 March 2012

This page contains code snippets for database access.

Much of the code on this page accesses a database table that has the following definition:

CREATE TABLE PAYMENT ( 
   PAYMENT_ID INT 
   PRIMARY KEY NOT NULL 
   GENERATED ALWAYS AS IDENTITY 
      (START WITH 1, INCREMENT BY 1), 	
   DESCRIPTION CHAR(30), 
   AMOUNT DECIMAL(10,2), 


Contents

[edit] Following the typical pattern

Here is the typical pattern for interacting with a relational database:

  • Define a Record, Handler, or external type and include annotations. For a Record type, you might retrieve details automatically from a database management system. 
  • Declare a variable that is based on the defined type. Your code will copy data between the database and that variable.
  • Declare variables to represent the SQL data sources. Each data source is a connection or a result set.
  • Configure EGL statements that reference the variables that you declared. For each statement, rely on the SQL code that is provided for you or customize that code.

[edit]


Defining a Record type

/*
 * The annotations help to define the default SQL code when  
 * a record of this type is used in a given EGL statement:
 *     -- Table refers to the table being accessed. If Table is not supplied,
 *        the name of the Record type is assumed to be the table name.
 *     -- Column refers to the column being accessed. If Column is not supplied, 
 *        the name of the field is assumed to be the name of the column.
 *     -- ID indicates that the field and its value are set to equal 
 *        in the default SQL INSERT statement (for an EGL add statement) and
 *        in the default SELECT statement (for an EGL get or open statement). 
 */
Record PaymentRec  { @Table {name="PAYMENT"} } 
   paymentId int {@ID, @GeneratedValue, @Column { name="PAYMENT_ID" } };
   description string? { @Column { name="DESCRIPTION" } } ;	
   amount decimal(10,2) { @Column { name="AMOUNT" } } ;
End

[edit]


Declaring variables

You might declare a new payment record as follows:

   mypayment PaymentRec;
mypayment.description = "shoes";
mypayment.amount = 123.50;

The next declaration lets your code open a database connection at the first database access and in accordance with connection details that are stored in an EGL deployment descriptor:

ds SQLDataSource?{@Resource};

For details on how to connect to a database, see SQL database bindings.

[edit] Adding a row

The following function uses the previous data-source declaration, accepts a record, and inserts the content of that record into the database:

function addPayment(newPayment PaymentRec in)
   try
      add newPayment to ds;
      onException(ex sqlException)
 
         // invokes a custom exception handler.
         logException(ex);        
    end
end

Here is a variation that shows the SQL code you can customize:

 
function addPayment(newPayment PaymentRec in)
   try
      add newPayment to ds
      with #sql{
          insert into PAYMENT
             (DESCRIPTION, AMOUNT)
          values
             (?, ?)
      };
      onException(ex sqlException)
          logException(ex);
   end
end

In either case, the GeneratedValue annotation in the Record type has the following effect: the add statement places a DBMS-generated value into the PAYMENT_ID column of a new row and into the paymentID field of the record being added.

[edit] Getting a row

The following code shows alternative ways to get a record:

function getPayment(someId Int in} returns (PaymentRec)
 
   aPayment PaymentRec;                                   
 
   // retrieves a table row that has a key value of someID
   // and places the content of that row in the customer record.
   get aPayment from ds using(someId);                       
 
   // alternative that shows the customizable SQL code.
   get aPayment from ds using(someId) with #sql{             
      SELECT *
      FROM PAYMENT
      WHERE PAYMENT_ID = ?
   };
 
   // a future alternative (not yet scheduled) 
   // declares a dictionary and creates a key/value pair 
   // for each item of column data retrieved from the database.
   vals Dictionary;                       
   get vals from ds using(someId) with #sql{              
      SELECT *
      FROM PAYMENT
      WHERE PAYMENT_ID = ?
   };                                                    
 
   return (aPayment);
end

[edit] Getting multiple rows with one statement

The following code shows alternative ways to get a list of records:

function getPayments(} returns (PaymentRec[])
 
   // declares a new list of payment records. 
   payments PaymentRec[];     
 
   // retrieves the data from each row into any element in the list.
   get payments from ds;                                     
 
 
   // alternative that retrieves a subset of rows. 
   get payments from ds with #sql {
       SELECT * FROM PAYMENT WHERE STATE = 'CO'
    };                                                     
 
    // alternative that includes a parameter in the query.
    state String = "CO";
    get payments from ds using(state) with #sql {
       SELECT * FROM PAYMENT WHERE STATE = ?
    };                                   
end

[edit] Looping through an SQL result set

The following code opens an SQL cursor and loops through the results.

function loopPayment()
 
   // declare a new data source 
   ds SQLDataSource?{@resource{uri = "binding:myDB" }};      // .8 syntax 
 
   // declares a new result set
   rs SQLResultSet?;                                         
 
   // uses the specified SQL query to open that result set
   open rs from ds with #sql{                                 
      SELECT * FROM PAYMENT
   };
 
   myPayment PaymentRec;
 
   // loops through the results and 
   // writes the payment details to the standard output.
   while(rs.getNext())
      get myPayment from rs;
      Syslib.writeStdOut 
      ("Payment purpose and amount: " + myPayment.description + 
           " (" + myPayment.amount + ")");
   end
end

[edit] For more information

For details on how to connect to a database, see SQL database bindings.

For a keystroke-by-keystroke tutorial, see Access a database with EGL Rich UI. The third lesson shows how to retrieve Record type details from a database management system.


Code snippets main page