CODEWITHSUNDEEP
mybatisibatis
user4313807
user4313807

Reputation: 41

Mybatis mapper file, parameter was changed to null in foreach statement

Okay, I am trying to generate sql for a Map<String,List<String>>, I am having a nested foreach statements to generate sql. This is my mybatis xml mapper file:

<select id="UserScaleResult.listAccordingToScaleBrief" resultType="java.util.Map">
    select distinct uu.id as uid, uu.fullname, ucn.card_number,
    uu.mobile_number

    from
    <include refid="UserScaleResult.baseCondition.from" />
    <include refid="UserScaleResult.UserType.from"/>
    <include refid="UserScaleResult.listAccordingToScale.from"/>
    <include refid="UserScaleResult.baseConditions.where" />
    <include refid="UserScaleResult.UserType.where" />
    <include refid="UserScaleResult.listAccordingToScale.where" />
    limit #{offset}, #{pageSize}
</select>


<sql id="UserScaleResult.listAccordingToScale.where">
    <if test="@Ognl@isNotEmpty(type)">
        and musr.type = #{type}
    </if>
    <if
        test="@Ognl@isNotEmpty(createTimeBegin) and @Ognl@isNotEmpty(createTimeEnd)">
        and musr.create_time between #{createTimeBegin} and
        #{createTimeEnd};
    </if>
    <if test="@Ognl@isNotEmpty(missionId)">
        and musr.mission_id = #{missionId}
    </if>
    <if test="@Ognl@isNotEmpty(scaleId)">
        and musr.scale_id = #{scaleId}
    </if>
    <if test="@Ognl@isNotEmpty(sdl)">
        and (
        <foreach collection="sdl.entrySet()" item="item" open="(" separator=") or (" close=")">
        tsrdl.dimension_id = #{item.key} 
            <if test="@Ognl@isNotEmpty(item.value)">
                and 
                <foreach collection="item.value" item="iitem" open="(" separator=" or " close=")">
                    tsrdl.level_id = #{iitem}
                </foreach>
            </if>
        </foreach>
        )
    </if>
</sql>

The log shows the #{iitem} are all null when it put it down to sql, although the number of parameters are correct:

2016-05-13 14:47:27,837 DEBUG [java.sql.Connection] - <==>  Preparing: select count(*) from user_user uu left join user_user_detail uud on uu.id = uud.user_id left join user_card_number ucn on uu.card_number_id =ucn.id left join org_org oo on ucn.org_id = oo.id inner join (SELECT user_id, MAX(IF(extension_item_id=?,DATA,'') )AS ? FROM user_extension_item_data GROUP BY user_id) AS ud on uu.id = ud.user_id inner join mission_user_scale_result musr on uu.id = musr.user_id inner join test_scale ts on musr.scale_id = ts.id inner join test_scale_result_dimension_level tsrdl on tsrdl.result_id = musr.id and ud.3 = ? and ( ( tsrdl.dimension_id = ? and ( tsrdl.level_id = ? or tsrdl.level_id = ? ) ) or ( tsrdl.dimension_id = ? and ( tsrdl.level_id = ? or tsrdl.level_id = ? or tsrdl.level_id = ? ) ) ) >
2016-05-13 14:47:27,838 DEBUG [java.sql.PreparedStatement] - <==> Parameters: 3(String), 3(String), 保密(String), 42(String), null, null, 47(String), null, null, null>

EDIT: Just did some experiment, when I substitute the '#' sign in #{iitem} with '$', the value appears and the sql gets parameters all good.

Upvotes: 0

Views: 1358

Answers (1)

09Q71AO534
09Q71AO534

Reputation: 4440

We need to use ${dataBaseTable} instead of #. The difference is that # is used for PreparedStatement substitution. $ is for direct String substitution.

Replace your #{iitem} with ${iitem}

....
     <foreach collection="item.value" item="iitem" open="(" separator=" or " close=")">  
         tsrdl.level_id = ${iitem} 
     </foreach>
....

Upvotes: 1

Related Questions