使用 JDBC 批量删除和插入

我想知道是否可以使用批处理来执行参数化 DELETE 和 INSERT 语句。我知道如何插入多行，但是，我首先想做一个 DELETE 语句（这需要不同的参数）。 以下是我插入多个语句的方法：

        String query = "INSERT INTO " + TABLE + "(FOO, BAR) VALUES (?,?);";

        PreparedStatement sql = connection.prepareStatement(query);

        for(...){
            sql.setString(1, fooValue);
            sql.setInt(2, barValue);
            sql.addBatch();
        }       

        sql.executeBatch();

        sql.close();

对于删除部分：

使用 addBatch 然后executeBatch：

Statement st = con.createStatement();
st.addBatch("DELETE FROM tbl1");
st.addBatch("DELETE FROM tbl2");
st.addBatch("DELETE FROM tbl3");
int[] results = st.executeBatch();

然后结果将包含一个数组，其中包含从每个表中删除的行数。

对于插入：

下面是一个示例，向您展示如何通过 JDBCPreparedStatement 在批处理过程中插入少量记录。

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";              
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();

资源链接：

JDBCPreparedStatement示例——批量更新 http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

UPDATE:

示例/完整程序 JDBC-批量PreparedStatement-执行DELETE java中使用PreparedStatement的executeUpdate方法进行查询

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementDeleteExample {
    public static void main(String... arg) {
           Connection con = null;
           PreparedStatement prepStmt = null;
           try {
                  // registering Oracle driver class
                  Class.forName("oracle.jdbc.driver.OracleDriver");

                  // getting connection
                  con = DriverManager.getConnection(
                               "jdbc:oracle:thin:@localhost:1521:orcl",
                               "ankit", "Oracle123");
                  System.out.println("Connection established successfully!");             

                  con.setAutoCommit(false); //Now, transactions won't be committed automatically.

                  prepStmt = con.prepareStatement("DELETE from EMPLOYEE where ID=? ");

                  //1) add set of parameters in PreparedStatement's object - BATCH of commands
                  prepStmt.setInt(1, 7); //substitute first occurrence of ? with 7
                  prepStmt.addBatch();

                  //2) add set of parameters in PreparedStatement's object - BATCH of commands                  
                  prepStmt.setInt(1, 8); //substitute first occurrence of ? with 8
                  prepStmt.addBatch();


                  //Execute PreparedStatement batch
                  prepStmt.executeBatch();
                  System.out.println("PreparedStatement Batch executed, DELETE done");

                  con.commit(); //commit all the transactions

           } catch (ClassNotFoundException e) {
                  e.printStackTrace();
           } catch (SQLException e) {
                  e.printStackTrace();
           }
           finally{
                  try {
                        if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                        if(con!=null) con.close(); // close connection
                  } catch (SQLException e) {
                        e.printStackTrace();
                  }
           }
    }
}

OUTPUT:

Connection established successfully!
PreparedStatement Batch executed, DELETE done

在本教程中，我们学习了如何使用PreparedStatement执行DELETE查询（DML命令）addBatch() and executeBatch()java JDBC 中的方法。

资源链接：

1. JDBC批处理（批量插入、更新和删除） http://www.technowlogeek.com/programming/java/jdbc/jdbc-batch-processing-batch-insert-update-and-delete/
2. JDBC-批量PreparedStatement示例-执行DELETE查询(DML 命令）使用PreparedStatement的addBatch()和executeBatch() java中的方法 http://www.javamadesoeasy.com/2015/07/jdbc-batch-preparedstatement-example_22.html