Custom mybatis queries

Hi, I am trying to develop my own custom mybatis queries, however I am stuck with

org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named ‘prefix’ in ‘class org.camunda.bpm.engine.impl.db.ListQueryParameterObject’

Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named ‘prefix’ in ‘class org.camunda.bpm.engine.impl.db.ListQueryParameterObject’

I followed the tutorial camunda-7-code-examples/snippets/custom-queries at main · camunda-consulting/camunda-7-code-examples · GitHub. As I have spring boot application, I have defines the session factory as bean.

@Configuration
public class CustomQueryConfiguration {

@Autowired
private ProcessEngine processEngine;
public Properties getSqlSessionFactoryProperties(ProcessEngineConfigurationImpl conf) {
    Properties properties = new Properties();
    ProcessEngineConfigurationImpl.initSqlSessionFactoryProperties(properties,
            conf.getDatabaseTablePrefix(), conf.getDatabaseType());
    return properties;
}
@Bean
public SqlSessionFactory createMyBatisSqlSessionFactory() {
    InputStream config = this.getClass().getResourceAsStream("/customMybatisConfiguration.xml");;
    ProcessEngineConfiguration processEngineConfiguration = processEngine.getProcessEngineConfiguration();
    DataSource dataSource = processEngineConfiguration.getDataSource();
    // use this transaction factory if you work in a non transactional
    // environment
    // TransactionFactory transactionFactory = new JdbcTransactionFactory();
    // use this transaction factory if you work in a transactional
    // environment (e.g. called within the engine or using JTA)
    TransactionFactory transactionFactory = new ManagedTransactionFactory();
    Environment environment = new Environment("customTasks", transactionFactory, dataSource);
    XMLConfigBuilder parser = new XMLConfigBuilder( //
            new InputStreamReader(config), //
            "", // set environment later via code
            getSqlSessionFactoryProperties((ProcessEngineConfigurationImpl) processEngineConfiguration));
    org.apache.ibatis.session.Configuration configuration = parser.getConfiguration();
    configuration.setEnvironment(environment);
    configuration = parser.parse();
    configuration.setDefaultStatementTimeout(processEngineConfiguration.getJdbcStatementTimeout());
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
    return sqlSessionFactory;
}

}

Here is the implementing service.

@Service(“customTaskServiceImpl”)
public class CustomTaskServiceImpl implements CustomTaskService {

private SqlSessionFactory sqlSessionFactory;
@Autowired
public CustomTaskServiceImpl(SqlSessionFactory sqlSessionFactory) {
    this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public List<Task> getTrackedTasks(){
    try (SqlSession sqlSession = sqlSessionFactory.openSession()){
        //the region is not realy needed, its here for future, because i will be using parameters
        String region = "hovno";
        ListQueryParameterObject queryParameterObject = new ListQueryParameterObject();
        queryParameterObject.setParameter(region);
        List<Task> tasks = sqlSession.selectList("customTask.selectTrivialTaskByQueryCriteria",queryParameterObject);
        return tasks;
    }
}

}

And here is my mapping file:

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

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

<mapper namespace="customTask">

    <select id="selectTrivialTaskByQueryCriteria" parameterType="org.camunda.bpm.engine.impl.db.ListQueryParameterObject" resultMap="taskResultMap">
        <!--        select ${distinct} -->
        select
        <include refid="columnSelection"/>
        from ${prefix}ACT_RU_TASK RES
    </select>



    <sql id="columnSelection">
        RES.REV_, RES.ID_, RES.NAME_, RES.PARENT_TASK_ID_, RES.DESCRIPTION_, RES.PRIORITY_,
        RES.CREATE_TIME_, RES.OWNER_, RES.ASSIGNEE_, RES.DELEGATION_, RES.EXECUTION_ID_,
        RES.PROC_INST_ID_, RES.PROC_DEF_ID_, RES.CASE_EXECUTION_ID_, RES.CASE_INST_ID_,
        RES.CASE_DEF_ID_, RES.TASK_DEF_KEY_, RES.DUE_DATE_, RES.FOLLOW_UP_DATE_,
        RES.SUSPENSION_STATE_, RES.TENANT_ID_
    </sql>

