Getting started

Setting up your project

To get started, you have to add the SQL expanders as an expansion resource to your application project. This can be done either in the µRadiant, or directly in the expansion settings file.

µRadiant

Open the model for your JEE application.
Go the to settings tab.
Add a new expansion resource with name net.democritus:querysearch-expanders and version 2.24.0.

Project files

Open the expansion settings file for your application, which is often found in the conf folder in the root of your project's workspace with the name expansionSettings.xml.

Add a child element to expansionResources as follows:

<expansionResource name="net.democritus:querysearch-expanders" version="2.24.0"/>

Adding QuerySearch to an element

The option enableQuerySearch can be used to add QuerySearch support to a DataElement. This option is actually translated behind the scenes into a QuerySearch element, with the same name as that of the DataElement. The expanded classes typically include the name of the QuerySearch object, such as <QuerySearch.name>QuerySearch and <QuerySearch.name>QueryFilter.

Why is the name of the parent DataElement not part of the expanded classes of the QuerySearch?

The parent element of a QuerySearch element is DataElement. To avoid collisions, it would make sense for the name of the parent element to be included in the name of generated classes, alongside the name of the QuerySearch element itself.

The reason for this not being the case is purely historical. Originally there was no QuerySearch metamodel and only one QuerySearch was expanded per DataElement. Where today the name of the QuerySearch itself is used, in the past this was the name of the DataElement. To remain backwards compatible, the enableQuerySearch option now creates a QuerySearch element before expansion, which has the same name as the DataElement it is linked to.

Example

A QueryFilter class previously (in version 1.x) have been <DataElement.name>QueryFilter and now <QuerySearch.name>QueryFilter, which is a match with the name of the QuerySearch now being the same as that of its parent DataElement.

Option

This option will enable the use of QuerySearch on a specific DataElement. As a result, the querySearch() pipeline will be added to the stack for that element and a QuerySearch and QueryFilter will be generated.

<options>
  <enableQuerySearch/>
</options>

Using metamodel

Alternatively to using the enableQuerySearch option, it is possible to directly make use of the QuerySearch metamodel elements. More information can be found on the dedicated page for the metamodel.

Implementation

The two most notable classes generated for a QuerySearch are the QuerySearch class in the data layer and the matching QueryFilter class in the shared layer.

The purpose of the QueryFilter POJO class, is to define fields that can be used as both enable conditions and inputs for constraints in a database query. These filter values are translated to "if filter value is defined (not null), then add constraint that uses this value". To enable this, boxed types are used for primitives (eg Integer instead of int), so the value null can be used as an enable condition.

The actual implementation of the constraints is placed in the method QuerySearch.buildConstraints(), which is passed the QueryFilter object. The constraints are implemented using the "onion constraints" methods to define them in a clean declarative manner.

note

The idea behind the design of QuerySearch is to be able to declaratively define combinable filters on the data in the database, that can be activated depending on filter criteria that are given. This means that typically you want to try to define the filter fields in terms of how it relates to a value in the database as a constraint parameter, rather than in terms of a specific constraint operation.

For example, if you want to verify that an integer value is inside a range, you could name them Integer valueMin and Integer valueMax. You would then define a constraint as "if both valueMin and valueMax are defined, add constraint database value is between those two values". To also perform open-ended checks, an additional constraint can be added that verifies that the value if smaller than valueMax if only that one is given. The result is that the query will adapt depending on the given filtering information, rather than what constraint is requested.

Anti-Pattern Example

If you want to verify that an integer value is inside a range, the filter fields could be defined as Integer valueBetweenCheckMin and Integer valueBetweenCheckMax. This would however imply that these fields are specifically intended to drive a between constraint on that field, which is not the intended use of QuerySearch.

Example

Here we add a constraint on the name of an element and its code field. Each constraint is only applied if the value is given and since they are added directly in the bottom and() constraint, they are combined as a conjunction if both are given.

MyDataElementQueryFilter.java

public class MyDataElementQueryFilter implements MyDataElementElementQueryFilter {

  // anchor:fields:start
  // anchor:fields:end

  // @anchor:fields:start
  // @anchor:fields:end
  // anchor:custom-fields:start
  private String name;
  private String code;
  // anchor:custom-fields:end

