MyBatis-plus的批量插入方式对比分析

MyBatis-plus的批量插入方式对比分析

  【摘要】Mybatis批量插入一直是开发者重点关注的问题，本文列举了Mybatis的五种插入方式进行对比分析，验证了五种批量插入的方式的优先级。

1 准备工作

1.1 新建spring项目

  略。

1.2 导入pom.xml依赖

<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>

1.3 配置yml文件

server:port: 8080spring:datasource:username: mysql用户名password: mysql密码url: jdbc:mysql://localhost:3306/数据库名字?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTCdriver-class-name: com.mysql.cj.jdbc.Drivermybatis:mapper-locations: classpath:mapping/*.xml

1.4 创建插入模型

@Data
public class User {private int id;private String username;private String password;
}

2 测试

2.1 Mybatis利用For循环批量插入

1、编写UserService服务类，测试一万条数据的耗时情况：

@Service
public class UserService {@Resourceprivate 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" );}}

2、编写UserMapper接口

@Mapper
public interface UserMapper {Integer insertUsers(User user);
}

3、编写UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ithuang.demo.mapper.UserMapper"><insert id="insertUsers">INSERT INTO user (username, password)VALUES(#{username}, #{password})</insert>
</mapper>

4、进行单元测试

@SpringBootTest
class DemoApplicationTests {@Resourceprivate UserService userService;@Testpublic void insert(){userService.InsertUsers();}
}

5、输出结果
  一万条数据耗时26348ms

2.2 MyBatis的手动批量提交

1、其他保持不变，Service层作稍微的变化

@Service
public class UserService {@Resourceprivate UserMapper userMapper;@Resourceprivate 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.setUsername("name" + i);user.setPassword("password" + i);userMapper.insertUsers(user);}sqlSession.commit();long end = System.currentTimeMillis();System.out.println("一万条数据总耗时：" + (end-start) + "ms" );}
}

2、结果输出
  一万条数据总耗时：24516ms。

2.3 Mybatis以集合方式批量新增

1、编写UserService服务类

@Service
public class UserService {@Resourceprivate 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.insertUsers(userList);long end = System.currentTimeMillis();System.out.println("一万条数据总耗时：" + (end-start) + "ms" );}
}

2、编写UserMapper接口

@Mapper
public interface UserMapper {Integer insertUsers(List<User> userList);
}

3、编写UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ithuang.demo.mapper.UserMapper"><insert id="insertUsers">INSERT INTO user (username, password)VALUES<foreach collection ="userList" item="user" separator =",">(#{user.username}, #{user.password})</foreach></insert>
</mapper>

4、输出结果
  一万条数据总耗时：521ms

2.4 MyBatis-Plus提供的SaveBatch方法

1、编写UserService服务

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> {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" );}
}

2、编写UserMapper接口

@Mapper
public interface UserMapper extends BaseMapper<User> {}

3、单元测试结果

  一万条数据总耗时：24674ms

2.5 MyBatis-Plus提供的InsertBatchSomeColumn方法

1、编写EasySqlInjector 自定义类

public class EasySqlInjector extends DefaultSqlInjector {@Overridepublic 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;}}

2、定义核心配置类注入此Bean

@Configuration
public class MybatisPlusConfig {

@Bean
public EasySqlInjector sqlInjector() {return new EasySqlInjector();
}

}
3、编写UserService服务类

public class UserService{@Resourceprivate 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" );}
}

4、编写EasyBaseMapper接口

public interface EasyBaseMapper<T> extends BaseMapper<T> {/*** 批量插入 仅适用于mysql** @param entityList 实体列表* @return 影响行数*/Integer insertBatchSomeColumn(Collection<T> entityList);
}

5、编写UserMapper接口

@Mapper
public interface UserMapper<T> extends EasyBaseMapper<User> {}

6、单元测试结果

  一万条数据总耗时：575ms

2.6 JDBC原生的批量插入

1、编写JDBC池化工具类

public class JDBCDruidUtils {private static DataSource dataSource;private static Connection conn;/*创建数据Properties集合对象加载加载配置文件*/static {Properties pro = new Properties();//加载数据库连接池对象try {//获取数据库连接池对象pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(pro);} catch (Exception e) {e.printStackTrace();}}/*获取连接*/public static Connection getConnection() throws SQLException {return dataSource.getConnection();}/*** 关闭conn,和 statement独对象资源** @param connection* @param statement* @MethodName: close* @return: void*/public static void close(Connection connection, Statement statement) {if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}/*** 关闭 conn , statement 和resultset三个对象资源** @param connection* @param statement* @param resultSet* @MethodName: close* @return: void*/public static void close(Connection connection, Statement statement, ResultSet resultSet) {close(connection, statement);if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {             e.printStackTrace();}}}/*获取连接池对象*/public static DataSource getDataSource() {return dataSource;}
}

# druid.properties配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
username=用户名
password=密码
initialSize=10
maxActive=50
maxWait=60000

2、编写UserService服务类

public void InsertUsersByJdbc() {long start = System.currentTimeMillis();Connection connection = null;PreparedStatement ps = null;try {connection = JDBCDruidUtils.getConnection();//控制事务:默认不提交connection.setAutoCommit(false);String sql = "INSERT INTO user (username, password) VALUES (?, ?)";ps = connection.prepareStatement(sql);User user;for (int i = 0; i < 1000000; i++) {user = new User();user.setUsername("name" + i);user.setPassword("password" + i);ps.setString(1, user.getUsername());ps.setString(2, user.getPassword());//将一组参数添加到此 PreparedStatement 对象的批处理命令中。ps.addBatch();}//执行批处理ps.executeBatch();//手动提交事务connection.commit();} catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCDruidUtils.close(connection, ps);}long end = System.currentTimeMillis();System.out.println("一万条数据总耗时：" + (end - start) + "ms");}

3、输出结果
  1万数据总耗时19000ms。

3 总结

  大量数据的场景下性能对比：InsertBatchSomeColumn>自定义xml以集合的方式>Jdbc原生>SaveBatch>手动for循环批量>自动for循环批量。
  网上很多人都说JDBC原生性能很好，但是我发现其非常差，有可能是我使用的是mybatis-plus依赖，如果这是推论正确，那就可以证明mybatis-plus在mybatis的基础上不仅增强了功能也增强了性能。所以可以得出结论：开发中用mybatis-plus是没有错的，如果想提高性能，只能实施其他方案，比如分库分表，千万别想着JDBC原生性能更好。