Revision as of 22:13, 28 December 2011

Overview

EDT 0.7.0 release provides quick assist support for the following EGL action statements to generate default SQL statements:

1. ADD Statement
2. DELETE Statement
3. GET Statement
4. OPEN Statement
5. REPLACE Statement

Use examples to show how to use the feature in EDT plugin.

EGL Records Definition

         record Product{@Table {name="Product"}}
                id bigint{@Id};
                name string;
                price float; 
         end 

The above is a basic record definition, one must use annotation @Id to specific which record field is used as primary key, also can use annotation @Table to specify to which table the record is mapped, just as 'Product' definition shows, but this is optional.

         record OrderItem type '''Entity'''{@table{name = "OrderItem"}}
               ITEM_ID int{@Id}; 
               NAME string{ @Column { insertable=true } }; 
               IMAGE string?{ @Column { updateable=true } }; 
               PRICE decimal(7, 2)?; 
               DESCRIPTION string?; 
         end 

The above is an entity record definition that is generated from a table schema.

EGL Variables Declaration

       basicRec Product;     
       itemEntity OrderItem; 
       rs SQLResultSet?; 
       ds SQLDataSource? = new SQLDataSource("jdbc:derby:C:/databases/EGLDerbyR7;create=true");    
 

Default SQL Generation

ADD Statement 

   Click anywhere in the EGL SQL statement and press CTRL+1, and press “Add SQL Statement” proposal, default SQL statement will be added to the below statement:            
            (1) add basicRec to ds; //for basic record
Change To:            
           add basicRec to ds with                  
                     #sql{ insert into Product (id, name, price) values (?, ?, ?)};

            (2) add itemEntity to ds; //for entity record
Change To:
             add itemEntity to ds with
                             #sql{ insert into OrderItem (ITEM_ID, Name, IMAGE, price, description) values (?, ?, ?, ?, ?) }; 

DELETE Statement

        (3) delete basicRec from ds; // for basic record
Change To:
          delete basicRec from ds with
                  #sql{ delete from Product where id = ? };

      (4) delete itemEntity from ds; //for entity record
Change To:
         delete itemEntity from ds with
                 #sql{ delete from OrderItem where ITEM_ID = ? };

GET Statement

     (5) GET basicRec from ds; //for basic record
Change To:
         GET basicRec from ds using basicRec.id with
                   #sql{ select id, rtrim(name), price from Product where id = ? };

     (6) GET itemEntity from ds; //for entity record
Change To:
         GET itemEntity from ds using itemEntity.ITEM_ID with
                   #sql{ select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description) from OrderItem where ITEM_ID = ? };

OPEN Statement

     (7) rows OrderItem; open rs from ds for rows;
Change To:
        open rs from ds using rows.ITEM_ID with
                  #sql{ select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description) from OrderItem where ITEM_ID =  ? };

REPLACE(Update) Statement 

    (8) replace itemEntity to ds ;
Change To:
       replace itemEntity to ds with
                #sql{ update OrderItem set Name = ?,IMAGE = ?, price = ?,description = ? where ITEM_ID = ? } ;

Current Limitation

  Note the feature can only generate default SQL statements for basic record or entity record that does not contain any entity relationship field.