java examples
Home Java Examples Resources Java Interview Questions
Brainpower SEO

How to commit or rollback transaction in JDBC?

Executing a database manipulation command such as insert, update or delete can sometime throws exception due to invalid data. To protect the integrity of our application data we must make sure when we a transaction was failed we must rollback all the executed command so that it affect the state of our data.

package com.javacoderanch.example.sql;

import java.sql.*;

public class TransactionRollbackExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost/testdb";
        String username = "root";
        String password = "";

        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);

            StringBuilder sb = new StringBuilder("INSERT INTO orders (username, order_date) " +
                    "VALUES ('javaduke', '2007-12-13')");
            Statement orderStmt = conn.createStatement();
            orderStmt.execute(sb.toString(), Statement.RETURN_GENERATED_KEYS);

            ResultSet keys = orderStmt.getGeneratedKeys();
            int id = 1;
            while (keys.next()) {
                id = keys.getInt(1);                
            }

            String insertDetail = "INSERT INTO order_details (order_id, product_id, quantity, price) " +
                    "VALUES (?, ?, ?, ?)";
            PreparedStatement detailStmt = conn.prepareStatement(insertDetail);
            detailStmt.setInt(1, id);
            detailStmt.setString(2, "P0000001");
            detailStmt.setInt(3, 10);
            detailStmt.setDouble(4, 100);
            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 occured
            //
            if (conn != null) {
                conn.rollback();
                System.out.println("Connection rollback...");
            }
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}