Mybatis批处理—foreach的用法

---

Mybatis批处理（查询、更新、插入）

批量更新

Mapper.java

void UpdateImprovement(List<ImproveReportModel> list);

Mapper.xml

 <update id="UpdateImprovement" parameterType="com.tfjybj.typing.model.ImproveReportModel">
        update
        ty_improve_report
        set
        is_query=1
        where
        user_id
        in
        (
        <foreach collection="list" item="ImproveReportModel" separator=",">
            #{ImproveReportModel.userId}
        </foreach>
        )
    </update>

批量查询

Mapper.java

List<TeamGrowthChartModel> selectTeamGrowthChart(@Param("className") List<String> className);

Mapper.xml

    <select id="selectTeamRanking" resultType="com.tfjybj.typing.model.TeamRankingModel">
        SELECT
        t.team_id As teamId,
        GROUP_CONCAT(distinct
        CONCAT_WS( ",", p.`user_name` )) As userNames,
        t.team_name As teamName,
        t.group_results As teamGrade,
        p.class_name as className
        FROM
        `ty_person` p
        RIGHT JOIN `ty_team` t ON p.team_id = t.team_id
        WHERE
        t.is_delete = 0
        AND p.is_delete = 0
        <if test="className!=null and className.size() > 0">
            AND p.`class_name` in
            <foreach item="item" collection="className" index="index" open="(" separator="," close=")">#{item}</foreach>
        </if>
        GROUP BY
        t.team_id
        ORDER BY
        t.group_results DESC
    </select>

批量插入

Mapper.java

void RedisToDb(List<TeamDetailResultModel> list);

Mapper.xml

    <insert id="RedisToDb">
        INSERT INTO team_detail_result
        (id, team_id, user_name,speed,right_rate,set_time,real_time,article_name,score)
        VALUES
        <foreach collection="list" item="teamDetailResultModel" separator=",">
            (#{teamDetailResultModel.id}, #{teamDetailResultModel.teamName}, #{teamDetailResultModel.speed}, #{teamDetailResultModel.rightRate}, #{teamDetailResultModel.setTime},#{teamDetailResultModel.real_time}, #{teamDetailResultModel.articleName}, #{teamDetailResultModel.score})
        </foreach>
    </insert>

注意：这里的 in 和 <trim prefix="(" suffix=")"> 以及 in ()的三种方式等价使用

<trim prefix="(" suffix=")"> 的用法

List<ReturnCodeEntity> selectByIds(@Param("ids") List<Long> ids);

<select id="selectByIds" parameterType="java.util.List"
            resultType="com.paic.ocss.gateway.model.entity.ReturnCodeEntity">
        SELECT
        id,
        code,
        message,
        recommendation,
        status
        FROM openapi_return_code
        WHERE id in
        <trim prefix="(" suffix=")">
            <foreach collection="ids" index="index" item="id" separator=",">
                #{id}
            </foreach>
        </trim>
    </select>

感谢阅读~