Day12-1 학생 관리 시스템 MVC 패턴으로 MySQL 사용

model

StudentDTO

package model;

public class StudentDTO {
    private int id;
    private String name;
    private int korean;
    private int english;
    private int math;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getKorean() {
        return korean;
    }

    public void setKorean(int korean) {
        this.korean = korean;
    }

    public int getEnglish() {
        return english;
    }

    public void setEnglish(int english) {
        this.english = english;
    }

    public int getMath() {
        return math;
    }

    public void setMath(int math) {
        this.math = math;
    }


}

 

Controller

StudentController

package controller;

import model.StudentDTO;

import java.sql.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class StudentController {
    private Connection connection;

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


    public void insert(StudentDTO studentDTO){
        String query = "INSERT INTO `student`(`name`, `korean`, `english`, `math`) VALUES(?, ?, ?, ?)";
        try {
            PreparedStatement pstmt = connection.prepareStatement(query);
            pstmt.setString(1, studentDTO.getName());
            pstmt.setInt(2, studentDTO.getKorean());
            pstmt.setInt(3, studentDTO.getEnglish());
            pstmt.setInt(4, studentDTO.getMath());

            pstmt.executeUpdate();

            pstmt.close();

        } catch (SQLException e) {
            System.out.println("INSERT ERROR!!!");
            e.printStackTrace();
        }
    }

    public  ArrayList<StudentDTO> selectAll() {
        ArrayList<StudentDTO> list = new ArrayList<>();
        String query = "SELECT * FROM `student`";

        try {
            PreparedStatement pstmt = connection.prepareStatement(query);
            ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                StudentDTO s = new StudentDTO();
                s.setId(resultSet.getInt("id"));
                s.setName(resultSet.getString("name"));
                s.setKorean(resultSet.getInt("korean"));
                s.setEnglish(resultSet.getInt("english"));
                s.setMath(resultSet.getInt("math"));

                list.add(s);
            }

            resultSet.close();
            pstmt.close();

        } catch (SQLException e) {
            System.out.println("SELECTALL ERROR!!!");
            e.printStackTrace();
        }

        return list;
    }

    public StudentDTO selectOne(int id) {
        String query = "SELECT * FROM `student` WHERE `id` = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(query);
            pstmt.setInt(1, id);

            ResultSet resultSet = pstmt.executeQuery();

            if (resultSet.next()) {
                StudentDTO s = new StudentDTO();
                s.setId(resultSet.getInt("id"));
                s.setName(resultSet.getString("name"));
                s.setKorean(resultSet.getInt("korean"));
                s.setEnglish(resultSet.getInt("english"));
                s.setMath(resultSet.getInt("math"));

                return s;
            }

            resultSet.close();
            pstmt.close();

        } catch (SQLException e) {
            System.out.println("SELECTONE ERROR!!!");
            e.printStackTrace();
        }

        return null;
    }

    public void update(StudentDTO s) {
        String query = "UPDATE `student` SET `korean` = ?, `english` = ?, `math` = ? WHERE `id` = ?";

        try {
            PreparedStatement pstmt = connection.prepareStatement(query);
            pstmt.setInt(1, s.getKorean());
            pstmt.setInt(2, s.getEnglish());
            pstmt.setInt(3, s.getMath());
            pstmt.setInt(4, s.getId());

            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            System.out.println("UPDATE ERROR!!!");
            e.printStackTrace();
        }
    }

    public void delete(int id) {
        String query = "DELETE FROM `student` WHERE `id` = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(query);
            pstmt.setInt(1, id);

            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            System.out.println("DELETE ERROR!!!");
            e.printStackTrace();
        }

    }

}

Viewer

StudentViewer

package viewer;

import controller.StudentController;
import dbConn.ConnectionMaker;
import model.StudentDTO;
import util.ScannerUtil;

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


public class StudentViewer {
    private Connection connection;
    private Scanner scanner;

