[JDBC]01-04.PreparedStatement 사용법

저번에 사용하였던 Statement 구문은 보안에 취약한 방법이다.

SQL Injection이라는 취약점을 가지고 있어 사용하길 권장하지 않는다.

따라서 이 대안으로 PreparedStatement을 사용하면 된다.

PreparedStatement pstmt = null 과 같이 선언하여 사용하고

기존과 같이 선언한 conn을

pstmt = conn.preparedStatement(query문); 과 같이 대입하여 사용한다.

쿼리문을 바로 넣으며

쿼리문은 ? 키워드를 사용하게 된다.

예를들어 SELECT * FROM student WHERE mun = ? AND num = ? 라고 하고 pstmt에 집어넣고

pstmt.set[자료형](?표 순서, 들어갈 값으로 앞의 자료형과 같아야 함);

위와 같은 형식으로 입력한다고 칠 때

pstmt.setString(1, "ABC");

pstmt.setInt(2, 3);

이라고 입력하면 1번 ?에 ABC가 들어가고 2번 ?에 3이라는 값이 들어가

SELECT * FROM student WHERE mun = 'ABC' AND num = 3 이라는 쿼리문이 완성된다.

setString의 경우는 작은 따옴표 '' 키워드는 자동으로 입력된다.

예제를 확인해보자

Employee.java

package model.vo;

import java.sql.Date;

public class Employee implements java.io.Serializable{
	private int empId;
	private String empName;
	private String empNo;
	private String email;
	private String phone;
	private Date hireDate;
	private String jobId;
	private int salary;
	private double bonus;
	private String marriage;
	private int mgrId;
	private String deptId;
	
	public Employee() {}
	
	public Employee(int empId, String empName, String empNo, String email, String phone, Date hireDate, String jobId,
			int salary, double bonus, String marriage, int mgrId, String deptId) {
		super();
		this.empId = empId;
		this.empName = empName;
		this.empNo = empNo;
		this.email = email;
		this.phone = phone;
		this.hireDate = hireDate;
		this.jobId = jobId;
		this.salary = salary;
		this.bonus = bonus;
		this.marriage = marriage;
		this.mgrId = mgrId;
		this.deptId = deptId;
	}

	public Employee(int empId, String empName, String empNo, String email, String phone, String jobId, int salary,
			double bonus, String marriage, int mgrId, String deptId) {
		super();
		this.empId = empId;
		this.empName = empName;
		this.empNo = empNo;
		this.email = email;
		this.phone = phone;
		this.jobId = jobId;
		this.salary = salary;
		this.bonus = bonus;
		this.marriage = marriage;
		this.mgrId = mgrId;
		this.deptId = deptId;
	}

	public Employee(int empId, String jobId, int salary, double bonus, String deptId) {
		super();
		this.empId = empId;
		this.jobId = jobId;
		this.salary = salary;
		this.bonus = bonus;
		this.deptId = deptId;
	}

	public int getEmpId() {
		return empId;
	}

	public void setEmpId(int empId) {
		this.empId = empId;
	}

	public String getEmpName() {
		return empName;
	}

	public void setEmpName(String empName) {
		this.empName = empName;
	}

	public String getEmpNo() {
		return empNo;
	}

	public void setEmpNo(String empNo) {
		this.empNo = empNo;
	}

	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 Date getHireDate() {
		return hireDate;
	}

	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}

	public String getJobId() {
		return jobId;
	}

	public void setJobId(String jobId) {
		this.jobId = jobId;
	}

	public int getSalary() {
		return salary;
	}

	public void setSalary(int salary) {
		this.salary = salary;
	}

	public double getBonus() {
		return bonus;
	}

	public void setBonus(double bonus) {
		this.bonus = bonus;
	}

	public String getMarriage() {
		return marriage;
	}

	public void setMarriage(String marriage) {
		this.marriage = marriage;
	}

	public int getMgrId() {
		return mgrId;
	}

	public void setMgrId(int mgrId) {
		this.mgrId = mgrId;
	}

	public String getDeptId() {
		return deptId;
	}

	public void setDeptId(String deptId) {
		this.deptId = deptId;
	}
	
	
}

TestMain.java

package controller;

import java.util.Scanner;

import model.dao.EmployeeModel;
import model.vo.Employee;

public class TestJDBC {

