I am using the Camunda 7.22 Spring Engine for my Java 21 project.
I want to perform a native query on the ACT_HI_PROCINST table.
Since I need additional data, I want to apply a JOIN:
My SQL query:
SELECT h.*,
d.NAME_ AS processDefinitionName,
d.VERSION_ AS processDefinitionVersion
FROM ACT_HI_PROCINST h
LEFT JOIN ACT_RE_PROCDEF d ON h.PROC_DEF_ID_ = d.ID_
val result = query.sql(sqlQuery).listpage(start, end);
However, the result contains null values for these fields.
I understand that I could execute a separate query for each missing value, but that would be very inefficient.
Is there a better way to achieve this?
I am using Native Query because I need to sort by state and apply pagination at the database level.
I would be very grateful for any kind of help.
The native query mechanism returns data as a list of typed objects, where the type is determined by the native query used. Therefore, the columns in the SELECT list must correspond to the fields in the typed object.
Yes i mean the processDefinitionName and processDefinitionVersion
I am sure that the data are correct.
Did it work in your test? did it realy return the HistoricProcessInstance with the name and version? If yes, would you mind posting a code sniped? maybe i did some other thing wrong.
I just tested it again:
the act_re_procdef raw looks like this:
"id_model11:1:1819" 1 "exmaple." "deploymentTest" "id_model11" 1 "1817" "Deployment not only active Test 2.bpmn" false 1 "15" 150 true
name: deploymentTest
but the response i get is
from this query:
SQL: SELECT h.*,
d.NAME_ AS processDefinitionName,
d.VERSION_ AS processDefinitionVersion
FROM ACT_HI_PROCINST h
LEFT JOIN ACT_RE_PROCDEF d ON h.PROC_DEF_ID_ = d.ID_
ORDER BY START_TIME_ DESC
I tried it with an normal query, and it worked fine. i only can post 1 image, but in my other reply you can see the screenshot
But i need custom querys, because of some sort and filter options, i am considering, accessing it directly throw a normal jdbc connection, and then mapping it to a dto. but im not sure if this is the best solution
Thanks again for the response and
Best regards David
I think the columes in the SELECT List are corresponding.
First of all:
Its the same data structur, as the response from the normal HistoricProcessInstanceQuery.list();
There the name and version is include. so i guess the normal query, automaticly joins the tables.
The result of a native query with the join to the left and the result of the normal query to the right:
This means that the column names must match the resultMap defined for historicProcessInstanceEntity in MyBatis as specified in the following mapping file: