Scout/Concepts/StatementBuilder

Scout

Community

Bugzilla

Statement builders enables to write WHERE statements for a SQL query based on the content of a SearchForm (using its FormData). This speeds up the development of Outline Services.

class: FormDataStatementBuilder

Description

Statements builders provide the possibility to write dynamically some SQL in an 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 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 DataModelConstants . 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 FormData as input. It may comes from the SearchFilter. 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 SequenceBox field From - To (two LongFields) for the Id
A StringField for the name
A SequenceBox field From - To (two DateFields) for the last update.

In the Outline Services the loadCategories(..) uses a SearchFilter as parameter. This SearchFilter contains the FormData corresponding to the SearchForm 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;
  }

Breadcrumbs

Scout/Concepts/StatementBuilder

Description

Example

See Also