BankingService.java

package banking;

import java.sql.*;



public class BankingService {
    private final Connection con;

    public BankingService(Connection con) {
        this.con = con;
    }

    /**
     * Record transaction in database
     */
    private void recordTransaction(int sender, int receiver, int amount, String type) {
        try {
            String sql = "INSERT INTO transactions(sender_ac, receiver_ac, amount, trans_type) VALUES (?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, sender);
            ps.setInt(2, receiver);
            ps.setInt(3, amount);
            ps.setString(4, type);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Get transaction history for an account
     */
    public ResultSet getTransactionHistory(int acNo) throws SQLException {
        String sql = "SELECT t.*, " +
                "CASE " +
                "  WHEN t.sender_ac = ? THEN 'DEBIT' " +
                "  ELSE 'CREDIT' " +
                "END as type, " +
                "CASE " +
                "  WHEN t.sender_ac = ? THEN t.receiver_ac " +
                "  ELSE t.sender_ac " +
                "END as other_account " +
                "FROM transactions t " +
                "WHERE t.sender_ac = ? OR t.receiver_ac = ? " +
                "ORDER BY t.trans_date DESC";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, acNo);
        ps.setInt(2, acNo);
        ps.setInt(3, acNo);
        ps.setInt(4, acNo);
        return ps.executeQuery();
    }


    public boolean createAccount(String name, int pass) throws SQLException {
        String sql = "INSERT INTO customer(cname,balance,pass_code) VALUES (?,1000,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, name);
        ps.setInt(2, pass);
        return ps.executeUpdate() == 1;
    }

    public ResultSet login(String name, int pass) throws SQLException {
        String sql = "SELECT * FROM customer WHERE cname=? AND pass_code=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, name);
        ps.setInt(2, pass);
        return ps.executeQuery();
    }

    public ResultSet getAccount(int acNo) throws SQLException {
        PreparedStatement ps = con.prepareStatement("SELECT * FROM customer WHERE ac_no=?");
        ps.setInt(1, acNo);
        return ps.executeQuery();
    }

    public boolean transfer(int sender, int receiver, int amt) throws Exception {
        con.setAutoCommit(false);
        PreparedStatement chk = con.prepareStatement("SELECT ac_no FROM customer WHERE ac_no=?");
        chk.setInt(1, receiver);
        if (!chk.executeQuery().next()) return false;

        PreparedStatement bal = con.prepareStatement("SELECT balance FROM customer WHERE ac_no=?");
        bal.setInt(1, sender);
        ResultSet rs = bal.executeQuery();
        if (rs.next() && rs.getInt(1) < amt) return false;

        PreparedStatement debit = con.prepareStatement("UPDATE customer SET balance=balance-? WHERE ac_no=?");
        debit.setInt(1, amt);
        debit.setInt(2, sender);
        debit.executeUpdate();

        PreparedStatement credit = con.prepareStatement("UPDATE customer SET balance=balance+? WHERE ac_no=?");
        credit.setInt(1, amt);
        credit.setInt(2, receiver);
        credit.executeUpdate();

        // Record the transaction
        recordTransaction(sender, receiver, amt, "TRANSFER");

        con.commit();
        con.setAutoCommit(true);
        return true;
    }

}