Revision as of 22:04, 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 For 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 (?, ?, ?, ?, ?) }; 

Default SQL For 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 = ? };

Default SQL For 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 = ? };

Default SQL For 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 =  ? }; Default SQL For 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.