Activiti native query search on candidates using PostgreSQL

Posted on February 19, 2014 by justme

I had a need to find all Activiti tasks which are available for a user who has a set of roles (or candidate groups in Activiti speak). This cannot be done using normal task queries, so I thought I could use native queries and an expression like

i.group_id_ IN (#{candidates})

Unfortunately this does not work. The cross-database solution is to adjust your query for the number of candidates (something like “in (?,?,?)”).
Fortunately though, we use PostgreSQL in this project which allows a solution using arrays. When you change the expression below and pass an array instead of just setting the object.

i.group_id_ = any (#{candidates})

Unfortunately, Activiti and the underlying MyBatis have no direct support for this combination, so some patching is needed.

For starters, a customized Activiti configuration class for use in the spring context.

public class DcSpringProcessEngineConfiguration extends SpringProcessEngineConfiguration {
 
    @Override
    protected InputStream getMyBatisXmlConfigurationSteam() {
        return ReflectUtil.getResourceAsStream("my/app/location/for/activitiMybatisMappings.xml");
    }
 
}

Now copy the original mappings.xml file from Activiti and make some changes, building the new activitiMybatisMappings.xml file. Two type aliases are added and used as a type handler and a plugin.

<!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"/>
        <typeAlias type="my.app.PostgresStringArrayTypeHandler" alias="PostgresStringArrayTypeHandler"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="ByteArrayRefTypeHandler"
                     javaType="org.activiti.engine.impl.persistence.entity.ByteArrayRef"
                     jdbcType="VARCHAR"/>
        <typeHandler handler="PostgresStringArrayTypeHandler"
                     javaType="[Ljava.lang.String;"/>
    </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>

The PostgresStringArrayTypeHandler uses setArray() instead of setObject() to pass the parameter to the prepared statement. Similar fixes are needed to catch other array types.

public class PostgresStringArrayTypeHandler extends BaseTypeHandler<String[]> {
 
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String[] parameter, JdbcType jdbcType)
            throws SQLException {
        Array array = ps.getConnection().createArrayOf("varchar", parameter);
        ps.setArray(i, array);
    }
 
    @Override
    public String[] getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return (String[]) rs.getArray(columnName).getArray();
    }
 
    @Override
    public String[] getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        return (String[]) rs.getArray(columnIndex).getArray();
    }
 
    @Override
    public String[] getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        return (String[]) cs.getArray(columnIndex).getArray();
    }
}