以前总是说批量插入和更新的效率比非批量的要高, 但是一直没有使用过批量处理数据的功能, 现在由于项目中需要处理的数据量比较大, 所以使用了批量处理的功能, java 代码如下:
1.java 实现批量插入数据:
- Connection conn;
- try {Class.forName("com.mysql.jdbc.Driver");
- conn = DriverManager.getConnection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
- conn.setAutoCommit(false);
- String sql = "insert into test_user (u_name,create_date) value (?,SYSDATE())";
- PreparedStatement prest = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
- ResultSet.CONCUR_READ_ONLY);
- conn.setAutoCommit(false);
- for (int i = 1; i <= 100; i++) {
- prest.setString(1, "a" + i);
- prest.addBatch();
- }
- prest.executeBatch();
- conn.commit();
- conn.close();
- } catch (Exception ex) {
- ex.printStackTrace();
- }
2.java 实现批量更新数据:
- Connection conn;
- try {
- Class.forName("com.mysql.jdbc.Driver");
- conn = DriverManager.getConnection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
- conn.setAutoCommit(false);
- // 保存当前自动提交模式
- boolean autoCommit = conn.getAutoCommit();
- // 关闭自动提交
- conn.setAutoCommit(false);
- Statement stmt =conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
- for (int i = 1; i <= num; i++) {
- // 通过遍历可以一次性插入多条 sql 语句
- stmt.addBatch("update test_user set test_user.u_name = ('d"+i+"') where test_user.u_name = ('c"+i+"')");
- }
- stmt.executeBatch();
- stmt.clearBatch();
- conn.commit();
- conn.close();
- } catch (Exception ex) {
- ex.printStackTrace();
- }
来源: http://www.bubuko.com/infodetail-2582470.html