JDBC连接MySQL快速批量插入数据

一、按单条插入数据库慢的原因

• 主要原因是单条写入时候需要应用于db之间大量的请求响应交互。每个请求都是一个独立的事务提交；这样网络延迟大的情况下多次请求会有大量的时间消耗的网络延迟上；
• 第二个是由于每个事务db都会有刷新磁盘操作写事务日志，保证事务的持久性；由于每个事务只是写入一条数据 所以磁盘io利用率不高，因为对于磁盘io是按块来的，所以连续写入大量数据效率更好。所以必须改成批量插入的方式，减少请求数与事务数；

---

二、实现批量插入的主要方法点

• 连接驱动设置url后面拼接：?rewriteBatchedStatements=true;
• 数据库关闭自动提交：setAutoCommit(false)
• 使用addBatch()和executeBatch()方法实现批量处理数据

---

三、实例演示

3.1 代码演示实例

package cn.wsj;

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

public class Mylog {
    public static class Goods{
        private int goodid;
        private String title;
        private float price;
        private int typeid;
        private int issale;
        private int score;
        private int shopid;
        private int paytype;
        private String detailtabname;

        public Goods() {
        }

        public Goods(int goodid, String title, float price, int typeid, int issale, int score, int shopid, int paytype, String detailtabname) {
            this.goodid = goodid;
            this.title = title;
            this.price = price;
            this.typeid = typeid;
            this.issale = issale;
            this.score = score;
            this.shopid = shopid;
            this.paytype = paytype;
            this.detailtabname = detailtabname;
        }

        public int getGoodid() {
            return goodid;
        }

        public String getTitle() {
            return title;
        }

        public float getPrice() {
            return price;
        }

        public int getTypeid() {
            return typeid;
        }

        public int getIssale() {
            return issale;
        }

        public int getScore() {
            return score;
        }

        public int getShopid() {
            return shopid;
        }

        public int getPaytype() {
            return paytype;
        }

        public String getDetailtabname() {
            return detailtabname;
        }
    }

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://192.168.237.130:3306/myshops?rewriteBatchedStatements=true", "root", "root");

        Random rand = new Random();
        List<Goods> data = new ArrayList<Goods>();
        for (int i = 1; i <= 5000000; i++) {
            Goods gd = new Goods(i,"商品"+i
                    ,rand.nextInt(10000),
                    1+rand.nextInt(14),1,100,
                    1+rand.nextInt(99),1,"healthvalue");
            data.add(gd);
        }
        //关闭自动事务
        con.setAutoCommit(false);
        String sql = "insert into goods values(?,?,?,?,?,?,?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        Long time = System.currentTimeMillis();
        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 p=0;p<params.length;p++){
                pst.setObject(p+1,params[p]);
            }
            pst.addBatch();
            if(i%10000==0) {
                pst.executeBatch();
                con.commit();
            }
        }
        System.out.println(System.currentTimeMillis()-time);

        pst.close();
        con.close();

    }
}

---

3.2 代码运行结果

500万行数据插入所花时间（毫秒）：

"C:\Program Files\Java\jdk1.8.0_231\bin\java.exe"
37459

Process finished with exit code 0

---

PS：如果有写错或者写的不好的地方，欢迎各位大佬在评论区留下宝贵的意见或者建议，敬上！如果这篇博客对您有帮助，希望您可以顺手帮我点个赞！不胜感谢！

原创作者：wsjslient

作者主页：https://blog.csdn.net/wsjslient

---