Java 정리 39

10 Jan 2019

Reading time ~7 minutes

Java 정리 39 - JDBC(6), CallableStatement를 사용한 CRUD, 오라클 외부접속, Transaction 처리

Procedure가 CURSOR를 out parameter로 내보낼 때

CREATE OR REPLACE PROCEDURE 프로시저명 (
    커서명 OUT SYS_REFCURSOR
)
IS
BEGIN
    ...
END;
/

호출

CallableStatement cstmt = con.prepareCall("{ call 프로시저명(?) }");

바인드변수 값 설정
- Types.REF_CURSOR로는 특정 DBMS에서 반환하는 CURSOR를 저장하고 사용할 수 없다.
- Oracle CURSOR를 사용할 땐 DBMS에서 제공하는 CURSOR 객체를 사용해야 한다.
  - oracle.jdbc.OracleTypes.CURSOR를 사용
  - DBMS Oracle 사용 시 Types.REF_CURSOR를 사용하면 에러 발생

// out parameter : SYS_REFCURSOR -> OracleTypes.CURSOR
cstmt.registerOutParameter(1, Types.REF_CURSOR);

cstmt.registerOutParameter(바인드변수인덱스, OracleTypes.CURSOR);

실행

cstmt.execute();

커서를 받아오기
- 오라클 CURSOR => 자바 ResultSet
- Statement의 getResultSet()는 인덱스를 파라미터로 받는 오버로딩이 method가 없어 사용불가
  - CallableStatement의 getObject()를 사용하여 Object를 ResultSet으로 형변환 시켜 사용한다.

ResultSet rs = (ResultSet)cstmt.getObject(인덱스);

CallableStatement를 사용한 CRUD

오라클 공부하며 만들었던 Procedure들과 추가로 필요한 Procedure를 만들어 호출하여 CRUD 수행하는 예제

PROCEDURE insert_test_proc(
     empno NUMBER,
     ename VARCHAR2,
     sal NUMBER,
     job VARCHAR2,
     msg OUT VARCHAR2,
     row_cnt OUT NUMBER
)                       
IS
    -- in parameter는 값 변경이 안되기 때문에 변경되는  값을 담을 변수 선언
    temp_sal NUMBER := sal;
    
BEGIN
     row_cnt := 0;
     IF empno BETWEEN 1 AND 9999 THEN
                                   
          IF temp_sal < 2500 THEN
              temp_sal := 2500;
          ELSIF temp_sal > 8000 THEN
              temp_sal := 8000;
          END IF;              
          
          IF job IN ('사원', '주임', '대리', '과장',  '차장', '부장', '이사') THEN
              
              INSERT INTO test_proc(empno, ename,  hiredate, sal, job)
              VALUES(empno, INITCAP(ename), SYSDATE,  temp_sal, job);
                     
              row_cnt := SQL%ROWCOUNT;
              COMMIT;
              msg := empno||'번 사원 정보가  추가되었습니다.';
              
          ELSE
              msg := job||'은 입력가능한 직급이  아닙니다';         
          ENd IF;   
                   
                   
     ELSE
          msg := empno||'사원번호는 1에서 9999사이가  아닙니다.';
     END IF;  
     
     EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN
          msg := empno||'번 사원번호는 이미  존재합니다.';
     WHEN OTHERS THEN                                   
          IF sqlcode = -12899 THEN
              msg := ename||'명은 한글 3자, 영어 10자를  초과하였습니다.';
          END IF;
END;

-- 프로시저를 이용한 UPDATE
-- 사원번호, 직급, 연봉을 입력받아 test_proc테이블에서 변경하는 프로세저
-- 제약들
--  * 직급은 사원, 주임, 대리, 과장, 차장, 부장, 이사,
--    상무, 전무, 대표이사가 입력 되었을 때만 변경
--  * 연봉이 현재 연봉보다 작다면 현재 연봉보다 5% 인상액으로 변경

