[JDBC] SQL문을 이클립스에서 적용하기 (DML, DDL, DCL, TCL)

먼저 오라클 SQL문 이클립스에서 적용하기 위해서는 세 가지 인터페이스인 Connection 과 PrepareStatement , ResultSet 에 대해 알아야 한다.

 

 

Connection 이란?

 

SQL 문을 이클립스에서 실행하고 결과 값을 가져올 수 있도록 하는 인터페이스이다. 즉, 자바와 오라클 DB를 위해 인터페이스 객체를 다음과 같이 먼저 생성(초기화)해야 한다.

 

Connection c = null;

 

 

PrepareStatement 란?

 

statement 와 달리 preparestatment 인터페이스를 사용하는 이유는 statement 를 상속받고 있으며, 작은따옴표(' ') 처리를 sql 문 입력시 하지 않아고 되기 때문이다. 그리고 sql 문을 미리 컴파일 할 수 있다는 장점이 있다. (→ 자세한 설명)

preparestatement 도 동일하게 먼저 생성(초기화) 해준다.

 

Preparestatement p = null;

 

 

ResultSet 이란?

 

executeQuery() 를 실행했을 때 그 결과값을 ResultSet 에 넣어준다. DDL 문인 select 를 실행했을 때 값을 알고자 할 때 넣어주며,  rs.next() 메서드와 함께 사용되어 결과물 값을 확인하거나 입력받아오기 위해 사용한다. 세세한 내용을 이하 DDL 문 설명에서 다시 다루도록 하겠다. ResultSet 도 사용될 경우, 동일하게 먼저 생성(초기화) 해준다.

 

ResultSet r = null;

 

 

위 세가지 인터페이스는 사용후 모두 자원의 반납(객체.close();) 를 해주어야 한다. 하지만, 여러번 sql 문을 사용하는 경우에는 Connection 객체는 한 번만 반납하면 되므로 singletonpattern 을 활용한다. (→ 자세한 설명)

 

---

 

그리고 데이터베이스 명령어인 DML, DDL, DCL, TCL 에 대해 알아보자.

 

 

DML 문은

 

Data Manipulation Language 의 약어로 INSERT, UPDATE, DELETE, SELECT 가 있다.  데이터 조작어로서 데이터에서 필요한 정보를 입력, 선택, 삭제, 업데이트 과정을 통해 데이터를 변경시킬 수 있다. commit 전까지 rollback 이 가능하다.

 

 

DDL 문은

 

Data Definition Language 의 약어로 CREATE, ALTER, DROP, RENAME, COMMENT, TRUNCATE 와 같이 입력값의 별도 commit 을 요하지 않는 명령어이다. 즉, auto commit 으로 완료된다.

 

 

DCL 문은

 

Data Control Language 의 약어로 GRANT, REVOKE 와 같이 입력값의 별도 commit 을 요하지 않는 명령어이다. 즉, auto commit 으로 완료된다. sys 나 system 에서 가지는 데이터 조작어 기능을 다른 user 가 사용할 수 있도록 권한을 부여 또는 철회하는 명령어를 말한다.

 

String sql = "";
 
 
 
// 1. DDL 문의 예
 
sql = "create table tbl_test (no number(20), name varchar2(30), phonenumber varchar2(40)) ";
 
sql = "drop table tbl_test ";
 
 
 
// 2. DML 문의 예
 
 
sql = " insert into tbl_test(no, name, msg) "
    + " values(jdbc_seq_examtest.nextval, '이름', '안녕하세요?') "; 
 
sql = " select name, msg "                
    + " from tbl_test "
    + " where no = ? ";
 
 
 
// 3. DCL 문의 예
 
sql = " grant select, insert on tbl_test to [USER] ";

 

TCL 문은

 

Transcation Control Language 의 약어로 COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION  명령어이다. 데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용된다. SAVEPOINT 를 사용하는 경우 일정 시점으로 ROLLBACK 이 가능하도록 해준다. 

 

---

 

★ 이제 구체적으로 SQL문을 이클립스에서 적용하는 방법을 알아보자. (→ 콘솔프로그램 구현시 활용)

 

 

1. 사용할 인터페이스 객체의 초기화

 

입력시 자동으로 import java.sql.*; 이 적용된다. 안되는 경우 import 해줄 것.

 

Connection co = null;
PreparedStatement ps = null;
ResultSet rs = null;

 

 

2. 오라클 드라이버 로딩

 

