2016/01/26

Advanced queries in KIE Server

As a follow up of Advanced queries in jBPM 6.4 article let's take a look at queries in KIE Server - BPM capability.
Since KIE Server's BPM capability is based on jbpm services api, it does provide access to QueryService and its advanced (DashBuilder DataSets based) operations.

We are going to use the same use case, product sale with 10 000 loaded process and task instances. Next we show how you can query data both via KIE Server client and directly via raw REST api.

KIE Server capabilities when it comes to advanced queries mirrors what's available in services api, so users can:

  • register query definitions
  • replace  query definitions
  • unregister query definitions
  • get list of queries or individual query definition
  • execute queries on top of query definitions with 
    • paging and sorting
    • filter parameters
    • query with custom param builder and mappers
So let's start simple and build our KIE Server client to use query services:

KieServicesConfiguration configuration = KieServicesFactory.newRestConfiguration(serverUrl, user, password);

Set<Class<?>> extraClasses = new HashSet<Class<?>>();
extraClasses.add(Date.class); // for JSON only to properly map dates
        
configuration.setMarshallingFormat(MarshallingFormat.JSON);
configuration.addJaxbClasses(extraClasses);
        
KieServicesClient kieServicesClient =  KieServicesFactory.newKieServicesClient(configuration);
        
QueryServicesClient queryClient = kieServicesClient.getServicesClient(QueryServicesClient.class);

now we are ready to make use of the query service via QueryServicesClient

List available query definitions available in the system

List<QueryDefinition> queryDefs = queryClient.getQueries(0, 10);
System.out.println(queryDefs);

Next let's register new query definition that we can use for advanced queries

QueryDefinition query = new QueryDefinition();
query.setName("getAllTaskInstancesWithCustomVariables");
query.setSource("java:jboss/datasources/ExampleDS");
query.setExpression("select ti.*,  c.country, c.productCode, c.quantity, c.price, c.saleDate " +
                       "from AuditTaskImpl ti " +
                       "    inner join (select mv.map_var_id, mv.taskid from MappedVariable mv) mv " +
                       "      on (mv.taskid = ti.taskId) " +
                       "    inner join ProductSale c " +
                       "      on (c.id = mv.map_var_id)");
        
queryClient.registerQuery(query);

Once the query is registered with can make use of it and start fetching data. At first very basic query:

List<TaskInstance> tasks = queryClient.query("getAllTaskInstancesWithCustomVariables", "UserTasks", 0, 10, TaskInstance.class);
System.out.println(tasks);

this will return task instances directly from the data set without any filtering and use UserTasks mapper to build up object representation and apply paging - first page and 10 results at most.

Now it's time to use more advanced queries capabilities and start filtering by process variables. As described in the Advanced queries in jBPM 6.4 article to be able to map custom variables we need to provide their column mapping - name and type. Following is an example that searches for tasks that:

  • processInstanceId is between 1000 and 2000 - number range condition
  • price is over 800 - number comparison condition
  • sale date is between 01.02.2016 and 01.03.2016 - date range condition
  • product in sale are EAP or Wildfly - logical and group condition
  • order descending by saleDate and country

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
              
Date from = sdf.parse("2016-02-01");                        
Date to = sdf.parse("2016-03-01");
        
QueryFilterSpec spec = new QueryFilterSpecBuilder()
   .between("processInstanceId", 1000, 2000)
   .greaterThan("price", 800)
   .between("saleDate", from, to)
   .in("productCode", Arrays.asList("EAP", "WILDFLY"))
   .oderBy("saleDate, country", false)
 .addColumnMapping("COUNTRY", "string")
 .addColumnMapping("PRODUCTCODE", "string")
 .addColumnMapping("QUANTITY", "integer")
 .addColumnMapping("PRICE", "double")
 .addColumnMapping("SALEDATE", "date")
.get();        
        
List<TaskInstance> tasks = queryClient.query("getAllTaskInstancesWithCustomVariables", "UserTasksWithCustomVariables", spec, 0, 10, TaskInstance.class);
System.out.println(tasks);

The query in above example uses QueryFilterSpec (and its builder) that allows to specify query parameters and sorting options. In addition it allows to specify column mapping for custom elements to be set as variables next to default column for task details. These column mappings are then delivered to mapper for transforming results - in this case we used built in mapper UserTasksWithCustomVariables that will collect all data details and given column mappings as custom variables data.

QueryFilterSpec maps to use of QueryParams in services api so in inherits the same limitation - all conditions are AND based and thus means all must match to get a hit.

To overcome the problem, services api introduced QueryParamBuilder so users can build advanced filters. Similar is on KIE Server, though they need to be built and included in one of following:

  • KIE Server itself (like in WEB-INF/lib)
  • Inside a project - kjar
  • Inside a project's dependency
Implementing QueryParamBuilder to be used in KIE Server requires a factory so it can be discovered and created on query time - every time query is issues new instance of QueryParamBuilder will be requested with given parameters.

Using QueryParamBuilder in KIE Server

To be able to use QueryParamBuilder user needs to:
  • Implement QueryParamBuilder that will produce new instance every time is requested and given a map of parameters

public class TestQueryParamBuilder implements QueryParamBuilder<ColumnFilter> {

    private Map<String, Object> parameters;
    private boolean built = false;
    public TestQueryParamBuilder(Map<String, Object> parameters) {
        this.parameters = parameters;
    }
    
    @Override
    public ColumnFilter build() {
        // return null if it was already invoked
        if (built) {
            return null;
        }
        
        String columnName = "processInstanceId";
        
        ColumnFilter filter = FilterFactory.OR(
                FilterFactory.greaterOrEqualsTo(((Number)parameters.get("min")).longValue()),
                FilterFactory.lowerOrEqualsTo(((Number)parameters.get("max")).longValue()));
        filter.setColumnId(columnName);
       
        built = true;
        return filter;
    }

}
Above builder will produce filter that will accept processInstanceId that are grater that min or lower that max. Where min and max are given on each query issued as part of the request.
  • Implement QueryParamBuilderFactory 
