Join query with XML based Mapped Statements

Flowable Engine
1

Hi, I try to create mybatis xml mapper. I want to make select from joined tables.

I Based on example from docs


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

  <select id="selectAttachmentCountByQueryCriteria" parameterType="org.flowable.standalone.cfg.AttachmentQuery" resultType="long">

    select count(distinct RES.ID_)

    <include refid="selectAttachmentByQueryCriteriaSql"/>

  </select>

  <select id="selectAttachmentByQueryCriteria" parameterType="org.flowable.standalone.cfg.AttachmentQuery" resultMap="org.flowable.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>

I want to make joined query of ACT_RU_DEADLETTER_JOB and ACT_RU_VARIABLE.

Exceptions

using ${limitBetween}

I look for variable value of limitBetween and in project i found mssql.properties.

This query ends with select distinct RES.* what cause


The column prefix 'RES' does not match with a table name or alias name used in the query.

Created query from mapper:


SELECT

    SUB.*

FROM

    (

        select

            ACT_RU_DEADLETTER_JOB.PROCESS_INSTANCE_ID_,

            ACT_RU_DEADLETTER_JOB.ELEMENT_NAME_,

            ACT_RU_DEADLETTER_JOB.EXCEPTION_MSG_,

            ACT_RU_DEADLETTER_JOB.CREATE_TIME_,

            ACT_RU_VARIABLE.TEXT_,

            row_number() over (

                order by

                    PROCESS_INSTANCE_ID_ asc

            ) rnk

        FROM

            (

                select distinct RES.*

                from

                    ACT_RU_DEADLETTER_JOB

                    join ACT_RU_VARIABLE on ACT_RU_DEADLETTER_JOB.PROCESS_INSTANCE_ID_ = ACT_RU_VARIABLE.PROC_INST_ID_

                WHERE

                    ACT_RU_VARIABLE.NAME_ = 'foo'

            ) RES

    ) SUB

WHERE

    SUB.rnk >= ?

    AND SUB.rnk < ?

This RES.* limits using this variable

using ${limitOuterJoinBetween}

This query does not contain RES.* but cointans ${mssqlOrDB2OrderBy} which cause:


There is no getter for property named 'mssqlOrDB2OrderBy'

Questions

  1. Is it ok that ${limitbetween} ends always with RES.*?

  2. For join select should be used ${limitOuterJoinBetween}?

    • if yes, how to set property mssqlOrDB2OrderBy

  3. Is there any example for join select with XML mapper?

  4. Why in inner FROM () RES there are no columns defined in outer FROM () SUB ?

    • i analized queries eg. process instance with variable, queries made contain after select distinct RES.* columns for joined tables.

  5. Do you need more data?

    • if yes, which one?
2

The mssqlOrDB2OrderBy is added by the java instance of the Query implementation. You will need your own implementation of AttachmentQuery that exposed those getters currently.