Day22_Banking 예제 2

2023.09.12

 

DB TABLES

 

* user : ICIA

 

/*
    ACCOUNT A
    CLIENT  B
    ACODE   C
*/

-- 먼저 초기화 진행
DROP TABLE ACCOUNT;
DROP TABLE ACODE;   
DROP TABLE CLIENT;


CREATE TABLE CLIENT(
    CNUM    NUMBER UNIQUE,
    CID     NVARCHAR2(50) PRIMARY KEY,
    CPW     NVARCHAR2(50),
    CNAME   NVARCHAR2(50),
    CPHONE  NVARCHAR2(50),
    CADDR   NVARCHAR2(50)
);

CREATE TABLE ACODE(
    CODENUM     NUMBER PRIMARY KEY,
    CODENAME    NVARCHAR2(10)
);

INSERT INTO ACODE VALUES(1, '일반');
INSERT INTO ACODE VALUES(2, '청약');
INSERT INTO ACODE VALUES(3, '주식');
INSERT INTO ACODE VALUES(4, '적금');

CREATE TABLE ACCOUNT(
    CACCOUNT    NVARCHAR2(50) PRIMARY KEY,
    CID         NVARCHAR2(50),
    CODENUM     NUMBER,
    BALANCE     NUMBER,
    CONSTRAINT FK_CID FOREIGN KEY (CID) REFERENCES CLIENT(CID),
    CONSTRAINT FK_CODENUM FOREIGN KEY (CODENUM) REFERENCES ACODE(CODENUM)
);

COMMIT;

 

---

 

Java Project / Package / Class 구성

 

 

---

 

BankDTO package

 

Account

package BankDTO;

public class Account {
    // 1. 필드
    private String cAccount;    // primary
    private String cId;         // foreign key
    private int codeNum;        // foreign key
    private int balance;

    // 2. 생성자 : default

    // 3. 메소드
    public String getcAccount() {
        return cAccount;
    }

    public void setcAccount(String cAccount) {
        this.cAccount = cAccount;
    }

    public String getcId() {
        return cId;
    }

    public void setcId(String cId) {
        this.cId = cId;
    }

    public int getcodeNum() {
        return codeNum;
    }

    public void setcodeNum(int codeNum) {
        this.codeNum = codeNum;
    }

    public int getBalance() {
        return balance;
    }

    public void setBalance(int balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "cAccount='" + cAccount + '\'' +
                ", cId='" + cId + '\'' +
                ", codeNum=" + codeNum +
                ", balance=" + balance +
                '}';
    }
}

 

 

Acode

package BankDTO;

public class Acode {

    // 1. 필드
    private int codeNum;        // primary
    private String codeName;

    // 2. 생성자 : default

    // 3. 메소드
    public int getCodeNum() {
        return codeNum;
    }

    public void setCodeNum(int codeNum) {
        this.codeNum = codeNum;
    }

    public String getCodeName() {
        return codeName;
    }

    public void setCodeName(String codeName) {
        this.codeName = codeName;
    }

    @Override
    public String toString() {
        return "Acode{" +
                "codeNum=" + codeNum +
                ", codeName='" + codeName + '\'' +
                '}';
    }
}

 

 

Client

package BankDTO;

public class Client {

    // 1. 필드
    private int cNum;       // unique
    private String cId;     // primary
    private String cPw;
    private String cName;
    private String cPhone;
    private String cAddr;

    // 2. 생성자 : default

    // 3. 메소드
    public int getcNum() {
        return cNum;
    }

    public void setcNum(int cNum) {
        this.cNum = cNum;
    }

    public String getcId() {
        return cId;
    }

    public void setcId(String cId) {
        this.cId = cId;
    }

    public String getcPw() {
        return cPw;
    }

    public void setcPw(String cPw) {
        this.cPw = cPw;
    }

    public String getcName() {
        return cName;
    }

    public void setcName(String cName) {
        this.cName = cName;
    }

    public String getcPhone() {
        return cPhone;
    }

    public void setcPhone(String cPhone) {
        this.cPhone = cPhone;
    }

    public String getcAddr() {
        return cAddr;
    }

