JDBC中的Batch
来源：互联网 发布：数据爬虫有什么用 编辑：程序博客网 时间：2024/06/08 04:09
在jdbc2.0里增加了批量处理的功能（batch），其允许将多个sql语句作为一个单元送至数据库去执行，这样做可以提高操作效率。
下面举个例子（使用statement的例子）：
在操作大量的数据时, 先Prepare一个INSERT语句再多次的执行, 会导致很多次的网络连接. 要减少JDBC的调用次数改善性能, 可以使用PreparedStatement或是Statement 的AddBatch()方法一次性发送多个给数据库（PreparedStatement和Statement 的区别就不说了）
例如：
使用普通的for循环时，效率要低的多
public class testplain { public static void main(String[] args) throws Exception { Connection conn = getOracleConnection(); PreparedStatement ps = null; try { ps = conn .prepareStatement("INSERT INTO batchtab employees values (?, ?)"); conn.setAutoCommit(false); for (int n = 1; n < 3; n++) { Integer i = new Integer(n); ps.setString(1, i.toString()); ps.setString(2, "value" + i.toString()); ps.executeUpdate(); } conn.commit(); }catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("Message: " + ex.getMessage()); System.out.println("Vendor error code: " + ex.getErrorCode()); } catch (Exception e) { e.printStackTrace(); System.err.println("Exception: " + e.getMessage()); } finally { if (conn != null) conn.close(); if (ps != null) ps.close(); } } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:test"; String username = "test"; String password = "test"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; } } 使用batch，将多个sql操作作为一个单元传输给数据库：
public class testbatch { public static void main(String[] args) throws Exception { Connection conn = getOracleConnection(); ResultSet rs = null; // Statement stmt = null; PreparedStatement stmt=null; try { // Create a prepared statement String sql = "INSERT INTO batchtab employees values (?, ?)"; stmt = conn.prepareStatement(sql); conn.setAutoCommit(false); stmt.clearBatch(); // Insert 3 rows of data for (int i=0; i<3; i++) { stmt.setString(1, ""+i); stmt.setString(2, "batch_value"+i); stmt.addBatch(); } int[] updateCounts = stmt.executeBatch(); System.out.println(updateCounts); conn.commit(); sql="SELECT * FROM batchtab"; stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); while (rs.next()) { String id = rs.getString("batch_id"); String name = rs.getString("batch_value"); System.out.println("id=" + id + " name=" + name); } } catch (BatchUpdateException b) { System.out.println("SQLException: " + b.getMessage()); System.out.println("SQLState: " + b.getSQLState()); System.out.println("Message: " + b.getMessage()); System.out.println("Vendor error code: " + b.getErrorCode()); System.out.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.out.print(updateCounts[i] + " "); } } catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("Message: " + ex.getMessage()); System.out.println("Vendor error code: " + ex.getErrorCode()); } catch (Exception e) { e.printStackTrace(); System.err.println("Exception: " + e.getMessage()); } finally { if( conn != null ) conn.close(); if(stmt !=null) stmt.close(); if(rs !=null) rs.close(); } } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:test"; String username = "test"; String password = "test"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; } } 下面举个例子（使用statement的例子）：
public class testbatch { public static void main(String[] args) throws Exception { Connection conn = getOracleConnection(); ResultSet rs = null; Statement stmt = null; try { stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); conn.setAutoCommit(false); stmt.clearBatch(); stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('1', 'batch_value1')"); stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('2', 'batch_value2')"); stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('3', 'batch_value3')"); int[] updateCounts = stmt.executeBatch(); System.out.println(updateCounts); conn.commit(); rs = stmt.executeQuery("SELECT * FROM batchtab"); while (rs.next()) { String id = rs.getString("batch_id"); String name = rs.getString("batch_value"); System.out.println("id=" + id + " name=" + name); } } catch (BatchUpdateException b) { System.out.println("SQLException: " + b.getMessage()); System.out.println("SQLState: " + b.getSQLState()); System.out.println("Message: " + b.getMessage()); System.out.println("Vendor error code: " + b.getErrorCode()); System.out.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.out.print(updateCounts[i] + " "); } } catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("Message: " + ex.getMessage()); System.out.println("Vendor error code: " + ex.getErrorCode()); } catch (Exception e) { e.printStackTrace(); System.err.println("Exception: " + e.getMessage()); } finally { if( conn != null ) conn.close(); if(stmt !=null) stmt.close(); if(rs !=null) rs.close(); } } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:testbatch"; String username = "test"; String password = "test"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; } } - jdbc中的batch
- JDBC中的Batch
- DRP——JDBC中的Batch
- jdbc batch
- JDBC batch
- jdbc批处理batch性能
- jdbc batch批处理
- jdbc五大核心的接口，批处理 Batch，jdbc中的事务处理
- JDBC学习之三Batch
- JDBC的批量操作Batch
- JDBC PreparedStatement example - update batch
- JDBC Batch 学习资料--转载
- JDBC进阶之批处理 Batch
- 使用JDBC进行批处理Batch
- JDBC(PreparedStatement)--Batch Update Record
- JDBC批量插入（batch）
- 标准JDBC步骤以及jdbc batch 批处理
- Spring Batch 中的Listener
- Apache 如何使用基础篇
- 领域模型的概念
- Hard Coding
- Ant 简单示例+环境变量配置说明
- Java的synchronized关键字：同步机制总结
- JDBC中的Batch
- Windows环境下用jwplayer+Nginx搭建视频点播服务器
- 免费域名+WordPress=教你申请独立博客
- jackrabbit OCM(一)
- 学习好网站
- [你必须知道的.NET] 第七回：品味类型---从通用类型系统开始
- 2011年7月《安全天下事之口令与隐私》
- 慎用Visual Studio C++默认的hash_map
- 爱情、婚姻、幸福、情人、生活
