Activiti几个复杂查询需求的实现

张童瑶
1,325 阅读6分钟

​ 在计算机尚未普及时，许多工作流程采用手工传递纸张表单的方式，一级一级审批签字，工作效率非常低下，对于数据统计以及生成报表的功能，需要经过大量的手工操作才能实现。随着电脑的普及，这些工作的参与者只需要在电脑的系统中填入工作内容，系统就会按照定义好的流程自动执行，各级审批者可以得到工作的信息并作出相应的审批和管理操作，数据统计和报表的生成均由系统代为完成，这样大大提高了工作效率，在这种背景下，各种的工作流应用以及中间件应运而生。

​ Activiti的好处是简化了数据库的存储方式，对于一个步骤繁琐的工作流程，使用Activiti之后程序员可以不用关注工作流中每一步所对应的流程变量的存储，不用单独开很多表结构去存储工作流每一步所对应的流程变量。缺点是因为所有的历史流程变量数据全部存在一张表中，一旦业务需求发生变更则需要做一些表连接去查询数据。实际工程项目中使用较多的查询包括用户待办任务查询、已办任务查询、所有经手任务查询、流程信息跟踪查询等等。现将部分复杂查询的实现过程记录如下：

1、待办任务查询

业务场景：

1.用户A可以受理一个案件（即启动工作流）

2.在启动工作流时用户A需要填写一张表，其数据会保存进历史流程变量表里，此时用户A有一个***待办任务T***

3.用户A点击“待办任务”菜单，获得用户A名下所有的待办任务列表以及相关联的流程变量

4.用户A可以办理***待办任务T***，页面需要回显***待办任务T***对应的表单，即用户A启动流程时填写过的表单

5.用户A检查自己填写过的字段，若发现有问题的字段可以修改该字段的内容，并点击保存结束该步骤

6.此时用户A的已办任务列表应该更新为最新的流程变量内容，工作流进行到下一步，由后面用户继续完成

用到的表

  • ACT_RU_TASK（运行时任务）
  • ACT_RE_PROCDEF（已部署的流程定义）
  • ACT_HI_VARINST（历史的流程运行中的变量信息）

思路：

（1）每一个流程都有唯一的标识符，保存在ACT_RE_PROCDEF表的ID_字段里，比如劳动监察执法流程的ID_即为KEY_case:1:630005，其中，key_case为事先定义好的流程标识符。利用这个ID可以查询出该流程正在运行的任务有哪些

（2）因为同一份流程变量在开始节点和工作流UserTask1节点保存了两份，并且后一份流程变量可能更新前面的流程变量，所以需要取到最新的流程变量，根据ACT_HI_VARINST表ID_字段增加的特点，取ID_大的那一份流程变量即可（考虑使用PARTITION BY）

（3）根据PROC_INST_ID_将任务节点与任务节点对应的流程变量关联起来，需要查询几个字段就需要做几次连接

以测试员工1为例，查询测试员工1的待办任务对应的SQL语句如下：

select
		f.*,
		p.NAME_ PNAME_,
		p.DGRM_RESOURCE_NAME_,
		case_id.TEXT_ CASE_ID_,
		case_type.TEXT_ CASE_TYPE_,
		tsr.TEXT_ TSR_,
		btsdw.TEXT_ BTSDW_,
		phone.TEXT_ PHONE_,
    	ssjd.TEXT_ SSJD_
from 
		ACT_RU_TASK f
		left join
		ACT_RE_PROCDEF p
		on f.PROC_DEF_ID_ = p.ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_tsrqk_anjianbianhao'
        ) case_id on f.PROC_INST_ID_ = case_id.PROC_INST_ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjclaspb_anjianlaiyuan'
        ) case_type on f.PROC_INST_ID_ = case_type.PROC_INST_ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_tsrqk_xingming'
        ) tsr on f.PROC_INST_ID_ = tsr.PROC_INST_ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_danweimingcheng'
        ) btsdw on f.PROC_INST_ID_ = btsdw.PROC_INST_ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_lianxidianhua'
        ) phone on f.PROC_INST_ID_ = phone.PROC_INST_ID_
		left join (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_suoshujiedao'
        ) ssjd on f.PROC_INST_ID_ = ssjd.PROC_INST_ID_
where 1=1
and f.ASSIGNEE_= '测试员工1'
group by f.PROC_INST_ID_
order by f.CREATE_TIME_ desc

其中，查询ID大的那部分流程变量的SQL语句还可以有如下写法

select result1.PROC_INST_ID_,result1.TEXT_ , max(result1.ID_) from
(SELECT PROC_INST_ID_,NAME_, ID_,
LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_) TEXT_
            FROM ACT_HI_VARINST v 
            WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_suoshujiedao' ) result1
 group by result1.PROC_INST_ID_

这种写法避免了使用复杂的RANGE BETWEEN，形式上更为简洁

2、已办任务查询

思路：与待办任务类似，不同之处在于查询的表由ACT_RU_TASK改为了ACT_HI_ACTINST（历史任务节点），在历史任务节点表中，有END_TIME_字段来标识该任务是否已被办理，若不为null再表示该任务为已办任务。同样的，需要查询某一任务节点相关联的流程变量，需要查询多少个流程变量就需要做多少次连接

以测试员工1为例，查询测试员工1的已办任务对应的SQL语句如下：

