批量使用JDBC PreparedStatement [英] using JDBC preparedStatement in a batch

问题描述

我使用 Statement 的批处理来查询我的数据库.我现在做了一些研究，我想重写我的应用程序以使用 preparedStatement 代替，但我很难弄清楚如何将查询添加到 preparedStatement 批处理.

Im using Statements batchs to query my data base. Iv'e done some research now and i want to rewrite my application to use preparedStatement instead but i'm having hard time to figure out how to add queries to a preparedStatement batch.

这就是我现在正在做的:

This is what i'm doing now:

private void addToBatch(String sql) throws SQLException{
sttmnt.addBatch(sql);
batchSize++;
if (batchSize == elementsPerExecute){
    executeBatches();
}
}

其中 sttmnt 是 Statement 类型的类成员.

where sttmnt is a class member of type Statement.

我想要做的是使用preparedStatement的setString(int, String)方法来设置一些动态数据，然后将其添加到批处理中.

What i want to do is to use the preparedStatement's setString(int, String) method to set some dynamic data and then add it to the batch.

不幸的是，我不完全理解它是如何工作的，以及我如何使用 setString(int, String) 到批处理中的特定 sql 或创建一个新的 preparedStatemnt对于我拥有的每个 sql，然后将它们全部加入一个批次.

Unfortunately, i don't fully understand how it works, and how i can use setString(int, String) to a specific sql in the batch OR create a new preparedStatemnt for every sql i have and then join them all to one batch.

有可能吗?还是我对 preparedStatement 的理解真的遗漏了什么?

is it possible to do that? or am i really missing something in my understanding of preparedStatement?

推荐答案

阅读 本文档的第 6.1.2 节 示例.基本上，您使用相同的语句对象并在设置所有占位符后调用批处理方法.另一个 IBM DB2示例 应该适用于任何 JDBC 实现.来自第二个站点:

Read the section 6.1.2 of this document for examples. Basically you use the same statement object and invoke the batch method after all the placeholders are set. Another IBM DB2 example which should work for any JDBC implementation. From the second site:

try {
  connection con.setAutoCommit(false);        
  PreparedStatement prepStmt = con.prepareStatement(    
    "UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
  prepStmt.setString(1,mgrnum1);            
  prepStmt.setString(2,deptnum1);
  prepStmt.addBatch();                      

  prepStmt.setString(1,mgrnum2);                        
  prepStmt.setString(2,deptnum2);
  prepStmt.addBatch();
  int [] numUpdates=prepStmt.executeBatch();
  for (int i=0; i < numUpdates.length; i++) {
    if (numUpdates[i] == -2)
      System.out.println("Execution " + i + 
        ": unknown number of rows updated");
    else
      System.out.println("Execution " + i + 
        "successful: " + numUpdates[i] + " rows updated");
  }
  con.commit();
} catch(BatchUpdateException b) {
  // process BatchUpdateException
} 

这篇关于批量使用JDBC PreparedStatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！