快捷導(dǎo)航

Java實(shí)現(xiàn)十秒向MySQL插入百萬(wàn)條數(shù)據(jù)

更新時(shí)間：2022年11月17日 11:37:03 作者：子非魚(yú)呀

這篇文章主要為大家詳細(xì)介紹了Java如何實(shí)現(xiàn)十秒向MySQL插入百萬(wàn)條數(shù)據(jù)，文中的示例代碼講解詳細(xì)，對(duì)我們學(xué)習(xí)或工作有一定借鑒價(jià)值，需要的可以參考一下

mysql數(shù)據(jù)庫(kù)準(zhǔn)備
方式一：普通插入
方式二：使用批處理插入
方式三：通過(guò)連接配置url設(shè)置【&rewriteBatchedStatements=true】（設(shè)置重寫(xiě)批處理語(yǔ)句）
方式四：通過(guò)數(shù)據(jù)庫(kù)連接取消自動(dòng)提交，手動(dòng)提交數(shù)據(jù)

mysql數(shù)據(jù)庫(kù)準(zhǔn)備

private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    //封裝與數(shù)據(jù)庫(kù)建立連接的類(lèi)
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    //封裝異常類(lèi)
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

方式一：普通插入

package com.wt;
 
import org.junit.Test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @Author wt
 * @Date 2022/11/14 21:17
 * @PackageName:com.wt
 * @ClassName: TestAddBatch01
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch01 {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);//填充sql語(yǔ)句種得占位符
                ps.execute();//執(zhí)行sql語(yǔ)句
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百萬(wàn)條數(shù)據(jù)插入用時(shí)：" + (System.currentTimeMillis() - start)+"【單位：毫秒】");
 
    }
 
}

用時(shí)：62分鐘多

方式二：使用批處理插入

package com.wt;
 
import org.junit.Test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
//            connection.setAutoCommit(false);//取消自動(dòng)提交
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
 
                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
//            connection.commit();//所有語(yǔ)句都執(zhí)行完畢后才手動(dòng)提交sql語(yǔ)句
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百萬(wàn)條數(shù)據(jù)插入用時(shí)：" + (System.currentTimeMillis() - start)+"【單位：毫秒】");
 
    }
 
 
}

方式一、二總結(jié)：到此可以看出其實(shí)其處理程序及批處理是沒(méi)有起作用的，為此我們使用方式三

方式三：通過(guò)連接配置url設(shè)置【&rewriteBatchedStatements=true】（設(shè)置重寫(xiě)批處理語(yǔ)句）

url地址后注意添加【&rewriteBatchedStatements=true】

private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";

方法三較于方法二的改變是只是url地址上的改變，其它沒(méi)有任何修改

package com.wt;
 
import org.junit.Test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
 
                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百萬(wàn)條數(shù)據(jù)插入用時(shí)：" + (System.currentTimeMillis() - start)+"【單位：毫秒】");
 
    }
 
 
}

用時(shí)：【10秒左右】

到此批處理語(yǔ)句才正是生效

注意

數(shù)據(jù)庫(kù)連接的url設(shè)置了【&rewriteBatchedStatements=true】時(shí)，java代碼種的sql語(yǔ)句不能有分號(hào)【;】號(hào)，否則批處理語(yǔ)句打包就會(huì)出現(xiàn)錯(cuò)誤，導(dǎo)致后面的sql語(yǔ)句提交出現(xiàn)【BatchUpdateException】異常

方式四：通過(guò)數(shù)據(jù)庫(kù)連接取消自動(dòng)提交，手動(dòng)提交數(shù)據(jù)

package com.wt;
 
import org.junit.Test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            connection.setAutoCommit(false);//取消自動(dòng)提交
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
 
                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            connection.commit();//所有語(yǔ)句都執(zhí)行完畢后才手動(dòng)提交sql語(yǔ)句
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百萬(wàn)條數(shù)據(jù)插入用時(shí)：" + (System.currentTimeMillis() - start)+"【單位：毫秒】");
 
    }
 
 
}

用時(shí)：【9秒左右】