Mybatis批处理—foreach的用法

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

批量更新

Mapper.java

void UpdateImprovement(List<ImproveReportModel> list);

  
 

  
1
 

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>

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
 

批量查询

Mapper.java

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

  
 

  
1
 

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>

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
 

批量插入

Mapper.java

void RedisToDb(List<TeamDetailResultModel> list);

  
 

  
1
 

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>

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
 

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

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

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

  
 

  
1
 

<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>

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

感谢阅读~

文章来源: blog.csdn.net，作者：张艳伟_Laura，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/Laura__zhang/article/details/120655626