最近做的項目需要將大批量資料插入到資料庫中,之前有印象用preparedstatement.executeBatch();來做,這樣可以減少與資料庫間的互動;今天上網尋找了具體怎麼實現,發現不僅僅只是單純用addBatch()和executeBatch()就行了,還需要將conn.setAutoCommit(false),這樣關閉自動事務提交,也可以減少互動。還需注意addBatch()到一定次數是要及時提交,否則容易發生記憶體溢滿的問題。
try { String url = "jdbc:oracle:thin:@IP:1521:orcl"; // orcl為資料庫的SID String user = "oracle"; String password = "oracle"; StringBuffer sql = new StringBuffer(); sql.append("insert into ex_log (EX_LOG_ID,EX_LOG_DATE) values (?,?)"); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = (Connection) DriverManager.getConnection(url,user,password); // 關閉事務自動認可 con.setAutoCommit(false); final int batchSize = 10000; int count = 0; Long startTime = System.currentTimeMillis(); PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql.toString()); for (int i = 0; i < list.size(); i++) { ExLog exLog = (ExLog)list.get(i); pst.setString(1, exLog.getExLogId()); pst.setString(2, exLog.getExLogDate()); // 把一個SQL命令加入命令列表 pst.addBatch(); if(++count % batchSize == 0 ){ pst.executeBatch(); count = 0; } } // 執行批次更新 pst.executeBatch(); // 語句執行完畢,提交本事務 con.commit(); Long endTime = System.currentTimeMillis(); System.out.println("用時:" + (endTime - startTime)); pst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }