batch preparedstatement with different sql queries

Question

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

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

        }

    }

Taken from: http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

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?

Answer 1

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 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.

• 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.