/*
* 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;
}
