用python和Java连接MySQL数据库，插入百万，千万条数据

python代码：

需要用到 pymysql模块，python没有的话可以通过以下方式安装：

运行Anaconda Prompt
pydemo是自己创建的python开发环境的名字

(C:\ProgramData\Anaconda3) C:\Users\Administrator\Documents>activate pydemo

(pydemo) C:\Users\Administrator\Documents>pip inatall pymysql

import pymysql.cursors
import random
import datetime
import uuid

def generateRandomDate():
    tm1=datetime.datetime.strptime("2020-1-1","%Y-%m-%d")
    add = datetime.timedelta(random.randint(1, 360))
    return datetime.datetime.strftime(tm1 + add, "%Y-%m-%d")

db= pymysql.connect(host="192.168.181.129",user="root",password="root",database="myshops")
cursor=db.cursor()
datas=list()

for i in range(10000000):
    data=(i+1,generateRandomDate(),random.randint(1,100000)
          ,random.randint(1,4),uuid.uuid1())
    datas.append(data)

def insert_data():)
    sql="insert into orders values(%s,%s,%s,%s,%s)";
    try:
        cursor.executemany(sql,datas)
        db.commit()
    except:
        db.rollback()


if __name__ == '__main__':
    insert_data()

JAVA 代码：

表属性类：

package com.njbdqn.jdbc.jdbc.entity;

public 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 detailname;

    public Goods() {
    }

    public Goods(int goodid, String title, float price, int typeid, int issale, int score, int shopid, int paytype, String detailname) {
        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.detailname = detailname;
    }

    public int getGoodid() {
        return goodid;
    }

    public void setGoodid(int goodid) {
        this.goodid = goodid;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public int getTypeid() {
        return typeid;
    }

    public void setTypeid(int typeid) {
        this.typeid = typeid;
    }

    public int getIssale() {
        return issale;
    }

    public void setIssale(int issale) {
        this.issale = issale;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public int getShopid() {
        return shopid;
    }

    public void setShopid(int shopid) {
        this.shopid = shopid;
    }

    public int getPaytype() {
        return paytype;
    }

    public void setPaytype(int paytype) {
        this.paytype = paytype;
    }

    public String getDetailname() {
        return detailname;
    }

    public void setDetailname(String detailname) {
        this.detailname = detailname;
    }
}

数据库连接配置类：

package com.njbdqn.jdbc;

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 (connection==null) {
                Class.forName("com.mysql.jdbc.Driver");
                connection  = DriverManager.getConnection(
                        "jdbc:mysql://192.168.181.129:3306/myshops?rewriteBatchedStatements=true",
                        "root", "kb10");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

}

数据库数据操作类：

package com.njbdqn.jdbc;

import com.njbdqn.jdbc.jdbc.entity.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;
    }

    /**
     *
     * @param data
     */
    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.getDetailname()};

                for (int p = 0; p < params.length; p++) {
                    pst.setObject(p+1,params[p]);
                }
                pst.addBatch();

                if (i%10000==0) {
                    pst.executeBatch();
                    connection.commit();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

}

执行插入数据类：

package com.njbdqn.jdbc.service.jdbc;

import com.njbdqn.jdbc.BaseDao;
import com.njbdqn.jdbc.jdbc.entity.Goods;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class InsertDateService {
    private BaseDao bdao= new BaseDao();
    private List<Goods> buildData(){
        Random rand = new Random();
        List<Goods> gds = 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");
            gds.add(gd);
        }
        return gds;
    }

    public void insert (){
        List<Goods> gds = buildData();
        long time = System.currentTimeMillis();
        for (Goods gd : gds) {
            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.getDetailname()};
            bdao.update(sql,params);
        }
        System.out.println(System.currentTimeMillis()-time);
    }
    public void insertBatch(){
        long time = System.currentTimeMillis();
        bdao.batchUpdate(buildData());
        System.out.println(System.currentTimeMillis()-time);
    }
    public static void main(String[] args) {
        InsertDateService ids = new InsertDateService();
        ids.insertBatch();
    }
}

在插入数据的类中insert和insertBatch方法对比其运行效率，insertBatch远高于insert，节约时间