저번에 사용하였던 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();
}
}
}
}
