07/07

계좌 이체내역

---

실습

---

DTO -> 화면에 보낼 데이터를 담는 가방
(1번의 통신으로 보낼 수 있도록 구성함)
1개의 데이터를 특정 포맷으로 보낼때도 사용(비번제외)

---

DBConnection 만들기

---

package db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
    public static Connection getInstance(){
        // MySQL 연결 정보
        String url = "jdbc:mysql://localhost:3306/metadb";
        String username = "root";
        String password = "2-------";

        // JDBC 드라이버 로드
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("디버그 : DB연결 성공");
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

---

Model 만들기

---

Account, Transaction, AccountDAO, TranjactionDAO 만들기

Account

Tranjaction

AccountDAO

package model.account;

import db.DBConnection;
import lombok.Getter;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Getter
public class AccountDAO {
    private Connection connection;

    public AccountDAO(Connection connection) {
        this.connection = connection;
    }

    // 계좌생성 (기본 1000원 입금 되어야 함)
    public void createAccount(int accountNumber, String accountPassword, int accountBalance) throws SQLException {
        String query = "INSERT INTO account_tb (account_number, account_password, account_balance, account_created_at) VALUES (?, ?, ?, now())";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setInt(1, accountNumber);
            statement.setString(2, accountPassword);
            statement.setInt(3, accountBalance);
            statement.executeUpdate();
        }
    }

    // 계좌 상세보기
    public Account getAccountByNumber(int accountNumber) throws SQLException {
        String query = "SELECT * FROM account_tb WHERE account_number = ?";
        //try with resource문
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setInt(1, accountNumber);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    return buildAccountFromResultSet(resultSet);
                }
            }
        }
        return null; // Account not found
    }

    // 전체 계좌 조회
    public List<Account> getAllAccounts() throws SQLException {
        List<Account> accounts = new ArrayList<>();
        String query = "SELECT * FROM account_tb";
        try (Statement statement = connection.createStatement()) {
            try(ResultSet resultSet = statement.executeQuery(query)){
                while (resultSet.next()) {
                    Account account = buildAccountFromResultSet(resultSet);
                    accounts.add(account);
                }
            }
        }
        return accounts;
    }

    // 계좌 잔액 수정
    public void updateAccount(int accountBalance, int accountNumber) throws SQLException {
        String query = "UPDATE account_tb SET account_balance = ?  WHERE account_number = ?";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setInt(1, accountBalance);
            statement.setInt(2, accountNumber);
            statement.executeUpdate();
        }
    }

    // 계좌 삭제
    public void deleteAccount(int accountNumber) throws SQLException {
        String query = "DELETE FROM account_tb WHERE account_number = ?";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setInt(1, accountNumber);
            statement.executeUpdate();
        }
    }

    private Account buildAccountFromResultSet(ResultSet resultSet) throws SQLException {
        int accountNumber = resultSet.getInt("account_number");
        String accountPassword = resultSet.getString("account_password");
        int accountBalance = resultSet.getInt("account_balance");
        Timestamp accountCreatedAt = resultSet.getTimestamp("account_created_at");

        return Account.builder()
                .accountNumber(accountNumber)
                .accountPassword(accountPassword)
                .accountBalance(accountBalance)
                .accountCreatedAt(accountCreatedAt)
                .build();
    }
}

TranjactionDAO

package model.transaction;

import db.DBConnection;
import dto.AccountDetailDTO;
import lombok.Getter;
import model.account.Account;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Getter
public class TransactionDAO {
    private Connection connection;

    public TransactionDAO(Connection connection) {
        this.connection = connection;
    }

