How do to commit or rollback transaction in JDBC?

Executing a database manipulation command such as insert, update or delete can sometime throw exception due to invalid data. To protect the integrity of our application data we must make sure that when a transaction fails we must rollback all the executed queries.

In this example we are using MySQL database. To enable transaction capability in MySQL make sure that you are using InnoDB storage engine to create your databases and tables.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.*;

public class TransactionRollbackExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            conn.setAutoCommit(false);

            String orderQuery = """
                    INSERT INTO purchase_order (username, order_date)
                    VALUES (?, ?)
                    """;

            try (PreparedStatement stmt = conn.prepareStatement(orderQuery,
                    PreparedStatement.RETURN_GENERATED_KEYS)) {
                stmt.setString(1, "jduke");
                stmt.setDate(2, new Date(System.currentTimeMillis()));
                stmt.execute();

                ResultSet keys = stmt.getGeneratedKeys();
                long orderId = 1L;
                if (keys.next()) {
                    orderId = keys.getLong(1);
                }

                // This is an invalid statement that will cause exception to
                // demonstrate a rollback.
                String orderDetailQuery = """
                        INSERT INTO purchase_order_detail (order_id, product_id, quantity, price)
                        VALUES (?, ?, ?, ?)
                        """;

                PreparedStatement detailStmt = conn.prepareStatement(orderDetailQuery);
                detailStmt.setLong(1, orderId);
                detailStmt.setInt(2, 1);
                detailStmt.setInt(3, 10);
                detailStmt.setBigDecimal(4, new BigDecimal("29.99"));
                detailStmt.execute();

                // Commit transaction to mark it as a success database operation
                conn.commit();
                System.out.println("Transaction commit...");
            } catch (SQLException e) {
                // Rollback any database transaction due to exception occurred
                conn.rollback();
                System.out.println("Transaction rollback...");
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Author
Recent Posts

Wayan

Founder at Kode Java

A programmer, runner and recreational diver, lives on the island of Bali, Indonesia. Programming in Java, Spring Framework, Hibernate / JPA, MySQL. To support me working on this project, buy me a cup of coffee, every little bit helps. Thank you.

Leave a ReplyCancel reply