    <resultMap id="taskResultMap" type="org.camunda.bpm.engine.impl.persistence.entity.TaskEntity">
        <id property="id" column="ID_" jdbcType="VARCHAR"/>
        <result property="revision" column="REV_" jdbcType="INTEGER"/>
        <result property="nameWithoutCascade" column="NAME_" jdbcType="VARCHAR"/>
        <result property="parentTaskIdWithoutCascade" column="PARENT_TASK_ID_" jdbcType="VARCHAR"/>
        <result property="descriptionWithoutCascade" column="DESCRIPTION_" jdbcType="VARCHAR"/>
        <result property="priorityWithoutCascade" column="PRIORITY_" jdbcType="INTEGER"/>
        <result property="createTime" column="CREATE_TIME_" jdbcType="TIMESTAMP" />
        <result property="ownerWithoutCascade" column="OWNER_" jdbcType="VARCHAR"/>
        <result property="assigneeWithoutCascade" column="ASSIGNEE_" jdbcType="VARCHAR"/>
        <result property="delegationStateString" column="DELEGATION_" jdbcType="VARCHAR"/>
        <result property="executionId" column="EXECUTION_ID_" jdbcType="VARCHAR" />
        <result property="processInstanceId" column="PROC_INST_ID_" jdbcType="VARCHAR" />
        <result property="processDefinitionId" column="PROC_DEF_ID_" jdbcType="VARCHAR"/>
        <result property="caseExecutionId" column="CASE_EXECUTION_ID_" jdbcType="VARCHAR" />
        <result property="caseInstanceIdWithoutCascade" column="CASE_INST_ID_" jdbcType="VARCHAR" />
        <result property="caseDefinitionId" column="CASE_DEF_ID_" jdbcType="VARCHAR"/>
        <result property="taskDefinitionKeyWithoutCascade" column="TASK_DEF_KEY_" jdbcType="VARCHAR"/>
        <result property="dueDateWithoutCascade" column="DUE_DATE_" jdbcType="TIMESTAMP"/>
        <result property="followUpDateWithoutCascade" column="FOLLOW_UP_DATE_" jdbcType="TIMESTAMP"/>
        <result property="suspensionState" column="SUSPENSION_STATE_" jdbcType="INTEGER" />
        <result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
        <!-- note: if you add mappings here, make sure to select the columns in 'columnSelection' -->
    </resultMap>

</mapper>

I guess I am missing something in the session factory creation. How can I get the ${prefix} directive working? Without using it, the execution of the select works.

1 Like

@tomorrow,
Please, let me know when you find the answer :slight_smile:

Actually, I did find a solution. I suspect it is a bug, but for now I am happy. It’s enough to fill the prefix manually, because it is the db prefix parameter is ignored in ProcessEngineConfigurationImpl.initSqlSessionFactoryProperties. I have changed my mehtod to

public Properties getSqlSessionFactoryProperties(ProcessEngineConfigurationImpl conf) {
    Properties properties = new Properties();
    ProcessEngineConfigurationImpl.initSqlSessionFactoryProperties(properties,
            conf.getDatabaseTablePrefix(), conf.getDatabaseType());
    properties.put("prefix", conf.getDatabaseTablePrefix());
    return properties;
}
1 Like

@tomorrow, are you sure this works?
I get

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘application’: Unsatisfied dependency expressed through field ‘customTaskService’; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘customTaskService’: Unsatisfied dependency expressed through field ‘sqlSessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘createMyBatisSqlSessionFactory’ defined in class path resource [ondebt/bpm/queries/CustomQueryConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method ‘createMyBatisSqlSessionFactory’ threw exception; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.lang.NullPointerException

Missing configuration file.

@dikey94 so it works for you now?

@tomorrow
Yes. Thanks!

btw, if you would be interesed, I also managed to get to work Commands with command context, which is good in creating custom queries for same query parameters as camunda does, in example TaskQuery, which I needed to alter a little bit.

https://forum.camunda.io/t/custom-mybatis-queries-and-command-context/4187