    public List<AccountDetailDTO> details(int accountNumber) throws SQLException {
        List<AccountDetailDTO> dtos = new ArrayList<>();
        String sql = "select \n" +
                "ac.account_number, \n" +
                "ac.account_balance, \n" +
                "ts.transaction_amount amount, \n" +
                "ts.transaction_w_account_number sender, \n" +
                "ts.transaction_d_account_number receiver,\n" +
                "if(ts.transaction_w_account_number= ?,ts.transaction_w_balance, ts.transaction_d_balance) balance,\n" +
                "ts.transaction_created_at transfer_date\n" +
                "from account_tb ac \n" +
                "inner join transaction_tb ts on ac.account_number = ts.transaction_w_account_number \n" +
                "OR ac.account_number = ts.transaction_d_account_number\n" +
                "where ac.account_number = ?";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, accountNumber);
            statement.setInt(2, accountNumber);
            try(ResultSet rs = statement.executeQuery()){
                while (rs.next()) {
                    AccountDetailDTO dto = AccountDetailDTO.builder()
                            .accountNumber(rs.getInt("account_number"))
                            .currentBalance(rs.getInt("account_balance"))
                            .sender(rs.getInt("sender"))
                            .receiver(rs.getInt("receiver"))
                            .amount(rs.getInt("amount"))
                            .balance(rs.getInt("balance"))
                            .transferDate(rs.getTimestamp("transfer_date"))
                            .build();
                    dtos.add(dto);
                }
            }
        }
        return dtos;
    }

    public void transfer(Transaction transaction) throws SQLException{
        String query = "INSERT INTO transaction_tb (transaction_amount, transaction_w_balance, transaction_d_balance, transaction_w_account_number, transaction_d_account_number, transaction_created_at) VALUES (?, ?, ?, ?, ?, now())";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setInt(1, transaction.getTransactionAmount());
            statement.setInt(2, transaction.getTransactionWBalance());
            statement.setInt(3, transaction.getTransactionDBalance());
            statement.setInt(4, transaction.getTransactionWAccountNumber());
            statement.setInt(5, transaction.getTransactionDAccountNumber());
            statement.executeUpdate();
        }
    }


}

---

빌더패턴 #수동

---

package test;

import lombok.Getter;

import java.sql.Timestamp;

@Getter
class Member {
    private Integer id;
    private String username;
    private String password;
    private Boolean status; // true 활동, false 휴먼
    private Timestamp createdAt; // 가입날짜 now()

    public Member id(Integer id){
        this.id = id;
        return this;
    }

    public Member username(String username){
        this.username = username;
        return this;
    }

    public Member password(String password){
        this.password = password;
        return this;
    }

    public static Member builder(){
        return new Member();
    }
}

public class Builder03 {

    public static void main(String[] args) {
        Member m1 = Member.builder().password("1234").id(1).username("ssar");
    }
}

---

빌더 패턴 #lombok 이용

---

package test;

import lombok.Builder;
import lombok.Getter;

import java.sql.Timestamp;

@Getter
class 인간 {
    private Integer id;
    private String username;
    private String password;
    private Boolean status; // true 활동, false 휴먼
    private Timestamp createdAt; // 가입날짜 now()

    @Builder
    public 인간(Integer id, String username, String password, Boolean status, Timestamp createdAt) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.status = status;
        this.createdAt = createdAt;
    }

}

public class Builder04 {
    public static void main(String[] args) {
        인간 s = 인간.builder().username("ssar").password("1234").build();
    }
}

---

BankAPP 만들기

---

import db.DBConnection;
import dto.AccountDetailDTO;
import model.account.Account;
import model.account.AccountDAO;
import model.transaction.Transaction;
import model.transaction.TransactionDAO;
import util.MyStringUtils;

import java.sql.Connection;
import java.util.List;