  // anchor:methods:start
  // anchor:methods:end

  // @anchor:methods:start
  // @anchor:methods:end
  // anchor:custom-methods:start
  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }
  // anchor:custom-methods:end

}

MyDataElementQuerySearch.java

public class MyDataElementQuerySearch implements MyDataElementQuerySearchLocal<MyDataElementQueryFilter> {

  private EntityManager entityManager;

  // @anchor:fields:start
  // @anchor:fields:end
  // anchor:custom-fields:start
  // anchor:custom-fields:end

  public MyDataElementQuerySearch(EntityManager entityManager) {
    this.entityManager = entityManager;
  }

  private void setPagingRestrictions(Paging paging, Query query) {
    int rowsPerPage = paging.getRowsPerPage();
    if (rowsPerPage <= 0) {
      return;
    }

    int page = paging.getPage();
    int offset = rowsPerPage * page; // 0-based page

    query.setFirstResult(offset);
    query.setMaxResults(rowsPerPage);
  }

  @SuppressWarnings("unchecked")
  private SearchResult<MyDataElementData> fetchData(SearchDetails<MyDataElementQueryFilter> searchDetails, Query countQuery, Query dataQuery) {
    List<Long> countList = countQuery.getResultList();
    Long total = countList.get(0);

    // @anchor:fetch-after-count:start
    if (total == 0 || searchDetails.getPaging().getRowsPerPage() == 0) {
      return SearchResult.success(Collections.emptyList(), total.intValue());
    }
    // @anchor:fetch-after-count:end
    // anchor:custom-fetch-after-count:start
    // anchor:custom-fetch-after-count:end

    setPagingRestrictions(searchDetails.getPaging(), dataQuery);

    List<MyDataElementData> resultData = (List<MyDataElementData>) dataQuery.getResultList();

    // @anchor:fetch-after-queries:start
    // @anchor:fetch-after-queries:end
    // anchor:custom-fetch-after-queries:start
    // anchor:custom-fetch-after-queries:end

    return SearchResult.success(resultData, total.intValue());
  }

  public SearchResult<MyDataElementData> querySearch(ParameterContext<SearchDetails<MyDataElementQueryFilter>> searchParameter) {
    SearchDetails<MyDataElementQueryFilter> searchDetails = searchParameter.getValue();
    MyDataElementQueryFilter queryFilter = searchDetails.getDetails();
    List<SortField> sortFields = searchDetails.getSortFields();

    QueryBuilder queryBuilder = select("o")
        .from("com.example.MyDataElement", "o")
        .where(buildConstraints(searchParameter.construct(queryFilter)))
        .orderBy(sortFields);

    // @anchor:query-before-build:start
    // @anchor:query-before-build:end
    // anchor:custom-query-before-build:start
    // anchor:custom-query-before-build:end

    Query query = queryBuilder.buildQuery(entityManager), countQuery = queryBuilder.buildCountQuery(entityManager);

    // @anchor:query-after-build:start
    // @anchor:query-after-build:end
    // anchor:custom-query-after-build:start
    // anchor:custom-query-after-build:end

    return fetchData(searchDetails, countQuery, query);
  }

  private QueryConstraint buildConstraints(ParameterContext<MyDataElementQueryFilter> filterParameter) {
    MyDataElementQueryFilter filter = filterParameter.getValue();
    QueryConstraint constraint = and(
        // @anchor:constraints:start
        // @anchor:constraints:end
        // anchor:custom-constraints:start
        when(filter.getName() != null, () ->
            equal("o.name", filter.getName())),
        when(filter.getCode() != null, () ->
            equal("o.code", filter.getCode()))
        // anchor:custom-constraints:end
    );

    // @anchor:after-constraints:start
    // @anchor:after-constraints:end
    // anchor:custom-after-constraints:start
    // anchor:custom-after-constraints:end

    return constraint;
  }

  // @anchor:methods:start
  // @anchor:methods:end
  // anchor:custom-methods:start
  // anchor:custom-methods:end

}

Setting up your project​

µRadiant​

Project files​

Adding QuerySearch to an element​

Using metamodel​

Implementation​

Example​

Setting up your project

µRadiant

Project files

Adding QuerySearch to an element

Using metamodel

Implementation

Example