[Java] 9. JDBC, CRUD

엔코아 플레이데이터(Encore Playdata) Backend 2기 백엔드 개발 부트캠프 (playdata.io)

백엔드 개발 부트캠프

---

1. 복습

MySQL의 버전이 올라가며 JDBC 드라이버 패키지 구조가 달라져서, 접속 방법이 약간 달라졌다.

* MySQL 버전이 올라가면서 드리이버 패키지 구조가 변경되었다.
* driver = "com.mysql.cj.jdbc.Driver"
* 
* 이에 따라 접속 방법도 변경되었다.
* url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
* id_mysql = "root";
* pw_mysql ="1234";

접속 url을 살펴보면, 어제 코드에는 존재하지 않았던 useSSL과 serverTimezone, allowPublicKeyRetrieval을 설정하는 부분이 추가 되었다.

* useSSL
* SSL : 보안 소켓 계층을 이르는 말로, 인터넷에서 데이터를 안전하게 전송하기 위한 인터넷 통신 규약 프로토콜.
* SSL은 웹 제품 뿐만 아니라 파일전송규약(FTP) 등 다른 TCP/IP 애플리케이션에 적용될 수 있으며, 인증 암호화 기능이 있다.
* 인증은 웹 브라우저와 웹 서버 간에 서로 상대의 신원을 확인하는 기능이다.
* 
* serverTimezone : 서버의 시간대 영역
* UTC : 세계 표준시
* UTC + 9 : 한국 시간
* 전세계의 기준 시간인 영국의 그리니치 평균 시간보다 9시간 빠른 시간을 의미한다.
* UTC는 경도가 0인 그리니치 천문대를 기준으로 하는 세계 협정 시간을 의미한다.
* 
* allowPublicKeyRetrieval : 일반적인 키 검색 허용 여부

따라서 해당 내용에 따라 MySQL에 접속하는 코드를 갱신 하여 실습을 진행하였다.

private String driver = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
private String id_mysql = "root";
private String pw_mysql = "0000";

public Connection conn = null; // MySQL 접속 결과(상태) 저장

public MySQLConnector() {
}

public void connectMySQL() {
    /*
     * 1. driver load : Class.forName("드라이버명");
     * 2. MySQL과 접속 : DriverManager.getConnection("접속주소", "id", "pw");
     * 이 두 작업은 외부와 통신하기 때문에 예외처리가 필요하다. 
     */
    try {
        Class.forName(driver);
        System.out.println("driver load 성공");
        conn = DriverManager.getConnection(url, id_mysql, pw_mysql);
        System.out.println("MySQL 접속 성공");

    } catch (ClassNotFoundException e) {
        System.out.println("ClassNotFoundException: " + e.getMessage());
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    }
}

---

2.  JDBC를 사용한 CRUD 구현

CRUD란 DB에 접근하는 표준 query로, 데이터 삽입, 조회, 수정, 삭제가 존재한다.

1. C (생성 된 테이블에 데이터 삽입) : insert into (필드명) values (데이터);

2. R (생성 된 테이블의 데이터 조회): select 필드명 from 테이블명;

3. U (생성 된 테이블의 데이터 수정): update 테이블명 set 필드명 = 신규데이터 where 검색필드 = 검색값;

4. D (생성 된 테이블의 데이터 삭제): delete from 테이블명 where 검색필드 = 검색값;

 

우리는 java에 query를 작성하여 사용하고 있지만, 사실 이 query는 java에서 실행되는 문장이 아니고, 실행할 수 도 없다. 그렇다면 작성한 query를 어떻게 실행시키는 것일까?

JDBC를 사용하면 이 문제를 해결할 수 있다. java와 DB 사이에 JDBC라는 것을 두고, java에서 query를 작성해 JDBC 객체에 넘겨주면, JDBC가 DB로 해당 객체를 넘겨 query를 실행 후, 그 결과를 받아와 java에 되돌려주는 것이다.

 

이 때 대표적으로 3가지 객체를 사용하는데, Statement / PreparedStatement / Resultset 을 사용한다.

Statement는 쿼리 내부에 변수가 없는 정적 쿼리에서 사용하고, PreparedStatement는 쿼리 내부에 변수가 있는 동적 쿼리에서 사용한다. 그리고 Resultset은 쿼리로 조회한 결과를 담아 올 때 사용하는 객체이다.

