How to execute 2 update queries in one transaction with JDBC

Question

I am new to JDBC and I am trying to update 2 tables in my database, so I would like to do it in 1 transaction so if one query fails, the other should also fail. I would like to provide such behaviour or just have an opportunity to do rollback if one of them fails.

Here are my 2 queries:

int i = stmt.executeUpdate("INSERT INTO product (title, price, `status`) " +
                "VALUES ( \"" + product.getTitle() + "\", " + product.getPrice() + ", " + product.getStatus().ordinal() + ");");
int j = stmt.executeUpdate("INSERT INTO product_categories (product_id, category_id) " +
                "VALUES (last_insert_id(), " + categoryId + ");");

Answer 1

If you want to execute multiple statements atomically, you need to use a transaction. A JDBC connection defaults to 'auto-commit' mode, which means each statement is executed in its own transaction. So you first need to disable auto-commit mode, using Connection.setAutoCommit(false).

With auto-commit mode disabled, executed statements will be executed in the current transaction, if there is no current transaction, one will be started. This transaction can then either be committed using Connection.commit() or rolled back using Connection.rollback().

You will need to do something like:

try (Connection connection = DriverManager.getConnection(...)) {
    connection.setAutoCommit(false);
    try (Statement stmt = connection.createStatement()) {
        stmt.executeUpdate(<your first update>);
        stmt.executeUpdate(<your second update>);

        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    }
}

For more details, see the JDBC tutorial chapter Using Transactions.

And please learn about prepared statements. Concatenating values into a query string is bad, because it can lead to SQL injection or weird errors if you forget to escape values. See also the JDBC tutorial chapter Using Prepared Statements.