史上最简单 springboot + mybatis + mysql 批量修改

悠悠 2023-02-16 05:29 6阅读 0赞

话不多说直接上代码

## 1、表结构 ##

CREATE TABLE `user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      `user_id` varchar(255) NOT NULL COMMENT '唯一UUID',
      `name` varchar(100) NOT NULL COMMENT '用户名',
      `age` int(3) DEFAULT NULL COMMENT '用户年龄',
      `description` longtext COMMENT '用户描述',
      `gmt_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后修改时间',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_unique_name` (`name`) USING BTREE COMMENT '用户名唯一索引约束'
    ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='用户表'

## 2、User ##

import lombok.Data;
    
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author pph
     */
    @Data
    public class UserAdminDO {
        /**
         * 自增主键
         */
        private Long id;
        /**
         * admin UUID
         */
        private String userId;
        /**
         * 用户名称
         */
        private String name;
        /**
         * 用户年龄
         */
        private Integer age;
        /**
         * 描述
         */
        private String description;
        /**
         * 记录创建时间
         */
        private Date gmtCreated;
        /**
         * 记录最后修改时间
         */
        private Date gmtModified;
    }

**省略 controller 与 service 代码 …**

## 3、Mapper ##

import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.List;
    
    /**
     * @author pph
     */
    @Mapper
    public interface UserMapper {
    
    	 /**
         * 编辑用户信息
         *
         * @param user 用户信息
         * @return 修改条数
         */
        int userEdit(User user);
    
        /**
         * 批量编辑用户信息
         *
         * @param users 用户信息集合
         * @return 修改条数
         */
        int userBatchEdit(@Param(value = "users") List<User> users);
    }

4、Mapper.xml

<update id="userEdit" parameterType="com.pph.model.User">
        UPDATE `user`
        <set>
            <if test="name != null">`name` = #{name},</if>
            <if test="age != null">`age` = #{age},</if>
            <if test="description != null">`description` = #{description},</if>
        </set>
        <where>
            `user_id` = #{userId}
        </where>
    </update>
    
    <update id="userBatchEdit" parameterType="java.util.List">
            UPDATE `user`
            <set>
                <foreach collection="users" item="user" index="index" open="`name` = CASE `user_id`" close="END,">
                    WHEN #{user.userId} THEN #{user.name}
                </foreach>
                <foreach collection="users" item="user" index="index" open="`age` = CASE `user_id`" close="END,">
                    WHEN #{user.userId} THEN #{user.age}
                </foreach>
                <foreach collection="users" item="user" index="index" open="`description` = CASE `user_id`" close="END,">
                    WHEN #{user.userId} THEN #{user.description}
                </foreach>
            </set>
            <where>
                <foreach collection="users" item="user" index="index" open="`user_id` IN(" close=")" separator=",">
                    #{user.userId}
                </foreach>
            </where>
        </update>

## 5、实际拼接执行 SQL ##

UPDATE `user` 
    SET `name` = CASE `user_id` 
    		WHEN 'user-1' THEN '哈哈'
    		WHEN 'user-2' THEN '嘻嘻' 
    		WHEN 'user-3' THEN '啧啧' 
    	END,
    	`age` = CASE `user_id` 
    		WHEN 'user-1' THEN 18
    		WHEN 'user-2' THEN 10
    		WHEN 'user-3' THEN 20
    	END ,
    	`description` = CASE `user_id` 
    		WHEN 'user-1' THEN '我是哈哈'
    		WHEN 'user-2' THEN '我是嘻嘻'
    		WHEN 'user-3' THEN '我是啧啧'
    	END 
    WHERE
    	`user_id` IN ( 'user-1', 'user-2', 'user-3' )