数据库批量添加数据的方法

使用查询列表的办法来批量添加数据。
比如：

insert into 表名 (字段名) select 字段名 from 自定义表

1. 在数据库函数这里写dbo.split方法，分割字符串

SQL实现split函数，自定义分割字符，自定义取出第几个分割字符前的字符串
自定义取出第几个分割字符前的字符串，默认位置（0）
格式：dbo.split(字段名,‘分隔字符’,取出的第几个字符串)
如果没有分隔的字符，则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。

USE [数据库名]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 2021/9/7 21:04:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
 @SplitString varchar(8000),-- nvarchar(4000)
 @Separator varchar(2) = ',' 
)
RETURNS @SplitStringsTable TABLE
(
 [id] int identity(1,1),
 [value] varchar(8000)-- nvarchar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString)) 
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=len(@SplitString)+1;
        
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
        
        if(len(@ReturnText)>0)
			INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);        
        SELECT @CurrentIndex=@NextIndex+1;
    END
    RETURN;
END

2. 批量插入数据时，现将列表值分割，再从分割后的表里进行插入。

insert into 表1名 ( 字段名)
SELECT pn.value as pn,isnull(k.列名2,'') as 字段名,'{0}' as Years,'{1}'  as Months,t.value as TotalNum,pmc.value as pmc
 FROM dbo.split('{2}',',') pn
LEFT  JOIN  表2名 k on k.列名1=pn.value
INNER JOIN (SELECT * FROM dbo.split('{3}',',')) as t on pn.id=t.id //查找分割后的值
INNER JOIN (SELECT * FROM dbo.split('{4}',',')) as pmc on pn.id=pmc.id
INNER JOIN (SELECT * FROM dbo.split('{5}',',')) as w on pn.id=w.id
LEFT  JOIN  S_PN as sp on sp.PN=pn.value", 字段名1...字段名6) //字段分别对应{}里的值

3. 下面介绍SQL server的一个免费插件，可以快速搜索定位SqlServer里面的文件名，关键字，函数等

https://www.red-gate.com/products/ SqlServer查询工具

实现层次1：使用PreparedStatment一行行插入

public static void InsertTypeOne(int rows) {
    // 一行行插入
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.GetConnection();
        String sqlstr = "insert into user_info(name,email)values(?,?)";
        ps = conn.prepareStatement(sqlstr);
        for (int i = 1; i <= rows; i++) {
            ps.setObject(1, "name_" + i);
            ps.setObject(2, i + "@gmail.com");
            ps.execute();
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.CloseResource(conn, ps);
    }
}

实现层次2：使用addBatch(),executeBatch(),clearBatch()攒一波批量插入

