Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EDT:Default SQL Generation"
(24 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
= Overview = | = Overview = | ||
− | + | EDT 0.7.0 release supports the following EGL action statements to generate default SQL statements: | |
− | + | #ADD Statement | |
+ | #DELETE Statement | ||
+ | #GET Statement | ||
+ | #OPEN Statement | ||
+ | #REPLACE Statement | ||
+ | |||
+ | The following sections show how to use the feature in EDT.<br> | ||
= EGL Records Definition = | = EGL Records Definition = | ||
+ | <pre>record Product{@Table {name="Product"}} | ||
+ | id bigint{@Id}; | ||
+ | name string; | ||
+ | price float; | ||
+ | end | ||
+ | </pre> | ||
+ | In that Record type, the '''Id''' annotation specifies which record field is used as the primary key. The optional '''@Table''' annotation specifies the table to which the record is mapped. | ||
+ | <pre>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 | ||
+ | </pre> | ||
+ | The previous definition was created from a database table schema, by use of the EGL retrieve feature.<br> | ||
− | + | = EGL Variables Declaration = | |
+ | <pre>basicRec Product; | ||
+ | itemEntity OrderItem; | ||
+ | rs SQLResultSet?; | ||
+ | ds SQLDataSource? = new SQLDataSource("jdbc:derby:C:/databases/EGLDerbyR7;create=true"); | ||
+ | |||
+ | </pre> | ||
+ | = Default SQL Generation = | ||
− | | + | == ADD Statement <br> == |
− | + | Click anywhere in the EGL SQL statement, press ctrl-1, and click '''Add SQL Statement'''. | |
− | + | Here is an example:<br> | |
− | = | + | *From this:<br> |
+ | <pre> add basicRec to ds; | ||
+ | |||
+ | </pre> | ||
+ | *To this: | ||
+ | <pre> add basicRec to ds | ||
+ | with #sql{ insert into Product (id, name, price) values (?, ?, ?)}; | ||
+ | </pre> | ||
+ | <br> (2) add itemEntity to ds; //for entity record <br>Change To: <br> add itemEntity to ds with <br> #sql{ insert into OrderItem (ITEM_ID, Name, IMAGE, price, description) values (?, ?, ?, ?, ?) }; | ||
+ | |||
+ | == DELETE Statement == | ||
+ | |||
+ | (3) delete basicRec from ds; // for basic record <br>Change To: <br> delete basicRec from ds with <br> #sql{ delete from Product where id = ? }; | ||
+ | |||
+ | (4) delete itemEntity from ds; //for entity record <br>Change To: <br> delete itemEntity from ds with <br> #sql{ delete from OrderItem where ITEM_ID = ? }; | ||
+ | |||
+ | == GET Statement<br> == | ||
+ | |||
+ | (5) GET basicRec from ds; //for basic record <br>Change To: <br> GET basicRec from ds using basicRec.id with <br> #sql{ select id, rtrim(name), price from Product where id = ? }; | ||
+ | |||
+ | (6) GET itemEntity from ds; //for entity record <br>Change To:<br> GET itemEntity from ds using itemEntity.ITEM_ID with <br> #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; <br>Change To: <br> open rs from ds using rows.ITEM_ID with<br> #sql{ select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description) from OrderItem where ITEM_ID = ? }; <br> | ||
− | + | == REPLACE(Update) Statement == | |
− | + | ''' ''' (8) replace itemEntity to ds ; <br>Change To: <br> replace itemEntity to ds with <br> #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. <br> | |
<br> | <br> | ||
− | <br> | + | <br> <br> |
Latest revision as of 14:54, 29 January 2012
Contents
Overview
EDT 0.7.0 release supports the following EGL action statements to generate default SQL statements:
- ADD Statement
- DELETE Statement
- GET Statement
- OPEN Statement
- REPLACE Statement
The following sections show how to use the feature in EDT.
EGL Records Definition
record Product{@Table {name="Product"}} id bigint{@Id}; name string; price float; end
In that Record type, the Id annotation specifies which record field is used as the primary key. The optional @Table annotation specifies the table to which the record is mapped.
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 previous definition was created from a database table schema, by use of the EGL retrieve feature.
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, press ctrl-1, and click Add SQL Statement.
Here is an example:
- From this:
add basicRec to ds;
- To this:
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.