使用java代码向mysql数据库插入100万条数据

使用java代码向mysql数据库插入100万条数据

使用springboot集成Mysql数据库，并使用java代码循环向msql数据库插入100万条数据，并测试插入时间

一、使用工具

idea代码开发工具
mysq5.7.38
Navicat可视化工具

二、项目结构图

创建springboot项目启动类

MySpringBootApplication.java

package com.xiaoa;

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

@SpringBootApplication
public class MySpringBootApplication {

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

}

创建插入数据类

MySchedulRunner.java,此类使用 JdbcTemplate 执行拼接的sql语句。

package com.xiaoa.run;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MySchedulRunner implements CommandLineRunner {
    private static final Logger LOGGER = LoggerFactory.getLogger(MySchedulRunner.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) {

        /*
         *向数据库t_user表循环插入数据
         */
        String sql = "";
        long t1 = System.currentTimeMillis();
        int batchNum = 1000000;
        for (int i = 1; i <= batchNum; i++) {
            sql = "INSERT INTO t_user VALUES (" + i + ",'张三', 20, '男', '2019-01-01 00:00:01')";
            jdbcTemplate.execute(sql);
        }
        long t2 = System.currentTimeMillis();
        LOGGER.info("插入{}条数据，耗时{}秒", batchNum, (t2 - t1) / 1000);

    }

三、结论

数据成功插入到myql数据库中！

经过测试此代码成功将100万条数据插入到了mysql数据库，耗时2116秒，约1000000/2116=472条/秒，速度比手工插入还是快很多的！

四、表结构与建表语句

表结构

使用Navicat查看表结构

建表语句

CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `createtime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

pom.xml与application.yml

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 http://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.0.4.RELEASE</version>
    </parent>

    <groupId>com.xiaoa</groupId>
    <artifactId>springboot-mysql</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>xiaoa-mysql</name>
    <description>springboot集成mysql</description>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MySQL连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <!--<version>2.0.4.RELEASE</version>-->
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>


</project>

配置文件application.yml

spring:
  #DB Configuration:
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.88.128:3306/canel?useUnicode=true&characterEncoding=utf8
    username: root
    password: 123456
server:
  port: 8085

链接: 完整代码