mybatis五种插入方式测评
文章目录
前期准备
-
创建一个Springboot项目
-
添加依赖:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--Mybatis依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> <!--Mybatis-Plus依赖--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency>
-
修改yml
server: port: 8080 spring: datasource: username: mysql用户名 password: mysql密码 url: jdbc:mysql://localhost:3306/数据库名字?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.cj.jdbc.Driver mybatis: mapper-locations: classpath:mapper/*.xml
-
创建user表
-
创建User类
@Data public class User { private int id; private String username; private String password; }
-
创建UserMapper
@Mapper public interface UserMapper { Integer insertUsers(User user);
-
创建UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.mapper.UserMapper"> <insert id="insertUsers"> INSERT INTO user (username, password) VALUES(#{username}, #{password}) </insert> </mapper>
1. Service里用for循环
创建UserService类
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public void InsertUsers(){
long start = System.currentTimeMillis();
for(int i = 0 ;i < 10000; i++) {
User user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userMapper.insertUsers(user);
}
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
}
}
花费:482355ms
2. MyBatis的手动批量提交
@Resource
private SqlSessionTemplate sqlSessionTemplate;
public void InsertUsers(){
//关闭自动提交
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
long start = System.currentTimeMillis();
for(int i = 0 ;i < 10000; i++) {
User user = new User();
user.setId(i+1);
user.setUsername("name" + i);
user.setPassword("password" + i);
userMapper.insertUsers(user);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
}
一万条数据总耗时:273523ms
瞬间变快
3. MyBatis以集合方式批量新增(推荐)
-
修改UserMapper.xml
<insert id="insertUsers"> INSERT INTO user (username, password) VALUES <foreach collection ="userList" item="user" separator =","> (#{user.username}, #{user.password}) </foreach> </insert>
-
修改UserMapper
package com.example.demo.mapper; import com.example.demo.entity.User; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface UserMapper { // Integer insertUsers(User user); Integer insertUsers(List<User> userList); }
-
更新UserService
public void InsertUsers(){ long start = System.currentTimeMillis(); List<User> userList = new ArrayList<>(); User user; for(int i = 0 ;i < 10000; i++) { user = new User(); user.setUsername("name" + i); user.setPassword("password" + i); userList.add(user); } userMapper.insertUsers(userList); long end = System.currentTimeMillis(); System.out.println("一万条数据总耗时:" + (end-start) + "ms" ); }
一万条数据总耗时:958ms
太牛了!
4. MyBatis-Plus提供的SaveBatch方法
继承BaseMapper
saveBatch的批量保存
@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> {
@Resource
private UserMapper userMapper;
public void InsertUsers(){
long start = System.currentTimeMillis();
List<User> userList = new ArrayList<>();
User user;
for(int i = 0 ;i < 10000; i++) {
user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userList.add(user);
}
saveBatch(userList);
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
}
}
一万条数据总耗时:267316ms
5. MyBatis-Plus提供的InsertBatchSomeColumn方法(推荐)
-
编写EasySqlInjector 自定义类
public class EasySqlInjector extends DefaultSqlInjector { @Override public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) { // 注意:此SQL注入器继承了DefaultSqlInjector(默认注入器),调用了DefaultSqlInjector的getMethodList方法,保留了mybatis-plus的自带方法 List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo); methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE)); return methodList; } }
-
添加配置类
MybatisPlusConfig
@Configuration public class MybatisPlusConfig { @Bean public EasySqlInjector sqlInjector() { return new EasySqlInjector(); } }
-
修改UserService
@Service public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> { @Resource private UserMapper userMapper; public void InsertUsers(){ long start = System.currentTimeMillis(); List<User> userList = new ArrayList<>(); User user; for(int i = 0 ;i < 10000; i++) { user = new User(); user.setUsername("name" + i); user.setPassword("password" + i); userList.add(user); } userMapper.insertBatchSomeColumn(userList); long end = System.currentTimeMillis(); System.out.println("一万条数据总耗时:" + (end-start) + "ms" ); } }
-
添加EasyBaseMapper接口
public interface EasyBaseMapper<T> extends BaseMapper<T> { /** * 批量插入 仅适用于mysql * * @param entityList 实体列表 * @return 影响行数 */ Integer insertBatchSomeColumn(Collection<T> entityList); }
-
修改UserMapper
继承EasyBaseMapper
@Mapper public interface UserMapper<T> extends EasyBaseMapper<User> { }
运行:一万条数据总耗时:1156ms
!!!!
学习于:https://mp.weixin.qq.com/s/b6k3i-Jln3ojkujGMejaZA