TIL - 3/20 - JDBC

DTO

Data Transfer Object

여러 계층간 데이터 전송을 위해 다양한 타입의 데이터를 하나로 묶어 전송하는 쓰임새의 클래스

유의어 : VO, Bean, Entity, …

작성 유의사항

모든 필드는 private

기본 생성자와 모든 필드를 초기화하는 생성자

모든 필드에 대한 getter, setter method

toString method overriding → 필드값 하나의 문자열로 반환

Serialization(option)

java.sql.PreparedStatement

1. Statement의 하위 인터페이스

2. 미리 sql문을 담은 상태로 생성됨(동적

3. placeholder(?)를 사용해 런타임에 매개변수에 값 전달 가능

   → 동적 쿼리 생성에 유용

String sql = "SELECT * FROM employee WHERE dept_code = ?";

placeholder(?)를 담아 sql문 선언하고,
pstmt = conn.prepareStatement(sql);

해당 쿼리를 담은 채 pstmt선언,
pstmt.setString(1, sc.nextLine());

placeholder의 인덱스(1부터 시작)과 넣을 값을 담아준다.
rset = pstmt.executeQuery();

statement와 동일하게 rset에 쿼리값을 넣어주기

while (rset.next()) {
                employeeDTOList.add(new EmployeeDTO(
                        rset.getString("emp_id"),
                        rset.getString("emp_name"),
                        rset.getString("emp_no"),
                        rset.getString("email"),
                        rset.getString("phone"),
                        rset.getString("dept_code"),
                        rset.getString("job_code"),
                        rset.getString("sal_level"),
                        rset.getInt("salary"),
                        rset.getDouble("bonus"),
                        rset.getString("manager_id"),
                        rset.getDate("hire_date"),
                        rset.getDate("quit_date"),
                        rset.getString("quit_yn").charAt(0)
                ));
            }
// List에 마지막 레코드까지 담아주기

statement의 한계점

String query = "SELECT * FROM employee WHERE emp_id = '" + empId+"'";

정적 쿼리 방식으로 인해, 값 검증 x

' OR '1 를 삽입하게 될 경우, sql Injection 으로 모든 db 조회 가능

statement를 이용한 sql injection 시도

prepared statement를 이용한 sql injection 시도, 조회가 안되는 것을 볼 수 있다.

Statement vs PreparedStatement

1. 파라미터 바인딩
   • Statement : 파라미터 바인딩 불가, 쿼리 문자열 그대로 실행
   • PreparedStatement : ?(홀더)를 통한 파라미터 바인딩 가능
2. SQL Injection
   • Statement : 취약
   • PreparedStatement : 방지 가능
3. 컴파일 방식
   • Statement : sql문 실행될 때마다 db 엔진에서 매번 새롭게 컴파일 진행(성능 저하)
   • PreparedStatement : 최초 1회 컴파일, 동일 sql문 반복 실행시 캐싱 (성능 우월)

XML

쿼리를 밖으로 빼서 관리해주기 위해 xml로 빼주자.

<?xml version="1.0" encoding="UTF-8" ?> // xml 문서의 최상단에 오는거
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> // properties타입 체크해주는거
<properties> // root tag
    <entry key="selectByEmpByFamilyName"> // key값을 통해 쿼리문을 꺼낼것임
        SELECT
            *
        FROM
            employee
        WHERE
            emp_name LIKE CONCAT(?, '%')
    </entry>
</properties>

String oldQuery = "SELECT * FROM employee WHERE EMP_NAME LIKE ?"; // 기존 쿼리

Properties prop = new Properties();
prop.loadFromXML(new FileInputStream("src/main/java/com/ino/mapper/employee-query.xml"));
String newQuery = prop.getProperty("selectByEmpByFamilyName"); // newQuery

DML도 동일하게 해보자.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <entry key="insertMenu">
        INSERT INTO
            tbl_menu(
                    menu_name
                  , menu_price
                  , category_code
                  , orderable_status
                    )
        VALUES(
                ?
              , ?
              , ?
              , ?
               )
    </entry>
</properties>

public class Application1 {
    public static void main(String[] args) {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        int result = 0;
        Properties prop = new Properties();
        Scanner sc = new Scanner(System.in);
        try {
            prop.loadFromXML(new FileInputStream("src/main/java/com/ino/mapper/menu-query.xml"));
            String sql = prop.getProperty("insertMenu");
            pstmt = conn.prepareStatement(sql);
            System.out.print("insert name: ");
            String mName = sc.nextLine();
            System.out.print("insert price: ");
            int menuPrice = sc.nextInt();
            System.out.print("insert cCode : ");
            int categoryCode = sc.nextInt();
            sc.nextLine();
            System.out.println("insert orderable_status: ");
            char orderable_status = sc.nextLine().charAt(0);
            pstmt.setString(1, mName);
            pstmt.setInt(2, menuPrice);
            pstmt.setInt(3, categoryCode);
            pstmt.setString(4, String.valueOf(orderable_status));
            result = pstmt.executeUpdate();
            System.out.println(result);
        } catch (IOException | SQLException e) {
            e.printStackTrace();
        } finally {
            close(pstmt);
            close(conn);
        }
    }
}

결과가 잘 나오는 것을 볼 수 있다 :)