Jump to: navigation, search

Scout/Concepts/StatementBuilder

< Scout‎ | Concepts
Revision as of 11:14, 3 November 2011 by Claudio.guglielmo.gmail.com (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


Scout
Wiki Home
Website
DownloadGit
Community
ForumsBlogTwitter
Bugzilla
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: C obj.pngFormDataStatementBuilder

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 C obj.pngDataModelConstants . 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)


Scout SearchForm.png

The SearchForm contains this fields:


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

See Also