JAVASQL插入数据优化.doc

资料来源互联网，分享与互联网我的数据库MySQL Assembly 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 + (+count) * preCount + 条); long end = System.currentTimeMillis(); System.out.println(数据导入完毕,所用时间为: + (end - start) + ms); catch (Exception e) mysqlConn.rollback(); System.out.println(数据出错,已进行回滚); throw new RuntimeException(); finally mysqlCmit(); 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 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 mysqlCmit(); DBUtils.free(rs, mysqlPs, mysqlConn); Java code/ 用的StringBuilder串接 long start = System.currentTimeMillis(); for (int i = 1; i 1) sb.append(,); sb.append(aa+ i +,bb,23); if(i % preCount = 0) System.out.