Using Custom XML Mapped Statements in Activiti

Why use MyBatis XML based Mapped Statements to execute custom SQL queries? One useful case is when SQL statements have to be separated from Java code (so that for example SQL statements can be modified without modifying Java code). Another useful case is when working with complicated statements, as mentioned in the MyBatis documentation

"The annotations are a lot cleaner for simple statements, however, Java Annotations are both limited and messier for more complicated statements. Therefore, if you have to do anything complicated, you're better off with XML mapped statements."

The following examples were already merged to Activiti code and will exist in upcoming 5.17.1 release. It is possible to use the XML based Mapped Statements feature and the following examples in the current 5.17.0 release (with a simple exception when extending AbstractQuery that I'll mention at the end of the post).

A custom select statement and resultMap

For a simple example, a custom SQL that selects partial data of a task and maps the results to a custom task class. The XML mapper file will look as follows:

 <mapper namespace="org.activiti.standalone.cfg.TaskMapper">  

  <resultMap id="customTaskResultMap" type="org.activiti.standalone.cfg.CustomTask">  
   <id property="id" column="ID_" jdbcType="VARCHAR"/>  
   <result property="name" column="NAME_" jdbcType="VARCHAR"/>  
   <result property="createTime" column="CREATE_TIME_" jdbcType="TIMESTAMP" />  
  </resultMap>  

  <select id="selectCustomTaskList" resultMap="customTaskResultMap">  
   select RES.ID_, RES.NAME_, RES.CREATE_TIME_ from ACT_RU_TASK RES  
  </select>  

 </mapper>  

Results will be mapped to CustomTask class.

public class CustomTask {  

  protected String id;  
  protected String name;  
  protected Date createTime;  

  public String getId() {  
   return id;  
  }  

  public String getName() {  
   return name;  
  }  

  public Date getCreateTime() {  
   return createTime;  
  }  

 }  

XML mapper files must be provided to the Process Engine configuration as follows:

...
<property name="customMybatisXMLMappers">  
  <set>  
   <value>org/activiti/standalone/cfg/custom-mappers/CustomTaskMapper.xml</value>  
  </set>  
 </property>  
...

The statement can be executed as follows:

List<CustomTask> tasks = managementService.executeCommand(new Command<List<CustomTask>>() {  
    @SuppressWarnings("unchecked")  
    @Override  
    public List<CustomTask> execute(CommandContext commandContext) {  
     return (List<CustomTask>) commandContext.getDbSqlSession().selectList("selectCustomTaskList");  
    }  
   });  

A custom AttachmentQuery

In this example a custom query that enables querying attachments based on id, name, type, userId, etc! will be used. Since Activiti uses XML Mapped Statements internally, it’s possible to make use of the underlying capabilities. Therefore, a class AttachmentQuery that extends org.activiti.engine.impl.AbstractQuery can be created as follows:

public class AttachmentQuery extends AbstractQuery<AttachmentQuery, Attachment> {  

  protected String attachmentId;  
  protected String attachmentName;  
  protected String attachmentType;  
  protected String userId;  

  public AttachmentQuery(ManagementService managementService) {  
   super(managementService);  
  }  

  public AttachmentQuery attachmentId(String attachmentId){  
   this.attachmentId = attachmentId;  
   return this;  
  }  

  public AttachmentQuery attachmentName(String attachmentName){  
   this.attachmentName = attachmentName;  
   return this;  
  }  

  public AttachmentQuery attachmentType(String attachmentType){  
   this.attachmentType = attachmentType;  
   return this;  
  }  

  public AttachmentQuery userId(String userId){  
   this.userId = userId;  
   return this;  
  }  

  @Override  
  public long executeCount(CommandContext commandContext) {  
   return (Long) commandContext.getDbSqlSession()  
           .selectOne("selectAttachmentCountByQueryCriteria", this);  
  }  

  @Override  
  public List<Attachment> executeList(CommandContext commandContext, Page page) {  
   return commandContext.getDbSqlSession()  
       .selectList("selectAttachmentByQueryCriteria", this);  
  }  

}

Note that when extending AbstractQuery extended classes should pass an instance of ManagementService to super constructor and methods executeCount and executeList need to be implemented to call the mapped statements. The XML file containing the mapped statements can look as follows:

<mapper namespace="org.activiti.standalone.cfg.AttachmentMapper">  

  <select id="selectAttachmentCountByQueryCriteria" parameterType="org.activiti.standalone.cfg.AttachmentQuery" resultType="long">  
   select count(distinct RES.ID_)  
   <include refid="selectAttachmentByQueryCriteriaSql"/>  
  </select>  

  <select id="selectAttachmentByQueryCriteria" parameterType="org.activiti.standalone.cfg.AttachmentQuery" resultMap="org.activiti.engine.impl.persistence.entity.AttachmentEntity.attachmentResultMap">  
   ${limitBefore}  
   select distinct RES.* ${limitBetween}  
   <include refid="selectAttachmentByQueryCriteriaSql"/>  
   ${orderBy}  
   ${limitAfter}  
  </select>  

  <sql id="selectAttachmentByQueryCriteriaSql">  
  from ${prefix}ACT_HI_ATTACHMENT RES  
  <where>  
   <if test="attachmentId != null">  
    RES.ID_ = #{attachmentId}  
   </if>  
   <if test="attachmentName != null">  
    and RES.NAME_ = #{attachmentName}  
   </if>  
   <if test="attachmentType != null">  
    and RES.TYPE_ = #{attachmentType}  
   </if>  
   <if test="userId != null">  
    and RES.USER_ID_ = #{userId}  
   </if>  
  </where>  
  </sql>  

 </mapper>  

Capabilities such as pagination, ordering, table name prefixing are available and can be used in the statements (since the parameterType is a subclass of AbstractQuery). Note that to map results the predefined org.activiti.engine.impl.persistence.entity.AttachmentEntity.attachmentResultMap resultMap can be used. Finally, the AttachmentQuery can be used as follows:

...
 // Get the total number of attachments  
 long count = new AttachmentQuery(managementService).count();  
 // Get attachment with id 10025  
 Attachment attachment = new AttachmentQuery(managementService).attachmentId("10025").singleResult();  
 // Get first 10 attachments  
 List<Attachment> attachments = new AttachmentQuery(managementService).listPage(0, 10);  
 // Get all attachments uploaded by user kermit  
 attachments = new AttachmentQuery(managementService).userId("kermit").list();  
...

Check CustomMybatisXMLMapperTest JUnit test class for more about working with Custom XML Mapped Statements.

The AbstractQuery in current 5.17.0 release class doesn't contain a constructor that accepts an instance of ManagementService. Therefore, extending classes need to call another super constructor for example the AttachmentQuery can look as follows:

public AttachmentQuery(ManagementService managementService){
super(((ManagementServiceImpl) managementService).getCommandExecutor());
}