SpringBoot使用mybatis批量新增500万数据到mysql数据库Demo

最新推荐文章于 2024-06-12 18:23:20 发布

谁不想飞舞青春

最新推荐文章于 2024-06-12 18:23:20 发布

阅读量1.4k

点赞数

分类专栏： SpringBoot篇文章标签： spring boot mybatis 数据库 mysql java

本文链接：https://blog.csdn.net/weixin_48040732/article/details/131498882

版权

SpringBoot篇专栏收录该内容

14 篇文章 0 订阅

订阅专栏

SpringBoot使用mybatis批量新增500万数据到mysql数据库Demo

说明
项目Demo代码地址
项目目录
mysql对应表建表语句
pom.xml
application.yml配置类
启动类代码
OrderInfo 实体类
TestController控制层
接口层TestService
TestServiceImpl实现层
TestDao数据接口层
dao层对应mapper.xml自定义sql语句
测试结果如下

说明

记录下学习mysql批量新增百万数据。留着以后备用。
本地环境mysql安装的5.7版本,项目使用jdk1.8版本，项目使用的mysql驱动版本为8.0版本。

项目Demo代码地址

SpringBoot使用mybatis批量新增500万数据到mysql数据库Demo

项目目录

在这里插入图片描述

mysql对应表建表语句