CREATE OR REPLACE PROCEDURE update_test_proc(
  i_empno NUMBER,
    i_job VARCHAR2,
    sal    NUMBER,
    res_msg OUT VARCHAR2,
    row_cnt OUT NUMBER
)
IS
    -- 현재 연봉을 받아오기 위해 만든 변수
    temp_sal NUMBER;

BEGIN
    row_cnt := 0;

    IF i_job IN ('사원','주임','대리','과장',
               '차장','부장','이사','상무',
               '전무','대표이사') THEN

        SELECT sal
        INTO temp_sal
        FROM test_proc
        WHERE empno = i_empno;

        IF sal < temp_sal THEN
            temp_sal := TRUNC(temp_sal*1.05, 0);
        ELSE
            temp_sal := sal;
        END IF;

        UPDATE test_proc
        SET sal = temp_sal, job = i_job
        WHERE empno = i_empno;

        row_cnt := SQL%ROWCOUNT;

        COMMIT;

        res_msg := i_empno||'번 사원의 정보가 변경되었습니다. 제시연봉 : '||sal||
                        ', 확정연봉 : '||temp_sal;

    ELSE
        res_msg := i_job||'은 사내 직무가 아닙니다.';
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        res_msg := i_empno||'번 사원은 존재하지 않습니다.';
    WHEN OTHERS THEN
        res_msg := sqlerrm;

END;
/

-- 프로시저를 이용한 DELETE
-- 언어가 아니라 confirm dialog 없이 삭제처리
-- 사원번호를 입력받아 test_proc 테이블에서 삭제하는 PROCEDURE

CREATE OR REPLACE PROCEDURE delete_test_proc(
    i_empno NUMBER,
    msg OUT VARCHAR2,
    cnt OUT NUMBER
)
IS

    res_msg VARCHAR2(15);

BEGIN

     DELETE FROM test_proc
     WHERE empno = i_empno;

     cnt := SQL%ROWCOUNT;

     IF cnt = 1 THEN
         COMMIT;
         msg := i_empno||'번 사원 퇴사. 행복하세요~~';
     ELSE
         msg := i_empno||'번 사원은 존재하지 않습니다.';
     END IF;

     EXCEPTION
     WHEN OTHERS THEN
         res_msg := '문제발생'||sqlerrm;

END;
/

추가로 SELECT를 위한 Procedure를 만들어 준다.
- Procedure에서 조회된 결과가 여러개라면 out parameter에 담을 수 없음
  - 커서의 제어권을 SYS_REFCURSOR로 밖으로 내준다
- 오라클에서 SYS_REFCURSOR를 바인드변수 타입 REF_CURSOR로 받음
  - java에선 ResultSet으로 받는다.
  - 오라클 CURSOR는 Types.REF_CURSOR로 out parameter 등록이 안된다.
    - OracleTypes.CURSOR를 사용

/*
    test_proc 테이블의 모든 사원 정보를 조회하여 out parameter에
    설정하는 Procedure
*/
CREATE OR REPLACE PROCEDURE select_all_test_proc(
    cur_emp OUT SYS_REFCURSOR,
    date_pattern IN VARCHAR2    
)
IS

BEGIN

    OPEN cur_emp FOR
    SELECT empno, ename, TO_CHAR(hiredate, date_pattern) hiredate, sal, job
    FROM test_proc;                                               

END;

하나의 행을 조회하는 Procedure

/*
  사원번호를 입력받아 사원명, 연봉, 직무, 입사일을 반환하는 Procedure
*/                                                                
CREATE OR REPLACE PROCEDURE select_one_test_proc(
    i_empno IN NUMBER,
    cur_emp OUT SYS_REFCURSOR,
    err_msg OUT VARCHAR2
)
IS
BEGIN         
    
    err_msg := '성공';
    OPEN cur_emp FOR             
        SELECT ename, sal, job, TO_CHAR(hiredate, 'yy-mm-dd day') hiredate
        FROM test_proc
        WHERE empno = i_empno;

    EXCEPTION
    WHEN OTHERS THEN
        err_msg := '문제 발생'||sqlerrm;

