JAVA插入数据优化

我的数据库MySQLAssembly codeCREATE 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 codepackage 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 codepackage 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 {mit();DBUtils.free(rs, mysqlPs, mysqlConn);}}}我用的批处理.最后执行10000条的记录是Assembly code当前进行完毕===>1000条当前进行完毕===>2000条当前进行完毕===>3000条当前进行完毕===>4000条当前进行完毕===>5000条当前进行完毕===>6000条当前进行完毕===>7000条当前进行完毕===>8000条当前进行完毕===>9000条当前进行完毕===>10000条数据导入完毕,所用时间为: 8140 msJava code主要看下我的测试类BatchExample 我用的批处理 addBatch每1000条打包发送一次.但结果还是不尽人意8140ms.而我换了一种方案用executeUpdate一次提交.SQL用StringBuilder串接效率提升很快.代码Java codepackage 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 {mit();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。