java 使用批处理的 JDBC 删除和插入

JDBC Delete & Insert using batch

提问by Sander_Blaadjes

I was wondering if it is possible to do both a parameterized DELETE and INSERT statement using batch. I am aware of how to insert multiple rows, however, I would first like to do a DELETE statement (Which requires different parameters). Here is how I am inserting multiple statements:

我想知道是否可以使用批处理同时执行参数化的 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();

回答by SkyWalker

For delete portion:

对于删除部分：

Use addBatch then executeBatch:

使用 addBatch 然后执行批处理：

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

Then results will contain an array with the number of rows deleted from each table.

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

For Insertion:

插入：

Here's an example to show you how to insert few records in batch process, via JDBC PreparedStatement.

这是一个示例，向您展示如何通过 JDBC PreparedStatement 在批处理中插入少量记录。

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();

Resource Link:

资源链接：

JDBC PreparedStatement example – Batch Update

JDBC PreparedStatement 示例——批量更新

UPDATE:

更新：

Example/ Full Programs JDBC- Batch PreparedStatement - Execute DELETE query using PreparedStatement's executeUpdate method in java

示例/完整程序 JDBC- Batch PreparedStatement - 在 Java 中使用 PreparedStatement 的 executeUpdate 方法执行 DELETE 查询

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

In this tutorial we learned how to Execute DELETE query(DML command) using PreparedStatement's addBatch()and executeBatch()methods in java JDBC.

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

Resource Link:

资源链接：

1. JDBC Batch Processing (Batch insert, update and delete)
2. JDBC- Batch PreparedStatement example- Execute DELETE query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java

1. JDBC 批处理（批量插入、更新和删除）
2. JDBC- Batch PreparedStatement 示例- 使用 Java 中 PreparedStatement 的 addBatch() 和 executeBatch() 方法执行 DELETE 查询（DML 命令）