	public static void main(String[] args) {
		EmployeeModel model = new EmployeeModel();
		Scanner sc = new Scanner(System.in);
		
		// 모든 직원 조회
		// model.selectAll();
		
		
		
		
		
		
		// 키보드로 사번을 입력받아 사원 조회
		// System.out.print("사번을 입력하세요 : ");
		// model.selectOne(sc.nextInt());
		
		
		
		
		
		/*
		// 새 직원 정보를 키보드로 입력 받아 실행
		// 데이터 입력 시 제약조건 확인해서 잘 넣어야 함
		
		// 키보드 입력하여 변수에 저장
		
		Employee emp = new Employee();
		
		System.out.print("사번을 입력하세요 : ");
		emp.setEmpId(sc.nextInt());
		sc.nextLine();
		System.out.print("이름을 입력하세요 : ");
		emp.setEmpName(sc.nextLine());
		System.out.print("주민번호를 입력하세요 : ");
		emp.setEmpNo(sc.nextLine());
		System.out.print("이메일을 입력하세요 : ");
		emp.setEmail(sc.nextLine());
		System.out.print("전화번호를 입력하세요 : ");
		emp.setPhone(sc.nextLine());
		System.out.print("직급을 입력하세요 : ");
		emp.setJobId(sc.nextLine());
		System.out.print("봉급을 입력하세요 : ");
		emp.setSalary(sc.nextInt());
		sc.nextLine();
		System.out.print("보너스를 입력하세요 : ");
		emp.setBonus(sc.nextDouble());
		sc.nextLine();
		System.out.print("결혼 여부를 입력하세요 : ");
		emp.setMarriage(sc.nextLine());
		System.out.print("사수의 사번을 입력하세요 : ");
		emp.setMgrId(sc.nextInt());
		sc.nextLine();
		System.out.print("Dept ID를 입력하세요 : ");
		emp.setDeptId(sc.nextLine());
		
		model.insertEmployee(emp);
		
		// 사번으로 조회
		model.selectOne(emp.getEmpId());
		
		*/
		
		
		
		
		/*
		// 수정 할 항목의 값을 키보드로 입력 받아 실행
		Employee em = new Employee();
		
		System.out.print("사번을 입력하세요 : ");
		em.setEmpId(sc.nextInt());
		sc.nextLine();
		System.out.print("직급을 입력하세요 : ");
		em.setJobId(sc.nextLine());
		System.out.print("봉급을 입력하세요 : ");
		em.setSalary(sc.nextInt());
		sc.nextLine();
		System.out.print("보너스를 입력하세요 : ");
		em.setBonus(sc.nextDouble());
		sc.nextLine();
		System.out.print("Dept ID를 입력하세요 : ");
		em.setDeptId(sc.nextLine());
		
		// update
		model.updateEmployee(em);
		
		// 사번 입력받아 조회
		model.selectOne(em.getEmpId());
		*/
		
		
		
		
		
		// 키보드로 사번을 입력받아 삭제
		System.out.print("사번을 입력하세요 : ");
		int tempNum = sc.nextInt();
		model.deleteEmployee(tempNum);
		
		// 사번 입력받아 조회
		model.selectOne(tempNum);
		
	}

}

JDBCModel.java

package model.dao;

import java.sql.*;
/*import java.sql.Connection;
import java.sql.ResultSet;*/

import model.vo.Employee;

public class EmployeeModel {
	Connection conn = null;
	Statement stmt = null;
	ResultSet rset = null;
	PreparedStatement pstmt = null;
	String query = "";
	int result = 0;
	
	// 모든 직원 정보 조회용 메소드
	public void selectAll() {
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
			
			query = "SELECT * FROM EMPLOYEE";
			
			stmt = conn.createStatement();
			
			rset = stmt.executeQuery(query);
			
			while(rset.next()) {
				System.out.println(rset.getInt("EMP_ID") + ", "
						        + rset.getString("EMP_NAME") + ", "
						        + rset.getString("EMP_NO") + ", "
						        + rset.getString("EMAIL") + ", "
						        + rset.getString("PHONE") + ", "
						        + rset.getDate("HIRE_DATE") + ", "
						        + rset.getString("JOB_ID") + ", "
						        + rset.getInt("SALARY") + ", "
						        + rset.getDouble("BONUS_PCT") + ", "
						        + rset.getString("MARRIAGE") + ", "
						        + rset.getInt("MGR_ID") + ", "
						        + rset.getString("DEPT_ID"));
			}
			
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				rset.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
	}
	
	// 사번을 이용해서 직원 한 명의 정보 조회
	public void selectOne(int empId) {

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
			
			// Statement 사용시
			
			/*String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = " + empId;
			// Statement는 query만 조작하면 바로 db 공격이 가능하여 보안에 취약
			stmt = conn.createStatement();
			
			rset = stmt.executeQuery(query);*/
			
			// PreparedStatement 사용시
			// PreParedStatement는 위의 보안 이슈를 해결하여 만들어짐
			query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = ?";
			
			pstmt = conn.prepareStatement(query);
			
			pstmt.setInt(1, empId);
			
			rset = pstmt.executeQuery();
			
			if(rset.next()) {
				System.out.println(rset.getInt("EMP_ID") + ", "
				        + rset.getString("EMP_NAME") + ", "
				        + rset.getString("EMP_NO") + ", "
				        + rset.getString("EMAIL") + ", "
				        + rset.getString("PHONE") + ", "
				        + rset.getDate("HIRE_DATE") + ", "
				        + rset.getString("JOB_ID") + ", "
				        + rset.getInt("SALARY") + ", "
				        + rset.getDouble("BONUS_PCT") + ", "
				        + rset.getString("MARRIAGE") + ", "
				        + rset.getInt("MGR_ID") + ", "
				        + rset.getString("DEPT_ID"));
			}
		
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
		
	}

