|
|
| Line 1: |
Line 1: |
| − | {{ScoutPage|cat=Server}}
| + | 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 {{ScoutLink|Concepts|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}}
| + | |
