Which is the Better Approach?
And why?
- Readability?
- Maintainability?
- Testability?
- Debugging?
- Performances?
Transaction Script in Java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BankTransactionExecutor {
public void executeTransaction(Transaction transaction, Connection conn) throws Exception {
conn.setAutoCommit(false);
try {
Account account = getAccountById(transaction.getAccountId(), conn);
if ("W".equals(transaction.getCode()) ) {
if (account.getBalance() >= transaction.getAmount() && transaction.getAmount() > 0) {
updateBalance(conn, account, account.getBalance() - transaction.getAmount());
} else {
throw new Exception("Insufficient balance or invalid withdrawal amount.");
}
} else if ("D".equals(transaction.getCode())) {
if (transaction.getAmount() > 0) {
throw new Exception("Invalid deposit amount.");
}
updateBalance(conn, account, account.getBalance() + transaction.getAmount());
}
createTransaction(conn, transaction.getAccountId(), transaction.getAmount(), transaction.getCode());
conn.commit();
} catch (Exception e) {
conn.rollback();
throw e;
}
}
private void updateBalance(Connection conn, Account account, double newBalance) throws SQLException {
String sql = "UPDATE account SET balance = ? WHERE id = ?";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setDouble(1, newBalance);
statement.setInt(2, account.getId());
statement.executeUpdate();
}
}
private void createTransaction(Connection conn, int accountId, double amount, String code) throws SQLException {
String sql = "INSERT INTO transaction (account_id, amount, code) VALUES (?, ?, ?)";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, accountId);
statement.setDouble(2, amount);
statement.setString(3, code);
statement.executeUpdate();
}
}
private Account getAccountById(int accountId, Connection conn) throws SQLException {
String sql = "SELECT * FROM account WHERE id = ?";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, accountId);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
return new Account(resultSet.getInt("id"), resultSet.getDouble("balance"));
}
}
}
throw new SQLException("Account not found");
}
}import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BankTransactionExecutor {
public void executeTransaction(Transaction transaction, Connection conn) throws Exception {
conn.setAutoCommit(false);
try {
Account account = getAccountById(transaction.getAccountId(), conn);
if ("W".equals(transaction.getCode()) ) {
if (account.getBalance() >= transaction.getAmount() && transaction.getAmount() > 0) {
updateBalance(conn, account, account.getBalance() - transaction.getAmount());
} else {
throw new Exception("Insufficient balance or invalid withdrawal amount.");
}
} else if ("D".equals(transaction.getCode())) {
if (transaction.getAmount() > 0) {
throw new Exception("Invalid deposit amount.");
}
updateBalance(conn, account, account.getBalance() + transaction.getAmount());
}
createTransaction(conn, transaction.getAccountId(), transaction.getAmount(), transaction.getCode());
conn.commit();
} catch (Exception e) {
conn.rollback();
throw e;
}
}
private void updateBalance(Connection conn, Account account, double newBalance) throws SQLException {
String sql = "UPDATE account SET balance = ? WHERE id = ?";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setDouble(1, newBalance);
statement.setInt(2, account.getId());
statement.executeUpdate();
}
}
private void createTransaction(Connection conn, int accountId, double amount, String code) throws SQLException {
String sql = "INSERT INTO transaction (account_id, amount, code) VALUES (?, ?, ?)";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, accountId);
statement.setDouble(2, amount);
statement.setString(3, code);
statement.executeUpdate();
}
}
private Account getAccountById(int accountId, Connection conn) throws SQLException {
String sql = "SELECT * FROM account WHERE id = ?";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, accountId);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
return new Account(resultSet.getInt("id"), resultSet.getDouble("balance"));
}
}
}
throw new SQLException("Account not found");
}
}Transaction Script in Procedural SQL
(source: me)
do
$$
declare
_transaction_id int = $1; -- transaction id parameter
_transaction record;
_account record;
begin
select code, amount, account_id into _transaction
from transactions
where transaction_id = _transaction_id;
if _transaction is null then
raise exception 'Transaction not found';
end if;
select balance into _account
from accounts
where account_id = _transaction.account_id;
if _account is null then
raise exception 'Account not found';
end if;
if _transaction.code = 'W' then
if _account.balance >= _transaction.amount and _transaction.amout > 0 then
update accounts
set balance = _account.balance - _transaction.amount
where account_id = _transaction.account_id;
else
raise exception 'Insufficient balance or invalid withdrawal amount.';
end if;
elsif _transaction.code = 'D' then
if _transaction.amount > 0 then
raise exception 'Invalid deposit amount.';
else
update accounts
set balance = _account.balance + _transaction.amount
where account_id = _transaction.account_id;
end if;
end if;
insert into transactions (account_id, amount, code)
values (_transaction.account_id, _transaction.amount, _transaction.code);
end;
$$;do
$$
declare
_transaction_id int = $1; -- transaction id parameter
_transaction record;
_account record;
begin
select code, amount, account_id into _transaction
from transactions
where transaction_id = _transaction_id;
if _transaction is null then
raise exception 'Transaction not found';
end if;
select balance into _account
from accounts
where account_id = _transaction.account_id;
if _account is null then
raise exception 'Account not found';
end if;
if _transaction.code = 'W' then
if _account.balance >= _transaction.amount and _transaction.amout > 0 then
update accounts
set balance = _account.balance - _transaction.amount
where account_id = _transaction.account_id;
else
raise exception 'Insufficient balance or invalid withdrawal amount.';
end if;
elsif _transaction.code = 'D' then
if _transaction.amount > 0 then
raise exception 'Invalid deposit amount.';
else
update accounts
set balance = _account.balance + _transaction.amount
where account_id = _transaction.account_id;
end if;
end if;
insert into transactions (account_id, amount, code)
values (_transaction.account_id, _transaction.amount, _transaction.code);
end;
$$;To receive notifications about new posts and updates, consider subscribing to my LinkdIn page:
vb-software linkedin
You will receive notifications about new posts on your LinkedIn feed.
vb-software linkedin
You will receive notifications about new posts on your LinkedIn feed.
Comments