关于JDBC+MySQL批量写入： 用insert values方式批量写入

恩，萌新刚来，听学长说写博客可以总结梳理自己的知识，所以来试试，自娱自乐，不喜莫喷。目前还是大二狗，学Java半年多，错误很多，望大神指正。

应为经常要写入大量数据所以做了一个批量写入测试，这篇文章是为另一篇一个6分钟爬去8万条数据的多线程Java爬虫中的批量写入做说明（还未更新上来请稍后）：

我用insert values这种，做过性能对比，代码和结果如下：

import java.sql.*;/** * Created by Me on 2017/4/7. */public class sqltest {    private Connection connection=null;    public static void main(String[] args) throws SQLException {        sqltest sqltest = new sqltest();        sqltest.connectionToSql();//        sqltest.CrestDatabase("tao");//        sqltest.creatTable("test");        System.out.println("1:++++++++++++");        sqltest.creatTable("tao01");        for(int i=0;i<6;i++) {            long start = System.currentTimeMillis();            sqltest.insert(10000);            long end = System.currentTimeMillis();            System.out.print(start - end);            System.out.println();        }        System.out.println("2:++++++++++++");        sqltest.creatTable("tao02");        for(int i=0;i<2;i++) {            long start1 = System.currentTimeMillis();            sqltest.insert1(10000);            long end1 = System.currentTimeMillis();            System.out.print(start1 - end1);            System.out.println();        }        System.out.println(sqltest.count());    }    public boolean connectionToSql(){        try{            Class.forName("com.mysql.jdbc.Driver");            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tao?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");        } catch (ClassNotFoundException e) {            e.printStackTrace();            return  false;        } catch (SQLException e) {            e.printStackTrace();            return false;        }        return true;    }    public boolean creatTable(String name){        StringBuilder s = new StringBuilder();        s.append("CREATE TABLE ");        s.append(name);        s.append(" ( id int not null, name varchar(64) , myText varchar(64))");        try {            Statement statement = connection.createStatement();            statement.execute(String.valueOf(s));        } catch (SQLException e) {            e.printStackTrace();        }        return true;    }    public boolean CrestDatabase(String database){        try {            Statement statement = connection.createStatement();            String sql = "CREATE DATABASE "+database;            statement.executeUpdate(sql);//            String sql1 = "USING "+database;//            statement.executeUpdate(sql1);            return true;        } catch (SQLException e) {            e.printStackTrace();            return false;        }    }    public void insert(int num){        StringBuilder s = new StringBuilder();        s.append("insert into tao01 values ");        for(int i=0;i<num;i++){            s.append("(");            s.append(i);            s.append(",'test','test'),");        }        s.append("(");        s.append(10000);        s.append(",'test','test')");        try {            Statement statement = connection.createStatement();            statement.execute(String.valueOf(s));        } catch (SQLException e) {            e.printStackTrace();        }    }    public void insert1(int num){        StringBuilder s =null;        for(int i=0;i<num;i++) {            s = new StringBuilder();            s.append("insert into tao02 values ");            s.append("(");            s.append(i);            s.append(" ,'test','test')");            try {                Statement statement = connection.createStatement();                statement.execute(String.valueOf(s));            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public int count(){        try{            Statement statement = connection.createStatement();            String sql = "SELECT COUNT(*) FROM TAO01";            ResultSet resultSet = statement.executeQuery(sql);            resultSet.next();            return resultSet.getInt(1);        } catch (SQLException e) {            e.printStackTrace();            return 0;        }    }}

结果如下：（单位：毫秒）

1:++++++++++++-618-815-161-216-230-2002:++++++++++++-445177

可以看出同样写入10000条数据，批量写入时普通写入的100~200倍，本来打算普通写入也做三次的，但是太慢了我忍不住关了