DTO, DAO를 이용한 sql CRUD 예제 2 (학생 관리 프로그램)

tb_student 테이블 생성
학번, 이름, 연락처, 성별, 등록날짜

studentDTO 클래스와 studentDAO 클래스를 사용하여 아래 기능을 구현하는 프로그램을 만들어보자.

********** 메뉴 **********
1. 학생 등록 2. 학생 리스트 3. 학생 검색 4. 학생 수정 5. 학생 삭제 6. 프로그램 종료
메뉴를 입력하세요 > 1

✅ 키(key)는 학번으로 사용

---

 

테이블 생성 쿼리

create table tb_student(
stu_idx bigint auto_increment primary key,
    stu_num varchar(20) not null,
    stu_name varchar(20) not null,
    stu_hp varchar(20) not null,
    stu_gender enum('남자', '여자') not null,
    stu_rdate datetime default now()
);

 

 

DB연결

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

public class ConnDb {
    private static Connection conn;

    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://127.0.0.1/aiclass?useSSL=false";
        String userid = "root";
        String userpw = "1234";

        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, userid, userpw);

        return conn;
    }

    public static void close() {
        try {
            conn.close();
            conn = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

학생 DTO

public class stuDTO {
    private String stuNum;
    private String name;
    private String hp;
    private String gender;
    private String rdate;

    public stuDTO(String stuNum, String name, String hp, String gender, String rdate) {
        this.stuNum = stuNum;
        this.name = name;
        this.hp = hp;
        this.gender = gender;
        this.rdate = rdate;
    }

    public String getStuNum() {
        return stuNum;
    }

    public void setStuNum(String stuNum) {
        this.stuNum = stuNum;
    }

    public String getName() {
        return name;
    }

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

    public String getHp() {
        return hp;
    }

    public void setHp(String hp) {
        this.hp = hp;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getRdate() {
        return rdate;
    }

    public void setRdate(String rdate) {
        this.rdate = rdate;
    }

    @Override
    public String toString() {
        return "stuDTO{" +
                "stuNum=" + stuNum +
                ", name='" + name + '\'' +
                ", hp='" + hp + '\'' +
                ", gender='" + gender + '\'' +
                ", rdate='" + rdate + '\'' +
                '}';
    }
}

 

학생 DAO

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

public class stuDAO {
    Connection conn;
    StringBuilder sql;
    Scanner sc;
    PreparedStatement pstmt;
    ResultSet rs;

    public void insert() throws SQLException {
        sc = new Scanner(System.in);
        System.out.print("학번을 입력하세요 > ");
        String stuNum = sc.next();
        System.out.print("이름을 입력하세요 > ");
        String name = sc.next();
        System.out.print("연락처를 입력하세요 > ");
        String hp = sc.next();
        System.out.print("성별을 입력하세요 > ");
        String gender = sc.next();

        try {
            conn = ConnDb.getConnection();
            sql = new StringBuilder();
            sql.append("insert into tb_student (stu_num, stu_name, stu_hp, stu_gender)");
            sql.append("values (?, ?, ?, ?)");
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, stuNum);
            pstmt.setString(2, name);
            pstmt.setString(3, hp);
            pstmt.setString(4, gender);
            int result = pstmt.executeUpdate();
            if (result >= 1) System.out.println("학생등록 성공");
            else System.out.println("학생등록 실패");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnDb.close();
            pstmt.close();
            pstmt = null;
        }

    }

    public void list() throws SQLException {
        try {
            conn = ConnDb.getConnection();
            sql = new StringBuilder();
            sql.append("select stu_num, stu_name, stu_hp, stu_gender from tb_student");
            pstmt = conn.prepareStatement(sql.toString());
            rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println("학번 : " + rs.getString("stu_num"));
                System.out.println("이름 : " + rs.getString("stu_name"));
                System.out.println("연락처 : " + rs.getString("stu_hp"));
                System.out.println("성별 : " + rs.getString("stu_gender"));
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnDb.close();
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
    }

    public void search() throws SQLException {
        sc = new Scanner(System.in);
        System.out.print("검색할 학생의 학번을 입력하세요 > ");
        String stuNum = sc.next();

        try {
            conn = ConnDb.getConnection();
            sql = new StringBuilder();
            sql.append("select stu_num, stu_name, stu_hp, stu_gender, stu_rdate from tb_student where stu_num = ?");
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, stuNum);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.println("학번 : " + rs.getString("stu_num"));
                System.out.println("이름 : " + rs.getString("stu_name"));
                System.out.println("연락처 : " + rs.getString("stu_hp"));
                System.out.println("성별 : " + rs.getString("stu_gender"));
                System.out.println("등록일 : " + rs.getString("stu_rdate"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void update() throws SQLException {
        sc = new Scanner(System.in);
        System.out.print("수정할 학생의 학번을 입력하세요 > ");
        String stuNum = sc.next();
        System.out.print("이름을 입력하세요 > ");
        String name = sc.next();
        System.out.print("연락처를 입력하세요 > ");
        String hp = sc.next();
        System.out.print("성별을 입력하세요 > ");
        String gender = sc.next();

        try {
            conn = ConnDb.getConnection();
            sql = new StringBuilder();
            sql.append("update tb_student set stu_name = ?, stu_hp = ?, stu_gender = ? where stu_num=?");
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, name);
            pstmt.setString(2, hp);
            pstmt.setString(3, gender);
            pstmt.setString(4, stuNum);
            int result = pstmt.executeUpdate();
            if (result >= 1) System.out.println("학생정보 수정 성공");
            else System.out.println("학생정보 수정 실패");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnDb.close();
            pstmt.close();
            pstmt = null;
        }
    }

    public void delete() throws SQLException {
        sc = new Scanner(System.in);
        System.out.print("삭제할 학생의 학번을 입력하세요 > ");
        String stuNum = sc.next();

        try {
            conn = ConnDb.getConnection();
            sql = new StringBuilder();
            sql.append("delete from tb_student where stu_num = ?");
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, stuNum);
            int result = pstmt.executeUpdate();
            if (result >= 1) System.out.println("학생 정보 삭제 성공");
            else System.out.println("학생 정보 삭제 실패");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            ConnDb.close();
            pstmt.close();
            pstmt = null;
        }
    }

}

 

실행 클래스

import java.sql.SQLException;
import java.util.Scanner;

public class homework {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        stuDTO stuDTO;
        stuDAO stuDAO = new stuDAO();
        while (true) {
            System.out.println("********** 메뉴 **********");
            System.out.println("1. 학생 등록 2. 학생 리스트 3. 학생 검색 4. 학생 수정 5. 학생 삭제 6. 프로그램 종료");
            System.out.print("메뉴를 선택하세요 : ");
            int input = sc.nextInt();

            if (input == 6) {
                System.out.println("프로그램을 종료합니다.");
                break;
            }
            switch (input) {
                case 1:
                    stuDAO.insert();
                    break;
                case 2:
                    stuDAO.list();
                    break;
                case 3:
                    stuDAO.search();
                    break;
                case 4:
                    stuDAO.update();
                    break;
                case 5:
                    stuDAO.delete();
                    break;
            }
        }
    }
}

 

실행 결과

 

학생 정보 등록

 

학생 정보 검색

 

학생 정보 수정

 

학생 정보 삭제