Mybatis—批量插入

批量插入

Mybatis批量插入

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver  
spring.datasource.url=jdbc:mysql:///test2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false  
spring.datasource.username=root  
spring.datasource.password=root 

mybatis.mapper-locations=classpath*:/mapper/**Mapper.xml

（1）xml中foreach sql拼接

mapper

@Mapper  
public interface UserMapper2 {  
    void batchInsert2(List<User> users);  
}

UserMapper2.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
<mapper namespace="com.liwang.audition.mapper.UserMapper2">  

    <insert id="batchInsert2">  
        insert into user (username,password)  
        values  
        <foreach collection="users" item="item" index="index" separator=",">  
            (#{item.username},#{item.password})  
        </foreach>  
    </insert>  

</mapper>

Test

@Autowired  
private UserMapper2 userMapper2;  

@Test  
public void setSaveBatchWithM2() {  
    List<User> list = getUsers();  
    userMapper2.batchInsert2(list);  
}  

private List<User> getUsers() {  
    List<User> list = new ArrayList<>();  
    for (int i = 0; i < 200; i++) {  
        User user = new User();  
        user.setUsername("liwang" + i);  
        user.setPassword("123456");  
        list.add(user);  
    }  
    return list;  
}

（2）BATCH批处理

mapper

@Mapper  
public interface UserMapper2 {  
    void batchInsert(User user);  
}

UserMapper2.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
<mapper namespace="com.liwang.audition.mapper.UserMapper2">  

    <insert id="batchInsert">  
        insert into user(username,password) values(#{username},#{password})  
    </insert>  

</mapper>

Test

@Autowired  
SqlSessionFactory sqlSessionFactory;  

@Test  
public void saveBatchWithM() {  
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);  
    try {  
        UserMapper2 mapper = sqlSession.getMapper(UserMapper2.class);  
        List<User> list = getUsers();  
        for (int i = 0; i < list.size(); i++) {  
            mapper.batchInsert(list.get(i));  
        }  
        sqlSession.commit();  
        sqlSession.clearCache();  
    } catch (Exception e) {  
        sqlSession.rollback();  
    } finally {  
        sqlSession.close();  
    }  
}  

private List<User> getUsers() {  
    List<User> list = new ArrayList<>();  
    for (int i = 0; i < 200; i++) {  
        User user = new User();  
        user.setUsername("liwang" + i);  
        user.setPassword("123456");  
        list.add(user);  
    }  
    return list;  
}

循环插入，统一提交。sqlsession.commit后，数据库才会显示有值。

Mybatis-plus批量插入

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver  
spring.datasource.url=jdbc:mysql:///test2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false  
spring.datasource.username=root  
spring.datasource.password=root

User

public class User implements Serializable {  

    @TableId(value = "id", type = IdType.AUTO)  
    private Long id;  
    private String username;  
    private String password;  

    @Override  
    public String toString() {  
        return "User{" +  
                "id=" + id +  
                ", username='" + username + '\'' +  
                ", password='" + password + '\'' +  
                '}';  
    }  

    public Long getId() {  
        return id;  
    }  

    public void setId(Long id) {  
        this.id = id;  
    }  

    public String getUsername() {  
        return username;  
    }  

    public void setUsername(String username) {  
        this.username = username;  
    }  

    public String getPassword() {  
        return password;  
    }  

    public void setPassword(String password) {  
        this.password = password;  
    }  

}

UserMapper

public interface UserMapper extends BaseMapper<User> {  
}

SaveBatchWithMP

public interface SaveBatchWithMP extends IService<User> {  
}

SaveBatchWithMPImpl

@Service  
public class SaveBatchWithMPImpl extends ServiceImpl<UserMapper, User> implements SaveBatchWithMP {  
}

Test

    @Autowired  
    SaveBatchWithMP saveBatchWithMP;  

    @Test  
    public void saveBatchWithMP() {  
        List<User> list = new ArrayList<>();  
        for (int i = 0; i < 200; i++) {  
            User user = new User();  
            user.setUsername("liwang" + i);  
            user.setPassword("123456");  
            list.add(user);  
        }  
        // 批量新增  
        saveBatchWithMP.saveBatch(list);  
        // 批量删除  

//        saveBatchWithMP.removeByIds(Arrays.asList("1","2"));  
    }

可以看看我的学习——个人博客： 网站：http://www.fuzm.wang/

—————————————————————————

很多知识都没有掌握，见谅，如有错误请指出，以期进步，感谢！。后续有新的学习，继续补充上来。