Mybatis 根据不同id进行批量更新

Mybatis批量更新

• 应用环境
  Mybatis、Mysql

• 实体类

  public class ServiceGroup {
  
      private Integer id;
  
      private String services;
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getServices() {
          return services;
      }
  
      public void setServices(String services) {
          this.services = services;
      }
  }
  

• Mapper接口

  public interface ServiceGroupMapper {
      
      public void updateServices(List<ServiceGroup> update);
  }
  
  

• Mapper.xml文件中的update语句

  <update id="updateServices" parameterType="com.zongze.domain.ServiceGroup">
          update service_group
          <trim prefix="set" suffixOverrides=",">
              <trim prefix=" services = case" suffix="end,">
                  <foreach collection="list" index="index" item="item">
                  when id = #{item.id, jdbcType=INTEGER} then #{item.services, jdbcType=LONGVARCHAR}
                  </foreach>
              </trim>
          </trim>
          where id in
          <foreach close=")" collection="list" item="item" open="(" separator=",">
              #{item.id, jdbcType=INTEGER}
          </foreach>
      </update>
  

• 控制台输出SQL语句

   update service_group set services = CASE WHEN id=? THEN ? WHEN id=? THEN ? END WHERE id in (?,?)
  

• Mysql语句

  update service_group set services = CASE WHEN id=1 THEN '{"服务1,服务2"}' WHEN id=2 THEN '{"服务2,服务3"}' END WHERE id in (1,2);