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.
DB Script to create citi_bank table and Inserting two account holders (database schema jdbcdb)
DB.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
1 2 3 4 5 6 7 8 9 10 |
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; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 |
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; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
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
1 2 3 4 5 6 7 8 |
package com.infotech.exception; public class AccountNotFoundException extends Exception{ private static final long serialVersionUID = -7562695630517823112L; public AccountNotFoundException(String message) { super(message); } } |
1 2 3 4 5 6 7 8 |
package com.infotech.exception; public class InsufficientBalanceException extends Exception{ private static final long serialVersionUID = -7562695630517823112L; public InsufficientBalanceException(String message) { super(message); } } |
Client program
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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