/* * The method: 1) obtains a connection, 2) starts a transaction, 3) performs * the database work, 4) commits or rolls back the connection, 5) releases * the connection */ public static void depositToAccount(int accountId, double amt) throws AccountDbFailure, SQLException { try (Connection dbConn = BankingAppDataSource.getConnection()) { // Turn off auto-commit so we can use transactions dbConn.setAutoCommit(false); /* * We are now operating under a transaction, rollback on an * exception or commit on successful completion */ try { depositToAccountWithConn(accountId, amt, dbConn); dbConn.commit(); /* * End of transaction. Since it was successful, * commit it. */ } catch (AccountDbFailure ex) { dbConn.rollback(); throw ex; /* * rethrow the exception so the caller function can * recover from this failure */ } catch (Exception ex) { /* * This is a catch-all -- rollback transaction and throw a more * useful exception */ dbConn.rollback(); throw new AccountDbFailure(AccountDbFailure.STMT_FAILED, ex.getMessage()); } } }
public static void doTransferRetry(int fromAcctId, double amt, int toAcctId) throws AccountDbFailure, SQLException { double curBal; double newBalance = 0.0; int rowsAffected; Connection dbConn; String updateCurBalSql = "UPDATE account SET balance = ? where id = ? ;"; dbConn = BankingAppDataSource.getConnection(); dbConn.setAutoCommit(false); curBal = getAcctBalanceWithConn(fromAcctId, dbConn); newBalance = curBal - amt; try (PreparedStatement updateCurBalStmt = dbConn.prepareStatement(updateCurBalSql)) { updateCurBalStmt.setDouble(1, newBalance); updateCurBalStmt.setInt(2, fromAcctId); rowsAffected = updateCurBalStmt.executeUpdate(); if (rowsAffected != 1) { dbConn.rollback(); throw new AccountDbFailure(AccountDbFailure.STMT_FAILED); } depositToAccountWithConn(toAcctId, amt, dbConn); dbConn.commit(); } catch (SQLException ex) { dbConn.rollback(); throw new AccountDbFailure(AccountDbFailure.STMT_FAILED); } dbConn.close(); }
/* * Call this method if we are not currently using a connection (so we need * to obtain a connection) */ public static double getAcctBalance(int accountId) throws AccountDbFailure, SQLException { double curBal = 0.0; try (Connection dbConn = BankingAppDataSource.getConnection()) { curBal = getAcctBalanceWithConn(accountId, dbConn); } return curBal; }