END;
/

Java 실행되는 클래스파일 내용은 생략
- 구현한 CallableStatement CRUD 패키지 링크

오라클 외부 접속

public class Test {
     public Test() throws SQLException{
          String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
          String id = "scott";
          String pass = "tiger";
          
          Connection con = null;
          
          try {
              con =  GetConnection.getInstance().getConn(url, id, pass);
              System.out.println(con);
          } finally {
              
          }
                   
     }
     
     public static void main(String[] args) {
          try {
              new Test();
          } catch (SQLException se) {
              se.printStackTrace();
          }
     }
}

그동안 localhost 또는 루프백(127.0.0.1)을 통해서 자신의 DBMS에만 접속했었음

String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

자신의 IP address를 직접 입력하고 접속 시도하면 에러발생
- DBMS를 가진 타인의 IP를 입력해도 접속 못하고 time out 에러발생

String url =  "jdbc:oracle:thin:@publicIpAddress:1521:orcl";

외부 접속을 가능케 하기 위해선 listener.ora 내용 변경, 방화벽 PORT열기, 오라클 서비스 재시작이 필요하다.
오라클이 설치된 경로 “..\product\11.2.0\dbhome_1\NETWORK\ADMIN” 경로에 listener.ora이란 txt파일이 존재
- DB에 접속하는 주소를 명시해 두는 파일

다른 유저가 접속할 수 있도록 listener.ora 내용 수정
- 메모장 관리자 권한으로 실행 후 내용 변경
- 기존 LISTENER의 코드블럭을 복사 후 HOST에 아이피 주소 또는 컴퓨터명을 명시

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 아이피주소)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\owner

방화벽 설정
- 인바운드, 아웃바운드 규칙을 추가하여 오라클 PORT번호를 열어준다.
오라클 재시작
- 서비스에서 OracleORCL, TNS 다시 시작

Transaction 처리

Transaction이란 DB 작업의 단위
Java는 auto commit이 되기 때문에 쿼리문 하나로 Transaction이 종료된다.
- DB는 항상 ACID이 유지되어야 한다.
  - insert + insert 작업이 한 트랜잭션으로 처리가 될 때 앞에 insert가 수행되고 뒤에 insert에서 에러가 나면 둘 다 수행되면 안됨.
Connection 객체를 사용하여 auto commit을 해제하고 commit과 rollback을 조건을 부여하여 실행하도록 만든다. * 조심! commit을 안하더라도 close()가 되면 commit이 되버린다. * 때문에 Connection 끊는걸 method 외부에서 수행해야 한다.
Transaction 대상 쿼리 : INSERT, UPDATE, DELETE
- Transaction 대상쿼리가 수행되면 반환형은 변경된 행수(int)였음
- 목표하는 행수가 실행되면 commit, 아니라면 rollback 수행되도록 코딩

// sql이 트랜잭션 대상 쿼리 문자열일 때
int cnt = stmt.executeUpdate(sql); 

// prepareStatement()에 들어간 파라미터가 트랜잭션 대상쿼리 문자열일 때
int cnt = pstmt.executeUpdate();

// 트랜잭션 처리 방법
// 1. auto commit 해제
con.setAutoCommit(false);

// 2. 쿼리를 수행하고 수행된 횟수를 저장
int cnt1 = stmt.executeUpdate(sql);

...

int cnt2 = pstmt.executeUpdate();

// 목표하는 행수가 실행되었다면 commit, 또는 rollback 수행
// 두 pstmt의 쿼리가 insert일때
if (cnt1 == 1 && cnt2 == 1) {
    con.commit();
} else {
    con.rollback();
}

Transaction 처리 예제

예제를 위한 테이블 생성
- test_transaction1테이블은 제목이 한글 20자까지 입력가능, test_transaction2는 10자까지 입력가능, 두 테이블에 INSERT가 들어가는 트랜잭션 처리 중 하나라도 문제가 발생하면 둘 다 들어가면 안된다.

CREATE TABLE test_transaction1(
    subject VARCHAR2(60),
    writer VARCHAR2(15),
    input_date DATE DEFAULT SYSDATE
);

CREATE TABLE test_transaction2(
    subject VARCHAR2(30),
    writer VARCHAR2(15),
    input_date DATE DEFAULT SYSDATE
);

올바른 트랜잭션 처리 코드
- commit과 rollback을 method 밖에서 수행해야만 한다.
- method안에서 close를 하지 않는다.

public class TestTransaction {

     // commit과 rollback을 DB작업 외부에서 처리할 수  있도록
     // Connection을 class field에 정의.     
     private Connection con = null;

     // transaction에 사용할 쿼리의 수만큼  쿼리실행객체를 선언
     private PreparedStatement pstmt1 = null;
     private PreparedStatement pstmt2 = null;

     public boolean insert(TransactionVO tv) throws  SQLException {
          boolean flag = false;
          
          String url =  "jdbc:oracle:thin:@localhost:1521:orcl";
          String id = "scott";
          String pass = "tiger";

          con = GetConnection.getInstance().getConn(url,  id, pass);

          // auto commit 해제
          con.setAutoCommit(false);

          String sql1 = "INSERT INTO  test_transaction1(subject, writer) values(?,?)";
          pstmt1 = con.prepareStatement(sql1);
          pstmt1.setString(1, tv.getSubject());
          pstmt1.setString(2, tv.getWriter());
          int cnt1 = pstmt1.executeUpdate();

          String sql2 = "INSERT INTO  test_transaction2(subject, writer) values(?,?)";
          pstmt2 = con.prepareStatement(sql2);
          pstmt2.setString(1, tv.getSubject());
          pstmt2.setString(2, tv.getWriter());
          int cnt2 = pstmt2.executeUpdate();

          // 트랜잭션에 해당하는 모든 쿼리의 목표 수행 수를 비교하여
          // commit/ rollback 여부를 결정한다.
          if (cnt1 == 1 && cnt2 == 1) {
              flag = true;
          }
          return flag;
     }
     
     public void add() {
          String inputData =
              JOptionPane.showInputDialog("제목과  작성자를 입력해주세요.\n예) 제목-작성자");
          
          String[] data = inputData.split("-");
          
          if (data.length != 2) {
              JOptionPane.showMessageDialog(null,  "입력형식을 확인해주세요.");
              return;
          }
          
          TransactionVO tv = new TransactionVO(data[0],  data[1]);
          
          try {              
              // DB업무를 사용하는 곳에서 수행 결과를 받아
              boolean flag = insert(tv);
              if (flag) {
                   con.commit();
                   System.out.println("커밋");
                   // insert는 성공하거나 예외(catch)
              } else {
                   // update나 delete가 transaction일 때  else문이 필요
                   con.rollback();
                   System.out.println("update나 delete의  롤백");
              };
          } catch (SQLException se) {
              try {
                   con.rollback();
                   System.out.println("insert 롤백");
              } catch (SQLException se2) {
                   se2.printStackTrace();
              }
              se.printStackTrace();
          } finally {
              try {
                   close();
              } catch (SQLException e) {
                   e.printStackTrace();
              }
          }
     }
     
     public void close() throws SQLException {
          if (pstmt1 != null) { pstmt1.close(); }
          if (pstmt2 != null) { pstmt2.close(); }
          if (con != null) { con.close(); }
     }
     
     public static void main(String[] args) {
          TestTransaction tt = new TestTransaction();
          tt.add();
     }
}

Java