TIL/JDBC
[JDBC] xml, injection, insert, update
yndev
2022. 2. 3. 23:57
이전에 쿼리문을 써줄 때 문장을 줄맞춤을 써주는게 번거롭고, 한 줄로 써도 오류를 찾기 힘들다.
이러한 쿼리문을 별도의 문서로 분리해서 사용해준다.
이렇게 작성을하고 실행 후 프로젝트 refresh(새로고침)를 해주면 employee-query.xml이 생성된 것을 확인할 수 있다.
package com.greedy.section02.preparedstatement;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Properties;
public class Test {
public static void main(String[] args) {
Properties prop = new Properties();
prop.setProperty("keyString", "valueString");
try {
prop.storeToXML(new FileOutputStream("src/com/greedy/section02/preparedstatement/employee-query.xml"), "");
} catch (IOException e) {
e.printStackTrace();
}
}
}
기본적으로 Design탭으로 돼있는데 Source탭으로 변경해준다.
entry 안에서 키값 넣어준 후 쿼리문 작성해준다.
String query 문 작성할 때 상단 xml파일에서 작성해 준 key값(selectEmpByFamilName)을 불러온다.
public class Application5 {
public static void main(String[] args) {
/* employee-query.xml에서 쿼리문 가져와서 수행 */
Connection con = getConnection();
PreparedStatement pstmt = null;
ResultSet rset = null;
EmployeeDTO row = null;
List<EmployeeDTO> empList = null;
Scanner sc = new Scanner(System.in);
System.out.print("조회할 이름의 성을 입력하세요 : ");
String empName = sc.nextLine();
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("src/com/greedy/section02/preparedstatement/employee-query.xml"));
String query = prop.getProperty("selectEmpByFamilyName");
System.out.println("query : " + query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, empName);
rset = pstmt.executeQuery();
empList = new ArrayList<>();
while(rset.next()) {
row = new EmployeeDTO();
row.setEmpId(rset.getString("EMP_ID"));
row.setEmpName(rset.getString("EMP_NAME"));
row.setEmpNo(rset.getString("EMP_NO"));
row.setEmail(rset.getString("EMAIL"));
row.setPhone(rset.getString("PHONE"));
row.setDeptCode(rset.getString("DEPT_CODE"));
row.setJobCode(rset.getString("JOB_CODE"));
row.setSalLevel(rset.getString("SAL_LEVEL"));
row.setSalary(rset.getInt("SALARY"));
row.setBonus(rset.getDouble("BONUS"));
row.setManagerId(rset.getString("MANAGER_ID"));
row.setHireDate(rset.getDate("HIRE_DATE"));
row.setEntDate(rset.getDate("ENT_DATE"));
row.setEntYn(rset.getString("ENT_YN"));
empList.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (InvalidPropertiesFormatException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
close(rset);
close(pstmt);
close(con);
}
for(EmployeeDTO emp : empList) {
System.out.println(emp);
}
}
}
사번, 이름(사번과 맞지 않는 이름)을 입력해서 출력문을 통해 회원정보가 있는지 확인
package com.greedy.section03.sqlinjection;
import static com.greedy.common.JDBCTemplate.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Application1 {
private static String empId = "200";
private static String empName = "홍길동";
public static void main(String[] args) {
Connection con = getConnection();
Statement stmt = null;
ResultSet rset = null;
String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = '" + empId + "' AND EMP_NAME = '" + empName + "'";
System.out.println(query);
try {
stmt = con.createStatement();
rset = stmt.executeQuery(query);
if(rset.next()) {
System.out.println(rset.getString("EMP_NAME") + "님 환영합니다.");
} else {
System.out.println("회원 정보가 없습니다.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(stmt);
close(rset);
close(con);
}
}
}
여기서 상단의 코드를 바꿔주면 사번 200번을 입력해도 이름까지 잘 출력이 된다.
해킹 기법 중 하나인 injection의 예시이다.
private static String empId = "200";
private static String empName = "' OR 1=1 AND EMP_ID = '200";
이러한 문제점을 막을 수 있는 것이 Prepared Statement로 작성하면 된다.
package com.greedy.section03.sqlinjection;
import static com.greedy.common.JDBCTemplate.close;
import static com.greedy.common.JDBCTemplate.getConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Application2 {
private static String empId = "200";
private static String empName = "' OR 1=1 AND EMP_ID = '200";
public static void main(String[] args) {
Connection con = getConnection();
PreparedStatement pstmt = null;
ResultSet rset = null;
String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = ? AND EMP_NAME = ?";
System.out.println(query);
try {
pstmt = con.prepareStatement(query);
pstmt.setString(1, empId);
pstmt.setString(2, empName);
rset = pstmt.executeQuery();
if(rset.next()) {
System.out.println(rset.getString("EMP_NAME") + "님 환영합니다.");
} else {
System.out.println("회원 정보가 없습니다.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(rset);
close(con);
}
}
}eclipse에서 sql insert하는 법
오라클에서 새로운 계정, 접속계정 따로 만들어줘서 수업 때 받은 쿼리문 수행해준 후에 생긴 테이블들.
나는 따로 REVIEW계정을 새로 생성했다.
eclipse로 돌아와서 기존 cnofig, lib 폴더 복사한 새로운 프로젝트 생성 후 oracle에서 새롭게 생성한 계정을 connection-info에서 수정해준다.
앞서 만들어놨던 employee-query를 복사해서 menu-query로 수정하고 메뉴를 insert할 key값 설정 후 안에 쿼리문을 작성해 줬다. 넣어야 할 값들이 values이기 때문에 ?(물음표)로 작성해준다.
아직 값을 넣기 전이고 xml파일이 있는 경로 입력하고 해당 key값을 입력 후 실행하면 상단의 xml파일에서 입력해준 쿼리문이 그대로 출력된다.
SELECT 수행 시 결과 값 ResultSet 객체를 result로 만들어준다.
INSERT/UPDATE/DELTE 수행 시 결과 값 삽입/수정/삭제 된 행의 개수를 나타내기 때문에 ResultSet대신 result를 써준다.
public class Application1 {
public static void main(String[] args) {
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("insertMenu");
System.out.println(query);
} catch (IOException e) {
e.printStackTrace();
}
}
}
이젠 ? 가 돼있는 곳에 값을 넣어준다.
(select시 executeQuery() insert/update/delete시 executeUpdate()로 수행함)
public static void main(String[] args) {
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("insertMenu");
System.out.println(query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, "알리오올리오");
pstmt.setInt(2, 20000);
pstmt.setInt(3, 4);
pstmt.setString(4, "Y");
result = pstmt.executeUpdate();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(con);
}
System.out.println("result : " + result);
}
}
이클립스에선 result값이 1로 된 것 확인되고 oracle에서는 내가 입력한 값이 insert된 것을 확인할 수 있다.
상단의 코드를 좀 더 프로그램화 시키자.
Scanner로 입력받아서 insert를 하는 작업
Scanner 입력해주고 하단에서 직접 입력해준 값을 변수로 변경해준다.
public class Application2 {
public static void main(String[] args) {
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("insertMenu");
System.out.println(query);
Scanner sc = new Scanner(System.in);
System.out.print("메뉴의 이름을 입력하세요 : ");
String menuName = sc.nextLine();
System.out.print("메뉴의 가격을 입력하세요 : ");
int menuPrice = sc.nextInt();
System.out.print("카테고리 코드를 입력하세요 : ");
int categoryCode = sc.nextInt();
System.out.print("판매 여부를 결정해주세요(Y/N) : ");
String orderableStatus = sc.next().toUpperCase();
pstmt = con.prepareStatement(query);
pstmt.setString(1, menuName);
pstmt.setInt(2, menuPrice);
pstmt.setInt(3, categoryCode);
pstmt.setString(4, orderableStatus);
result = pstmt.executeUpdate();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(con);
}
System.out.println("result : " + result);
}
}
위에서 별도의 변수들로 다루는 것 보다
menuDTO 클래스를 만들어서 데이터타입을 하나로 지정해서 만드는게 더 편리할 것이다.
menuDTO 클래스(필드값, 기본생성자, 매개변수 생성자, getter/setter, toString) 클래스를 만들어준다. 코드 생략
public class Application3 {
public static void main(String[] args) {
//다른 클래스에서 작성한다고 가정
Scanner sc = new Scanner(System.in);
System.out.print("메뉴의 이름을 입력하세요 : ");
String menuName = sc.nextLine();
System.out.print("메뉴의 가격을 입력하세요 : ");
int menuPrice = sc.nextInt();
System.out.print("카테고리 코드를 입력하세요 : ");
int categoryCode = sc.nextInt();
System.out.print("판매 여부를 결정해주세요(Y/N) : ");
String orderableStatus = sc.next().toUpperCase();
MenuDTO newMenu = new MenuDTO();
newMenu.setName(menuName);
newMenu.setPrice(menuPrice);
newMenu.setCategoryCode(categoryCode);
newMenu.setOrderableStatus(orderableStatus);
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("insertMenu");
System.out.println(query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, newMenu.getName());
pstmt.setInt(2, newMenu.getPrice());
pstmt.setInt(3, newMenu.getCategoryCode());
pstmt.setString(4, newMenu.getOrderableStatus());
result = pstmt.executeUpdate();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(con);
}
if(result > 0) {
System.out.println("메뉴 등록에 성공하였습니다.");
} else {
System.out.println("메뉴 등록에 실패하였습니다.");
}
}
}
eclipse에서 UPDATE를 통해 기존의 데이터의 값을 수정한다.
menu-query에서 updateMenu 키값 설정 후 UPDATE를 수행할 쿼리문을 먼저 작성해준다.
public class Application1 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("변경할 메뉴 번호를 입력하세요 : ");
int menuCode = sc.nextInt();
System.out.print("변경할 메뉴의 이름을 입력하세요 : ");
String menuName = sc.next();
System.out.print("변경할 메뉴의 가격을 입력하세요 : ");
int menuPrice = sc.nextInt();
MenuDTO changedMenu = new MenuDTO();
changedMenu.setCode(menuCode);
changedMenu.setName(menuName);
changedMenu.setPrice(menuPrice);
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("updateMenu");
pstmt = con.prepareStatement(query);
pstmt.setString(1, changedMenu.getName());
pstmt.setInt(2, changedMenu.getPrice());
pstmt.setInt(3, changedMenu.getCode());
result = pstmt.executeUpdate();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(con);
}
if(result > 0) {
System.out.println("메뉴 변경에 성공하였습니다.");
} else {
System.out.println("메뉴 변경에 실패하였습니다.");
}
}
}
위의 코드에서 delete를 수행해보자.
xml파일에서 deleteMenu key설정 후 쿼리문 작성
public class Application1 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("삭제할 메뉴 번호를 입력하세요 : ");
int menuCode = sc.nextInt();
Connection con = getConnection();
PreparedStatement pstmt = null;
int result = 0;
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("mapper/menu-query.xml"));
String query = prop.getProperty("deleteMenu");
pstmt = con.prepareStatement(query);
pstmt.setInt(1, menuCode);
result = pstmt.executeUpdate();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(pstmt);
close(con);
}
if(result > 0) {
System.out.println("메뉴 삭제에 성공하였습니다.");
} else {
System.out.println("메뉴 삭제에 실패하였습니다.");
}
}
}실행 로그를 확인하는 법.
아직 자세히 배우지 않아서 로그들이 어떻게 진행되는지 확인만 하는 정도.
전체적으로 어떻게 실행되는지 로그가 보여진다.
내가 입력한 값이 틀렸는지 확인도 할 수 있음.
