activiti数据库表结构全貌解析 -

hjwromantic

浏览: 27661 次
性别:

最近访客更多访客>>

youc

扁舟垂钓

余敏君

zwjob

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (35)

社区版块

存档分类

activiti数据库表结构全貌解析

下面本人介绍一些activiti这款开源流程设计引擎的数据库表结构，首先阐述：我们刚开始接触或者使用一个新的东西（技术）时我们首先多问一下自己几个为什么？为什么activiti在工作流程领域这么流行呢？仅仅是因为开源么？实现如此强大的流程引擎，activiti底层设计是如何进行的？activiti中依赖哪些技术等？这些可能应该是那些刚接触这个开源流程引擎产品的人应该有的疑问。我们在用开源产品的都是其实应该多问自己为什么？这样才能有所进步，不是么？兴许你一时兴起，“起笔”就把一款属于你自己的开源作品给做出来了！

了解一个开源作品，它的底层很重要。在使用它之前，你是否尝试过了解它的底层。那么这些尝试是否对你有必要呢？个人解决有必要，首先这个东西确实在你看来是个有用的东西，你对它感兴趣。兴许将来你在应用它的时候可能会发现它的BUG，其实大牛写的东西也未必是完美的，兴许你在使用时候就发现其中不满意的东西，那么你就可以向开元社区提交的你的BUG!就比如说我们在了解activiti的底层数据结构之后，在我们使用activiti的时候发现一些数据查询过程中出现性能瓶颈时，我们可以尝试分析activiti的数据查询规则，activiti的数据访问层依赖于mybatis,那么我可以分析打包在jar包里的关于mybatis的sql配置部分，看看那些所谓大牛们写的sql是否存在问题。当你发现问题时，你可以对它进行修改，然后重新打包。从而满足自己在项目有中的需要。这些都是一些关于进阶了解一个开源作品的方式。在这样过程中你会发现你在某方面会有所进步。以上内容抛砖引玉，希望对你有所帮助！

好吧，请允许我废话了这么久，下面开始解析activiti的数据库底层的模型截图：

以上就activiti底层数据库23张表结构，个人觉得了解底层数据库模型是有必要的，让我们直观的了解一个开源作品的底层设计结构，对日后大伙使用的时候能有很大的帮助，特别是activiti的高级应用。大致看一下这些模型你们就知道activiti的23张表直接约束关系了！那么大家在使用activiti提供的servcies API查询activiti流程控制数据时就更清晰一些了！比如如下说明：

1）activiti的历史任务是单独的表来储存，表之间没有任何外间关联，从以上模型就可以看出

1、ACT_HI_ACTINST 流程活动历史记录信息
2、ACT_HI_ATTACHMENT 
3、ACT_HI_COMMENT 流程评论信息
4、ACT_HI_DETAIL 流程明细信息
5、ACT_HI_IDENTITYLINK 流程身份关系信息
6、ACT_HI_PROCINST 流程历史信息
7、ACT_HI_TASKINST 任务历史信息
8、ACT_HI_VARINST 历史流程中的参数

2）historyService可查询历史数据表（可查询以上这些表，与流程历史相关数据的查询都可以通过<span style="font-family: Arial, Helvetica, sans-serif;">historyService来查询</span>）
1、historyService.createHistoricActivityInstanceQuery(); //查询ACT_HI_ACTINST表
2、historyService.createHistoricDetailQuery(); //查询ACT_HI_DETAIL表
3、historyService.createHistoricProcessInstanceQuery(); //查询ACT_HI_PROCINST表
4、historyService.createHistoricTaskInstanceQuery(); //查询ACT_HI_TASKINST表
5、historyService.createHistoricVariableInstanceQuery(); //查询ACT_HI_VARINST表

activiti其中数据的查询和判断都是类似的！在这里就不过多介绍了。

