JDBC Transactions

Most of the times transactions are important when we deal with JDBC. When we perform database operations if we don't realize the importance of transactions it will end up with a tragedy. I have created a scenario to demonstrate the importance of a transaction.
Scenario :
Lets assume a scenario where a transaction credit 500 rs from user1 and debit that 500 rs to user2 .

initial state of accounts

after executed non transaction method

after executed a proper transaction

Simply what we do in a transaction is do commitment of the transaction if all the operation of the transaction is completed successfully. for example just assume a transaction T that have operations called A,B,C. T is considered to be completed transaction if all operations A,B,C are completed successfully. if any operation fail to complete transaction T is considered as a incompleted transaction.

Important : Though JDBC supports transactions , all the DBMS es may not support transactions by default. for example I have used MySQL  as the DBMS in this scenario. But by default MySQL has enabled MyISAM storage engine on its tables. In order to get support for transactions in MySQL we have to use storage engine InnoDB for our tables. We can specify it when creating tables. (see the code)

SQL query for table creation:

CREATE TABLE `account` (
 `id` INT(11) NOT NULL DEFAULT '0',
 `name` VARCHAR(25) NULL DEFAULT NULL,
 `balance` FLOAT NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB;

Java Code for Invalid Transaction and proper Transaction

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package transactiondemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

/**
 *
 * @author Kanishka
 */
public class TransactionPerform {

    private final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private final String DB_USER = "root";
    private final String DB_PASS = "";
    private final String HOST = "localhost";
    private final String DATABASE = "test";
    private final String DB_CONNECTION_URL = "jdbc:mysql://" + HOST + ":3306/" + DATABASE;

    public TransactionPerform() {
    }

    public boolean doIncorrectTransaction() throws SQLException {
        boolean isDone = false;
        Connection dbConn = null;
        PreparedStatement preps_credit = null;
        PreparedStatement preps_debit = null;

        String creditQuery = "update account ac set ac.balance=ac.balance - ? where ac.id= ? ";
        String debitQuery = "update account ac set ac.balance=ac.balance + ? where ac.id= ? ";
        try {
            dbConn = getConnection();

            //debit 
            //operatin 1
            preps_credit = dbConn.prepareStatement(creditQuery);
            preps_credit.setFloat(1, 500.0f);
            preps_credit.setInt(2, 1);
            preps_credit.executeUpdate();

            //operation 2
            preps_debit = dbConn.prepareStatement(debitQuery);
            preps_debit.setFloat(1, 500);
            preps_debit.setInt(2, 2);
            //make an artificial interrupt before commiting the 2nd operation
            if (1 > 0) {
                throw new Exception("Unknow exception occured before commit the transaction");
            }
            preps_debit.executeUpdate();
            isDone = true;
            System.out.println("===Transaction Completed Successfully!===");

        } catch (Exception e) {
            System.err.println("Error occured " + e.getMessage());
        } finally {
            if (preps_credit != null) {
                preps_credit.close();
            }
            if (preps_debit != null) {
                preps_debit.close();
            }
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return isDone;
    }

    public boolean doCorrectTransaction() throws SQLException {
        boolean isDone = false;
        Connection dbConn = null;
        PreparedStatement preps_credit = null;
        PreparedStatement preps_debit = null;

        String creditQuery = "update account ac set ac.balance=ac.balance - ? where ac.id= ? ";
        String debitQuery = "update account ac set ac.balance=ac.balance + ? where ac.id= ? ";
        try {
            dbConn = getConnection();
            dbConn.setAutoCommit(false);//begining of the transaction

            //debit 
            //operatin 1
            preps_credit = dbConn.prepareStatement(creditQuery);
            preps_credit.setFloat(1, 500.0f);
            preps_credit.setInt(2, 1);
            preps_credit.executeUpdate();

            //operation 2
            preps_debit = dbConn.prepareStatement(debitQuery);
            preps_debit.setFloat(1, 500.0f);
            preps_debit.setInt(2, 2);
            //make an artificial interrupt before commiting the 2nd operation
            if (1 > 0) {
                //throw new  Exception("Unknown exception occured before commit the transaction");
            }
            preps_debit.executeUpdate();
            dbConn.commit();    //end of the transaction
            isDone = true;
            System.out.println("===Transaction Completed Successfully!===");

        } catch (Exception e) {
            System.err.println("Error occured :" + e.getMessage());
            dbConn.rollback();  //rollback the transaction
            System.out.println("Operation rollbacked!");
        } finally {
            if (preps_credit != null) {
                preps_credit.close();
            }
            if (preps_debit != null) {
                preps_debit.close();
            }
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return isDone;
    }

    private Connection getConnection() {
        Connection dbConn = null;
        try {
            Class.forName(DB_DRIVER);
            dbConn = DriverManager.getConnection(DB_CONNECTION_URL, DB_USER, DB_PASS);
            return dbConn;
        } catch (ClassNotFoundException e) {
            System.err.println("DB Driver class not found! : " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("Canno't create a connection to the Database! : " + e.getMessage());
        }
        return dbConn;
    }
}