public class BankApp {
    public static void main(String[] args) {
        Connection connection = DBConnection.getInstance();
        AccountDAO accountDAO = new AccountDAO(connection);
        TransactionDAO transactionDAO = new TransactionDAO(connection);
//        try {
//            // 이체 요청 정보
//            String wAccountPassword = "1234";
//            int wAccountNumber = 3333;
//            int dAccountNumber = 2222;
//            int amount = 1200;
//
//            // 0원 이체 확인하기 (컨트롤러에서 체크)
//            if(amount <= 0){
//                System.out.println("[유효성 오류] 0원 이하의 금액을 이체할 수 없습니다");
//                return;
//            }
//
//            // 동일 계좌 이체 확인하기 (컨트롤러에서 체크)
//            if(wAccountNumber == dAccountNumber){
//                System.out.println("[유효성 오류] 입출금 계좌가 동일할 수 없습니다");
//                return;
//            }
//
//            // --------------------------------------------- 트랜잭션 시작
//            connection.setAutoCommit(false);
//
//            // 계좌 찾기 (서비스에서 체크)
//            Account wAccount = accountDAO.getAccountByNumber(wAccountNumber);
//            Account dAccount = accountDAO.getAccountByNumber(dAccountNumber);
//
//            // 계좌 존재 확인 (서비스에서 체크)
//            if(wAccount == null){
//                throw new RuntimeException("출금 계좌가 존재하지 않습니다");
//            }
//            if(dAccount == null){
//                throw new RuntimeException("입금 계좌가 존재하지 않습니다");
//            }
//
//            // 계좌 비밀번호 확인 (서비스에서 체크)
//            if(!wAccount.getAccountPassword().equals(wAccountPassword)){
//                throw new RuntimeException("출금 계좌의 비밀번호가 올바르지 않습니다");
//            }
//
//            // 계좌 잔액 확인 (서비스에서 체크)
//            if(wAccount.getAccountBalance() < amount){
//                throw new RuntimeException("출금 계좌의 잔액이 부족합니다");
//            }
//
//            // 계좌 업데이트 (서비스에서 업데이트)
//            int wBalance = wAccount.getAccountBalance() - amount;
//            int dBalance = dAccount.getAccountBalance() + amount;
//            accountDAO.updateAccount(wBalance, wAccountNumber);
//            accountDAO.updateAccount(dBalance, dAccountNumber);
//
//            // 트랜잭션 이력 남기기 (서비스에서 인서트)
//            Transaction transaction = Transaction.builder()
//                    .transactionAmount(amount)
//                    .transactionWAccountNumber(wAccountNumber)
//                    .transactionDAccountNumber(dAccountNumber)
//                    .transactionWBalance(wBalance)
//                    .transactionDBalance(dBalance)
//                    .build();
//            transactionDAO.transfer(transaction);
//            connection.commit();
//            // --------------------------------------------- 트랜잭션 종료
//        } catch (Exception e) {
//            try {
//                connection.rollback();
//                System.out.println("[catch] "+e.getMessage());
//            }catch (Exception innerEx){
//                innerEx.printStackTrace();
//            }
//        } finally {
//            try {
//                connection.setAutoCommit(true);
//            }catch (Exception e){
//                e.printStackTrace();
//            }
//
//        }


        try {
            int accountNumber = 1111;
            List<AccountDetailDTO> dtos = transactionDAO.details(accountNumber);
            System.out.println("=============000님의 계좌 상세===============");
            System.out.println("본인 계좌번호 : "+dtos.get(0).getAccountNumber());
            System.out.println("본인 계좌현재잔액 : "+dtos.get(0).getCurrentBalance());
            System.out.println("=============트랜잭션 내역 시작===============");
            dtos.forEach(dto -> {
                System.out.print("[");
                if(dto.getSender() == accountNumber){
                    System.out.println("**출금**");
                }else{
                    System.out.println("**입금**");
                }
                System.out.println("    보낸 계좌 : "+dto.getSender());
                System.out.println("    받은 계좌 : "+dto.getReceiver());
                System.out.println("    이체 금액 : "+dto.getAmount());
                System.out.println("    잔액 : "+dto.getBalance());
                System.out.println("    날짜 : "+ MyStringUtils.dateFormat(dto.getTransferDate()));
                System.out.println("]");
                System.out.println();
            });
            System.out.println("=============트랜잭션 내역 끝===============");
        }catch (Exception e){

        }

    }
}

---

MyStringUtils

---

package util;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class MyStringUtils {


    public static String dateFormat(Timestamp transferDate){
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm");
        String formattedDate = dateFormat.format(transferDate);
        return formattedDate;
    }

}

---

출력 결과

---