[JDBC] 학생 관리 프로그램 (feat. PreparedStatemet)

💡 PreparedStatemet 를 사용한 학생 관리 프로그램

✔️ model.vo

package com.kh.jdbc.day03.student.model.vo;

import java.sql.Date;

// 주제 선정 → 시나리오 → 와이어 프레임 → DB설계 → 클래스 설계 → 시퀀스 설계
public class Student {
	// 멤버 변수
	private String studentId;                                                                         
	private String studentPwd;                                                                        
	private String studentName;                                                                       
	private char gender;                                                                              
	private int age;                                                                                  
	private String email;                                                                             
	private String phone;                                                                             
	private String address;                                                                           
	private String hobby;                                                                             
	private Date enrollDate;

	// 기본 생성자
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	// 매개변수 생성자
	public Student(String studentId, String studentPwd, String studentName, char gender, int age, String email,
			String phone, String address, String hobby) {
		super();
		this.studentId = studentId;
		this.studentPwd = studentPwd;
		this.studentName = studentName;
		this.gender = gender;
		this.age = age;
		this.email = email;
		this.phone = phone;
		this.address = address;
		this.hobby = hobby;
	}

	
	public Student(String studentPwd, String email, String phone, String address, String hobby) {
		super();
		this.studentPwd = studentPwd;
		this.email = email;
		this.phone = phone;
		this.address = address;
		this.hobby = hobby;
	}

	public Student(String studentId, String studentPwd) {
		super();
		this.studentId = studentId;
		this.studentPwd = studentPwd;
	}