public static void InsertTypeTwo(int rows) {
    // 使用addBatch(),executeBatch(),clearBatch() 批量插入数据
    // mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
    // ?rewriteBatchedStatements=true 写在配置文件的url后面
    // 如之前是 jdbc:mysql://localhost:3306/stock?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
    // 更新为  jdbc:mysql://localhost:3306/stock?rewriteBatchedStatements=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.GetConnection();
        String sqlstr = "insert into user_info(name,email)values(?,?)";
        ps = conn.prepareStatement(sqlstr);
        for (int i = 1; i <= rows; i++) {
            ps.setObject(1, "name_" + i);
            ps.setObject(2, i + "@gmail.com");
            // 1. 攒sql
            ps.addBatch();
            if (i % 500 == 0){
                // 2. 攒一批执行一次
                ps.executeBatch();
                // 3. 执行后要清空一下
                ps.clearBatch();
            }else if (i == rows){
                ps.executeBatch();
                ps.clearBatch();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.CloseResource(conn, ps);
    }
}

实现层次3：在2的基础上，关闭自动提交

public static void InsertTypeThree(int rows) {
    // 插入数据时先禁止自动提交，数据全部execute后，再进行提交，最后再恢复自动提交
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.GetConnection();
        // 1. 设置不自动提交数据
        conn.setAutoCommit(false);
        String sqlstr = "insert into user_info(name,email)values(?,?)";
        ps = conn.prepareStatement(sqlstr);
        for (int i = 1; i <= rows; i++) {
            ps.setObject(1, "name_" + i);
            ps.setObject(2, i + "@gmail.com");
            // 1. 攒sql
            ps.addBatch();
            if (i % 500 == 0) {
                // 2. 攒一批执行一次
                ps.executeBatch();
                // 3. 执行后要清空一下
                ps.clearBatch();
            } else if (i == rows) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        // 2. 提交数据
        conn.commit();
        // 3. 提交完再设置自动提交
        conn.setAutoCommit(true);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.CloseResource(conn, ps);
    }
}

在本地测试时，效率如下：

public static void main(String[] args) {
    int rows = 20000;
    long tstart = System.currentTimeMillis();
    //        InsertTypeOne(rows);// 插入 20000 行数据，共耗时 41748
    //        InsertTypeTwo(rows);// 插入 20000 行数据，共耗时 904
    InsertTypeThree(rows);// 插入 20000 行数据，共耗时 846
    long tend = System.currentTimeMillis();
    System.out.println("插入 " + rows + " 行数据，共耗时 " + (tend - tstart));
}

其中，方式3在数据量增大的情况下，是会有明显地效率提升。这里只使用了2万行数据，体现得不明显。

package batchinsert;

import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class BatchInsert {
    public static void main(String[] args) {
        int rows = 20000;
        long tstart = System.currentTimeMillis();
//        InsertTypeOne(rows);// 插入 20000 行数据，共耗时 41748
//        InsertTypeTwo(rows);// 插入 20000 行数据，共耗时 904
        InsertTypeThree(rows);// 插入 20000 行数据，共耗时 846
        long tend = System.currentTimeMillis();
        System.out.println("插入 " + rows + " 行数据，共耗时 " + (tend - tstart));
    }

    public static void InsertTypeOne(int rows) {
        // 一行行插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.GetConnection();
            String sqlstr = "insert into user_info(name,email)values(?,?)";
            ps = conn.prepareStatement(sqlstr);
            for (int i = 1; i <= rows; i++) {
                ps.setObject(1, "name_" + i);
                ps.setObject(2, i + "@gmail.com");
                ps.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(conn, ps);
        }
    }

    public static void InsertTypeTwo(int rows) {
        // 使用addBatch(),executeBatch(),clearBatch() 批量插入数据
        // mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
        // ?rewriteBatchedStatements=true 写在配置文件的url后面
        // 如之前是 jdbc:mysql://localhost:3306/stock?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
        // 更新为  jdbc:mysql://localhost:3306/stock?rewriteBatchedStatements=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.GetConnection();
            String sqlstr = "insert into user_info(name,email)values(?,?)";
            ps = conn.prepareStatement(sqlstr);
            for (int i = 1; i <= rows; i++) {
                ps.setObject(1, "name_" + i);
                ps.setObject(2, i + "@gmail.com");
                // 1. 攒sql
                ps.addBatch();
                if (i % 500 == 0) {
                    // 2. 攒一批执行一次
                    ps.executeBatch();
                    // 3. 执行后要清空一下
                    ps.clearBatch();
                } else if (i == rows) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(conn, ps);
        }
    }

    public static void InsertTypeThree(int rows) {
        // 插入数据时先禁止自动提交，数据全部execute后，再进行提交，最后再恢复自动提交
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.GetConnection();
            // 1. 设置不自动提交数据
            conn.setAutoCommit(false);
            String sqlstr = "insert into user_info(name,email)values(?,?)";
            ps = conn.prepareStatement(sqlstr);
            for (int i = 1; i <= rows; i++) {
                ps.setObject(1, "name_" + i);
                ps.setObject(2, i + "@gmail.com");
                // 1. 攒sql
                ps.addBatch();
                if (i % 500 == 0) {
                    // 2. 攒一批执行一次
                    ps.executeBatch();
                    // 3. 执行后要清空一下
                    ps.clearBatch();
                } else if (i == rows) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            // 2. 提交数据
            conn.commit();
            // 3. 提交完再设置自动提交
            conn.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(conn, ps);
        }
    }
}

1. addBatch(sql)

Class.forName("com.mysql.jdbc.Driver");//指定连接类型
Connection con = DriverManager.getConnection(url, username, password);
PreparedStatement pst = con.prepareStatement("");
for (int i = 0; i < 10; i++) {
    StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO extenal_studentcj(grade,clazz,zkzh,NAME,scoretext,times) VALUES(");
    sql.append("'").append(i).append("',");
    sql.append("'").append(i).append("',");
    sql.append("'").append(i).append("',");
    sql.append("'").append(i).append("',");
    sql.append("'").append(i).append("',");
    sql.append("'").append(i).append("'");
    sql.append(");");
    pst.addBatch(sql.toString());
}
 
 
pst.executeBatch();
pst.close();
con.close();

2. addBatch()

Class.forName("com.mysql.jdbc.Driver");//指定连接类型
        Connection con = DriverManager.getConnection(url, username, password);
 
        String sql = "INSERT INTO extenal_studentcj(grade,clazz,zkzh,NAME,scoretext,times) VALUES(?,?,?,?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        for (int i = 0; i < 10; i++) {
            int idx = 1;
            pst.setString(idx++, i + "");
            pst.setString(idx++, i + "");
            pst.setString(idx++, i + "");
            pst.setString(idx++, i + "");
            pst.setString(idx++, i + "");
            pst.setLong(idx++, i);
            pst.addBatch();
        }
        pst.executeBatch();
        pst.close();
        con.close();

后来才发现要批量执行的话，JDBC连接URL字符串中需要新增一个参数：rewriteBatchedStatements=true

例如：jdbc:mysql://127.0.0.1:8080/xihudb?rewriteBatchedStatements=true

没有 rewriteBatchedStatements=true 则都是forEach. 开启后 addBatch(),addBatch(sql)使用的都是一次发送

3. 拼接SQL语句

拼接SQL

<insert id="insert" parameterType="com.Info">
    insert into pp (str1, str2)
    values
    <foreach collection="ps" item="p" separator=",">
        (#{p.str1,jdbcType=VARCHAR},
        #{p.str2,jdbcType=VARCHAR},
    </foreach>
</insert>

三者之间的相对速度比较：
JDBC BATCH（1）> Mybatis BATCH（2） > 拼接SQL（4）