具有不同sql查询的批处理preparestatement [英] batch preparedstatement with different sql queries

问题描述

我发现了与该实际上没有的问题类似的现有问题类似一个明确的问题答案.

I found existing questions similar to this one that did not actually have a clear answer to the question.

带有一个sql查询的常规批处理preparestate语句看起来像这样:

A normal batch preparedstatement with one sql query would look something like this:

private static void batchInsertRecordsIntoTable() throws SQLException {

        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;

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

        try {
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(insertTableSQL);

            dbConnection.setAutoCommit(false);

            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.setInt(1, 103);
            preparedStatement.setString(2, "mkyong103");
            preparedStatement.setString(3, "system");
            preparedStatement.setTimestamp(4, getCurrentTimeStamp());
            preparedStatement.addBatch();

            preparedStatement.executeBatch();

            dbConnection.commit();

            System.out.println("Record is inserted into DBUSER table!");

        } catch (SQLException e) {

            System.out.println(e.getMessage());
            dbConnection.rollback();

        } finally {

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }

    }

来源: http://www.mkyong .com/jdbc/jdbc-preparedstatement-example-batch-update/

但是，我正在寻找一种对不同 sql查询执行批处理事务的方法.即INSERT INTO TABLE A和INSERT INTO TABLE B没有SQL注入攻击的风险.我知道，preparedstatement是避免此类攻击的首选方法，但我不知道在区分SQL查询时执行批处理事务的方法吗?

However, I'm looking for a way to perform batch transactions on different sql queries. i.e. INSERT INTO TABLE A and INSERT INTO TABLE B without the risk of SQL Injection attacks. I know that preparedstatements are the preferred method of avoiding such attacks but I don't know of a way to do batch transactions on differentiating SQL queries?

推荐答案

对于两(2)个不同的SQL查询，您将需要两(2)个不同的PreparedStatement对象，并且每个对象都有自己的批处理，但是您可以简单地要将查询发送到服务器，请执行每个批处理:

For two (2) different SQL queries you will need two (2) different PreparedStatement objects and each one will have its own batch, but you can simply execute each batch when you want to send the queries to the server:

try (
        PreparedStatement thisPs = conn.prepareStatement("INSERT INTO thisTable (thisId, thisText) VALUES (?,?)");
        PreparedStatement thatPs = conn.prepareStatement("INSERT INTO thatTable (thatId, thatText) VALUES (?,?)")) {

    thisPs.setInt(1, 1);
    thisPs.setString(2, "thisText1");
    thisPs.addBatch();

    thatPs.setInt(1, 1);
    thatPs.setString(2, "thatText1");
    thatPs.addBatch();

    thisPs.setInt(1, 2);
    thisPs.setString(2, "thisText2");
    thisPs.addBatch();

    thatPs.setInt(1, 2);
    thatPs.setString(2, "thatText2");
    thatPs.addBatch();

    thisPs.executeBatch();
    thatPs.executeBatch();
}

另外，请注意术语.谈论批量交易"有点模棱两可:

Also, be aware of terminology. Talking about a "batch transaction" is somewhat ambiguous:

• addBatch和executeBatch是作为单个 batch (传输)将多个语句发送到服务器的机制的一部分.这会影响将语句发送(传输)到数据库服务器的方式.

• addBatch and executeBatch are part of the mechanism to send multiple statements to the server as a single batch (transmission). This affects the way the statements are sent (transmitted) to the database server.

数据库事务是一种机制，通过该机制，许多语句将作为一个完整的组进行处理，即，将对整个组进行处理(提交")或整个组将被丢弃(回滚"). Connection#setAutoCommit()，Connection#commit()和Connection#rollback()方法控制此行为.这会影响数据库服务器执行语句的方式.

A database transaction is the mechanism whereby a number of statements will be processed as a complete group, i.e., either the whole group will be processed ("committed") or the whole group will be discarded ("rolled back"). The Connection#setAutoCommit(), Connection#commit(), and Connection#rollback() methods control this behaviour. This affects the way the statements are executed by the database server.

这篇关于具有不同sql查询的批处理preparestatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！