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

比眉伴天荒 2023-06-27 03:22 107阅读 0赞

## 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>
                when 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);