CREATE TABLE `order_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `period` int(11) NOT NULL COMMENT '账期月份',
  `amount` decimal(20,2) NOT NULL COMMENT '金额',
  `user_name` varchar(20) NOT NULL COMMENT '下单人',
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator` varchar(20) NOT NULL COMMENT '创建人',
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `modifier` varchar(20) NOT NULL COMMENT '修改人',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_period` (`period`),
  KEY `idx_modified` (`modified`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单信息表';

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>batching</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>batching</name>
    <description>batching</description>
    <properties>
        <java.version>1.8</java.version>
        <!--下列版本都是2022/04/16最新版本,都是父项目的基本依赖,用来子项目继承父项目依赖-->
        <pagehelper-starter.version>1.4.2</pagehelper-starter.version>
        <mybatis.version>3.5.9</mybatis.version>
        <mysql-connector.version>8.0.28</mysql-connector.version>
        <druid.version>1.2.9</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--MyBatis分页插件1.4.2版本才支持spring-boot2.6.6-->
        <!--pagehelper分页官网:https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter/-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>${pagehelper-starter.version}</version>
        </dependency>
        <!-- MyBatis就是用来创建数据库连接进行增删改查等操作,提供了原生JDBC,如Connection,Statement,ResultSet这些底层-->
        <!-- MyBatis官网:https://mybatis.org/mybatis-3/zh/dependency-info.html-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <!--Mysql数据库驱动-->
        <!--Mysql驱动官网:https://mvnrepository.com/artifact/mysql/mysql-connector-java/-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector.version}</version>
        </dependency>
        <!--集成druid连接池-->
        <!--druid版本官网:https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.yml配置类

server:
  port: 8080

mybatis:
  mapper-locations:
    - classpath:mapper/*.xml #找到mybatis位置,自定义sql语句
  #当查询语句中resultType="java.util.HashMap"时,如果返回的字段值为null时,设置如下参数为true,让它返回
  configuration:
    call-setters-on-nulls: true

spring:
  datasource:
    #mysql批量新增需要在url后面添加rewriteBatchedStatements=true才能生效
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
    driverClassName: com.mysql.cj.jdbc.Driver #mysql8.0驱动,mysql5.7驱动是com.mysql.jdbc.Driver
    username: 你自己数据库的用户名
    password: 你自己数据库的密码
    druid:
      initial-size: 3 #连接池初始大小
      min-idle: 5 #最小空闲连接数
      max-active: 20 #最大空闲连接数
      web-stat-filter:
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" #不统计这些请求数据
      stat-view-servlet: #访问监控网页的用户名和密码
        #默认为true,内置监控页面首页/druid/index.html
        enabled: true
        login-username: druid
        login-password: druid

启动类代码

package com.example.batching;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class BatchingApplication {

    public static void main(String[] args) {
        SpringApplication.run(BatchingApplication.class, args);
    }

}

OrderInfo 实体类

package com.example.batching.entity;

import java.math.BigDecimal;

public class OrderInfo {

    private int id;
    private int period;//账期月份
    private BigDecimal amount;//金额
    private String userName;//下单人
    private String phone;//手机号
    private String created;//创建时间
    private String creator;//创建人
    private String modified;//修改时间
    private String modifier;//修改人

    public int getId() {
        return id;
    }

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

    public int getPeriod() {
        return period;
    }

    public void setPeriod(int period) {
        this.period = period;
    }

    public BigDecimal getAmount() {
        return amount;
    }

    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getCreated() {
        return created;
    }

    public void setCreated(String created) {
        this.created = created;
    }

    public String getCreator() {
        return creator;
    }

    public void setCreator(String creator) {
        this.creator = creator;
    }

    public String getModified() {
        return modified;
    }

    public void setModified(String modified) {
        this.modified = modified;
    }

    public String getModifier() {
        return modifier;
    }

    public void setModifier(String modifier) {
        this.modifier = modifier;
    }
}

TestController控制层

package com.example.batching.controller;

import com.example.batching.entity.OrderInfo;
import com.example.batching.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping(value = "/order")
public class TestController {

    @Autowired
    private TestService testService;

    //批量新增数据处理
    @PostMapping(value = "/batchSave")
    public String batchSave() {
        //随机生成电话号码
        String[] start = {"130", "131", "132", "133", "134", "150", "151", "155", "158", "166", "180", "181", "184", "185", "188"};
        List<OrderInfo> orderInfoList=new ArrayList<>();
        //生成500万数据批量新增到mysql数据库里面
        for(int i=1;i<=5000000;i++){
            OrderInfo orderInfo=new OrderInfo();
            orderInfo.setPeriod(202206);
            orderInfo.setAmount(new BigDecimal(i));
            orderInfo.setUserName("用户"+i);
            orderInfo.setPhone(start[(int) (Math.random() * start.length)]+(10000000+(int)(Math.random()*(99999999-10000000+1))));
            orderInfo.setCreator("用户"+i);
            orderInfo.setModifier("用户"+i);
            orderInfoList.add(orderInfo);
            //每一万条数据进行批量新增
            if(i%10000==0){
                testService.batchSave(orderInfoList);
                //新增完成后清空list集合防止内存溢出
                orderInfoList.clear();
                System.out.println("当前已新增完数据："+i+"行");
            }
        }
        return "成功";
    }
}

接口层TestService

package com.example.batching.service;

import com.example.batching.entity.OrderInfo;

import java.util.List;

public interface TestService {
    void batchSave(List<OrderInfo> orderInfoList);
}

TestServiceImpl实现层

package com.example.batching.service.impl;

import com.example.batching.dao.TestDao;
import com.example.batching.entity.OrderInfo;
import com.example.batching.service.TestService;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class TestServiceImpl implements TestService {

    @Resource
    private TestDao testDao;
    @Resource
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public void batchSave(List<OrderInfo> orderInfoList) {
        //批量新增处理,需要在jdbc连接那里添加rewriteBatchedStatements=true属性，批量新增才能生效
        // ExecutorType.SIMPLE: 这个执行器类型不做特殊的事情。它为每个语句的执行创建一个新的预处理语句。自动提交不关闭的前提下，默认设置是这个
        // ExecutorType.REUSE: 这个执行器类型会复用预处理语句。
        // ExecutorType.BATCH: 这个执行器会批量执行所有更新语句,如果 SELECT 在它们中间执行还会标定它们是 必须的,来保证一个简单并易于理解的行为。
        //如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            TestDao testMapper = sqlSession.getMapper(TestDao.class);
            orderInfoList.stream().forEach(orderInfo -> testMapper.batchSave(orderInfo));
            //提交数据
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}

TestDao数据接口层

package com.example.batching.dao;

import com.example.batching.entity.OrderInfo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TestDao {
    void batchSave(OrderInfo orderInfo);
}

dao层对应mapper.xml自定义sql语句

<?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">
<!-- namespace必须指向Dao接口 -->
<mapper namespace="com.example.batching.dao.TestDao">

    <insert id="batchSave" parameterType="com.example.batching.entity.OrderInfo">
        INSERT INTO order_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="period != null">
                period,
            </if>
            <if test="amount != null">
                amount,
            </if>
            <if test="userName != null">
                user_name,
            </if>
            <if test="phone != null">
                phone,
            </if>
            <if test="creator != null">
                creator,
            </if>
            <if test="modifier != null">
                modifier,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="period != null">
                #{period},
            </if>
            <if test="amount != null">
                #{amount},
            </if>
            <if test="userName != null">
                #{userName},
            </if>
            <if test="phone != null">
                #{phone},
            </if>
            <if test="creator != null">
                #{creator},
            </if>
            <if test="modifier != null">
                #{modifier},
            </if>
        </trim>
    </insert>
</mapper>