	// getter, setter 메소드
	public String getStudentId() {
		return studentId;
	}
	public void setStudentId(String studentId) {
		this.studentId = studentId;
	}
	public String getStudentPwd() {
		return studentPwd;
	}
	public void setStudentPwd(String studentPwd) {
		this.studentPwd = studentPwd;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public char getGender() {
		return gender;
	}
	public void setGender(char gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getHobby() {
		return hobby;
	}
	public void setHobby(String hobby) {
		this.hobby = hobby;
	}
	public Date getEnrollDate() {
		return enrollDate;
	}
	public void setEnrollDate(Date enrollDate) {
		this.enrollDate = enrollDate;
	}
	
	// 데이터 확인용
	@Override
	public String toString() {
		return "학생 [아이디 =" + studentId + ", 비밀번호 =" + studentPwd + ", 이름 =" + studentName
				+ ", 성별 =" + gender + ", 나이 =" + age + ", 이메일 =" + email + ", 전화번호 =" + phone + ", 주소 ="
				+ address + ", 취미 =" + hobby + ", 가입일 =" + enrollDate + "]";
	}
}

✔️ run

package com.kh.jdbc.day03.student.run;

import com.kh.jdbc.day03.student.view.StudentView;

public class MainRun {

	public static void main(String [] args) {
		StudentView view = new StudentView();
		view.studentProgram();
	}
}

✔️ view

package com.kh.jdbc.day03.student.view;

import java.util.List;
import java.util.Scanner;

import com.kh.jdbc.day03.student.controller.StudentController;
import com.kh.jdbc.day03.student.model.vo.Student;

public class StudentView {
	// 컨트롤러 연결
	private StudentController controller;
	
	public StudentView() {
		controller = new StudentController();
	}
	
	public void studentProgram() {
		Student student = null;
		List<Student> sList  = null;
		int result = 0;
		theEnd :
		while(true) {
			int input = printMenu();
			switch(input) {
				case 1 : 
					// 전체 정보 조회하기
					sList = controller.selectAllStudent();
					printAllStudent(sList);
					break;
				case 2 : 
					// 아이디로 정보 조회하기
					// SELECT * FROM STDUENT_TBL WHERE STUDENT_ID = 'user11'
					String studentId = inputStdId("검색");
					student = controller.printStudentById(studentId);
					printOneStudent(student);
					break;
				case 3 : 
					// 이름으로 정보 조회하기
					// SELECT * FROM STDUENT_TBL WHERE STUDENT_NAME = '관리자'
					String studentName = inputStdName();
					sList = controller.selectAllByName(studentName);
					printAllStudent(sList);
					break;
				case 4 : 
					// 학생 정보 등록
					// INSERT INTO STUDENT_TBL VALUES(1,2,3,4,5,6,7,8,9,SYSDATE)
					student = inputStudentInfo();
					result = controller.insertStudent(student);
					break;
				case 5 : 
					// 데이터 수정
					// UPDATE SET 
					studentId = inputStdId("수정");
					student = controller.printStudentById(studentId);	// 아이디 중복 여부 확인
					if(student != null) {
						// 있는거
						student = modifyStudent();
						student.setStudentId(studentId);
						result = controller.updateStudent(student);
					} else {
						// 없는거
						displayError("해당 정보가 존재하지 않습니다.");
					}
					break;
				case 6 : 
					// 데이터 삭제
					// DELETE FROM STUDENT_TBL WHERE STUDENT_ID = 'khuser01'
					studentId = inputStdId("삭제");
					result = controller.deleteStudent(studentId);
					if(result > 0) {
						// 성공
						displaySuccess("삭제 성공");
					} else {
						// 실패
						displayError("해당 정보가 존재하지 않습니다.");
					}
					break;
				case 9 :
					// 학생 로그인
					// SELECT * FROM STUDENT_TBL WHERE STUDENT_ID = 'user11' AND STUDENT_PW = '1234'
					student = inputLoginInfo();
					student = controller.studentLogin(student);
					if(student != null) {
						// 로그인 성공
						displaySuccess("로그인 성공");
					} else {
						// 로그인 실패
						displayError("해당 정보가 존재하지 않습니다.");
					}
					break;
				case 0 : 
					break theEnd;
			}
		}
	}

	// 로그인 성공
	private void displaySuccess(String message) {
		System.out.println("[서비스 성공] : "+ message);
		
	}

	// 로그인 실패
	private void displayError(String message) {
		System.out.println("[서비스 실패] : "+ message);
	}
	
	// 수정할 데이터 입력
	private Student modifyStudent() {
		Scanner sc = new Scanner(System.in);
		System.out.println("===== 학생 정보 수정 =====");
		System.out.print("비밀번호 : ");
		String studentPw = sc.next();
		System.out.print("이메일 : ");
		String email = sc.next();
		System.out.print("전화번호 : ");
		String phone = sc.next();
		System.out.print("주소 : ");
		sc.nextLine();	// 공백 제거, 엔터 제거
		String address = sc.nextLine();
		System.out.print("취미(,로 구분) : ");
		String hobby = sc.next();
		Student student = new Student(studentPw, email, phone, address, hobby);
		return student;
	}
	// 학생 정보 등록
	private Student inputStudentInfo() {
		Scanner sc = new Scanner(System.in);
		System.out.print("아이디 : ");
		String studentId = sc.next();
		System.out.print("비밀번호 : ");
		String studentPw = sc.next();
		System.out.print("이름 : ");
		String studentName = sc.next();
		System.out.print("성별 : ");
		char gender = sc.next().charAt(0);
		System.out.print("나이 : ");
		int age = sc.nextInt();
		System.out.print("이메일 : ");
		String email = sc.next();
		System.out.print("전화번호 : ");
		String phone = sc.next();
		System.out.print("주소 : ");
		sc.nextLine();	// 공백 제거, 엔터 제거
		String address = sc.nextLine();
		System.out.print("취미(,로 구분) : ");
		String hobby = sc.nextLine();
		Student student = new Student(studentId, studentPw, studentName, gender, age, email, phone, address, hobby);
		return student;
	}
	
	// 아이디, 비밀번호 입력
	private Student inputLoginInfo() {
		Scanner sc = new Scanner(System.in);
		System.out.println("===== 학생 로그인 =====");
		System.out.print("아이디 : ");
		String studentID = sc.nextLine();
		System.out.print("비밀번호 : ");
		String studentPw = sc.nextLine();
		Student student = new Student(studentID, studentPw);
		System.out.println(student.toString());
		return student;
	}

	// 이름 검색
	private String inputStdName() {
		Scanner sc = new Scanner(System.in);
		System.out.print("검색할 이름 입력 : ");
		String studentName = sc.next();
		return studentName;
	}
	
	// 아이디 검색
	private String inputStdId(String category) {
		Scanner sc = new Scanner(System.in);
		System.out.print(category + "할 아이디 입력 : ");
		String studentId = sc.next();
		return studentId;
	}

	// 아이디로 고객 정보 조회
	private void printOneStudent(Student student) {
		System.out.println("====== 학생 정보 조회(아이디) ======");
		System.out.printf("이름 : %s, 나이 : %d, 아이디: %s, 성별  %s, 이메일 : %s, 전화번호 : %s, 주소 : %s, 취미 : %s, 가입날짜 : %s\n"
				, student.getStudentName()
				, student.getAge()
				, student.getStudentId()
				, student.getGender()
				, student.getEmail()
				, student.getPhone()
				, student.getAddress()
				, student.getHobby()
				, student.getEnrollDate());
	}


	// 학생 전체 정보 출력
	private void printAllStudent(List<Student> sList) {
		System.out.println("====== 학생 전체 조회 ======");
		for(Student student : sList) {
			System.out.printf("이름 : %s, 나이 : %d, 아이디: %s, 성별  %s, 이메일 : %s, 전화번호 : %s, 주소 : %s, 취미 : %s, 가입날짜 : %s\n"
					, student.getStudentName()
					, student.getAge()
					, student.getStudentId()
					, student.getGender()
					, student.getEmail()
					, student.getPhone()
					, student.getAddress()
					, student.getHobby()
					, student.getEnrollDate());
		}
	}
	// 메인 메뉴
	private int printMenu() {
		Scanner sc = new Scanner(System.in);
		System.out.println("===== 학생관리 프로그램 =====");
		System.out.println("9. 학생 로그인");
		System.out.println("1. 학생 전체 조회");
		System.out.println("2. 학생 아이디로 조회");
		System.out.println("3. 학생 이름으로 조회");
		System.out.println("4. 학생 정보 등록");
		System.out.println("5. 학생 정보 수정");
		System.out.println("6. 학생 정보 삭제");
		System.out.println("0. 프로그램 종료");
		System.out.print("메뉴 선택 : ");
		int input = sc.nextInt();
		return input;
	}
}

✔️ controller

package com.kh.jdbc.day03.student.controller;

import java.util.List;

import com.kh.jdbc.day03.student.model.dao.StudentDAO;
import com.kh.jdbc.day03.student.model.vo.Student;

// 역할 -> 보통 반환값 처리
public class StudentController {
	private StudentDAO sDao;
	// 생성자
	public StudentController() {
		sDao = new StudentDAO();
	}

	// 아이디로 고객정보 조회
	public Student printStudentById(String studentId) {
		Student student = sDao.selectOneById(studentId);
		return student;
	}

	// 매개변수/리턴값이 있는지 여부 -> 쿼리문을 생객해보자!
	// SELECT * FROM STUDENT_TBL
	public List<Student> selectAllStudent() {
		List<Student> sList = sDao.selectAll();
		return sList;
	}
	
	// 이름으로 고객정보 조회
	public List<Student> selectAllByName(String studentName) {
		List<Student> sList = sDao.selectOneByName(studentName);
		return sList;
	}
	
	// 고객 정보 등록
	public int insertStudent(Student student) {
		int result = sDao.insertStudent(student);
		return result;
	}
	// 고객 정보 삭제
	public int deleteStudent(String studentId) {
		int result = sDao.deleteStudent(studentId);
		return result;
	}
	// 고객 정보 수정
	public int updateStudent(Student student) {
		int result = sDao.updateStudent(student);
		return 0;
	}
	// 로그인
	public Student studentLogin(Student student) {
		Student result = sDao.selectLoginInfo(student);	// student 변수명 겹쳐서 변경
		return result;
	}

}

✔️ model.dao

package com.kh.jdbc.day03.student.model.dao;

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

import com.kh.jdbc.day03.student.model.vo.Student;

// 역할 -> 데이터 접근 계층
public class StudentDAO {
	private final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
	// localhost = 127.0.0.1 -> 컴퓨터의 DB 주소
	private final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	private final String USER = "STUDENT";
	private final String PASSWORD = "STUDENT";

	public List<Student> selectAll() {
		String query = "SELECT * FROM STUDENT_TBL";
		List<Student> sList = new ArrayList<Student>(); // DB 반환값들을 저장하기 위해 선언
		Connection conn = null;	// finally 로 자원 종료하기 위해서 전역변수로 선언
		Statement stmt = null;
		ResultSet rset = null;

		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			// 후처리
			while (rset.next()) { // 반환 값이 있을 때 필수!, 반환 값 1개 일 경우 if문 사용
				Student student = rsetToStudent(rset);
				sList.add(student);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sList;
	}

	
	// 이름으로 고객 정보 조회
	public List<Student> selectOneByName(String studentName) {
		List<Student> sList = new ArrayList<Student>();
		String query = "SELECT * FROM STUDENT_TBL WHERE STUDENT_NAME = ?";
		Student student = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);	// 쿼리문 준비(쿼리문 넣기)
			pstmt.setString(1,  studentName);						// setString()으로 확인
			rset = pstmt.executeQuery();					// 쿼리문 실행
//			Statement stmt = conn.createStatement();
			// SELECT * FROM STUDENT_TBL WHERE STDUENT_NAME
//			ResultSet rset = stmt.executeQuery(query);
			while(rset.next()) {
				student = rsetToStudent(rset);
				sList.add(student);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sList;
	}
	
	// 아이디로 고객 정보 조회
	public Student selectOneById(String studentId) {
		// PreparedStatement 사용
		// 1. 위치홀더 세팅
		// 2. PreparedStatement 객체 생성 with query
		// 3. 입력값 세팅
		// 4. 쿼리문 실행 및 결과 받기(feat. method())
		String query = "SELECT * FROM STUDENT_TBL WHERE STUDENT_ID = ?";
		Student student = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, studentId);
			rset = pstmt.executeQuery();
//			Statement stmt = conn.createStatement();
			// SELECT * FROM STUDENT_TBL WHERE STUDENT_ID
//			ResultSet rset = stmt.executeQuery(query);
			// 후처리
			if(rset.next()) {
				student = rsetToStudent(rset);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return student;
	}
	
	// 로그인
	public Student selectLoginInfo(Student student) {
		// PreparedStatement -> SQL Injection 공격을 막기 위해서 사용함!
		// PreparedStatement 를 사용할 때는 쿼리문 찢을 필요 없이 ? 만 넣어주면 됨.
		// ? 의 명칭 -> 위치홀더
		String query = "SELECT * FROM STUDENT_TBL WHERE STUDENT_ID = ? AND STUDENT_PWD = ?";
		Student result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);	// 쿼리문을 미리 넣어놓기
			// 시작은 1로 하고 마지막 수는 물음표의 갯수와 같아.(물음표 = 위치홀더)
			pstmt.setString(1, student.getStudentId());
			pstmt.setString(2, student.getStudentPwd());
			rset = pstmt.executeQuery();					// 쿼리문 실행만 시킴
//			Statement stmt = conn.createStatement();
//			ResultSet rset = stmt.executeQuery(query);
			// 후처리
			if(rset.next()) {
				result = rsetToStudent(rset);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	// 고객 정보 등록
	public int insertStudent(Student student) {
		// INSERT INTO STUDENT_TBL VALUES(1,2,3,4,5,6,7,8,9,SYSDATE)
		String query = "INSERT INTO STUDENT_TBL VALUES(?,?,?,?,?,?,?,?,?,SYSDATE)";
		int result = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, student.getStudentId());
			pstmt.setString(2, student.getStudentPwd());
			pstmt.setString(3, student.getStudentName());
			pstmt.setString(4, String.valueOf(student.getGender()));
//			pstmt.setString(4, student.getGender()+"");
			pstmt.setInt(5, student.getAge());
			pstmt.setString(6, student.getEmail());
			pstmt.setString(7, student.getPhone());
			pstmt.setString(8, student.getAddress());
			pstmt.setString(9, student.getHobby());
			result = pstmt.executeUpdate();	// *********** 쿼리문 실행 빼먹지 않기 ***********
//			Statement stmt = conn.createStatement();
//			result = stmt.executeUpdate(query);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	// 데이터 삭제
	public int deleteStudent(String studentId) {
		String query = "DELETE FROM STUDENT_TBL WHERE STUDENT_ID = ?";
		int result = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, studentId);
			result = pstmt.executeUpdate();
//			Statement stmt = conn.createStatement();
			// SELECT * FROM STUDENT_TBL WHERE STUDENT_ID
//			result = stmt.executeUpdate(query);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	// 데이터 수정
	public int updateStudent(Student student) {
		String query = "UPDATE STUDENT_TBL SET STUDENT_PWD = ?, EMAIL = ?, PHONE = ?, ADDRESS = ?, HOBBY = ? WHERE STUDENT_ID = ?";
		int result = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, student.getStudentPwd());
			pstmt.setString(2, student.getEmail());
			pstmt.setString(3, student.getPhone());
			pstmt.setString(4, student.getAddress());
			pstmt.setString(5, student.getHobby());
			pstmt.setString(6, student.getStudentId());
			result = pstmt.executeUpdate();
//			Statement stmt = conn.createStatement();
			// SELECT * FROM STUDENT_TBL WHERE STUDENT_ID
//			result = stmt.executeUpdate(query);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	// 후처리 메소드
	private Student rsetToStudent(ResultSet rset) throws SQLException {
		Student student = new Student();
		student.setStudentId(rset.getString(1)); // 컬럼명 대신 컬럼의 순번으로 대체할 수 있음!
		student.setStudentPwd(rset.getString("STUDENT_PWD"));
		student.setStudentName(rset.getString("STUDENT_NAME"));
		// 문자는 문자열에서 문자로 잘라서 사용, chatAt() 메소드 사용
		student.setGender(rset.getString("GENDER").charAt(0));
		student.setAge(rset.getInt("AGE"));
		student.setEmail(rset.getString("EMAIL"));
		student.setPhone(rset.getString("PHONE"));
		student.setAddress(rset.getString("ADDRESS"));
		student.setHobby(rset.getString("HOBBY"));
		student.setEnrollDate(rset.getDate("ENROLL_DATE"));
		return student;
	}
}