JDBC单例模式-insert海量数据时的效率优化

一、配置文件

1、装载驱动，创建SQL链接

配置类构造器私有，不允许外界调用创建多个对象，仅能在调用其中的public静态方法时创建一个对象

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtils {
    private DBUtils(){}
    private static Connection connection;

    public static Connection getConnection(){
        try {
            if (null==connection){
                Class.forName("com.mysql.jdbc.Driver");
                connection= DriverManager.getConnection("jdbc:mysql://single/myshops?rewriteBatchedStatements=true","user","javakb10");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

2、BaseDao类

构造器内初始化connection，直接调用配置类里的静态方法

import cn.kgc.xym.jdbc_alone.entitiy.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class BaseDao {
    private Connection connection;
    private PreparedStatement pst;
    private ResultSet rst;

    public BaseDao() {
        connection = DBUtils.getConnection();
    }

    public int update(String sql, Object[] params) {
        int count = 0;
        try {
            pst = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            count= pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public ResultSet query(String sql,Object[] params){
        try {
            pst = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
            rst=pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rst;
    }

    public void batchUpdate(List<Goods> data){
        try {
            connection.setAutoCommit(false);
            String sql = "insert into goods values(?,?,?,?,?,?,?,?,?)";
            pst=connection.prepareStatement(sql);
            for (int i = 1; i <data.size() ; i++) {
                Goods gd = data.get(i - 1);
                Object[] params = {gd.getGoodid(), gd.getTitle(), gd.getPrice(), gd.getTypeid(), gd.getIssale()
                        , gd.getScore(), gd.getShopid(), gd.getPaytype(), gd.getDetailtabname()};
                for (int j = 0; j < params.length; j++) {
                    pst.setObject(j + 1, params[j]);
                }
                pst.addBatch();
                if (i % 10000 == 0) {
                    pst.executeBatch();
                    connection.commit();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void destory() {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3、插入数据，并优化速度

本次测试案例50W条数据，最初用循环直接 插入时，需要大概70秒时间，原因是每执行一条插入语句，就会自动commit提交一次。经过分析，思考出两种方法：

• 使用StringBuilder将多条insert插入语句拼接为一条，一次性拼接10000条再运行
• 关闭自动提交，然后使用addBatch批量添加，最后再commit一次。即一次向服务器发送多条sql语句，然后由服务器一次性处理。此方法需要把数据库连接配置rewritrBatchedStatement改为true，默认为false

package cn.kgc.xym.jdbc_alone.services;

import cn.kgc.xym.jdbc_alone.db.BaseDao;
import cn.kgc.xym.jdbc_alone.entitiy.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class InsertDataService {
    private BaseDao bdao = new BaseDao();
    private List<Goods> buildData(){
        Random random = new Random();
        List<Goods> gds = new ArrayList<>();
        for (int i = 1; i <=5000000 ; i++) {
            Goods gd = new Goods(i, "商品" + i, random.nextInt(10000),
                    1 + random.nextInt(14), 1, 100, 1 + random.nextInt(99),
                    1, "healthvalue");
            gds.add(gd);
        }
        return gds;
    }

    public void inserttest(){
        long l = System.currentTimeMillis();
        bdao.batchUpdate(buildData());
        System.out.println(System.currentTimeMillis() - l);
    }

    public void insert(){
        List<Goods> goods = buildData();
        long time = System.currentTimeMillis();
        for (Goods gd : goods) {
            String sql = "insert into goods values(?,?,?,?,?,?,?,?,?)";
            Object [] params = {gd.getGoodid(),gd.getTitle(),gd.getPrice(),gd.getTypeid(),gd.getIssale()
                    ,gd.getScore(),gd.getShopid(),gd.getPaytype(),gd.getDetailtabname()};
            bdao.update(sql,params);
        }
        System.out.println(System.currentTimeMillis() - time);
    }
}