下面是actviti中mybatis的映射配置：

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
  <settings>
    <setting name="lazyLoadingEnabled" value="false" />
  </settings>
  <typeAliases>
    <typeAlias type="org.activiti.engine.impl.persistence.ByteArrayRefTypeHandler" alias="ByteArrayRefTypeHandler"/>
  </typeAliases>
  <typeHandlers>
    <typeHandler handler="ByteArrayRefTypeHandler" 
                 javaType="org.activiti.engine.impl.persistence.entity.ByteArrayRef"
                 jdbcType="VARCHAR"/>
  </typeHandlers>
  <mappers>
    <mapper resource="org/activiti/db/mapping/entity/Attachment.xml" />
    <mapper resource="org/activiti/db/mapping/entity/ByteArray.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Comment.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Deployment.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Execution.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Group.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricActivityInstance.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricDetail.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricProcessInstance.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricVariableInstance.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricTaskInstance.xml" />
    <mapper resource="org/activiti/db/mapping/entity/HistoricIdentityLink.xml" />
    <mapper resource="org/activiti/db/mapping/entity/IdentityInfo.xml" />
    <mapper resource="org/activiti/db/mapping/entity/IdentityLink.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Job.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Membership.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Model.xml" />
    <mapper resource="org/activiti/db/mapping/entity/ProcessDefinition.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Property.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Resource.xml" />
    <mapper resource="org/activiti/db/mapping/entity/TableData.xml" />
    <mapper resource="org/activiti/db/mapping/entity/Task.xml" />
    <mapper resource="org/activiti/db/mapping/entity/User.xml" />
    <mapper resource="org/activiti/db/mapping/entity/VariableInstance.xml" />
    <mapper resource="org/activiti/db/mapping/entity/EventSubscription.xml" />
  </mappers>
</configuration>

下面是就那其中一个映射配置来说明，如HistoricProcessInstance.xml：

<?xml version="1.0" encoding="UTF-8" ?>

