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
|
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
You May Also Like:
Transaction basic concept
Insert and Select Rows with JdbcRowSet Objects
Update and Delete Rows with JdbcRowSet Objects
Jdbc RowSetListener example
If you have any feedback or suggestion please feel free to drop in below comment box.