* Java에서 SQL을 MySQL에 요청하는 방법
*  1) Statement 객체 생성 : 쿼리 내부에 변수가 포함되지 않을 경우
*  반드시 현재 연결(접속)되어 있는 Connection 객체를 통해 생성해야한다. 따라서 new 키워드를 사용할 수 없다.
*  Statement stmt = conn.createStatement();
*  ResultSet rs = stmt.executeQuery("select * from member");
*  int n = stmt.executeUpdate("insert into member values (1, 'ssss')");
*  int n = stmt.executeUpdate("update member set id = 3 where name = 'lhs1'");
*  int n = stmt.executeUpdate("delete from member where id = 3");
*  
*  2) PreparedStatement 객체 생성 : 쿼리 내부에 변수가 포함되는 경우 (동적쿼리)
*  PreparedStatement pstmt = conn.prepareStatement("쿼리");
*  
*  ex1) 
*  int idx = 3; // member 테이블로부터 id가 3인 레코드만 조회하겠다면?
*  String query = "select * from member where id = ?";
*  
*  PreparedStatement pstmt = conn.prepareStatement(query);
*  pstmt.setInt(1, idx); // 첫 번째 물음표는 idx를 사용해라
*  ResultSet rs = pstmt.executeQuery(); // select 이기 때문에 ResultSet 사용
*  
*  ex2) 
*  int idx = 5;
*  String name = "LES";
*  String query = "insert into member values (?, ?)";
*  
*  PreparedStatement pstmt = conn.prepareStatement(query);
*  pstmt.setInt(1, idx); // 첫 번째 물음표는 idx를 사용해라
*  pstmt.setString(2, name); // 두 번째 물음표는 name을 사용해라
*  int n = pstmt.executeUpdate(); // insert 이기 떄문에 int n 사용
*  
*  
*  공통 메서드
*  ResultSet rs = executeQuery() : select
*  while(rs.next()) { // 데이터를 확인
*  	rs.getInt("필드명");
*  	rs.getString("필드명");
*  	// "필드명" 대신 필드에 대한 index도 사용은 가능 -> 필드에 대한 index는 0이아니라 1부터 시작한다.
*  }
*  int n = executeUpdate() : insert / update / delete
*  if (n > 0) {
*  	System.out.println("~~성공");
*  }

여기서 주의해야할 점은, DB의 필드에 대한 index는 1부터 시작하기 때문에 Array의 index와 헷갈리지 말아야 하는 점이다.

해당 내용을 바탕으로 CRUD 기능을 하는 메서드를 구현하였는데, 우선 C에 해당하는 insert를 구현해보았다.

public void insert() { // 테이블에 데이터 삽입(추가)
    int idx = 12;
    String name = "영어";
    String query = "insert into member values (?, ?)";

    PreparedStatement pstmt = null;
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, idx);
        pstmt.setString(2, name);
        int n = pstmt.executeUpdate();
        if (n > 0)
            System.out.println("----- insert -----");
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt);
    }
}

다음으로 R에 해당하는 select를 구현해보았다. select는 * 을 붙이면 해당하는 모든 칼럼을 가져올 수 있고, where을 붙여 조건에 맞는 데이터만 가져올 수도 있다.

public void selectAll() { // 테이블 데이터 전체 조회 : select * from member
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select * from member");

        System.out.println("----- selectAll -----");
        while (rs.next())
            System.out.println(rs.getInt("id") + " : " + rs.getString("name"));
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(stmt, rs);
    }
}

public void selectOne() { // 테이블 데이터 하나만 조회
    int idx = 11;
    String query = "select * from member where id = ?";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, idx);
        rs = pstmt.executeQuery();

        System.out.println("----- selectOne -----");
        while (rs.next())
//				System.out.println(rs.getInt(1) + " : " + rs.getString(2));
            System.out.println(rs.getInt("id") + " : " + rs.getString("name"));

    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt, rs);
    }
}

세번째로 U에 해당하는 update를 구현해 보았다.

