准备

表结构

CREATE TABLE `tb_users` (`id` varchar(255) NOT NULL,`name` varchar(100) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MyBatis配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--通过properties标签加载外部properties文件--><properties resource="jdbc.properties"/><!--自定义别名--><typeAliases><typeAlias type="com.mytest.domain.User" alias="user"/></typeAliases><!--数据源环境--><environments default="developement"><environment id="developement"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!--加载映射文件--><mappers><mapper resource="com/mytest/mapper/UserMapper.xml"/></mappers></configuration>

全部插入

逐条插入

mapper.xml

	<insert id="batchAddUser" parameterType="user">insert into tb_users(id, name, age)values (#{id}, #{name}, #{age})</insert>

测试代码

public class InsertTest {public static void main(String[] args) throws IOException {InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = factory.openSession();long start = System.currentTimeMillis();for (int i = 0; i < 300000; i++) {User user = new User();user.setId(UUID.randomUUID().toString());user.setName("插入第" + i + "条数据");user.setAge(i);sqlSession.insert("batchAddUser", user);sqlSession.commit();}// 插入数据库long total = System.currentTimeMillis() - start;System.out.println("共耗时：" + total + "ms");sqlSession.close();}
}

伴随着计算机磁盘高速运转下，共耗时
在这里插入图片描述

循环插入

mapper.xml

	<insert id="batchAddUser" parameterType="arraylist">insert into tb_users(id, name, age) values<foreach collection="list" index="index" item="u" separator=",">(#{u.id}, #{u.name}, #{u.age})</foreach></insert>

测试方法

public class InsertTest {public static void main(String[] args) throws IOException {InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = factory.openSession();long start = System.currentTimeMillis();List<User> lists = new ArrayList<>();for (int i = 0; i < 300000; i++) {User user = new User();user.setId(UUID.randomUUID().toString());user.setName("插入第" + i + "条数据");user.setAge(i);lists.add(user);}// 插入数据库sqlSession.insert("batchAddUser", lists);sqlSession.commit();long total = System.currentTimeMillis() - start;System.out.println("共耗时：" + total + "ms");sqlSession.close();}
}

借助列表插入在三个字段情况下耗时，但可能会报超出包最大异常，少量数据可取
在这里插入图片描述

分批插入

隔一段时间提交插入一次
mapper.xml

	<insert id="batchAddUser" parameterType="arraylist">insert into tb_users(id, name, age) values<foreach collection="list" index="index" item="u" separator=",">(#{u.id}, #{u.name}, #{u.age})</foreach></insert>

测试方法

public class InsertTest {public static void main(String[] args) throws IOException {InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = factory.openSession();long start = System.currentTimeMillis();List<User> lists = new ArrayList<>();for (int i = 0; i < 300000; i++) {User user = new User();user.setId(UUID.randomUUID().toString());user.setName("插入第" + i + "条数据");user.setAge(i);lists.add(user);if (i % 1000 == 0) {sqlSession.insert("batchAddUser", lists);sqlSession.commit();lists.clear();}}// 插入剩余数据if (!lists.isEmpty()) {sqlSession.insert("batchAddUser", lists);sqlSession.commit();}long total = System.currentTimeMillis() - start;System.out.println("共耗时：" + total + "ms");sqlSession.close();}
}

时间和循环插入差不多但可以避免超出包最大异常，还可以增大每次提交的数据量，进一步缩短时间
在这里插入图片描述

JDBC分批插入

public class JdbcInsertTest {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;String url = "jdbc:mysql:///mytest?serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";try {connection = DriverManager.getConnection(url, username, password);// 关闭自动提交事务，改为手动提交connection.setAutoCommit(false);long start = System.currentTimeMillis();String sqlInsert = "INSERT INTO tb_users (id, name, age) VALUES (?, ?, ?)";preparedStatement = connection.prepareStatement(sqlInsert);for (int i = 0; i < 300000; i++) {preparedStatement.setString(1, UUID.randomUUID().toString());preparedStatement.setString(2, "插入第" + i + "条数据");preparedStatement.setInt(3, i);preparedStatement.addBatch();if (i % 10000 == 0) {preparedStatement.executeBatch();connection.commit();}}// 处理剩余preparedStatement.executeBatch();connection.commit();long total = System.currentTimeMillis() - start;System.out.println("共耗时：" + total + "毫秒");} catch (SQLException e) {e.printStackTrace();} finally {try {if (connection != null) {connection.close();}if (preparedStatement != null) {preparedStatement.close();}} catch (SQLException e) {e.printStackTrace();}}}
}