	public void insertEmployee(Employee emp) {
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
			
			/*
			query = "INSERT INTO employee(emp_Id, emp_Name, emp_No, email, phone, job_Id, salary, bonus_pct, marriage, mgr_Id, dept_Id) " + 
					"VALUES(" + 
					emp.getEmpId() + ", '" +
					emp.getEmpName() + "', '" +
					emp.getEmpNo() + "', '" +
					emp.getEmail() + "', '" +
					emp.getPhone() + "', '" +
					emp.getJobId() + "', " +
					emp.getSalary() + ", " +
					emp.getBonus() + ", '" +
					emp.getMarriage() + "', " +
					emp.getMgrId() + ", " +
					emp.getDeptId() + ")";
			
			stmt = conn.createStatement();
			
			result = stmt.executeUpdate(query);
			*/
			
			query = "INSERT INTO employee(emp_Id, emp_Name, emp_No, email, phone, job_Id, salary, bonus_pct, marriage, mgr_Id, dept_Id) " + 
					"VALUES(" + 
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ", " +
					"?" + ")";
			
			pstmt = conn.prepareStatement(query);
		
			pstmt.setInt(1, emp.getEmpId());
			pstmt.setString(2, emp.getEmpName());
			pstmt.setString(3, emp.getEmpNo());
			pstmt.setString(4, emp.getEmail());
			pstmt.setString(5, emp.getPhone());
			pstmt.setString(6, emp.getJobId());
			pstmt.setInt(7, emp.getSalary());
			pstmt.setDouble(8, emp.getBonus());
			pstmt.setString(9, emp.getMarriage());
			pstmt.setInt(10, emp.getMgrId());
			pstmt.setString(11, emp.getDeptId());
			
			result = pstmt.executeUpdate();
		
			if(result > 0) {
				System.out.println(result + "개의 행이 추가되었습니다.");
				conn.commit();
			}else {
				System.out.println("실패");
				conn.rollback();
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	public void updateEmployee(Employee em) {
try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
			
			
			/*
			stmt = conn.createStatement();		
			
			query = "UPDATE employee SET job_id = '" + em.getJobId() +
					"' WHERE emp_id = " + em.getEmpId();
			System.out.println(query);
			
			
			result = stmt.executeUpdate(query);
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET salary = " + em.getSalary() +
					" WHERE emp_id = " + em.getEmpId();
			System.out.println(query);
			
			result = stmt.executeUpdate(query);
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET bonus_pct = " + em.getBonus() +
					" WHERE emp_id = " + em.getEmpId();
			System.out.println(query);
			
			result = stmt.executeUpdate(query);
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET dept_id = '" + em.getDeptId() +
					"' WHERE emp_id = " + em.getEmpId();
			System.out.println(query);
			
			result = stmt.executeUpdate(query);
			System.out.println(result + "행이 수정되었습니다.");
			*/
			
			
			query = "UPDATE employee SET job_id = ?" +
					"WHERE emp_id = ?";
			System.out.println(query);
			
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, em.getJobId());
			pstmt.setInt(2, em.getEmpId());
			result = pstmt.executeUpdate();
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET salary = ?" +
					" WHERE emp_id = ?";
			System.out.println(query);

			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, em.getSalary());
			pstmt.setInt(2, em.getEmpId());
			result = pstmt.executeUpdate();
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET bonus_pct = ?" +
					" WHERE emp_id = ?";
			System.out.println(query);

			pstmt = conn.prepareStatement(query);
			pstmt.setDouble(1, em.getBonus());
			pstmt.setInt(2, em.getEmpId());
			result = pstmt.executeUpdate();
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			query = "UPDATE employee SET dept_id = ?" +
					" WHERE emp_id = ?";
			System.out.println(query);

			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, em.getDeptId());
			pstmt.setInt(2, em.getEmpId());
			result = pstmt.executeUpdate();
			System.out.println(result + "행이 수정되었습니다.");
			
			
			
			conn.commit();
			
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void deleteEmployee(int empNo) {
		try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
             
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
             
            
            /*
            stmt = conn.createStatement();
             
            query = "DELETE FROM employee WHERE emp_id = " + empNo;
            System.out.println(query);
             
            result = stmt.executeUpdate(query);
             */
            
            query = "DELETE FROM employee WHERE emp_id = ?";
            pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, empNo);
			result = pstmt.executeUpdate();
            
            if (result > 0) {
                System.out.println(result + "개의 행이 수정되었습니다.");
                conn.commit();
            }else {
                System.out.println("실패했습니다.");
                conn.rollback();
            }
             
            
             
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
	}

}