mysql mybatis 批量更新，批量查询，批量插入

最新推荐文章于 2023-07-23 15:06:51 发布

采坑先锋

最新推荐文章于 2023-07-23 15:06:51 发布

阅读量277

点赞数 1

分类专栏： mysql mybatis 文章标签： mysql mybatis

本文链接：https://blog.csdn.net/qq_31564573/article/details/105670287

版权

mysql 同时被 2 个专栏收录

24 篇文章 0 订阅

订阅专栏

mybatis

12 篇文章 0 订阅

订阅专栏

1.批量更新

sql:
1.1根据多个条件批量更新

 <update id="updateStateByPositionIds">
   update table set state = #{state} where
    (position_id, group_id) in
    <foreach collection="relationVos" item="item" separator="," close=")" open="(">
      (#{item.positionId}, #{item.groupId})
    </foreach>
  </update>

mapper:

//多个条件
int updatePushStateByPositionIds(@Param("relationVos")List<PositionAndGroupSo> positionAndGroupSoList,
                                @Param("state") Byte state);
//单个条件
int updatePushStateByPositionIds(@Param("positions")List<string> positions,
                                 @Param("state") Byte state);

@Data
public class PositionAndGroupRequest implements Serializable {
    private static final long serialVersionUID = 7171837841501948123L;
    private Integer   groupId;
    private Integer   positionId;
}

1.2单个条件批量更新

 <update id="updateStateByPositionIds">
   update table set state = #{state} where
    position_id in
    <foreach collection="positions" item="item" separator="," close=")" open="(">
      #{item}
    </foreach>
  </update>

2.批量查询

mapper:

List<string> selectStateByPositionIdList(@Param("relationVos")List<PositionAndGroupSo> positionAndGroupSoList);
//单个条件
int selectStateByPositionIds(@Param("positions")List<string> positions);

@Data
public class PositionAndGroupRequest implements Serializable {
    private static final long serialVersionUID = 7171837841501948123L;
    private Integer   groupId;
    private Integer   positionId;
}

2.1 多个条件批量更新

 <select id="selectStateByPositionIdList">
   select state table where
    (position_id, group_id) in
    <foreach collection="relationVos" item="item" separator="," close=")" open="(">
      (#{item.positionId}, #{item.groupId})
    </foreach>
  </select>

2.1 单个条件批量更新

 <select id="selectStateByPositionIds">
   select state table where
    (position_id, group_id) in
    <foreach collection="positions" item="item" separator="," close=")" open="(">
      #{item}
    </foreach>
  </select>

3.批量插入

mapper:

List<string> insertStateByPositionIdList(@Param("relationVos")List<PositionAndGroupSo> positionAndGroupSoList);
//单个条件
int insertStateByPositionIds(@Param("positions")List<string> positions);

@Data
public class PositionAndGroupRequest implements Serializable {
    private static final long serialVersionUID = 7171837841501948123L;
    private Integer   groupId;
    private Integer   positionId;
}

3.1 多个条件批量新增

 <insert id="insertStateByPositionIdList">
   insert into  table (position_id, group_id)
   values
   
    <foreach collection="relationVos" item="item" separator="," >
      (#{item.positionId}, #{item.groupId})
    </foreach>
  </insert>

3.1 单个条件批量插入

 <insert id="insertStateByPositionIds">
   insert into  table (position_id)
   values
    <foreach collection="positions" item="item" separator="," close=")" open="(">
      #{item}
    </foreach>
  </insert>