Support for jOOQ SQL queries for querying process instance

Camunda 7 Topics Discussion & Questions
1

We have a custom requirement for a specific API for process instance, which requires us to use a SQL query to get information that is required.
Analyzing different approaches, configuring myBatis is possible. But since we have a special case for that particular API, it seems that configuring seems to be an unnecessary overhead in terms of code maintenance

Is there a way to query Camunda tables using jOOQ library? If so, is there documentation for the same?

2

@AymanPatel - Camunda provides NativeQuery method for custom queries. Below is the sample code . Would this option work ?

List<HistoricTaskInstance> nativeActInstList = historyService.createNativeHistoricTaskInstanceQuery().
sql(sb.toString()).parameter("processInstanceId", processInstanceId).list();
3

I want to query active process instance and not historic task instance.

4

you can use below . For Process instances which are active or not active all will be stored in the ACT_HI_PROCINSTwhich has a “State_” variable which tell whether a process is active or complete

    List<HistoricProcessInstance> processHierarchyList = (List<HistoricProcessInstance>) historyService.createNativeHistoricProcessInstanceQuery()
            .sql(sb.toString())
            .parameter("rootProcessInstanceId", processInstanceObject.getRootProcessInstanceId())
            .list();
5

What is sb in sb.toString() ?

6

that is a string variable holding your sql query .