select
        f.*,
        p.NAME_ PNAME_,
        p.DEPLOYMENT_ID_,
        p.DGRM_RESOURCE_NAME_,
        case_id.TEXT_ CASE_ID_,
        case_type.TEXT_ CASE_TYPE_,
        ssjd.TEXT_ SSJD_
from
(
    select
         n.* 
    from ACT_HI_TASKINST n 
    where (
            n.ASSIGNEE_ = '测试员工1'
          )

) f
left join
ACT_RE_PROCDEF p
on f.PROC_DEF_ID_ = p.ID_
left join (
    SELECT PROC_INST_ID_, NAME_, 
    LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_, 
    LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
    FROM ACT_HI_VARINST v
    WHERE v.NAME_ = 'ldbzjctsdjb_tsrqk_anjianbianhao'
) case_id on f.PROC_INST_ID_ = case_id.PROC_INST_ID_
left join (
    SELECT PROC_INST_ID_, NAME_, 
    LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_, 
    LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
    FROM ACT_HI_VARINST v
    WHERE v.NAME_ = 'ldbzjclaspb_anjianlaiyuan'
) case_type on f.PROC_INST_ID_ = case_type.PROC_INST_ID_
left join (
    SELECT PROC_INST_ID_, NAME_, 
    LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_, 
    LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
    FROM ACT_HI_VARINST v
    WHERE v.NAME_ = 'ldbzjctsdjb_tsrqk_xingming'
) tsr on f.PROC_INST_ID_ = tsr.PROC_INST_ID_
left join (
    SELECT PROC_INST_ID_, NAME_, 
    LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_, 
    LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
    FROM ACT_HI_VARINST v
    WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_danweimingcheng'
) btsdw on f.PROC_INST_ID_ = btsdw.PROC_INST_ID_
left join (
    SELECT PROC_INST_ID_, NAME_, 
    LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_, 
    LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
    FROM ACT_HI_VARINST v
    WHERE v.NAME_ = 'ldbzjctsdjb_btsrqk_suoshujiedao'
) ssjd on f.PROC_INST_ID_ = ssjd.PROC_INST_ID_
where 1=1
and f.ASSIGNEE_= '测试员工1'
and f.END_TIME_ is not NULL
group by f.PROC_INST_ID_
order by f.END_TIME_ desc

3、案件文书管理查询

这一步骤实际上需要查询的是用户所对应的所有相关案件，包括待办任务、已办任务以及该用户申请提交的任务（不管办理人是不是该用户，只要是该用户提交的申请均应该查询出来），以完成案件文书的上传以及统一管理工作

思路：

（1）引入OA_handlecase以拿到由该用户提交的记录

（2）引入ACT_HI_ACTINST以拿到该用户办理过的记录

（3）引入ACT_RU_TASK以拿到该用户待办的记录

（4）将这些表中对应的任务节点与ACT_HI_VARINST做关联以拿到某一任务节点所对应的流程变量

最终代码如下：

SELECT c.CASE_ID, c.ldbzjctsdjb_tsrqk_anjianbianhao
       , p.PROC_INST_ID_
       , p.START_TIME_
       , p.R_ASSIGNEE_
       , p.H_ASSIGNEE_
       , c.USERNAME
       , c.ldbzjctsdjb_tsrqk_xingming
       , c.ldbzjctsdjb_tsrqk_dizhi
       , c.ldbzjctsdjb_tsrqk_lianxidianhua
       , c.ldbzjctsdjb_btsrqk_danweimingcheng
       , p.SSJD_
    FROM oa_handlecase c
    RIGHT JOIN (
        SELECT p.PROC_INST_ID_
               , p.START_TIME_
               , r.ASSIGNEE_ R_ASSIGNEE_
               , h.ASSIGNEE_ H_ASSIGNEE_
               , case_id.TEXT_ CASE_ID_
               , ssjd.TEXT_ SSJD_
        FROM act_hi_procinst p
        LEFT JOIN ACT_RU_TASK r 
        ON p.PROC_INST_ID_ = r.PROC_INST_ID_
        LEFT JOIN ACT_HI_TASKINST h 
        ON p.PROC_INST_ID_ = h.PROC_INST_ID_
        LEFT JOIN (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST case_id 
            WHERE case_id.NAME_ = 'ldbzjctsdjb_tsrqk_anjianbianhao'
        ) case_id ON p.PROC_INST_ID_ = case_id.PROC_INST_ID_
        LEFT JOIN (
            SELECT PROC_INST_ID_,NAME_,
            LAST_VALUE(TASK_ID_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TASK_ID_,
            LAST_VALUE(TEXT_) OVER (PARTITION BY PROC_INST_ID_ ORDER BY ID_ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TEXT_
            FROM ACT_HI_VARINST case_id 
            WHERE case_id.NAME_ = 'ldbzjctsdjb_btsrqk_suoshujiedao'
        ) ssjd ON p.PROC_INST_ID_ = ssjd.PROC_INST_ID_
        GROUP BY p.PROC_INST_ID_
    ) p ON c.ldbzjctsdjb_tsrqk_anjianbianhao = p.CASE_ID_
    WHERE 1=1
    and (
        p.R_ASSIGNEE_ = '测试员工1' OR 
        p.H_ASSIGNEE_ = '测试员工1' OR 
        c.USERNAME = '测试员工1'
    )
avatar
文章
阅读
粉丝