    public StudentViewer(ConnectionMaker connectionMaker){
        scanner = new Scanner(System.in);
        connection = connectionMaker.makeConnection();
    }


    public void showMenu() {
        String message = "1. 입력 2. 목록 보기 3. 종료";
        while (true) {
            int userChoice = ScannerUtil.nextInt(scanner, message);
            if (userChoice == 1) {
                add();
            } else if (userChoice == 2) {
                printList();
            } else if (userChoice == 3) {
                System.out.println("사용해주셔서 감사합니다.");
                break;
            }

            try {
                connection.close();
            } catch (SQLException e){
                System.out.println("VIEWER CONNECTION ERROR");
                e.printStackTrace();
            }
        }
    }


    public void add() {
        StudentDTO s = new StudentDTO();

        String message = "학생의 이름을 입력해주세요.";
        s.setName(ScannerUtil.nextLine(scanner, message));

        message = "학생의 국어 점수를 입력해주세요.";
        s.setKorean(ScannerUtil.nextInt(scanner, message, 0, 100));

        message = "학생의 영어 점수를 입력해주세요.";
        s.setEnglish(ScannerUtil.nextInt(scanner, message, 0, 100));

        message = "학생의 수학 점수를 입력해주세요.";
        s.setMath(ScannerUtil.nextInt(scanner, message, 0, 100));

        StudentController controller = new StudentController(connection);
        controller.insert(s);

    }

    public void printList() {
        StudentController controller = new StudentController(connection);
        ArrayList<StudentDTO> list = controller.selectAll();

        if (list.isEmpty()) {
            System.out.println("아직 등록된 학생이 존재하지 않습니다.");
        } else {
            for (StudentDTO s : list) {
                System.out.printf("%d. %s\n", s.getId(), s.getName());
            }

            String message = "상세보기할 학생의 번호나 뒤로 가실려면 0을 입력해주세요.";
            int userChoice = ScannerUtil.nextInt(scanner, message);

            while (userChoice != 0 && controller.selectOne(userChoice) == null) {
                System.out.println("잘못 입력하셨습니다.");
                userChoice = ScannerUtil.nextInt(scanner, message);
            }

            if (userChoice != 0) {
                printOne(userChoice);
            }
        }

    }


    public void printOne(int id) {
        StudentController controller = new StudentController(connection);
        StudentDTO s = controller.selectOne(id);

        if (s == null){
            System.out.println("해당 번호는 유효하지 않습니다.");
            printList();
        } else {
            System.out.printf("번호: %d번 이름: %s 국어: %d점 영어: %d점 수학: %d점\n",
                    s.getId(), s.getName(), s.getKorean(), s.getEnglish(), s.getMath());
            String message = "1. 수정 2. 삭제 3. 목록으로";
            int userChoice = ScannerUtil.nextInt(scanner, message, 1, 3);
            if (userChoice == 1) {
                update(id);
            } else if (userChoice == 2) {
                delete(id);
            } else if (userChoice == 3) {
                printList();
            }
        }
    }


    public void update(int id) {
        StudentController controller = new StudentController(connection);
        StudentDTO s = new StudentDTO();

        String message = "새로운 국어 점수를 입력해주세요.";
        s.setEnglish(ScannerUtil.nextInt(scanner, message, 0, 100));

        message = "새로운 영어 점수를 입력해주세요.";
        s.setEnglish(ScannerUtil.nextInt(scanner, message, 0, 100));

        message = "새로운 수학 점수를 입력해주세요.";
        s.setMath(ScannerUtil.nextInt(scanner, message, 0, 100));

        controller.update(s);
        printOne(id);
    }


    public void delete(int id) {
        StudentController controller = new StudentController(connection);
        String message = "정말로 삭제하시겠습니까? Y/N";
        String yesNo = ScannerUtil.nextLine(scanner, message);

        if (yesNo.equalsIgnoreCase("Y")) {
            controller.delete(id);
            printList();
        } else {
            printOne(id);
        }
    }

}