OracleDriver 를 메모리에 로딩시켜주고 객체를 생성한다. 이 객체를 다시 DriverManager 에 등록시키는 일련의 과정을 말한다.

 

Class.forName 은 JVM 구동시 forname 이하 ( ) 부분의 class 인스턴스의 정보 즉, 여기서는 oracle.jdbc.driver.OracleDriver 를 등록시켜준다. (→ 자세한 설명)

 

+ JDBC 4.0 이후로는 메소드를 호출하지 않아도 자동으로 드라이버를 초기화한다고 한다. 

 

DriverManager 는 JDBC 드라이버들의 집합을 관리하고 데이터베이스를 연결하는 클래스로 위에서 사전에 OracleDriver 를 등록해주어야 사용가능하다. 파라미터 형식은 jdbc:subprotocol:subname 로 연결하고자 하는 IP 주소가 등록되는데 subprotocol 은 oracle 이고 이하 IP 주소는 jdbc:oracle:thin:@127.0.0.1:1521:xe 로 입력한다.

여기서 @ 이하 값은 IP 주소로 변수로 입력받아도 되고, 특정 IP 나 127.0.0.1 과 같이 자신의 IP 가 연결되도록 할 수 있다.

 

두 번째 파라미터 값은 연결하고자하는 오라클 user 를 의미하고,  그 다음 파라미터에는 접속 비밀번호를 입력한다. 실행시 입력할 필요없도록 등록해주는 것으로 필수사항은 아니다.

 

추가로, auto commit 사항을 변경할 수 있는데 Connection 객체.setAutoCommit(false); 으로 수동 commit 으로 변경가능하다. JDBC 에서 기초 설정값은 auto commit 이다.

 

Class.forName("oracle.jdbc.driver.OracleDriver");
    
co = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "HR", "passwd");
co.setAutoCommit(false); // 수동 commit 으로 전환 

 

 

3. SQL 문의 작성

 

이클립스에서 작성한 SQL 이 실제 DB 에 반영될 수 있도록 오라클 명령어로 작성한다. 이 때, 유의할 점은 줄 바꿈의 띄어쓰기를 하지 않아 DB 에서 해석하지 못하는 경우를 유의해야 한다. 예를 들어 " select name, msg|from tbl_test "; 와 같이 | 부분의 띄어쓰기를 통해 의미단위를 구분할 필요가 있다.

(위의 DDL, DML, DCL, TCL 부분 참고)

 

 

4. SQL 문을 DB 로 전달하기

 

Connection 과 PreparedStatement 을 이용하여 SQL 문을 DB 로 전달한다.

 

만약 DDL 문인 경우에는 executeQuery(); 메서드를, 그 외의 경우에는 executeUpdate(); 메서드를 활용한다. 

 

pstmt.executeQuery();  결과값은 ResultSet 형식으로 나오는데, 앞서 초기화해 두었던 변수 rs 에 받는다. 이 SQL 이 수행되면 rs.next 가 null 값이 아니다.

pstmt.executeUpdate(); 결과값은 정수 int 형식으로 나오는데, 그 값이 수행되면 1 의 값을 갖는다.

 

pstmt = conn.prepareStatement(sql); 
 
 
// DDL 문인 경우
rs = pstmt.executeQuery();            
            
// 그 외의 경우
n = pstmt.executeUpdate();            

 

 

5. SQL Exception 잡아주기

 

SQL 을 입력하면, 그 구문의 오류가 발생하는지를 이클립스에서 잡아줄 수 있는데 try/catch 문을 이용한다. 이클립스에서는 노란색 경고표시로 나타나므로 적용시켜주면 된다.

실제 콘솔프로그램 구현시, 어디서 문제가 발생했는지 파악하기 쉬으므로 catch 문에는 e.printStackTrace(); 를 추가해준다.

 

 

6. 자원반납

 

try/catch 문 이하 반드시 자원반납이 이뤄질 수 있도록 finally 를 작성해 각각의 자원을 close 해준다.

 

} catch(ClassNotFoundException e) {
        System.out.println(">> ojdbc6.jar 파일이 없습니다. <<");
} catch (SQLException e) {
            System.out.println(">> 작성하신 SQL 문장에 오류가 있습니다. << ");
        e.printStackTrace();
} finally {
    try {
            if(rs != null)
                 rs.close();
                  
             if(pstmt != null) 
                   pstmt.close(); 
                  
              if(conn != null) 
                  conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}