荆棘从死亡中来，死亡又包含着新生。
      •  0 回帖  •  829 浏览  •  4 年前
   

mybatis 模糊匹配由 $ 传参修改为 #后异常

错误如下：

property. Cause: java.sql.SQLException: Parameter index out of bounds. 3 is not between valid values of 1 and 2

使用$传参正常运行时：

    <select id="selectByConditionAccountHead" parameterType="com.jsh.erp.datasource.entities.AccountHeadExample" resultMap="ResultMapEx">
        select ah.*, s.supplier OrganName, p.Name HandsPersonName, a.Name AccountName
        from jsh_accounthead ah
        left join jsh_supplier s on ah.OrganId=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_person p on ah.HandsPersonId=p.id and ifnull(p.delete_Flag,'0') !='1'
        left join jsh_account a on ah.AccountId=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="billNo != null">
            and ah.BillNo like '%${billNo}%'
        </if>
        <if test="type != null">
            and ah.Type='${type}'
        </if>
        <if test="beginTime != null">
            and ah.BillTime &gt;= '%${beginTime}%'
        </if>
        <if test="endTime != null">
            and ah.BillTime &lt;= '%${endTime}%'
        </if>
        and ifnull(ah.delete_Flag,'0') !='1'
        order by ah.Id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

直接修改为#传参提示错误

    <select id="selectByConditionAccountHead" parameterType="com.jsh.erp.datasource.entities.AccountHeadExample" resultMap="ResultMapEx">
        select ah.*, s.supplier OrganName, p.Name HandsPersonName, a.Name AccountName
        from jsh_accounthead ah
        left join jsh_supplier s on ah.OrganId=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_person p on ah.HandsPersonId=p.id and ifnull(p.delete_Flag,'0') !='1'
        left join jsh_account a on ah.AccountId=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="billNo != null">
            and ah.BillNo like '%#{billNo}%'
        </if>
        <if test="type != null">
            and ah.Type='#{type}'
        </if>
        <if test="beginTime != null">
            and ah.BillTime &gt;= '%#{beginTime}%'
        </if>
        <if test="endTime != null">
            and ah.BillTime &lt;= '%#{endTime}%'
        </if>
        and ifnull(ah.delete_Flag,'0') !='1'
        order by ah.Id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

解决方式

按如下方式修改，对传入参数加工后，传入

    <select id="selectByConditionAccountHead" parameterType="com.jsh.erp.datasource.entities.AccountHeadExample" resultMap="ResultMapEx">
        select ah.*, s.supplier OrganName, p.Name HandsPersonName, a.Name AccountName
        from jsh_accounthead ah
        left join jsh_supplier s on ah.OrganId=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_person p on ah.HandsPersonId=p.id and ifnull(p.delete_Flag,'0') !='1'
        left join jsh_account a on ah.AccountId=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="billNo != null and billNo != ''">
            <bind name="billNo" value="'%' + _parameter.billNo + '%'"/>
            and ah.BillNo like #{billNo}
        </if>
        <if test="type != null">
            and ah.Type= #{type}
        </if>
        <if test="beginTime != null and beginTime != ''">
            and ah.BillTime &gt;= #{beginTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and ah.BillTime &lt;= #{endTime}
        </if>
        and ifnull(ah.delete_Flag,'0') !='1'
        order by ah.Id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>