public void update() { // 테이블 데이터 수정
    int idx = 12;
    String name = "불어";
    String query = "update member set name = ? where id = ?";

    PreparedStatement pstmt = null;
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, name);
        pstmt.setInt(2, idx);
        int n = pstmt.executeUpdate();
        if (n > 0)
            System.out.println("----- update -----");
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt);
    }
}

마지막으로 D에 해당하는 delete를 구현해 보았다.

public void delete() { // 테이블 데이터 삭제
    int idx = 12;
    String query = "delete from member where id = ?";

    PreparedStatement pstmt = null;
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, idx);
        int n = pstmt.executeUpdate();
        if (n > 0)
            System.out.println("----- delete -----");
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt);
    }
}

위의 코드들을 보면 JDBC 객체를 생성해 사용했지만, 해당 객체를 일일이 close() 하는 부분은 없다. 해당 부분은 코드가 많이 겹치기 때문에, method overloading을 이용하여 close 메서드를 여러개 만들어 깔끔히 처리해 주었다.

public void close(Statement stmt, ResultSet rs) {
    try {
        rs.close();
        stmt.close();
    } catch (SQLException e) {
        System.out.println("Close SQLException: " + e.getMessage());
    }
}

public void close(PreparedStatement pstmt) {
    try {
        pstmt.close();
    } catch (SQLException e) {
        System.out.println("Close SQLException: " + e.getMessage());
    }
}

public void close(PreparedStatement pstmt, ResultSet rs) {
    try {
        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        System.out.println("Close SQLException: " + e.getMessage());
    }
}

---

3.  CRUD 실습_1) 입력 값에 따라 작업을 수행하는 프로그램

scanner를 통해 입력한 숫자에 따라 작업을 수행하는 프로그램을 만드는 실습을 진행하였다. 해당 실습의 요구사항은 다음과 같다.

입력값 1 : 전체 사원 조회

입력값 2 : 신규 사원 추가

입력값 3 : 전 사원 급여 10% 인상

입력값 4 : 과장 직책 사원 삭제

입력값 5 : 작업 종료

초기 테이블 현황

우선, 전체 사원 조회를 위한 메서드를 구현하였다. 전체를 조회할 때는 특별히 변수가 필요하지 않기 때문에, 정적 쿼리를 사용하는 Statement를 사용하였다.

public void selectAll() {
    Statement stmt = null;
    ResultSet rs = null;
    String query = "select * from employee";

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);

        while(rs.next()) {
            System.out.println(
                    "seq_no: " + rs.getInt("seq_no") + " empNo: " + rs.getInt("empNo") +
                    " empName: " + rs.getString("empName") + " job: " + rs.getString("job") +
                    " mgr: " + rs.getString("mgr") + " hireDate: " + rs.getString("hireDate") +
                    " sale: " + rs.getInt("sale") + " commission: " + rs.getInt("commission") +
                    " deptNo: " + rs.getInt("deptNo")
            );
        }
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(stmt, rs);
    }
}

두 번째로, 신규 사원을 추가하는 메서드를 구현하였다. insert를 하기위해 여러 변수를 넣어줘야 했기에, 동적 쿼리를 사용하는 PreparedStatement를 사용하였다.

여기서 주의할 점은, insert의 맨 첫 값이 NULL로 설정되어 있는 부분이다.

초기 테이블 현황을 보면, 테이블의 칼럼이 총 9개이다. 이 때 맨 앞의 seq_no는 테이블 생성 구문에서 auto_increament를 사용해, 새 데이터가 들어오면 자동으로 1씩 증가하게 생성하였다.

DB에선 해당 칼럼에 NULL값이 들어오면 알아서 auto_increament를 진행하기 때문에, seq_no에 직접 값을 넣어주기보단 NULL을 넘겨 해결하였다.

public void insert() {
    int empNo = 1005;
    String empName = "이현수";
    String job = "팀장";
    String mgr = "01";
    String hireDate = "1995-08-15";
    int sale = 500;
    int commission = 200;
    int deptNo = 20;

    String query = "insert into employee values (NULL, ?, ?, ?, ?, ?, ?, ?, ?)"; 
    PreparedStatement pstmt = null;
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, empNo);
        pstmt.setString(2, empName);
        pstmt.setString(3, job);
        pstmt.setString(4, mgr);
        pstmt.setString(5, hireDate);
        pstmt.setInt(6, sale);
        pstmt.setInt(7, commission);
        pstmt.setInt(8, deptNo);
        int n = pstmt.executeUpdate();
        if (n > 0)
            System.out.println("insert 완료");
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt);
    }		
}

