本文主要讲述通过MyBatis、JDBC等做大数据量数据插入的案例和结果。
30万条数据插入插入数据库验证
- 实体类、mapper和配置文件定义
- User实体
- mapper接口
- mapper.xml文件
- jdbc.properties
- sqlMapConfig.xml
- 不分批次直接梭哈
- 循环逐条插入
- MyBatis实现插入30万条数据
- JDBC实现插入30万条数据
- 总结
验证的数据库表结构如下:
CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id', `username` varchar(64) DEFAULT NULL COMMENT '用户名称', `age` int(4) DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';
话不多说,开整!
实体类、mapper和配置文件定义
User实体
/** *
用户实体
* * @Author zjq * @Date 2021/8/3 */ @Data public class User { private int id; private String username; private int age; }mapper接口
public interface UserMapper { /** * 批量插入用户 * @param userList */ void batchInsertUser(@Param("list") List userList); }
mapper.xml文件
insert into t_user(username,age) values ( #{item.username}, #{item.age} )
jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/test jdbc.username=root jdbc.password=root
sqlMapConfig.xml
不分批次直接梭哈
MyBatis直接一次性批量插入30万条,代码如下:
@Test public void testBatchInsertUser() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session = sqlSessionFactory.openSession(); System.out.println("===== 开始插入数据 ====="); long startTime = System.currentTimeMillis(); try { List userList = new ArrayList(); for (int i = 1; i User user = new User(); user.setId(i); user.setUsername("共饮一杯无 " + i); user.setAge((int) (Math.random() * 100)); userList.add(user); } session.insert("batchInsertUser", userList); // 最后插入剩余的数据 session.commit(); long spendTime = System.currentTimeMillis()-startTime; System.out.println("成功插入 30 万条数据,耗时:"+spendTime+"毫秒"); } finally { session.close(); } }
p可以看到控制台输出:/p blockquote pCause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.超出最大数据包限制了,可以通过调整max_allowed_packet限制来提高可以传输的内容,不过由于30万条数据超出太多,这个不可取,梭哈看来是不行了 😅😅😅
既然梭哈不行那我们就一条一条循环着插入行不行呢
循环逐条插入
mapper接口和mapper文件中新增单个用户新增的内容如下:
/** * 新增单个用户 * @param user */ void insertUser(User user);
insert into t_user(username,age) values ( #{username}, #{age} )
调整执行代码如下:
@Test public void testCirculateInsertUser() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session = sqlSessionFactory.openSession(); System.out.println("===== 开始插入数据 ====="); long startTime = System.currentTimeMillis(); try { for (int i = 1; i User user = new User(); user.setId(i); user.setUsername("共饮一杯无 " + i); user.setAge((int) (Math.random() * 100)); // 一条一条新增 session.insert("insertUser", user); session.commit(); } long spendTime = System.currentTimeMillis()-startTime; System.out.println("成功插入 30 万条数据,耗时:"+spendTime+"毫秒"); } finally { session.close(); } } InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session = sqlSessionFactory.openSession(); System.out.println("===== 开始插入数据 ====="); long startTime = System.currentTimeMillis(); int waitTime = 10; try { List User user = new User(); user.setId(i); user.setUsername("共饮一杯无 " + i); user.setAge((int) (Math.random() * 100)); userList.add(user); if (i % 1000 == 0) { session.insert("batchInsertUser", userList); // 每 1000 条数据提交一次事务 session.commit(); userList.clear(); // 等待一段时间 Thread.sleep(waitTime * 1000); } } // 最后插入剩余的数据 if(!CollectionUtils.isEmpty(userList)) { session.insert("batchInsertUser", userList); session.commit(); } long spendTime = System.currentTimeMillis()-startTime; System.out.println("成功插入 30 万条数据,耗时:"+spendTime+"毫秒"); } catch (Exception e) { e.printStackTrace(); } finally { session.close(); } } InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session = sqlSessionFactory.openSession(); System.out.println("===== 开始插入数据 ====="); long startTime = System.currentTimeMillis(); int waitTime = 10; try { List User user = new User(); user.setId(i); user.setUsername("共饮一杯无 " + i); user.setAge((int) (Math.random() * 100)); userList.add(user); if (i % 1000 == 0) { session.insert("batchInsertUser", userList); // 每 1000 条数据提交一次事务 session.commit(); userList.clear(); } } // 最后插入剩余的数据 if(!CollectionUtils.isEmpty(userList)) { session.insert("batchInsertUser", userList); session.commit(); } long spendTime = System.currentTimeMillis()-startTime; System.out.println("成功插入 30 万条数据,耗时:"+spendTime+"毫秒"); } catch (Exception e) { e.printStackTrace(); } finally { session.close(); } } Connection connection = null; PreparedStatement preparedStatement = null; String databaseURL = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "root"; try { connection = DriverManager.getConnection(databaseURL, user, password); // 关闭自动提交事务,改为手动提交 connection.setAutoCommit(false); System.out.println("===== 开始插入数据 ====="); long startTime = System.currentTimeMillis(); String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)"; preparedStatement = connection.prepareStatement(sqlInsert); Random random = new Random(); for (int i = 1; i preparedStatement.setString(1, "共饮一杯无 " + i); preparedStatement.setInt(2, random.nextInt(100)); // 添加到批处理中 preparedStatement.addBatch(); if (i % 1000 == 0) { // 每1000条数据提交一次 preparedStatement.executeBatch(); connection.commit(); System.out.println("成功插入第 "+ i+" 条数据"); } } // 处理剩余的数据 preparedStatement.executeBatch(); connection.commit(); long spendTime = System.currentTimeMillis()-startTime; System.out.println("成功插入 30 万条数据,耗时:"+spendTime+"毫秒"); } catch (SQLException e) { System.out.println("Error: " + e.getMessage()); } finally { if (preparedStatement != null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }