Mybatis 批量插入和更新小例

た入场券 2021-12-07 12:04 300阅读 0赞

SpringBoot配置Mybatis前文有博文，数据库mysql:

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

package com.example.demo.biz.dto;
    
    public class User {
    
        private int id;
    
        private String userName;
    
        private String passWord;
    
        private String gender;
    
        private String email;
    
        private String mobile;
    
        private String identity;
    
        private String nationality;
    
        private String description;
    
        private String address;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public void setUserName(String userName) {
            this.userName = userName;
        }
    
        public String getUserName() {
            return userName;
        }
    
        public String getPassWord() {
            return passWord;
        }
    
        public void setPassWord(String passWord) {
            this.passWord = passWord;
        }
    
        public String getGender() {
            return gender;
        }
    
        public String getEmail() {
            return email;
        }
    
        public String getMobile() {
            return mobile;
        }
    
        public String getIdentity() {
            return identity;
        }
    
        public String getNationality() {
            return nationality;
        }
    
        public String getDescription() {
            return description;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setGender(String gender) {
            this.gender = gender;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public void setMobile(String mobile) {
            this.mobile = mobile;
        }
    
        public void setIdentity(String identity) {
            this.identity = identity;
        }
    
        public void setNationality(String nationality) {
            this.nationality = nationality;
        }
    
        public void setDescription(String description) {
            this.description = description;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", userName='" + userName + '\'' +
                    ", passWord='" + passWord + '\'' +
                    ", gender='" + gender + '\'' +
                    ", email='" + email + '\'' +
                    ", mobile='" + mobile + '\'' +
                    ", identity='" + identity + '\'' +
                    ", nationality='" + nationality + '\'' +
                    ", description='" + description + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

<insert id="insertBatchUser" parameterType="java.util.List">
              insert into c_tbl_users (username,pwd,gender,email,mobile,identity,address)
              values
            <foreach collection="list" item="user" separator=",">
                (#{user.userName},#{user.passWord},#{user.gender},#{user.email},#{user.mobile},#{user.identity},#{user.address})
            </foreach>
        </insert>
    
        
        <update id="updateBatchUser" parameterType="java.util.List">
           update c_tbl_users
            <trim prefix="set" suffixOverrides=",">
                <trim prefix="address =case" suffix="end,">
                    <foreach collection="list" item="i" index="index">
                        <if test="i.address!=null">
                            when username=#{i.userName} then #{i.address}
                        </if>
                    </foreach>
                </trim>
    
                <trim prefix="email =case" suffix="end,">
                    <foreach collection="list" item="i" index="index">
                        <if test="i.email!=null">
                            when username=#{i.userName} then #{i.email}
                        </if>
                    </foreach>
                </trim>
            </trim>
            WHERE
            <foreach collection="list" separator="or" item="i" index="index" >
                username=#{i.userName}
            </foreach>
        </update>

package com.example.demo.biz.dao;
    
    import com.example.demo.biz.dto.User;
    import org.apache.ibatis.annotations.Param;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * xml  形式配置
     */
    @Repository
    public interface UserMapper {
        /**
         * 批量插入
         * @param userList
         */
        void insertBatchUser(List<User> userList);
    
        /**
         * 批量更新
         * @param userList
         */
        void updateBatchUser(List<User> userList);
    }

测试：

package com.example.demo.biz;
    
    import com.example.demo.biz.dao.UserMapper;
    import com.example.demo.biz.dto.User;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.ArrayList;
    import java.util.List;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class UserBatchTest {
        @Autowired
        protected UserMapper userMapper;
    
        @Test
        public void batchInsert(){
    
            List<User> list = new ArrayList<User>();
    
            for (int i = 0; i < 2 ; i++) {
                User user1 = new User();
                user1.setUserName("黑心寥"+i);
                user1.setPassWord("heixinliao");
                user1.setAddress("长安"+i);
                user1.setEmail("hahalong@163.com");
                user1.setGender("1");
                user1.setIdentity("321736165504567129");
                list.add(user1);
            }
    
            userMapper.insertBatchUser(list);
        }
    
        @Test
        public void batchUpdate(){
            List<User> list = getUserList();
            userMapper.updateBatchUser(list);
        }
    
        private List<User> getUserList(){
            List<User> list = new ArrayList<User>();
            for (int i = 0; i < 2 ; i++) {
                User user1 = new User();
                user1.setUserName("黑心寥"+i);
                user1.setPassWord("heixinliao");
                user1.setAddress("金陵"+i);
                user1.setEmail("hahalong@qq.com");
                user1.setGender("1");
                user1.setIdentity("321736165504567129");
                list.add(user1);
            }
            return list;
        }
    }

转载于:https://www.cnblogs.com/xiaozhuanfeng/p/10951209.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211207/caa3203dc5304ef6afb036056a89aea5.png