세 번째로, 전 사원의 급여를 10% 인상하는 기능을 구현하였다. 해당 기능의 구현 알고리즘을 정리하자면,

1. select를 통해 모든 사원의 데이터를 가져온다.

2. while문 내에서 가져온 데이터를 한 줄씩 읽으며, 읽은 사원의 급여를 임시저장한다.

3. 임시저장한 급여를 10% 인상한다.

4. 인상 된 급여를 DB에 update 한다.

public void update() {
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    String query1 = "select * from employee";		
    String query2 = "update employee set sale = ? where empNo = ?";

    try {
        pstmt = conn.prepareStatement(query2);
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query1);

        int sale = 0;
        while(rs.next()) {
            sale = rs.getInt("sale");
            sale += (sale * 10 / 100);
            pstmt.setInt(1, sale);
            pstmt.setInt(2, rs.getInt("empNo"));
            int n = pstmt.executeUpdate();
            if (n > 0)
                System.out.println("update 완료");
        }
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(stmt, rs);
        close(pstmt);
    }
}

마지막으로, 과장 직책인 사원을 삭제하는 메서드를 구현하였다. "과장" 이라는 변수를 쿼리에 넣어줘야 했기 때문에 동적 쿼리를 사용하는 PreparedStatement를 사용하였다.

public void delete() {
    PreparedStatement pstmt = null;
    String job = "과장";
    String query = "delete from employee where job = ?";
    try {
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, job);
        int n = pstmt.executeUpdate();
        if (n > 0)
            System.out.println("delete 완료");
    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
    } finally {
        close(pstmt);
    }
}

남은 것은 입력 값으로 5 or 1~4가 아닌 다른 숫자가 들어온다면 프로그램을 종료하는 기능이다.

이렇게 입력 값이 다르게 들어올 때 각 입력값에 따라 if-else if-else를 사용하여 구현할 수도 있지만, 이 경우 switch-case구문을 사용하여 구현해보았다.

또한, 반복문을 종료시키며 연이어 프로그램을 종료시킬 수도있지만, 이 경우 exit() 기능을 사용해 입력값으로 1~4가 아닌 숫자가 들어올 시 프로그램을 바로 종료시킬 수 있도록 구현하였다.

public static void main(String[] args) {
    Scanner scan = new Scanner(System.in);
    MySQLConnector mysql = new MySQLConnector();
    mysql.connectMySQL();
    while (true) {
        System.out.println("원하는 작업번호를 입력 해 주세요.");
        System.out.print("1. 전체 사원조회 / 2. 사원추가 / 3. 전사원 급여 10% 올림 / 4. 과장 직책 사원 삭제 / 5. 작업종료: ");
        int input = scan.nextInt();

        switch (input) {
        case 1:
            mysql.selectAll();
            break;
        case 2:
            mysql.insert();
            break;
        case 3:
            mysql.update();
            break;
        case 4:
            mysql.delete();
            break;
        case 5:
            System.exit(0);
        default:
            System.exit(0);
        }
    }
}

---

4. 9일차 후기

CRUD는 기본적인 DB 사용방법이기 때문에, 추후에 배울 Mybatis, JPA를 위해서도 반드시 사용할 줄 알아야한다.

또한, java내에선 query를 실행할 수 없기 때문에 JDBC를 사용해 DB와 통신하는 과정을 이해하는 것도 중요하다.

 

웹에서 전송되는 수 많은 데이터를 개인 컴퓨터에 저장할 수 없기에, 웹 개발을 함에 있어서 DB는 절대 빠질 수 없다. 요즘엔 JPA가 강세를 보이고 있기 때문에 개발자가 직접 query를 작성할 일은 많이 줄어들었지만, JPA를 사용하려면 query를 알아야 하는 것도 사실이다. 때문에 이번 수업을 통해,  앞으로 사용할 JPA를 잘 다룰 수 있으면 좋을 것 같다는 생각을 했다.