|
|
| (2 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| − | {{ScoutPage|cat=Concepts}}
| + | The Scout documentation has been moved to https://eclipsescout.github.io/. |
| − | | + | |
| − | Statement builders enables to write WHERE statements for a SQL query based on the content of a {{ScoutLink|Concepts|SearchForm|SearchForm}} (using its {{ScoutLink|Concepts|FormData|FormData}}). This speeds up the development of {{ScoutLink|Concepts|Outline_Service|Outline Services}}.
| + | |
| − | | + | |
| − | * class: {{ScoutJavadoc|FormDataStatementBuilder|C}}
| + | |
| − | | + | |
| − | == Description ==
| + | |
| − | Statements builders provide the possibility to write dynamically some SQL in an {{ScoutLink|Concepts|Outline_Service|OutlineService}}. It is the junction between the database and Java in order to write the query that corresponds to the values contained in the formData. The builder rely on SqlStyle to make the code independent from the Database Engine.
| + | |
| − | | + | |
| − | Basically the Statements builder needs to be initialized with knowledge on how should the statements be written. This is achieve with <code>setValueDefinition(..)</code> function that binds a FieldData of the FormData, with the corresponding name of the field in the Database and one operator.
| + | |
| − | | + | |
| − | The operators are defined as constants of <!-- org.eclipse.scout.rt.shared.data.model.DataModelConstants --> {{ScoutJavadoc|DataModelConstants|C}}. The most useful are:
| + | |
| − | | + | |
| − | For values (like numbers) :
| + | |
| − | * OPERATOR_EQ (value equal)
| + | |
| − | * OPERATOR_GE (value greater or equal than)
| + | |
| − | * OPERATOR_LE (value lower or equal than)
| + | |
| − | | + | |
| − | For Dates (without the day part), there are the same kind of operators :
| + | |
| − | * OPERATOR_DATE_EQ
| + | |
| − | * OPERATOR_DATE_GE (date greater or equal than)
| + | |
| − | * OPERATOR_DATE_LE (date lower or equal than)
| + | |
| − | | + | |
| − | For Strings / varchars
| + | |
| − | * OPERATOR_CONTAINS (is a string in an other)
| + | |
| − | * OPERATOR_STARTS_WITH (beginning of a string matches)
| + | |
| − | * OPERATOR_ENDS_WITH (end of a string matches)
| + | |
| − | | + | |
| − | | + | |
| − | The <code>build(..)</code> takes the corresponding {{ScoutLink|Concepts|FormData|FormData}} as input. It may comes from the {{ScoutLink|Concepts|SearchFilter|SearchFilter}}. Foreach value set in the FormData, the corresponding WHERE condition is added (depending on the operator)
| + | |
| − | | + | |
| − | The statement starts with an <code>and</code> and is an empty string if there is no constraint. Therefore it can not be used right after WHERE. If you have no other condition to add, you can add a <code>WHERE 1=1</code> witch is ignored by your database engine.
| + | |
| − | | + | |
| − | In the <code>SQL.select(..)</code> function, it is necessary to add the corresponding variables binding. That is very simple: add the map returned by the function <code>getBindMap()</code>
| + | |
| − | | + | |
| − | | + | |
| − | == Example ==
| + | |
| − | Lets consider a simple Table CATEGORY containing three columns:
| + | |
| − | * category_id (int)
| + | |
| − | * name (varchar)
| + | |
| − | * last_update (timestamp)
| + | |
| − | | + | |
| − | | + | |
| − | [[Image:Scout SearchForm.png]]
| + | |
| − | | + | |
| − | The SearchForm contains this fields:
| + | |
| − | * A {{ScoutLink|Concepts|SequenceBox|SequenceBox field}} From - To (two LongFields) for the Id
| + | |
| − | * A {{ScoutLink|Concepts|StringField|StringField}} for the name
| + | |
| − | * A {{ScoutLink|Concepts|SequenceBox|SequenceBox field}} From - To (two DateFields) for the last update.
| + | |
| − | | + | |
| − | | + | |
| − | In the {{ScoutLink|Concepts|Outline_Service|Outline Services}} the <code>loadCategories(..)</code> uses a {{ScoutLink|Concepts|SearchFilter|SearchFilter}} as parameter. This SearchFilter contains the {{ScoutLink|Concepts|FormData|FormData}} corresponding to the {{ScoutLink|Concepts|Search_Form|SearchForm}} in the Client.
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | public Object[][] loadCategories(SearchFilter filter) throws ProcessingException {
| + | |
| − | FormDataStatementBuilder categoriesStatementBuilder = new FormDataStatementBuilder(SQL.getSqlStyle());
| + | |
| − | categoriesStatementBuilder.setValueDefinition(
| + | |
| − | CategoriesSearchFormData.CategoryIdFrom.class, "category_id", DataModelConstants.OPERATOR_GE);
| + | |
| − | categoriesStatementBuilder.setValueDefinition(
| + | |
| − | CategoriesSearchFormData.CategoryIdTo.class, "category_id", DataModelConstants.OPERATOR_LE);
| + | |
| − | categoriesStatementBuilder.setValueDefinition(
| + | |
| − | CategoriesSearchFormData.Name.class, "name", DataModelConstants.OPERATOR_CONTAINS);
| + | |
| − | categoriesStatementBuilder.setValueDefinition(
| + | |
| − | CategoriesSearchFormData.LastUpdateFrom.class, "last_update", DataModelConstants.OPERATOR_DATE_GE);
| + | |
| − | categoriesStatementBuilder.setValueDefinition(
| + | |
| − | CategoriesSearchFormData.LastUpdateTo.class, "last_update", DataModelConstants.OPERATOR_DATE_LE);
| + | |
| − | | + | |
| − | Object[][] result = SQL.select(
| + | |
| − | "select category_id, " +
| + | |
| − | " name, " +
| + | |
| − | " last_update" +
| + | |
| − | " from category " +
| + | |
| − | " where 1 = 1 " +
| + | |
| − | categoriesStatementBuilder.build(filter.getFormData()),
| + | |
| − | categoriesStatementBuilder.getBindMap());
| + | |
| − | return result;
| + | |
| − | }
| + | |
| − | </source>
| + | |
| − | | + | |
| − | == See Also ==
| + | |
| − | * {{ScoutLink|Concepts|Server Plug-In|Server Plug-In}}
| + | |
| − | * {{ScoutLink|Concepts|Outline_Service|OutlineService}}
| + | |
| − | * {{ScoutLink|Concepts|FormData|FormData}}
| + | |
| − | * {{ScoutLink|Concepts|Search_Form|Search Form}}
| + | |
