JAVA_SQL插入数据优化

我的数据库MySQL 

Assembly code

 

CREATE TABLE `users` (

  `id` int(11) NOT NULL auto_increment,

  `firstname` varchar(50) NOT NULL,

  `lastname` varchar(50) NOT NULL,

  `age` int(11) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

我的数据库辅助类

Java code

 

package com.jdbc.batch;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public final class DBUtils {

    private static String mysqlurl = "jdbc:mysql://localhost:3306/mytest";

    private static String accessurl = "jdbc:mysql://localhost:3306/mytest";

    private static String user = "root";

    private static String password = "root";

    // 获得连接

    public static Connection getAccessConn() throws SQLException {

        return DriverManager.getConnection(accessurl, user, password);

    }

    public static Connection getMySqlConn() throws SQLException {

        return DriverManager.getConnection(mysqlurl, user, password);

    }

     // 释放连接

    public static void free(ResultSet rs, PreparedStatement ps, Connection conn) {

        try {

            if (rs != null) {

                rs.close();

            }

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            try {

                if (ps != null) {

                    ps.close();

                }

            } catch (SQLException e) {

                e.printStackTrace();

            } finally {

                try {

                    if (conn != null) {

                        conn.close();

                    }

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        }

    }

 

    // 加载驱动

    static {

        try {

            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {

            System.out.println("驱动加载出错");

        }

    }

}

我的测试类

Java code

 

package com.jdbc.batch;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public class BatchExample {

    private static Connection mysqlConn = null;

    private static ResultSet rs = null;

    // 总条数

    private static int allCount = 10000;

    // 分批条数

    private static int preCount = 1000;

    // 计数器

    private static int count = 0;

    private static String insertSQL = "insert into users(firstname, lastname, age) values(?, ?, ?)";

    private static PreparedStatement mysqlPs = null;

    public static void main(String[] args) throws SQLException {

        try {

            mysqlConn = DBUtils.getMySqlConn();

            mysqlPs = mysqlConn.prepareStatement(insertSQL);

            mysqlConn.setAutoCommit(false);

            long start = System.currentTimeMillis();

            for (int i = 1; i <= allCount; i++) {

                mysqlPs.setString(1, "firstname" + i);

                mysqlPs.setString(2, "lastname" + i);

                mysqlPs.setInt(3, 23);

                mysqlPs.addBatch();       

                if ((i % preCount) == 0){

                    mysqlPs.executeBatch();

                    System.out.println("当前进行完毕===>" + (++count) * preCount + "条");

                }

            }       

            long end = System.currentTimeMillis();

            System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");

        } catch (Exception e) {

            mysqlConn.rollback();

            System.out.println("数据出错,已进行回滚");

            throw new RuntimeException();

        } finally {

            mysqlConn.commit();

            DBUtils.free(rs, mysqlPs, mysqlConn);

        }

    }

 

}

 

我用的批处理.最后执行10000条的记录是

Assembly code

 

当前进行完毕===>1000条
当前进行完毕===>2000条
当前进行完毕===>3000条
当前进行完毕===>4000条
当前进行完毕===>5000条
当前进行完毕===>6000条
当前进行完毕===>7000条
当前进行完毕===>8000条
当前进行完毕===>9000条
当前进行完毕===>10000条
数据导入完毕,所用时间为: 8140 ms

 

Java code

 

主要看下我的测试类

BatchExample 我用的批处理 addBatch每1000条打包发送一次.但结果还是不尽人意8140ms。 

而我换了一种方案 用executeUpdate一次提交.SQL用StringBuilder串接 效率提升很快.
代码

Java code

 

package com.jdbc.batch;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public class BufferQuery {

    private static Connection mysqlConn = null;

    private static ResultSet rs = null;

    // 总条数

    private static int allCount = 10000;

    // 分批条数

    private static int preCount = 1000;

    // 计数器

    private static int count = 0;

 

    private static String insertSQL = "insert into users(firstname, lastname, age) values(?, ?, ?)";

 

    private static PreparedStatement mysqlPs = null;

 

    public static void main(String[] args) throws SQLException {

        try {

            StringBuilder sb = new StringBuilder();

            sb.append("insert into users(firstname, lastname, age) values");

            mysqlConn = DBUtils.getMySqlConn();

            mysqlPs = mysqlConn.prepareStatement(insertSQL);

            mysqlConn.setAutoCommit(false);

            long start = System.currentTimeMillis();

           

            for (int i = 1; i <= allCount; i++) {

                if(i > 1) sb.append(",");

                sb.append("('aa"+ i +"','bb',23)");   

 

                if(i % preCount == 0){

                    System.out.println("导入进行===>" + (++count * preCount) + "条");               

                }               

            }

            mysqlPs.executeUpdate(sb.toString());

            long end = System.currentTimeMillis();

            System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");

 

        } catch (Exception e) {

            mysqlConn.rollback();

            System.out.println("数据出错,已进行回滚");

            throw new RuntimeException();

        } finally {

            mysqlConn.commit();

            DBUtils.free(rs, mysqlPs, mysqlConn);

        }

    }

 }

 

Java code

// 用的StringBuilder串接

           long start = System.currentTimeMillis();

           

            for (int i = 1; i <= allCount; i++) {

                if(i > 1) sb.append(",");

                sb.append("('aa"+ i +"','bb',23)");    

 

                if(i % preCount == 0){

                    System.out.println("导入进行===>" + (++count * preCount) + "条");               

                }               

            }

            mysqlPs.executeUpdate(sb.toString());

 
运行结果为:

Assembly code

 

导入进行===>1000条

导入进行===>2000条

导入进行===>3000条

导入进行===>4000条

导入进行===>5000条

导入进行===>6000条

导入进行===>7000条

导入进行===>8000条

导入进行===>9000条

导入进行===>10000条

数据导入完毕,所用时间为: 219 ms