MyBatis同一SQL多次使用相同参数报索引越界异常如何解决

问题场景 #

开发中需要在单条SQL语句内多次复用相同查询参数，Mapper接口方法定义如下：

List<PoliceCompanyJointCheck> selectJointCheckPageRedList(@Param("pageModel") PageModel<PoliceCompanyJointCheck> pageModel,
                                                          @Param("query") PoliceCompanyJointCheckQuery query,
                                                          @Param("companyId") String companyId,
                                                          @Param("checkRecordType") Integer checkRecordType);

对应XML映射文件的SQL编写如下：

<select id="selectJointCheckPageRedList" resultType="com.jiumai.base.pm.entity.PoliceCompanyJointCheck">
    SELECT joint_check_table.check_id AS checkId, joint_check_table.check_date AS checkDate,
           joint_check_table.check_record_type AS checkRecordType, joint_check_table.company_id AS companyId,
           joint_check_table.company_name AS companyName,
           joint_check_table.rest_score AS restScore,
           pafen_table.total_pafen AS totalPafen,
           (joint_check_table.rest_score + pafen_table.total_pafen) AS totalScore,
           joint_check_table.create_id AS createId, joint_check_table.create_name AS createName, joint_check_table.create_date AS createDate,
           joint_check_table.modify_id AS modifyId, joint_check_table.modify_name AS modifyName, joint_check_table.modify_date AS modifyDate,
           joint_check_table.del_flag AS delFlag, joint_check_table.version AS version
        FROM
    (
        SELECT <include refid="Company_Column_List"></include>
        FROM
        (
            SELECT <include refid="Project_Column_List"></include>
            FROM pm_police_company_joint_check
            WHERE del_flag = 1 AND check_record_type = #{checkRecordType}
            <if test="companyId != null and companyId != ''">
                AND company_id = #{companyId}
            </if>
            <if test="query.year != null">
                AND YEAR(check_date) = #{query.year}
            </if>
            <if test="query.startMonth != null">
                AND #{query.startMonth} <= MONTH(check_date)
            </if>
            <if test="query.endMonth != null">
                AND #{query.endMonth} >= MONTH(check_date)
            </if>
            GROUP BY proj_id
        ) AS proj_check_table GROUP BY company_id
    ) AS joint_check_table LEFT JOIN (
      SELECT pafen_id, company_id, check_date, SUM(pafen_score) AS total_pafen
      FROM pm_pafen
      WHERE del_flag = 1
        <if test="companyId != null and companyId != ''">
            AND company_id = #{companyId}
        </if>
        <if test="query.year != null">
            AND YEAR(check_date) = #{query.year}
        </if>
        <if test="query.startMonth != null">
            AND #{query.startMonth} <= MONTH(check_date)
        </if>
        <if test="query.endMonth != null">
            AND #{query.endMonth} >= MONTH(check_date)
        </if>
        GROUP BY company_id
    ) AS pafen_table ON joint_check_table.company_id = pafen_table.company_id
    ORDER BY totalScore DESC
</select>

异常表现 #

调用方法时抛出参数下标越界异常，控制台打印的MyBatis-Plus分页插件自动生成的count统计SQL仅包含5个?参数占位符，错误日志尝试为第6个参数赋值，异常信息如下：

java.sql.SQLException: Parameter index out of range (6 > number of parameters, which is 5)

移除SQL中右表pafen查询部分最后4个<if>条件标签后方法可正常运行，初步排查方向为参数重复引用导致的问题。

问题根因 #

MyBatis原生支持单条SQL内多次复用同名参数，该问题和MyBatis核心能力无关，是低版本MyBatis-Plus分页插件自动生成count SQL的解析bug导致：插件在对包含多层嵌套子查询、重复参数引用的SQL做count优化改写时，参数占位符计数逻辑出错，统计到的占位符数量和MyBatis上下文实际要赋值的参数数量不匹配，最终触发下标越界。

解决方案 #

可根据项目实际情况选择以下任意一种方案修复：

• 升级MyBatis-Plus版本至3.5.3及以上，新版本已经修复多子查询场景下重复参数的count SQL解析计数bug，升级后无需修改原有业务SQL即可正常运行，是最推荐的根治方案。
• 若暂时无法升级依赖，可自定义count查询语句：在XML中手写对应id的count查询SQL，通过分页插件的countId属性指定使用自定义count语句，跳过插件自动生成count SQL的逻辑，从根源上避免解析错误。
• 应急场景可临时关闭分页插件的count优化配置：在MyBatis-Plus分页插件配置项中关闭optimizeJoin开关，让插件生成count SQL时不做子查询的裁剪优化，该方案会一定程度降低count查询性能，仅适合临时止血使用。
• 将两个子查询中重复的过滤条件抽为公共<sql>片段，通过<include>标签在两处引用，减少重复参数书写的同时，也能规避部分低版本插件的参数解析异常。

内容的提问来源于stack exchange，提问作者Z.H.Chen