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

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

QueryDefinition query = new QueryDefinition();
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)");

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

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")
List<TaskInstance> tasks = queryClient.query("getAllTaskInstancesWithCustomVariables", "UserTasksWithCustomVariables", spec, 0, 10, TaskInstance.class);

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;
    public ColumnFilter build() {
        // return null if it was already invoked
        if (built) {
            return null;
        String columnName = "processInstanceId";
        ColumnFilter filter = FilterFactory.OR(
        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 {

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

    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.


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() {

    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;

    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.


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

Raw REST API use of described examples

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

Register query definition
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables
  • 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
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/data?mapper=UserTasks&orderBy=&page=0&pageSize=10
  • GET

Query with filter spec
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/filtered-data?mapper=UserTasksWithCustomVariables&page=0&pageSize=10
  • 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
  • http://localhost:8230/kie-server/services/rest/server/queries/definitions/getAllTaskInstancesWithCustomVariables/filtered-data?mapper=UserTasksWithCustomVariables&builder=test&page=0&pageSize=10
  • POST
Request body:
  "min" : 10,
  "max" : 20

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

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

As usual, feedback is welcome :)


  1. That's great, thanks for sharing this new feature. I was already impressed with the QueryClient - now it is much more impressive and powerful!

  2. Hi Maciej,

    One question: If I add my mapper on a new package of my Kjar, is it necessary that agrege in the package-name-white-list? (Plus add in org.jbpm.services.api.query.QueryResultMapper)

    thank you

    1. it does not have to be in package-name-while-list as it's not used for authoring at all. It needs to have the services folder with named org.jbpm.services.api.query.QueryResultMapper that defined FQCN of the mapper impl and that's all.

    2. Hello Maciej,

      I have two questions, would you please kindly help?

      1) I need to add my custom mapper, but i develop process using business central and then export the KJar from it. As you said above, Kjar should contains custom mapper and service folder with named query, how can i add them using business central?
      or how can i develop custom mapper and deploy in business central/kie server??

      2) Also, i found QueryServicesClient.QUERY_MAP_RAW always return the same record.

      following is my code :

      public static void registerQuery() {
      String QUERY_NAME = "getAllTimesheets";
      // building the query
      QueryDefinition queryDefinition = QueryDefinition.builder().name(QUERY_NAME)
      // .expression("select id, employee_no, employee_name, charge_item_name from timesheet")
      .expression("select * from ProcessInstanceLog")

      // two queries cannot have the same name

      System.out.println("Registered new Query");

      List instances = KieServerUtils.getQueryServicesClient().query(QUERY_NAME,
      0, 10, List.class);

      for (List l: instances) {
      for (Object o: l) {

      there are 25 different records in ProcessInstanceLog table in my DB, but the above program return the SAME record (first record) 25 times. SO STRANGE!

      Version: 6.5.0.Final-redhat-2

      Can you please help? thank you very much.

    3. Did you found a solution? thanks

  3. Hello Maciej,

    Thank you for sharing this amazing information.
    Is there a way to register the query definition "statically". I mean defining it somewhere in the project, in a manner that it's available to remote client and it avoid the remote client dealing with the implementation details like the datasource name and the specific sql query?

    1. recently there was enhancement implemented to allow to define your queries in kjar so they will be registered when kjar is deployed and removed when kjar is undeployed. This is coming out with version 7

    2. This comment has been removed by the author.

  4. Hi Maciej,

    Thanks a lot for these posts, they've been really helpful!

    Going through this example I came across a problem when a process starts without going through a human task.
    The ProcessInstance and the DataObject get persisted, but there is no entry in the MappedVariable table. So I'm unable to join them.

    If the process stops at a human task, it's working fine and I'm able to make the join between the 3 tables.

    I'm using BPMS 6.4.4.

    Am I missing some configuration?


    1. Since there is no wait state in the process marshallers for process variables are not invoked as process instance is removed - no longer active. You can register this process event listener to call marshallers on process instance completion and thus persist that variables - https://github.com/kiegroup/jbpm/blob/master/jbpm-flow/src/main/java/org/jbpm/process/instance/event/listeners/MarshalVariablesProcessEventListener.java

  5. Hello, I have a problem with preflight OPTIONS request without authorization headers in version 6.4 for the REST API in a custom external web app. How do I solve this issue?

    1. not sure what the problem is so please elaborate a bit more

    2. My web app is on a different machine and when I am sending a request the browser sent a preflight OPTIONS request without authorization headers because is CORS and I get unauthorized as a response. For the kie server I fixed it changing the security-constraint in web.xml in kie-server.war like here https://github.com/ibek/vacation-planner/blob/master/vacation-process/src/main/resources/config/web.xml#L17. But I am having the same issue for the bussiness-central/rest/* requests. What is the proper way to configure it so requests with method OPTIONS don't need authorization?

    3. this might not be possible in workbench. Though feel free to drop an email to drools mailing list maybe there will be someone who has done it somehow.

    4. Thank you Maciej! :)

  6. Hi Maciej,
    I am trying to execute the same example with a simple Student object and using *****configuration.setMarshallingFormat(MarshallingFormat.JSON);****
    but after executing the ********Long processInstanceId = processClient.startProcess(containerId, processId, params);*******

    getting the below exception

    SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
    SLF4J: Defaulting to no-operation (NOP) logger implementation
    SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
    Exception in thread "main" org.kie.server.client.KieServicesException: Error while creating service response! The actual result type class java.util.LinkedHashMap does not match the expected type class org.kie.server.api.model.KieServerInfo!
    at org.kie.server.client.impl.AbstractKieServicesClientImpl.checkResultType(AbstractKieServicesClientImpl.java:527)
    at org.kie.server.client.impl.AbstractKieServicesClientImpl.makeHttpGetRequestAndCreateServiceResponse(AbstractKieServicesClientImpl.java:142)
    at org.kie.server.client.impl.KieServicesClientImpl.getServerInfo(KieServicesClientImpl.java:123)
    at org.kie.server.client.impl.KieServicesClientImpl.init(KieServicesClientImpl.java:74)
    at org.kie.server.client.impl.KieServicesClientImpl.(KieServicesClientImpl.java:61)
    at org.kie.server.client.KieServicesFactory.newKieServicesClient(KieServicesFactory.java:101)
    at com.anil.kie.kieserver.work.KieServerRemoteSetUpUtil.main(KieServerRemoteSetUpUtil.java:40)

    This is my actual Code

    //Map> extraClasses = new ConcurrentHashMap>();
    Set> extraClasses= new HashSet>();
    String serverUrl = "http://localhost:8080/kie-server/services/rest/server";
    String user = "jbpmAdmin";
    String password = "jbpmAdmin@1";

    String containerId = "sampleContainerId";
    String processId = "jbpmProj.scriptExample";
    KieServicesConfiguration configuration = KieServicesFactory.newRestConfiguration(serverUrl, user, password);

    //Marshaller marshaller = MarshallerFactory.getMarshaller(extraClasses, MarshallingFormat.JSON, Student.class.getClassLoader());
    KieServicesClient kieServicesClient = KieServicesFactory.newKieServicesClient(configuration);
    //KieServicesClient kieServicesClient = KieServicesFactory.newKieServicesClient(configuration,kieContainer.getClassLoader());
    ProcessServicesClient processClient = kieServicesClient.getServicesClient(ProcessServicesClient.class);

    Map params = new HashMap();
    Student student =new Student();
    params.put("student", student);
    //params.put("age", 25);
    Long processInstanceId = processClient.startProcess(containerId, processId, params);

    System.out.println("processInstanceId is :::::"+processInstanceId);


    please suggest

    1. this looks like your client cannot connect to kie server for whatever reason - check if your user is authenticated and authorised on kie server...

  7. Hi, Maciej!

    Can we add a custom query mapper to project deployment?

    1. yes, they can be provided as part of kjar, follow the same procedure - service loader approach (META-INF/services/org.jbpm.services.api.query.QueryResultMapper) to register them when kjar is deployed to kie server.

    2. Big thanks!I have one more question - how can custom query be included to kjar?

    3. you need to include a query-definitions.json file in kjar that will define queries in json format, example:
      "query-name" : "first-query",
      "query-source" : "${org.kie.server.persistence.ds}",
      "query-expression" : "select * from ProcessInstanceLog",
      "query-target" : "PROCESS"

      "query-name" : "second-query",
      "query-source" : "${org.kie.server.persistence.ds}",
      "query-expression" : "select * from NodeInstanceLog",
      "query-target" : "CUSTOM"


    4. Hi Maciej,

      I've added new file query-definitions.json with custom query into folder src/main/resources/, but seems the custom query not registered in DB. It doesn't work. Can you please guide me what's wrong? Thank you so much.


    5. Does any one able to make it work with kjar deployment in 6.4?

  8. I'm using this feature. I want to know if there is a possibility to use variables in the queries. I want to use a query like "select xxx from table yyy where yyy.zzz = ?".

    My DB engine is getting problems when I use equalsTo(...), because it just concatenates my variable to the query (the engine setups an execution plan for every different string). The DBAs asked to use parameters in the query (the classic '?').

    1. as far as I know it's not possible, but feel free to open jira (feature request) for dash builder as it might be considered for future releases https://issues.jboss.org/projects/DASHBUILDE/summary

  9. Hi Maciej,

    Tried adopting this on my data model in version 7.9.0. The query got registered but when executing the query, found that the value of TASKID in the database table MAPPEDVARIABLE is always NULL. Any pointers on what could be the reason for it.

  10. Are these custom queries getting executed while kie server boot up?

  11. Thanks for sharing, nice post! Post really provice useful information!

    Giaonhan247 chuyên dịch vụ vận chuyển hàng đi mỹ cũng như dịch vụ ship hàng mỹ từ dịch vụ nhận mua hộ hàng mỹ từ website nổi tiếng Mỹ là mua hàng amazon về VN uy tín, giá rẻ.

  12. This comment has been removed by the author.