mybatis foreach的使用

将list数组中的参数遍历作为条件,在将查询结果放入list中

List<Customer> findCustomerIdList(List list);
<select id="findCustomerIdList" resultType="Customer" parameterType="java.util.List">
        SELECT
        ID as id,
        SALES_ID as salesId,
        IS_BELONG as isBelong
        FROM
        sales_customer
        where
            IS_BELONG = 1
        <foreach collection="list" item="item" index="index" open="" separator="" close="">
            <choose>
                <when test="list.size() == 1">
                    AND ID = #{item.id}
                </when>
                <when test="index == 0 and list.size() != 1">
                    AND (ID = #{item.id}
                </when>
                <when test="index == list.size()-1 and list.size() != 1">
                    or ID = #{item.id})
                </when>
                <when test="index != 0 and index != list.size()-1 and list.size() != 1">
                    or ID = #{item.id}
                </when>
            </choose>
        </foreach>
    </select>

👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀

List<String> selectChildId(List<String> list);
<select id="selectChildId" parameterType="java.util.List" resultType="java.lang.String">
        SELECT
        ID
        FROM
        CHINA
        WHERE  PARENT_ID IN
        <if test="list != null and list.size()> 0">
                <foreach collection="list" item="item"  open="(" separator=","     close=")">
                        #{item}
                </foreach>
        </if>
</select>

//sql
       SELECT
        ID
        FROM
        CHINA
        WHERE  PARENT_ID IN  (?,?,?,?,?)

👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀

遍历String字符串（字符串以“,”隔开）

    <select id="selectChildId" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT
        ID
        FROM
        CHINA
        WHERE  PARENT_ID IN
        <if test="str != null and str.length > 0 ">
                <foreach collection="str.split(',')"  item="item"  open="("  separator=","     close=")">
                        #{item}
                </foreach>
        </if>
</select>

👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀👀

int insertCustomerBatch(List<Customer> record);
<insert id="insertCustomerBatch" parameterType="java.util.List" >
        <selectKey resultType ="java.lang.Integer" keyProperty= "ID" order= "AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey >
        insert into customer
        (ID, SALES_ID,CREATOR_ID, CREATE_TIME,MODIFIER_ID, MODIFY_TIME)
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (
                #{item.id,jdbcType=VARCHAR}, #{item.salesId,jdbcType=VARCHAR}, #{item.creatorId,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=TIMESTAMP},#{item.modifierId,jdbcType=VARCHAR}, #{item.modifyTime,jdbcType=TIMESTAMP}
            )
        </foreach>
    </insert>

//sql
    insert into customer
        (ID, SALES_ID,CREATOR_ID, CREATE_TIME,MODIFIER_ID, MODIFY_TIME)
        values
        (?,?,?,?,?,?),(?,?,?,?,?,?),(?,?,?,?,?,?)

将查询结果放入一个List中

mapper

        List<String> selectByParentIdString(String id);

xml中

  <select id="selectByParentIdString" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT
        ID
        FROM
        CHINA
        WHERE
        1 = 1
        <if test=" id != null and id.length>0"
        and
        PARENT_ID = #{id}
        </if>
    </select>

vans