JDBC transaction management example

In previous post we learnt about Transaction basic concept

In this post we will learn how to manage transaction in JDBC.

In JDBC API we have Connection interface which provides:

setAutoCommit(boolean) method to disable auto-commit mode by passing the false value. In JDBC by default commit is true so if you execute any statement it’s commits immediately
commit() method to commit the transaction if all statements are executed successfully.
rollback() method to cancel the transaction if any one of statements fails.

Project structure in eclipse:

DB Script to create citi_bank table and Inserting two account holders (database schema jdbcdb)
DB.sql

CREATE TABLE `citi_bank` (
  `account_no` bigint(11) NOT NULL,
  `account_balance` double default NULL,
  `account_type` varchar(45) NOT NULL,
  `account_holder_name` varchar(45) NOT NULL,
  PRIMARY KEY  (`account_no`)
);

INSERT INTO citi_bank(
   account_no
  ,account_balance
  ,account_type
  ,account_holder_name
) VALUES (
   9876835861
  ,85000  
  ,'Saving'
  ,'Martin'
),(
   9876598791
  ,20000  
  ,'Saving'
  ,'Paul'
);

CREATE TABLE `citi_bank` (

`account_no` bigint(11) NOT NULL,

`account_balance` double default NULL,

`account_type` varchar(45) NOT NULL,

`account_holder_name` varchar(45) NOT NULL,

PRIMARY KEY (`account_no`)

);

INSERT INTO citi_bank(

account_no

,account_balance

,account_type

,account_holder_name

) VALUES (

9876835861

,85000

,'Saving'

,'Martin'

),(

9876598791

,20000

,'Saving'

,'Paul'

);

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;

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

public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

Model class Account.java

package com.infotech.model;

public class Account {

	private Long accountNumber;
	private Double accountBalance;
	private String accountType;
	private String accountHolderName;
	
	public Long getAccountNumber() {
		return accountNumber;
	}
	public void setAccountNumber(Long accountNumber) {
		this.accountNumber = accountNumber;
	}
	public Double getAccountBalance() {
		return accountBalance;
	}
	public void setAccountBalance(Double accountBalance) {
		this.accountBalance = accountBalance;
	}
	public String getAccountType() {
		return accountType;
	}
	public void setAccountType(String accountType) {
		this.accountType = accountType;
	}
	public String getAccountHolderName() {
		return accountHolderName;
	}
	public void setAccountHolderName(String accountHolderName) {
		this.accountHolderName = accountHolderName;
	}
}

package com.infotech.model;

public class Account {

private Long accountNumber;

private Double accountBalance;

private String accountType;

private String accountHolderName;

public Long getAccountNumber() {

return accountNumber;

}

public void setAccountNumber(Long accountNumber) {

this.accountNumber = accountNumber;

}

public Double getAccountBalance() {

return accountBalance;

}

public void setAccountBalance(Double accountBalance) {

this.accountBalance = accountBalance;

}

public String getAccountType() {

return accountType;

}

public void setAccountType(String accountType) {

this.accountType = accountType;

}

public String getAccountHolderName() {

return accountHolderName;

}

public void setAccountHolderName(String accountHolderName) {

this.accountHolderName = accountHolderName;

}

Service interface and It’s implementation class:

package com.infotech.service;

import java.sql.SQLException;
import com.infotech.exception.AccountNotFoundException;
import com.infotech.exception.InsufficientBalanceException;
import com.infotech.model.Account;

public interface BankService {
	public void fundTransfer(Account fromAccount,Account toAccount,Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException;
}

package com.infotech.service;

import java.sql.SQLException;

import com.infotech.exception.AccountNotFoundException;

import com.infotech.exception.InsufficientBalanceException;

import com.infotech.model.Account;

public interface BankService {

public void fundTransfer(Account fromAccount,Account toAccount,Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException;

}

package com.infotech.service;

import java.sql.Connection;
import java.sql.SQLException;
import com.infotech.dao.BankDAO;
import com.infotech.dao.impl.BankDAOImpl;
import com.infotech.model.Account;
import com.infotech.util.DBUtil;

public class BankServiceImpl implements BankService{