public class TestQueryParamBuilderFactory implements QueryParamBuilderFactory {

    @Override
    public boolean accept(String identifier) {
        if ("test".equalsIgnoreCase(identifier)) {
            return true;
        }
        return false;
    }

    @Override
    public QueryParamBuilder newInstance(Map<String, Object> parameters) {
        return new TestQueryParamBuilder(parameters);
    }

}
Factory is responsible for returning new instances of the query param builder only if the given identifier is accepted by the factory. Identifier is given as part of query request and there can be only one query builder factory selected based on the identifier. In this case "test" identifier needs to be given to use this factory, and in turn query param builder.

There is last tiny bit required to make this to work - we need to make it discoverable so let's add service file into META-INF folder of the jar that will package these implementation.

META-INF/services/org.jbpm.services.api.query.QueryParamBuilderFactory

where the content of this file is fully qualified class name of the factory.

with this we can issue a request that will make use of newly created query builder for advanced filters:

Map<String, Object> params = new HashMaplt;String, Object>();
params.put("min", 10);
params.put("max", 20);

Listlt;TaskInstance> instances = queryClient.query("getAllTaskInstancesWithCustomVariables", "UserTasksWithCustomVariables", "test", params, 0, 10, TaskInstance.class);
So what we have done here:

  • reference registered query by name - getAllTaskInstancesWithCustomVariables
  • reference mapper by name - UserTasksWithCustomVariables
  • reference query param builder identifier - test
  • sent params (min and max) that will be used by new instance of query builder before query is executed

Similar to this you can register and use custom mappers and it is even simpler than query param builders as there is no need for factory as services api comes with registry that KIE Server uses to register found mappers by ServiceLoader based discovery.

Implement mapper so it can be used in KIE Server:


public class ProductSaleQueryMapper extends UserTaskInstanceWithCustomVarsQueryMapper {

    private static final long serialVersionUID = 3299692663640707607L;

    public ProductSaleQueryMapper() {
        super(getVariableMapping());
    }

    protected static Map<String, String> getVariableMapping() {
        Map<String, String> variablesMap = new HashMap<String, String>();
        
        variablesMap.put("COUNTRY", "string");
        variablesMap.put("PRODUCTCODE", "string");
        variablesMap.put("QUANTITY", "integer");
        variablesMap.put("PRICE", "double");
        variablesMap.put("SALEDATE", "date");
        
        return variablesMap;
    }

    @Override
    public String getName() {
        return "ProductSale";
    }
}

Here we simply extend the UserTaskInstanceWithCustomVarsQueryMapper and provide directly column mapping so it can be used without column mapping on request level. To be able to use it, mapper needs to be made discoverable so we need to create service file within META-INF folder of the jar that will package this implementation.

META-INF/services/org.jbpm.services.api.query.QueryResultMapper

where the content of this file is fully qualified class name of the mapper.

Now we can directly use it by referencing it by name:

List<TaskInstance> tasks = queryClient.query("getAllTaskInstancesWithCustomVariables", "ProductSale", 0, 10, TaskInstance.class);
System.out.println(tasks);

Raw REST API use of described examples

Get query definitions
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions?page=0&pageSize=10
Method:
  • GET

Register query definition
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables
Method:
  • POST
Request body:
{
  "query-name" : "getAllTaskInstancesWithCustomVariables1",
  "query-source" : "java:jboss/datasources/ExampleDS",
  "query-expression" : "select ti.*,  c.country, c.productCode, c.quantity, c.price, c.saleDate from AuditTaskImpl ti     inner join (select mv.map_var_id, mv.taskid from MappedVariable mv) mv       on (mv.taskid = ti.taskId)     inner join ProductSale c       on (c.id = mv.map_var_id)",
  "query-target" : "CUSTOM"

}

Query for tasks - no filtering
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/data?mapper=UserTasks&orderBy=&page=0&pageSize=10
Method:
  • GET


Query with filter spec
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/filtered-data?mapper=UserTasksWithCustomVariables&page=0&pageSize=10
Method:
  • POST
Request body:
{
  "order-by" : "saleDate, country",
  "order-asc" : false,
  "query-params" : [ {
    "cond-column" : "processInstanceId",
    "cond-operator" : "BETWEEN",
    "cond-values" : [ 1000, 2000 ]
  }, {
    "cond-column" : "price",
    "cond-operator" : "GREATER_THAN",
    "cond-values" : [ 800 ]
  }, {
    "cond-column" : "saleDate",
    "cond-operator" : "BETWEEN",
    "cond-values" : [ {"java.util.Date":1454281200000}, {"java.util.Date":1456786800000} ]
  }, {
    "cond-column" : "productCode",
    "cond-operator" : "IN",
    "cond-values" : [ "EAP", "WILDFLY" ]
  } ],
  "result-column-mapping" : {
    "PRICE" : "double",
    "PRODUCTCODE" : "string",
    "COUNTRY" : "string",
    "SALEDATE" : "date",
    "QUANTITY" : "integer"
  }
}

Query with custom query param builder
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/filtered-data?mapper=UserTasksWithCustomVariables&builder=test&page=0&pageSize=10
Method:
  • POST
Request body:
{
  "min" : 10,
  "max" : 20
}

Query for tasks - custom mapper
Endpoint:
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/data?mapper=ProductSale&orderBy=&page=0&pageSize=10
Method:
  • GET

With this, we have went over support for advanced queries in KIE Server for BPM capability.

As usual, feedback is welcome :)



Posted by at