camunda 自定义sql查询数据

历史流程实例对应的表：ACT_HI_PROCINST

@Resource
HistoryService historyService;

// 查询历史流程实例列表----不分页

List<HistoricProcessInstance> processInstancesList = historyService.createNativeHistoricProcessInstanceQuery()
                .sql(customSql(" SELECT HPI.* ,DEF.NAME_, DEF.VERSION_, DEF.DEPLOYMENT_ID_ FROM ", queryDto, "HPI.START_TIME_  DESC")).list();

// 查询历史流程实例列表----分页

List<HistoricProcessInstance> processInstanceList = historyService.createNativeHistoricProcessInstanceQuery()
                .sql(customSql("SELECT HPI.* ,DEF.NAME_, DEF.VERSION_, DEF.DEPLOYMENT_ID_ FROM ", queryDto,  "HPI.START_TIME_  DESC"))
                .listPage(page.getStartRow(), page.getPageSize());

// 查询历史流程实例数量

long count = historyService.createNativeHistoricProcessInstanceQuery()
                .sql(customSql("select count(*) from ", queryDto,  "HPI.START_TIME_  DESC")).count();

/**
     * 自定义sql查询
     *
     * @param initSql 初始化sql
     * @param dto     查询参数
     * @param orderBy 排序参数
     * @return 拼接后的sql
     */
    private String customSql(String initSql, MyHistoricProcessInstanceQueryDto dto, String orderBy) {
        String customSql = "";
        if (Objects.isNull(dto)) {
            return customSql;
        }
        String tenantId = dto.getTenantId();
        String processDefinitionKey = dto.getProcessDefinitionKey();
        List<String> processInstanceBusinessKeyIn = dto.getProcessInstanceBusinessKeyIn();
        String processInstanceBusinessKey = dto.getProcessInstanceBusinessKey();
        String processInstanceId = dto.getProcessInstanceId();
        List<String> stateList = dto.getStateList();


        if (StringUtils.isEmpty(tenantId)) {
            return customSql;
        }
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append(initSql)
                .append(managementService.getTableName(HistoricProcessInstance.class)).append(" HPI ")
                .append(" LEFT JOIN ")
                .append(managementService.getTableName(ProcessDefinitionEntity.class)).append(" DEF ")
                .append(" ON HPI.PROC_DEF_ID_ = DEF.ID_  ")
                .append(" WHERE HPI.TENANT_ID_ = '").append(tenantId).append("'");

        if (StringUtils.isNotEmpty(processDefinitionKey)) {
            stringBuilder.append(" and HPI.PROC_DEF_KEY_ = '").append(processDefinitionKey).append("'");
        }

        if (StringUtils.isNotEmpty(processInstanceBusinessKey)) {
            stringBuilder.append(" and HPI.BUSINESS_KEY_ = '").append(processInstanceBusinessKey).append("'");
        }

        if (CollectionUtils.isNotEmpty(processInstanceBusinessKeyIn)) {
            stringBuilder.append(" and HPI.BUSINESS_KEY_ in ( '").append(String.join("','", processInstanceBusinessKeyIn)).append("' )");
        }

        if (StringUtils.isNotEmpty(processInstanceId)) {
            stringBuilder.append(" and HPI.PROC_INST_ID_ = '").append(processInstanceId).append("'");
        }
        if (CollectionUtils.isNotEmpty(stateList)) {
            stringBuilder.append(" and HPI.STATE_ in ( '").append(String.join("','", stateList).toUpperCase()).append("' )");
        }

        if (StringUtils.isNotEmpty(orderBy)) {
            stringBuilder.append(" ORDER BY ").append(orderBy.toUpperCase());
        } else {
            stringBuilder.append(" ORDER BY HPI.START_TIME_ DESC");
        }

        return stringBuilder.toString();
    }