	BankDAO bankDAO = new BankDAOImpl();
	@Override
	public void fundTransfer(Account fromAccount, Account toAccount, Double amount) throws SQLException {
		Connection connection = DBUtil.getConnection();
		connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		try{
			connection.setAutoCommit(false);
			
			bankDAO.withdraw(connection,fromAccount, toAccount, amount);
			bankDAO.deposit(connection,fromAccount, toAccount, amount);
			
			connection.commit();

		} catch (Exception e) {
			if(connection != null){
				connection.rollback();
			}
			e.printStackTrace();
		}finally {
			if(connection != null){
				connection.close();
			}
		}
	}

	
	//Without handling transaction
/*	@Override
	public void fundTransfer(Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException {
		Connection connection = DBUtil.getConnection();
		bankDAO.withdraw(connection, fromAccount, toAccount, amount);
		bankDAO.deposit(connection, fromAccount, toAccount, amount);
	}*/
}

package com.infotech.service;

import java.sql.Connection;

import java.sql.SQLException;

import com.infotech.dao.BankDAO;

import com.infotech.dao.impl.BankDAOImpl;

import com.infotech.model.Account;

import com.infotech.util.DBUtil;

public class BankServiceImpl implements BankService{

BankDAO bankDAO = new BankDAOImpl();

@Override

public void fundTransfer(Account fromAccount, Account toAccount, Double amount) throws SQLException {

Connection connection = DBUtil.getConnection();

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

try{

connection.setAutoCommit(false);

bankDAO.withdraw(connection,fromAccount, toAccount, amount);

bankDAO.deposit(connection,fromAccount, toAccount, amount);

connection.commit();

} catch (Exception e) {

if(connection != null){

connection.rollback();

}

e.printStackTrace();

}finally {

if(connection != null){

connection.close();

}

//Without handling transaction

/* @Override

public void fundTransfer(Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException {

Connection connection = DBUtil.getConnection();

bankDAO.withdraw(connection, fromAccount, toAccount, amount);

bankDAO.deposit(connection, fromAccount, toAccount, amount);

}*/

}

DAO(Data access Object) interface and it’s implementation class:

package com.infotech.dao;

import java.sql.Connection;
import java.sql.SQLException;
import com.infotech.exception.AccountNotFoundException;
import com.infotech.exception.InsufficientBalanceException;
import com.infotech.model.Account;

public interface BankDAO {
	public void withdraw(Connection connection,Account fromAccount,Account toAccount,Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException;
	public void deposit(Connection connection,Account fromAccount,Account toAccount,Double amount)throws SQLException, AccountNotFoundException;
}

package com.infotech.dao;

import java.sql.Connection;

import java.sql.SQLException;

import com.infotech.exception.AccountNotFoundException;

import com.infotech.exception.InsufficientBalanceException;

import com.infotech.model.Account;

public interface BankDAO {

public void withdraw(Connection connection,Account fromAccount,Account toAccount,Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException;

public void deposit(Connection connection,Account fromAccount,Account toAccount,Double amount)throws SQLException, AccountNotFoundException;

}

package com.infotech.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.infotech.dao.BankDAO;
import com.infotech.exception.AccountNotFoundException;
import com.infotech.exception.InsufficientBalanceException;
import com.infotech.model.Account;

public class BankDAOImpl implements BankDAO {

	private final String UPDATE_ACCOUNT_SQL = "UPDATE citi_bank set account_balance = ? WHERE account_no=?";
	@Override
	public void withdraw(Connection connection,Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException {
		Account fromAccountFromDb = getCurrentAccountDetail(connection, fromAccount.getAccountNumber());
		if(fromAccountFromDb !=null){
			if(fromAccountFromDb.getAccountBalance()<amount){
				throw new InsufficientBalanceException("Insufficient funds");
			}
		}else{
			throw new AccountNotFoundException("Source account doesn't exist..");
		}
		
		PreparedStatement ps = connection.prepareStatement(UPDATE_ACCOUNT_SQL);
		Double updateBalance = fromAccountFromDb.getAccountBalance()-amount;
		
		ps.setDouble(1, updateBalance);
		ps.setLong(2, fromAccount.getAccountNumber());
		
		int executeUpdate = ps.executeUpdate();
		if(executeUpdate == 1){
			System.out.println("Amount $"+amount+" is transferred from account:"+fromAccount.getAccountNumber()+" to account:"+toAccount.getAccountNumber());
		}
	}

	@Override
	public void deposit(Connection connection,Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException {
		Account toAccountFromDb = getCurrentAccountDetail(connection, toAccount.getAccountNumber());
		if(toAccountFromDb == null)
			throw new AccountNotFoundException("Destination account doesn't exist..");
	
		PreparedStatement ps = connection.prepareStatement(UPDATE_ACCOUNT_SQL);
		Double updateBalance = toAccountFromDb.getAccountBalance()+amount;
		
		ps.setDouble(1, updateBalance);
		ps.setLong(2, toAccount.getAccountNumber());
		
		int executeUpdate = ps.executeUpdate();
		if(executeUpdate == 1){
			System.out.println("Amount $"+amount+" is deposited in account:"+toAccount.getAccountNumber());
		}
	}
	
	private Account getCurrentAccountDetail(Connection connection, Long fromAccountNumber) throws SQLException{
		String GET_ACCOUNT_SQL="SELECT *FROM citi_bank WHERE account_no=?";
		try(PreparedStatement ps=connection.prepareStatement(GET_ACCOUNT_SQL)){
			ps.setLong(1, fromAccountNumber);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				Account dBaccount = new Account();
				dBaccount.setAccountNumber(rs.getLong("account_no"));
				dBaccount.setAccountBalance(rs.getDouble("account_balance"));
				dBaccount.setAccountHolderName(rs.getString("account_holder_name"));
				dBaccount.setAccountType(rs.getString("account_type"));
				return dBaccount;
			}
		}
		return null;
	}
}

package com.infotech.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.infotech.dao.BankDAO;

import com.infotech.exception.AccountNotFoundException;

import com.infotech.exception.InsufficientBalanceException;

import com.infotech.model.Account;

public class BankDAOImpl implements BankDAO {

private final String UPDATE_ACCOUNT_SQL = "UPDATE citi_bank set account_balance = ? WHERE account_no=?";

@Override

public void withdraw(Connection connection,Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException, InsufficientBalanceException {

Account fromAccountFromDb = getCurrentAccountDetail(connection, fromAccount.getAccountNumber());

if(fromAccountFromDb !=null){

if(fromAccountFromDb.getAccountBalance()<amount){

throw new InsufficientBalanceException("Insufficient funds");

}

}else{

throw new AccountNotFoundException("Source account doesn't exist..");

}

PreparedStatement ps = connection.prepareStatement(UPDATE_ACCOUNT_SQL);

Double updateBalance = fromAccountFromDb.getAccountBalance()-amount;

ps.setDouble(1, updateBalance);

ps.setLong(2, fromAccount.getAccountNumber());

int executeUpdate = ps.executeUpdate();

if(executeUpdate == 1){

System.out.println("Amount $"+amount+" is transferred from account:"+fromAccount.getAccountNumber()+" to account:"+toAccount.getAccountNumber());

}

@Override

public void deposit(Connection connection,Account fromAccount, Account toAccount, Double amount) throws SQLException, AccountNotFoundException {

Account toAccountFromDb = getCurrentAccountDetail(connection, toAccount.getAccountNumber());

if(toAccountFromDb == null)

throw new AccountNotFoundException("Destination account doesn't exist..");

PreparedStatement ps = connection.prepareStatement(UPDATE_ACCOUNT_SQL);

Double updateBalance = toAccountFromDb.getAccountBalance()+amount;

ps.setDouble(1, updateBalance);

ps.setLong(2, toAccount.getAccountNumber());

int executeUpdate = ps.executeUpdate();

if(executeUpdate == 1){

System.out.println("Amount $"+amount+" is deposited in account:"+toAccount.getAccountNumber());

}

private Account getCurrentAccountDetail(Connection connection, Long fromAccountNumber) throws SQLException{

String GET_ACCOUNT_SQL="SELECT *FROM citi_bank WHERE account_no=?";

try(PreparedStatement ps=connection.prepareStatement(GET_ACCOUNT_SQL)){

ps.setLong(1, fromAccountNumber);

ResultSet rs = ps.executeQuery();

if(rs.next()){

Account dBaccount = new Account();

dBaccount.setAccountNumber(rs.getLong("account_no"));

dBaccount.setAccountBalance(rs.getDouble("account_balance"));

dBaccount.setAccountHolderName(rs.getString("account_holder_name"));

dBaccount.setAccountType(rs.getString("account_type"));

return dBaccount;

}

return null;

}

Exception classes:AccountNotFoundException.java and InsufficientBalanceException.java

package com.infotech.exception;

public class AccountNotFoundException extends Exception{
	private static final long serialVersionUID = -7562695630517823112L;
	public AccountNotFoundException(String message) {
		super(message);
	}
}

package com.infotech.exception;

public class AccountNotFoundException extends Exception{

private static final long serialVersionUID = -7562695630517823112L;

public AccountNotFoundException(String message) {

super(message);

}

package com.infotech.exception;

public class InsufficientBalanceException extends Exception{
	private static final long serialVersionUID = -7562695630517823112L;
	public InsufficientBalanceException(String message) {
		super(message);
	}
}

package com.infotech.exception;

public class InsufficientBalanceException extends Exception{

private static final long serialVersionUID = -7562695630517823112L;

public InsufficientBalanceException(String message) {

super(message);

}

Client program

package com.infotech.client;

import java.sql.SQLException;
import com.infotech.exception.AccountNotFoundException;
import com.infotech.exception.InsufficientBalanceException;
import com.infotech.model.Account;
import com.infotech.service.BankService;
import com.infotech.service.BankServiceImpl;

public class ClientTest {

	public static void main(String[] args) {
		
		BankService bankService = new BankServiceImpl();
		
		Double transferAmout = 1000.00;
		Account fromAccount = new Account();
		fromAccount.setAccountNumber(9876835861L);
		
		Account toAccount = new Account();
		toAccount.setAccountNumber(9876598791L);
		
		try {
			bankService.fundTransfer(fromAccount, toAccount, transferAmout);
		} catch (SQLException | AccountNotFoundException | InsufficientBalanceException e) {
			System.out.println(e.getMessage());
		}
	}
}

package com.infotech.client;

import java.sql.SQLException;

import com.infotech.exception.AccountNotFoundException;

import com.infotech.exception.InsufficientBalanceException;

import com.infotech.model.Account;

import com.infotech.service.BankService;

import com.infotech.service.BankServiceImpl;

public class ClientTest {

public static void main(String[] args) {

BankService bankService = new BankServiceImpl();

Double transferAmout = 1000.00;

Account fromAccount = new Account();

fromAccount.setAccountNumber(9876835861L);

Account toAccount = new Account();

toAccount.setAccountNumber(9876598791L);

try {

bankService.fundTransfer(fromAccount, toAccount, transferAmout);

} catch (SQLException | AccountNotFoundException | InsufficientBalanceException e) {

System.out.println(e.getMessage());

}

That’s all for this topic JDBC transaction management example

Leave a Reply Cancel reply