JDBC删除和批量插入 [英] JDBC Delete & Insert using batch

问题描述

我想知道是否可以使用批处理同时执行参数化的DELETE和INSERT语句.我知道如何插入多行，但是，我首先要执行DELETE语句(需要不同的参数). 这是我插入多个语句的方式:

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:

        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:

For delete portion:

Use addBatch then executeBatch:

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.

下面是一个示例，向您展示如何通过JDBC PreparedStatement在批处理过程中插入几条记录.

Here’s an example to show you how to insert few records in batch process, via 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();

资源链接:

JDBC PreparedStatement示例–批量更新

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

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

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

输出:

Connection established successfully!
PreparedStatement Batch executed, DELETE done

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

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

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

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

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