<!--
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~       http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
  
  <!-- HISTORIC TASK INSTANCE INSERT -->
  
  <insert id="insertHistoricTaskInstance" parameterType="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
      insert into ${prefix}ACT_HI_TASKINST (
        ID_,
        PROC_DEF_ID_,
        PROC_INST_ID_,
        EXECUTION_ID_,
        NAME_,
        PARENT_TASK_ID_,
        DESCRIPTION_,
        OWNER_,
        ASSIGNEE_,
        START_TIME_,
        CLAIM_TIME_,
        END_TIME_,
        DURATION_,
        DELETE_REASON_,
        TASK_DEF_KEY_,
        FORM_KEY_,
        PRIORITY_,
        DUE_DATE_,
        CATEGORY_,
        TENANT_ID_
      ) values (
        #{id ,jdbcType=VARCHAR},
        #{processDefinitionId, jdbcType=VARCHAR},
        #{processInstanceId, jdbcType=VARCHAR},
        #{executionId, jdbcType=VARCHAR},
        #{name ,jdbcType=VARCHAR},
        #{parentTaskId ,jdbcType=VARCHAR},
        #{description ,jdbcType=VARCHAR},
        #{owner ,jdbcType=VARCHAR},
        #{assignee ,jdbcType=VARCHAR},
        #{startTime, jdbcType=TIMESTAMP},
        #{claimTime, jdbcType=TIMESTAMP},
        #{endTime, jdbcType=TIMESTAMP},
        #{durationInMillis ,jdbcType=BIGINT},
        #{deleteReason ,jdbcType=VARCHAR},
        #{taskDefinitionKey ,jdbcType=VARCHAR},
        #{formKey ,jdbcType=VARCHAR},
        #{priority, jdbcType=INTEGER},
        #{dueDate, jdbcType=TIMESTAMP},
        #{category, jdbcType=VARCHAR},
        #{tenantId, jdbcType=VARCHAR}
      )
  </insert>

  <!-- HISTORIC TASK INSTANCE UPDATE -->
  
  <update id="updateHistoricTaskInstance" parameterType="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
    update ${prefix}ACT_HI_TASKINST set
      EXECUTION_ID_ = #{executionId, jdbcType=VARCHAR},
      NAME_ = #{name, jdbcType=VARCHAR},
      PARENT_TASK_ID_ = #{parentTaskId, jdbcType=VARCHAR},
      DESCRIPTION_ = #{description, jdbcType=VARCHAR},
      OWNER_ = #{owner, jdbcType=VARCHAR},
      ASSIGNEE_ = #{assignee, jdbcType=VARCHAR},
      CLAIM_TIME_ = #{claimTime, jdbcType=TIMESTAMP},
      END_TIME_ = #{endTime, jdbcType=TIMESTAMP},
      DURATION_ = #{durationInMillis ,jdbcType=BIGINT},
      DELETE_REASON_ = #{deleteReason ,jdbcType=VARCHAR},
      TASK_DEF_KEY_ = #{taskDefinitionKey ,jdbcType=VARCHAR},
      FORM_KEY_ = #{formKey ,jdbcType=VARCHAR},
      PRIORITY_ = #{priority, jdbcType=INTEGER},
      DUE_DATE_ = #{dueDate, jdbcType=TIMESTAMP},
      CATEGORY_ = #{category, jdbcType=VARCHAR}
    where ID_ = #{id}
  </update>

  <!-- HISTORIC TASK INSTANCE DELETE -->
  
  <delete id="deleteHistoricTaskInstance" parameterType="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
    delete from ${prefix}ACT_HI_TASKINST where ID_ = #{id}
  </delete>

  <!-- HISTORIC TASK INSTANCE RESULT MAP -->

  <resultMap id="historicTaskInstanceResultMap" type="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
    <id property="id" column="ID_" jdbcType="VARCHAR" />
    <result property="processDefinitionId" column="PROC_DEF_ID_" jdbcType="VARCHAR" />
    <result property="processInstanceId" column="PROC_INST_ID_" jdbcType="VARCHAR" />
    <result property="executionId" column="EXECUTION_ID_" jdbcType="VARCHAR" />
    <result property="name" column="NAME_" jdbcType="VARCHAR" />
    <result property="parentTaskId" column="PARENT_TASK_ID_" jdbcType="VARCHAR" />
    <result property="description" column="DESCRIPTION_" jdbcType="VARCHAR" />
    <result property="owner" column="OWNER_" jdbcType="VARCHAR" />
    <result property="assignee" column="ASSIGNEE_" jdbcType="VARCHAR" />
    <result property="startTime" column="START_TIME_" jdbcType="TIMESTAMP" />
    <result property="claimTime" column="CLAIM_TIME_" jdbcType="TIMESTAMP" />
    <result property="endTime" column="END_TIME_" jdbcType="TIMESTAMP" />
    <result property="durationInMillis" column="DURATION_" jdbcType="BIGINT" />
    <result property="deleteReason" column="DELETE_REASON_" jdbcType="VARCHAR" />
    <result property="taskDefinitionKey" column="TASK_DEF_KEY_" jdbcType="VARCHAR" />
    <result property="formKey" column="FORM_KEY_" jdbcType="VARCHAR" />
    <result property="priority" column="PRIORITY_" jdbcType="INTEGER" />
    <result property="dueDate" column="DUE_DATE_" jdbcType="TIMESTAMP" />
    <result property="category" column="CATEGORY_" jdbcType="VARCHAR" />
    <result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
  </resultMap>
  
  <resultMap id="historicTaskInstanceAndVariablesResultMap" type="org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity">
    <id property="id" column="ID_" jdbcType="VARCHAR" />
    <result property="processDefinitionId" column="PROC_DEF_ID_" jdbcType="VARCHAR" />
    <result property="processInstanceId" column="PROC_INST_ID_" jdbcType="VARCHAR" />
    <result property="executionId" column="EXECUTION_ID_" jdbcType="VARCHAR" />
    <result property="name" column="NAME_" jdbcType="VARCHAR" />
    <result property="parentTaskId" column="PARENT_TASK_ID_" jdbcType="VARCHAR" />
    <result property="description" column="DESCRIPTION_" jdbcType="VARCHAR" />
    <result property="owner" column="OWNER_" jdbcType="VARCHAR" />
    <result property="assignee" column="ASSIGNEE_" jdbcType="VARCHAR" />
    <result property="startTime" column="START_TIME_" jdbcType="TIMESTAMP" />
    <result property="claimTime" column="CLAIM_TIME_" jdbcType="TIMESTAMP" />
    <result property="endTime" column="END_TIME_" jdbcType="TIMESTAMP" />
    <result property="durationInMillis" column="DURATION_" jdbcType="BIGINT" />
    <result property="deleteReason" column="DELETE_REASON_" jdbcType="VARCHAR" />
    <result property="taskDefinitionKey" column="TASK_DEF_KEY_" jdbcType="VARCHAR" />
    <result property="formKey" column="FORM_KEY_" jdbcType="VARCHAR" />
    <result property="priority" column="PRIORITY_" jdbcType="INTEGER" />
    <result property="dueDate" column="DUE_DATE_" jdbcType="TIMESTAMP" />
    <result property="category" column="CATEGORY_" jdbcType="VARCHAR" />
    <result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
    <collection property="queryVariables" column="TASK_ID_" javaType="ArrayList" ofType="org.activiti.engine.impl.persistence.entity.HistoricVariableInstanceEntity">
      <id property="id" column="VAR_ID_"/>
      <result property="name" column="VAR_NAME_" javaType="String" jdbcType="VARCHAR" />
      <result property="variableType" column="VAR_TYPE_" javaType="org.activiti.engine.impl.variable.VariableType" jdbcType="VARCHAR" />
      <result property="revision" column="VAR_REV_" jdbcType="INTEGER" />
      <result property="processInstanceId" column="VAR_PROC_INST_ID_" jdbcType="VARCHAR" />
      <result property="executionId" column="VAR_EXECUTION_ID_" jdbcType="VARCHAR" />
      <result property="taskId" column="VAR_TASK_ID_" jdbcType="VARCHAR" />
      <result property="byteArrayRef" column="VAR_BYTEARRAY_ID_" typeHandler="ByteArrayRefTypeHandler"/>
      <result property="doubleValue" column="VAR_DOUBLE_" jdbcType="DOUBLE" />
      <result property="textValue" column="VAR_TEXT_" jdbcType="VARCHAR" />
      <result property="textValue2" column="VAR_TEXT2_" jdbcType="VARCHAR" />
      <result property="longValue" column="VAR_LONG_" jdbcType="BIGINT" />
    </collection>
  </resultMap>

  <!-- HISTORIC TASK INSTANCE SELECT -->
  
  <select id="selectHistoricTaskInstance" resultMap="historicTaskInstanceResultMap">
    select * from ${prefix}ACT_HI_TASKINST where ID_ = #{historicTaskInstanceId}
  </select>
  
  <select id="selectHistoricTaskInstanceIdsByProcessInstanceId" resultType="string" parameterType="org.activiti.engine.impl.db.ListQueryParameterObject" >
    select ID_ 
    from ${prefix}ACT_HI_TASKINST 
    where PROC_INST_ID_ = #{parameter}
  </select>

  <select id="selectHistoricTaskInstancesByQueryCriteria" parameterType="org.activiti.engine.impl.HistoricTaskInstanceQueryImpl" resultMap="historicTaskInstanceResultMap">
  	${limitBefore}
    select distinct RES.* ${limitBetween}
    <include refid="selectHistoricTaskInstancesByQueryCriteriaSql"/>
    ${orderBy}
    ${limitAfter}
  </select>
  
  <select id="selectHistoricTaskInstanceCountByQueryCriteria" parameterType="org.activiti.engine.impl.HistoricTaskInstanceQueryImpl" resultType="long">
    select count(RES.ID_)
    <include refid="selectHistoricTaskInstancesByQueryCriteriaSql"/>
  </select>
  
  <sql id="selectHistoricTaskInstancesByQueryCriteriaSql">
    from ${prefix}ACT_HI_TASKINST RES
    <include refid="commonSelectHistoricTaskInstancesByQueryCriteriaSql"/>
  </sql>
  
  <select id="selectHistoricTaskInstancesWithVariablesByQueryCriteria" parameterType="org.activiti.engine.impl.HistoricTaskInstanceQueryImpl" resultMap="historicTaskInstanceAndVariablesResultMap">
    ${limitBefore}
    select distinct RES.*,
    VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,
    VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,
    VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_, 
    VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_
    ${limitBetween}
    <include refid="selectHistoricTaskInstancesWithVariablesByQueryCriteriaSql"/> 
    ${orderBy}
    ${limitAfter}
  </select>
  
  <select id="selectHistoricTaskInstancesWithVariablesByQueryCriteria_mssql_or_db2" parameterType="org.activiti.engine.impl.HistoricTaskInstanceQueryImpl" resultMap="historicTaskInstanceAndVariablesResultMap">
    ${limitBefore}
    select distinct TEMPRES_ID_ as ID_,
    TEMPRES_PROC_DEF_ID_ as PROC_DEF_ID_, TEMPRES_PROC_INST_ID_ as PROC_INST_ID_, TEMPRES_EXECUTION_ID_ as EXECUTION_ID_,
    TEMPRES_NAME_ as NAME_, TEMPRES_PARENT_TASK_ID_ as PARENT_TASK_ID_,
    TEMPRES_DESCRIPTION_ as DESCRIPTION_, TEMPRES_OWNER_ as OWNER_, TEMPRES_ASSIGNEE_ as ASSIGNEE_,
    TEMPRES_START_TIME_ as START_TIME_, TEMPRES_CLAIM_TIME_ as CLAIM_TIME_, TEMPRES_END_TIME_ as END_TIME_,
    TEMPRES_DURATION_ as DURATION_, TEMPRES_TASK_DEF_KEY_ as TASK_DEF_KEY_, TEMPRES_FORM_KEY_ as FORM_KEY_,
    TEMPRES_PRIORITY_ as PRIORITY_, TEMPRES_DUE_DATE_ as DUE_DATE_,
    TEMPRES_DELETE_REASON_ as DELETE_REASON_,
    TEMPVAR_ID_ as VAR_ID_, TEMPVAR_NAME_ as VAR_NAME_, TEMPVAR_TYPE_ as VAR_TYPE_, TEMPVAR_REV_ as VAR_REV_,
    TEMPVAR_PROC_INST_ID_ as VAR_PROC_INST_ID_, TEMPVAR_EXECUTION_ID_ as VAR_EXECUTION_ID_, TEMPVAR_TASK_ID_ as VAR_TASK_ID_,
    TEMPVAR_BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, TEMPVAR_DOUBLE_ as VAR_DOUBLE_, 
    TEMPVAR_TEXT_ as VAR_TEXT_, TEMPVAR_LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, TEMPVAR_TEXT2_ as VAR_TEXT2_, TEMPVAR_LONG_ as VAR_LONG_
    ${limitOuterJoinBetween}
    RES.ID_ as TEMPRES_ID_, RES.PROC_DEF_ID_ as TEMPRES_PROC_DEF_ID_, RES.PROC_INST_ID_ as TEMPRES_PROC_INST_ID_, 
    RES.EXECUTION_ID_ as TEMPRES_EXECUTION_ID_, RES.NAME_ as TEMPRES_NAME_ , RES.PARENT_TASK_ID_ as TEMPRES_PARENT_TASK_ID_,
    RES.DESCRIPTION_ as TEMPRES_DESCRIPTION_, RES.OWNER_ as TEMPRES_OWNER_, RES.ASSIGNEE_ as TEMPRES_ASSIGNEE_,
    RES.START_TIME_ as TEMPRES_START_TIME_, RES.END_TIME_ as TEMPRES_END_TIME_, RES.CLAIM_TIME_ as TEMPRES_CLAIM_TIME_,
    RES.DURATION_ as TEMPRES_DURATION_, RES.TASK_DEF_KEY_ as TEMPRES_TASK_DEF_KEY_,
    RES.FORM_KEY_ as TEMPRES_FORM_KEY_, RES.PRIORITY_ as TEMPRES_PRIORITY_,
    RES.DUE_DATE_ as TEMPRES_DUE_DATE_, RES.DELETE_REASON_ as TEMPRES_DELETE_REASON_,
    VAR.ID_ as TEMPVAR_ID_, VAR.NAME_ as TEMPVAR_NAME_, VAR.VAR_TYPE_ as TEMPVAR_TYPE_, VAR.REV_ as TEMPVAR_REV_,
    VAR.PROC_INST_ID_ as TEMPVAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as TEMPVAR_EXECUTION_ID_, VAR.TASK_ID_ as TEMPVAR_TASK_ID_,
    VAR.BYTEARRAY_ID_ as TEMPVAR_BYTEARRAY_ID_, VAR.DOUBLE_ as TEMPVAR_DOUBLE_, 
    VAR.TEXT_ as TEMPVAR_TEXT_, VAR.TEXT2_ as TEMPVAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as TEMPVAR_LAST_UPDATED_TIME_, VAR.LONG_ as TEMPVAR_LONG_
    <include refid="selectHistoricTaskInstancesWithVariablesByQueryCriteriaSql"/> 
    ${orderBy}
    ${limitAfter}
  </select>
  
  <sql id="selectHistoricTaskInstancesWithVariablesByQueryCriteriaSql">  
    from ${prefix}ACT_HI_TASKINST RES
    <choose>
      <when test="includeTaskLocalVariables && includeProcessVariables">
        left outer join ${prefix}ACT_HI_VARINST VAR ON RES.ID_ = VAR.TASK_ID_ or RES.PROC_INST_ID_ = VAR.EXECUTION_ID_
      </when>
      <otherwise>
        <if test="includeTaskLocalVariables">
          left outer join ${prefix}ACT_HI_VARINST VAR ON RES.ID_ = VAR.TASK_ID_
        </if>
        <if test="includeProcessVariables">
          left outer join ${prefix}ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null
        </if>
      </otherwise>
    </choose>
    <include refid="commonSelectHistoricTaskInstancesByQueryCriteriaSql"/>
  </sql>
  
  <sql id="commonSelectHistoricTaskInstancesByQueryCriteriaSql">
    <if test="candidateUser != null || candidateGroups != null">
      inner join ${prefix}ACT_HI_IDENTITYLINK HI on HI.TASK_ID_ = RES.ID_
    </if>
    <if test="processFinished || processUnfinished || processInstanceBusinessKey != null || processInstanceBusinessKeyLike != null">
      inner join ${prefix}ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_
    </if>
    <if test="processDefinitionKey != null || processDefinitionKeyLike != null || processDefinitionName != null || processDefinitionNameLike != null">
      inner join ${prefix}ACT_RE_PROCDEF D on RES.PROC_DEF_ID_ = D.ID_
    </if>
    <foreach collection="queryVariableValues" index="index" item="var">
      <choose>
        <when test="var.local">
          inner join ${prefix}ACT_HI_VARINST A${index} on RES.ID_ = A${index}.TASK_ID_ 
        </when>
        <otherwise>
          inner join ${prefix}ACT_HI_VARINST A${index} on RES.PROC_INST_ID_ = A${index}.PROC_INST_ID_ 
        </otherwise>
      </choose>       
    </foreach>
    <where>
      <if test="taskId != null">
        RES.ID_ = #{taskId}
      </if>
      <if test="processDefinitionId != null">
        and RES.PROC_DEF_ID_ = #{processDefinitionId}
      </if>
      <if test="processDefinitionKey != null">
        and D.KEY_ = #{processDefinitionKey}
      </if>
      <if test="processDefinitionKeyLike != null">
        and D.KEY_ like #{processDefinitionKeyLike}
      </if>
      <if test="processDefinitionName != null">
        and D.NAME_ = #{processDefinitionName}
      </if>
      <if test="processDefinitionNameLike != null">
        and D.NAME_ like #{processDefinitionNameLike}
      </if>
      <if test="processInstanceId != null">
        and RES.PROC_INST_ID_ = #{processInstanceId}
      </if>
      <if test="processInstanceBusinessKey != null">
        and HPI.BUSINESS_KEY_ = #{processInstanceBusinessKey}
      </if>
      <if test="processInstanceBusinessKeyLike != null">
        and HPI.BUSINESS_KEY_ like #{processInstanceBusinessKeyLike}
      </if>
      <if test="taskDefinitionKey != null">
        and RES.TASK_DEF_KEY_ = #{taskDefinitionKey}
      </if>
      <if test="taskDefinitionKeyLike != null">
        and RES.TASK_DEF_KEY_ like #{taskDefinitionKeyLike}
      </if>
      <if test="executionId != null">
        and RES.EXECUTION_ID_ = #{executionId}
      </if>
      <if test="taskName != null">
        and RES.NAME_ = #{taskName}
      </if>
      <if test="taskNameLike != null">
        and RES.NAME_ like #{taskNameLike}
      </if>
      <if test="taskParentTaskId != null">
        and RES.PARENT_TASK_ID_ = #{taskParentTaskId}
      </if>
      <if test="taskDescription != null">
        and RES.DESCRIPTION_ = #{taskDescription}
      </if>
      <if test="taskDescriptionLike != null">
        and RES.DESCRIPTION_ like #{taskDescriptionLike}
      </if>
      <if test="taskDeleteReason != null">
        and RES.DELETE_REASON_ = #{taskDeleteReason}
      </if>
      <if test="taskDeleteReasonLike != null">
        and RES.DELETE_REASON_ like #{taskDeleteReasonLike}
      </if>
      <if test="taskOwner != null">
        and RES.OWNER_ = #{taskOwner}
      </if>
      <if test="taskOwnerLike != null">
        and RES.OWNER_ like #{taskOwnerLike}
      </if>
      <if test="taskAssignee != null">
        and RES.ASSIGNEE_ = #{taskAssignee}
      </if>
      <if test="taskAssigneeLike != null">
        and RES.ASSIGNEE_ like #{taskAssigneeLike}
      </if>
      <if test="taskPriority != null">
        and RES.PRIORITY_ = #{taskPriority}
      </if>
      <if test="taskMinPriority != null">
        and RES.PRIORITY_ >= #{taskMinPriority}
      </if>
      <if test="taskMaxPriority != null">
        and RES.PRIORITY_ <= #{taskMaxPriority}
      </if>
      <if test="unfinished">
        and RES.END_TIME_ is null
      </if>
      <if test="finished">
        and RES.END_TIME_ is not null
      </if>
      <if test="processFinished">
        and HPI.END_TIME_ is not null
      </if>
      <if test="processUnfinished">
        and HPI.END_TIME_ is null
      </if>
      <if test="dueDate != null">
        and RES.DUE_DATE_ = #{dueDate}
      </if>
      <if test="dueBefore != null">
        and RES.DUE_DATE_ < #{dueBefore}
      </if>
      <if test="dueAfter != null">
        and RES.DUE_DATE_ > #{dueAfter}
      </if>
      <if test="withoutDueDate">
        and RES.DUE_DATE_ is null
      </if>
      <if test="creationDate != null">
        and RES.START_TIME_ = #{creationDate}
      </if>
      <if test="creationBeforeDate != null">
        and RES.START_TIME_ < #{creationBeforeDate}
      </if>
      <if test="creationAfterDate != null">
        and RES.START_TIME_ > #{creationAfterDate}
      </if>
      <if test="completedDate != null">
        and RES.END_TIME_ = #{completedDate}
      </if>
      <if test="completedBeforeDate != null">
        and RES.END_TIME_ < #{completedBeforeDate}
      </if>
      <if test="completedAfterDate != null">
        and RES.END_TIME_ > #{completedAfterDate}
      </if>
      <if test="category != null">
        and RES.CATEGORY_ = #{category}
      </if>
      <if test="tenantId != null">
        and RES.TENANT_ID_ = #{tenantId}
      </if>
      <if test="tenantIdLike != null">
        and RES.TENANT_ID_ like #{tenantIdLike}
      </if>
      <if test="withoutTenantId">
        and (RES.TENANT_ID_ = '' or RES.TENANT_ID_ is null)
      </if>
      <if test="candidateUser != null || candidateGroups != null">
        and RES.ASSIGNEE_ is null
        and HI.TYPE_ = 'candidate'
        and
        (
          <if test="candidateUser != null">
            HI.USER_ID_ = #{candidateUser}
          </if>
          <if test="candidateUser != null && candidateGroups != null && candidateGroups.size() > 0">
            or
          </if>
          <if test="candidateGroups != null && candidateGroups.size() > 0">
            HI.GROUP_ID_ IN
            <foreach item="group" index="index" collection="candidateGroups"
                     open="(" separator="," close=")">
              #{group}
            </foreach>
          </if>
        )
      </if>
      <if test="involvedUser != null">
        and (
          exists(select LINK.USER_ID_ from ${prefix}ACT_HI_IDENTITYLINK LINK where USER_ID_ = #{involvedUser} and LINK.TASK_ID_ = RES.ID_)
          or RES.ASSIGNEE_ = #{involvedUser}
          or RES.OWNER_ = #{involvedUser}
          )
      </if>
      <foreach item="queryVar" collection="queryVariableValues" index="index">
        <if test="!queryVar.local">
          <!-- When process instance variable is queried for, taskId should be null -->
          and A${index}.TASK_ID_ is null
        </if>
        <if test="queryVar.name != null">
          <!-- Match-all variable-names when name is null -->
          and A${index}.NAME_= #{queryVar.name}
        </if>
        <if test="!queryVar.type.equals('null')">
          and A${index}.VAR_TYPE_ = #{queryVar.type}
        </if>
        <!-- Variable value -->
        <if test="queryVar.textValue != null && queryVar.longValue == null && queryVar.doubleValue == null">
          <choose>
            <when test="queryVar.operator.equals('EQUALS_IGNORE_CASE') || queryVar.operator.equals('NOT_EQUALS_IGNORE_CASE')">
              and lower(A${index}.TEXT_)
            </when>
            <otherwise>
              and A${index}.TEXT_
            </otherwise>
          </choose> 
          <choose>
              <when test="queryVar.operator.equals('LIKE')">LIKE</when>
              <otherwise><include refid="executionVariableOperator" /></otherwise>
          </choose>          
          #{queryVar.textValue}
        </if>
        <if test="queryVar.textValue2 != null">
          and A${index}.TEXT2_ 
          <choose>
            <when test="queryVar.operator.equals('LIKE')">LIKE</when>
            <otherwise><include refid="executionVariableOperator" /></otherwise>
          </choose>          
          #{queryVar.textValue2}
        </if>
        <if test="queryVar.longValue != null">
          and A${index}.LONG_
          <include refid="executionVariableOperator" />
          #{queryVar.longValue}
        </if>
        <if test="queryVar.doubleValue != null">
          and A${index}.DOUBLE_ 
          <include refid="executionVariableOperator" />
          #{queryVar.doubleValue}
        </if>
        <!-- Null variable type -->
        <if test="queryVar.textValue == null && queryVar.textValue2 == null && queryVar.longValue == null && queryVar.doubleValue == null">
          <choose>
            <when test="queryVar.operator.equals('NOT_EQUALS')">
              and (A${index}.TEXT_ is not null or A${index}.TEXT2_ is not null or A${index}.LONG_ is not null or A${index}.DOUBLE_ is not null or A${index}.BYTEARRAY_ID_ is not null)
            </when>
            <otherwise>
              and A${index}.TEXT_ is null and A${index}.TEXT2_ is null and A${index}.LONG_ is null and A${index}.DOUBLE_ is null and A${index}.BYTEARRAY_ID_ is null
            </otherwise>
          </choose>          
        </if>
      </foreach>
    </where>
  </sql>
  
  <sql id="executionVariableOperator">
    <choose>
      <when test="queryVar.operator.equals('EQUALS')">=</when>
      <when test="queryVar.operator.equals('EQUALS_IGNORE_CASE')">=</when>
      <when test="queryVar.operator.equals('NOT_EQUALS')"><></when>
      <when test="queryVar.operator.equals('NOT_EQUALS_IGNORE_CASE')"><></when>
      <when test="queryVar.operator.equals('GREATER_THAN')">></when>
      <when test="queryVar.operator.equals('GREATER_THAN_OR_EQUAL')">>=</when>
      <when test="queryVar.operator.equals('LESS_THAN')"><</when>
      <when test="queryVar.operator.equals('LESS_THAN_OR_EQUAL')"><=</when>
   </choose>
  </sql>

  <select id="selectHistoricTaskInstanceByNativeQuery" parameterType="java.util.Map" resultMap="historicTaskInstanceResultMap">
    <if test="resultType == 'LIST_PAGE'">
      ${limitBefore}
    </if>
    ${sql}
    <if test="resultType == 'LIST_PAGE'">
      ${limitAfter}
    </if>
  </select>
  
  <select id="selectHistoricTaskInstanceByNativeQuery_mssql_or_db2" parameterType="java.util.Map" resultMap="historicTaskInstanceResultMap">
    <if test="resultType == 'LIST_PAGE'">
      ${limitBeforeNativeQuery}
    </if>
    ${sql} 
    <if test="resultType == 'LIST_PAGE'">
      ${limitAfter}
    </if>
  </select>

  <select id="selectHistoricTaskInstanceCountByNativeQuery" parameterType="java.util.Map" resultType="long">
    ${sql}
  </select>
</mapper>

看了这些，如果你觉得你对activiti的底层有足够的了解，你可以完全重写或者扩展actviti中的service API。activiti就是通过这些sql映射配置来完成数据查询，当然如果你在使用过程中如果发现其中一些sql查询效率很低的话，大伙可以由针对的进行修改达到自己的要求。现在网络上一些关于activiti也有类似的问题出现。大伙使用过程相信也会遇到。在这里就这样抛砖引玉，希望对初学的你有所帮助.......

分享到：