Scout/Concepts/StatementBuilder

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 The Scout documentation has been moved to https://eclipsescout.github.io/. (using its The Scout documentation has been moved to https://eclipsescout.github.io/.). This speeds up the development of The Scout documentation has been moved to https://eclipsescout.github.io/..

• class: The Scout documentation has been moved to https://eclipsescout.github.io/.

Description

Statements builders provide the possibility to write dynamically some SQL in an The Scout documentation has been moved to https://eclipsescout.github.io/.. 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 The Scout documentation has been moved to https://eclipsescout.github.io/. 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 setValueDefinition(..) 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 The Scout documentation has been moved to https://eclipsescout.github.io/.. 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 build(..) takes the corresponding The Scout documentation has been moved to https://eclipsescout.github.io/. as input. It may comes from the The Scout documentation has been moved to https://eclipsescout.github.io/.. Foreach value set in the FormData, the corresponding WHERE condition is added (depending on the operator)

The statement starts with an and 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 WHERE 1=1 witch is ignored by your database engine.

In the SQL.select(..) function, it is necessary to add the corresponding variables binding. That is very simple: add the map returned by the function getBindMap()

Example

Lets consider a simple Table CATEGORY containing three columns:

• category_id (int)
• name (varchar)
• last_update (timestamp)

The SearchForm contains this fields:

• A The Scout documentation has been moved to https://eclipsescout.github.io/. From - To (two LongFields) for the Id
• A The Scout documentation has been moved to https://eclipsescout.github.io/. for the name
• A The Scout documentation has been moved to https://eclipsescout.github.io/. From - To (two DateFields) for the last update.

In the The Scout documentation has been moved to https://eclipsescout.github.io/. the loadCategories(..) uses a The Scout documentation has been moved to https://eclipsescout.github.io/. as parameter. This SearchFilter contains the The Scout documentation has been moved to https://eclipsescout.github.io/. corresponding to the The Scout documentation has been moved to https://eclipsescout.github.io/. in the Client.

  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;
  }

See Also

• The Scout documentation has been moved to https://eclipsescout.github.io/.
• The Scout documentation has been moved to https://eclipsescout.github.io/.
• The Scout documentation has been moved to https://eclipsescout.github.io/.
• The Scout documentation has been moved to https://eclipsescout.github.io/.