    public void setcAddr(String cAddr) {
        this.cAddr = cAddr;
    }

    @Override
    public String toString() {
        return "Client{" +
                "cNum=" + cNum +
                ", cId='" + cId + '\'' +
                ", cPw='" + cPw + '\'' +
                ", cName='" + cName + '\'' +
                ", cPhone='" + cPhone + '\'' +
                ", cAddr='" + cAddr + '\'' +
                '}';
    }
}

 

---

 

BankDAO package

 

DBConnection

package BankDAO;

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

public class DBConnection {
    public static Connection DBConnect(){
        Connection con = null;

        String user = "ICIA";
        String password = "1111";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(url, user, password);
            System.out.println("DB 접속 성공!");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("DB 접속 실패...");
            throw new RuntimeException(e);
        }
        return con;
    }
}

 

 

BankSQL

package BankDAO;

import BankDTO.Account;
import BankDTO.Client;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BankSQL {
    Connection con;
    PreparedStatement pstmt;
    ResultSet rs;

    // 1. DB 접속 메소드
    public void connect() {
        con = DBConnection.DBConnect();
    }

    // 2. DB 해제 메소드
    public void conClose() {
        try {
            con.close();
            System.out.println("DB 접속 해제!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 3-1. 회원번호 생성
    public int clientNum() {
        int cNum = 0;
        String sql = "SELECT MAX(CNUM) FROM CLIENT";
        try {
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                cNum += rs.getInt(1) + 1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return cNum;
    }

    // 3-2. 회원가입 메소드
    public void joinClient(Client client) {
        String sql = "INSERT INTO CLIENT VALUES(?,?,?,?,?,?)";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, client.getcNum());
            pstmt.setString(2, client.getcId());
            pstmt.setString(3, client.getcPw());
            pstmt.setString(4, client.getcName());
            pstmt.setString(5, client.getcPhone());
            pstmt.setString(6, client.getcAddr());
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.printf("%s 님, 회원가입을 축하합니다!", client.getcName());
            } else {
                System.out.println("회원가입 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 4. 로그인 메소드
    public boolean login(String inputId, String inputPw) {
        boolean loginResult = false;
        boolean IdResult = false;
        boolean PwResult = false;
        String sql = "SELECT CID FROM CLIENT WHERE CPW = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, inputPw);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                IdResult = true;
            }
            sql = "SELECT CPW FROM CLIENT WHERE CID = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, inputId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PwResult = true;
            }
            if (IdResult && PwResult) {
                sql = "SELECT CNAME FROM CLIENT WHERE CID = ? AND CPW = ?";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, inputId);
                pstmt.setString(2, inputPw);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    System.out.println("로그인 성공!");
                    System.out.println(rs.getString(1) + " 님, 어서오세요!");
                    loginResult = true;
                }
            } else {
                System.out.println("로그인 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return loginResult;
    }

    // 5-1. 계좌번호 생성 메소드
    public String addAccount() {
        String newAccount = "110-";
        for (int i = 0; i < 3; i++) {
            int account1 = (int) ((Math.random() * 9) + 1);
            newAccount += account1;
        }
        newAccount += "-";

        for (int i = 0; i < 6; i++) {
            int account2 = (int) ((Math.random() * 9) + 1);
            newAccount += account2;
        }
        return newAccount;
    }

    // 5-2. 계좌생성 메소드
    public void createAccount(Account acc) {
        String sql = "INSERT INTO ACCOUNT VALUES(?,?,?,?)";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, acc.getcAccount());
            pstmt.setString(2, acc.getcId());
            pstmt.setInt(3, acc.getcodeNum());
            pstmt.setInt(4, acc.getBalance());
            int result = pstmt.executeUpdate();

            if (result < 0) {
                System.out.println("계좌 생성 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    // 5-3. 계좌정보 출력 메소드
    public void accountInfo(String newAccount) {
        String sql = "SELECT C.CODENAME, A.BALANCE FROM ACCOUNT A, ACODE C" +
                " WHERE A.CODENUM = C.CODENUM AND A.CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, newAccount);
            rs = pstmt.executeQuery();
                while (rs.next()) {
                    System.out.printf("[%s]계좌 생성 완료!\n초기잔액 : %,d원\n", rs.getString(1), rs.getInt(2));
                }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    // 5-4. 계좌 개수 카운트 메소드
    public int countAccount(String loginId) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM ACCOUNT WHERE CID = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, loginId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }

    // 6. 계좌 목록 조회 메소드
    public void selectClient(String loginId) {
        String sql = "SELECT C.CODENAME, CACCOUNT, BALANCE FROM ACCOUNT A, ACODE C" +
                " WHERE A.CODENUM = C.CODENUM AND A.CID = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, loginId);
            rs = pstmt.executeQuery();
            System.out.println("계좌종류\t\t계좌번호\t\t\t\t잔액");
            while (rs.next()) {
                System.out.printf("[%s]\t\t%s\t\t%,d원\n", rs.getString(1), rs.getString(2), rs.getInt(3));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 7-1. 계좌 조회 메소드
    public boolean checkAccount(String cAccount) {
        boolean check = false;
        String sql = "SELECT * FROM ACCOUNT WHERE CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cAccount);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                check = true;
            } else {
                System.out.println("조회할 수 없는 계좌입니다...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return check;
    }
    // 7-2. 입금 메소드
    public void deposit(String cAccount, int money) {
        String sql = "UPDATE ACCOUNT SET BALANCE = BALANCE + ? WHERE CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, money);
            pstmt.setString(2, cAccount);
            int result = pstmt.executeUpdate();

            if (result > 0) {
                System.out.println("입금 성공!");
            } else {
                System.out.println("입금 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 8-1. 잔액조회 메소드
    public int getBalance(String cAccount) {
        int balance = 0;
        String sql = "SELECT BALANCE FROM ACCOUNT WHERE CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cAccount);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                balance = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return balance;
    }
    // 8-2. 출금 메소드
    public void withdraw(String cAccount, int money) {
        String sql = "UPDATE ACCOUNT SET BALANCE = BALANCE - ? WHERE CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, money);
            pstmt.setString(2, cAccount);
            int result = pstmt.executeUpdate();

            if (result > 0) {
                System.out.println("출금 성공!");
            } else {
                System.out.println("출금 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 9. 송금 메소드
    public void transferMoney(String sendAccount, String receiveAccount, int money) {
        String sql1 = "UPDATE ACCOUNT SET BALANCE = BALANCE - ? WHERE CACCOUNT = ?";
        String sql2 = "UPDATE ACCOUNT SET BALANCE = BALANCE + ? WHERE CACCOUNT = ?";
        try {
            pstmt = con.prepareStatement(sql1);
            pstmt.setInt(1, money);
            pstmt.setString(2, sendAccount);
            pstmt.executeUpdate();
            pstmt = con.prepareStatement(sql2);
            pstmt.setInt(1, money);
            pstmt.setString(2, receiveAccount);
            int result = pstmt.executeUpdate();

            if (result > 0) {
                System.out.println("송금 성공!");
            } else {
                System.out.println("송금 실패...");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

 

---

 

BankMain package

 

BankMain

package BankMain;

import BankDAO.BankSQL;
import BankDTO.Account;
import BankDTO.Client;

import java.util.Scanner;

public class BankMain {
    public static void main(String[] args) {
        BankSQL sql = new BankSQL();
        Client client = new Client();
        Account acc = new Account();
        Scanner sc = new Scanner(System.in);
        boolean run1 = true;
        sql.connect();

        while (run1) {
       	// before login
            String loginId = null;
            boolean run2 = false;
            boolean check = false;
            System.out.println("============라즈베리증권============");
            System.out.println("[1]회원가입    [2]로그인    [3]종료");
            System.out.println("===================================");
            System.out.print("메뉴선택 > ");
            String menu = sc.next();

            switch (menu) {
                case"1":
                    client.setcNum(sql.clientNum());
                    System.out.print("아이디 > ");
                    client.setcId(sc.next());
                    System.out.print("비밀번호 > ");
                    client.setcPw(sc.next());
                    System.out.print("이름 > ");
                    client.setcName(sc.next());
                    System.out.print("연락처 > ");
                    client.setcPhone(sc.next());
                    System.out.print("주소 > ");
                    client.setcAddr(sc.next());
                    sql.joinClient(client);
                    break;
                case"2":
                    System.out.print("아이디 > ");
                    String inputId = sc.next();
                    System.out.print("비밀번호 > ");
                    String inputPw = sc.next();
                    run2 = sql.login(inputId, inputPw);
                    if (run2) {
                        loginId = inputId;
                    }
                    break;
                case"3":
                    System.out.println("프로그램을 종료합니다...");
                    run1 = false;
                    break;
                default:
                    System.out.println("1~3중에 입력하세요...");
                    break;
            }
            System.out.println();
            
            while (run2) {
            // after login
                System.out.printf("======================라즈베리증권[%s 님]======================\n",loginId);
                System.out.println("[1]계좌생성    [2]입금    [3]출금    [4]조회    [5]송금    [6]로그아웃");
                System.out.println("==================================================================");
                System.out.print("메뉴선택 > ");
                menu = sc.next();

                switch (menu) {
                    case"1":
                        if (sql.countAccount(loginId) == 3) {
                            System.out.println("이미 3개의 계좌가 존재합니다!\n한 사람당 최대 3개의 계좌만 생성 가능합니다...");
                        } else {
                            String newAccount = sql.addAccount();
                            acc.setcAccount(newAccount);
                            acc.setcId(loginId);
                            System.out.println("================계좌 종류================");
                            System.out.println("[1]일반    [2]청약    [3]주식    [4]적금");
                            System.out.println("========================================");
                            System.out.print("종류선택 > ");
                            int accountType = sc.nextInt();
                            if (accountType > 4) {
                                System.out.println("1~4중에 선택하세요...");
                                break;
                            } else {
                                acc.setcodeNum(accountType);
                                System.out.print("초기잔액 > ");
                                acc.setBalance(sc.nextInt());
                                sql.createAccount(acc);
                                sql.accountInfo(newAccount);
                            }
                        }
                        break;
                    case"2":
                        System.out.print("입금할 계좌 > ");
                        String depositAccount = sc.next();
                        boolean check1 = sql.checkAccount(depositAccount);
                        if (check1) {
                            System.out.print("입금 금액 > ");
                            int money = sc.nextInt();
                            sql.deposit(depositAccount, money);
                        }
                        break;
                    case"3":
                        System.out.print("출금할 계좌 > ");
                        String withdrawAccount = sc.next();
                        check = sql.checkAccount(withdrawAccount);
                        if (check) {
                            System.out.print("출금 금액 > ");
                            int money = sc.nextInt();
                            if (sql.getBalance(withdrawAccount) < money) {
                                System.out.println("잔액이 부족합니다...");
                            } else {
                                sql.withdraw(withdrawAccount, money);
                            }
                        }
                        break;
                    case"4":
                        sql.selectClient(loginId);
                        break;
                    case"5":
                        System.out.print("출금 계좌 > ");
                        String sendAccount = sc.next();
                        check = sql.checkAccount(sendAccount);
                        if (check) {
                            System.out.print("입금 계좌 > ");
                            String receiveAccount = sc.next();
                            if (sendAccount.equals(receiveAccount)) {
                                System.out.println("동일한 계좌로의 송금은 진행할 수 없습니다...");
                            } else {
                                boolean checkSA = sql.checkAccount(receiveAccount);
                                if (checkSA) {
                                    System.out.print("보내실 금액 > ");
                                    int money = sc.nextInt();
                                    if (sql.getBalance(sendAccount) < money) {
                                        System.out.println("잔액이 부족합니다...");
                                    } else {
                                        sql.transferMoney(sendAccount, receiveAccount, money);
                                    }
                                }
                            }
                        }
                        break;
                    case"6":
                        System.out.println("로그아웃! 초기화면으로 돌아갑니다...");
                        run2 = false;
                        break;
                    default:
                        System.out.println("1~6중에 입력하세요...");
                        break;
                }
                System.out.println();
            }
        }
    }
}