한빛출판네트워크

저자: 『Java Programming with Oracle SQLJ』의 저자 제이슨 프라이스, 역 김대곤

"SQLJ를 배우자"의 시리즈인 이 기사에서는 SQLJ를 사용하여 DML(데이터 조작 언어) SQL문장을 사용하는 자바 프로그램 작성법을 살펴볼 것이다. DML문이란 데이터베이스 테이블에 있는 데이터를 조회하고, 수정하는데 사용하는 SQL문장이다. 여기서는 DML문 외에도 데이터베이스의 널(NULL)값 처리 방법과 데이터베이스 예외상황 처리법에 대해서도 살펴볼 것이다.

여기에서 사용된 SQL 스크립트, 소스 코드, 기타 파일들은 오라일리 웹사이트에서 다운로드 할 수 있다. 사용된 파일에 대한 자세한 정보와 SQLJ에 대한 소개는 본 시리즈의 첫번째 기사인 "SQLJ 프로그램 개발을 위한 환경 설정하기"를 참고하면 된다.

단일 행 조회

SELECT INTO SQL문은 데이터베이스 테이블에서 한 행을 조회하여 특정 컬럼의 값을 호스트 변수로 저장할 때 사용된다. 이러한 호스트 변수의 값은 자바 프로그램에 의해 참조 가능하다.

다음 예제는 customers 테이블에서 id가 2인 행의 first_name, last_name, dob, phone 컬럼의 값을 조회하는 코드이다. 조회된 값들은 호스트 변수에 저장된다.

// declare host variables
int id = 2;
String first_name = null;
String last_name = null;
java.sql.Date dob = null;
String phone = null;

// perform SELECT to get the customer details for
//  the customer #2 from the customers table
#sql {
  SELECT
    first_name, last_name, dob, phone
  INTO
    :first_name, :last_name, :dob, :phone
  FROM
    customers
  WHERE
    id = :id
};

위 예제에서는 다섯 개의 호스트 변수가 선언되었다. 여기서 선언된 다섯 개의 호스트 변수는 id, first_name, last_name, dob, phone이다. id는 int로 선언되어 id컬럼에 사용된 데이터베이스 NUMBER 타입과 대응되며, first_name, last_name, phone은 자바 String 타입으로 선언되었다. 자바 String 타입은 first_name, last_name, phone 컬럼에 사용된 VARCHAR2 타입에 대응된다. dob 변수는 java.sql.Date 타입으로 선언되었으며 java.sql.Date는 데이터베이스 DATE 타입과 대응된다.

또한 SELECT INTO 문장에서는 데이터베이스에서 조회되는 first_name, last_name, dob, phone 컬럼의 값을 저장하기 위해 네 개의 콜론으로 구별되는 호스트 표현을 사용하였다.

SELECT
  first_name, last_name, dob, phone
INTO
  :first_name, :last_name, :dob, :phone

INTO 절의 호스트 표현의 기본 설정은 OUT이며, 이것은 네 개의 호스트 변수의 값을 SQL문장에서 결정한다는 것을 의미한다.

SELECT INTO 문의 WHERE 절에서는 customers 테이블에서 조회하고자 하는 행을 지정하기 위해 id 호스트 변수에 저장된 값을 사용하였다.

WHERE
  id = :id

WHERE 절에 사용된 호스트 변수의 기본설정은 IN이며, 이것은 id변수의 값을 SQL문이 참조할 것이라는 말이다.

		Java Programming with Oracle SQLJ

SELECT INTO문이 수행되고 난 후, first_name, last_name, dob, phone 호스트 변수에는 customers 테이블에서 조회한 값들이 저장되어 있다.

이전 SQL 문에서는 각 호스트 변수의 OUT/IN 표현을 쓰지 않고 기본설정에 근거하여 사용하였다. 그러나 다음과 같이 명시적으로 OUT/IN을 쓸 수도 있다.

#sql {
  SELECT
    first_name, last_name, dob, phone
  INTO
    :OUT first_name, :OUT last_name, :OUT dob, :OUT phone
  FROM
    customers
  WHERE
    id = :IN id
};

first_name, last_name, dob, phone 호스트 변수 앞에 쓰인 OUT은 SELECT INTO문장이 변수 값을 저장할 것을 의미한다. 그리고 id 호스트 변수 앞에 쓰인 IN은 SELECT INTO문장이 id변수에 저장된 값을 읽어 사용할 것이라는 것을 나타낸다.

데이터베이스에서 제공되는 자체 함수도 SQL문장에 사용할 수 있다. 다음 예제는 데이터베이스 COUNT() 함수를 사용하여 products 테이블의 행 수를 구하고, 그 값을 number_of_products 호스트 변수에 저장하고 있다.

int number_of_products;

#sql {
  SELECT
    COUNT(*)
  INTO
    :number_of_products
  FROM
    products
};

호스트 변수와 표현은 UPDATE, DELETE, INSERT SQL문에서도 사용할 수 있다. 앞으로는 이러한 SQL문에 대해 차근차근 살펴볼 것이다.

행의 수정

UPDATE SQL문은 테이블의 행들을 수정하고자 할 때 사용한다. SQLJ 프로그램에서 UPDATE문을 사용할 때, SET절과 WHERE절에 호스트 표현이 사용될 수 있다. 다음은 그 예를 든 것이다.

int new_quantity = 10;
int cust_id = 2;
int prod_id = 3;

#sql {
  UPDATE
    purchases
  SET
    quantity = :new_quantity
  WHERE
    purchased_by = :cust_id
  AND
    product_id = :prod_id
};

위 예제에서는 purchases 테이블에서 product_id가 2인 행들의 quantity 컬럼의 값들을 업데이트한 것이다.

행의 삭제

DELETE SQL문은 테이블에서 행을 삭제할 때 사용한다. SQLJ 프로그램에서 DELETE문을 사용할 때 호스트 표현은 WHERE절에 나타날 수 있다. 예를 들면 다음과 같다.

int cust_id = 2;

#sql {
  DELETE FROM
     customers
  WHERE
    id = :cust_id
};

이 예제는 customers 테이블에서 id 컬럼 값이 2인 행들을 삭제하였다.

행의 추가

INSERT SQL문은 테이블에 행을 추가할 때 사용한다. SQLJ 프로그램에서 INSERT문이 사용될 경우 호스트 표현은 VALUES절에 나타날 수 있다. 예를 들면 다음과 같다.

int id = 13;
int type_id = 1;
String name = "Life Story";
String description = "The Life and Times of Jason Price";
double price = 19.95;

#sql {
  INSERT INTO
    products (id, type_id, name, description, price)
  VALUES
    (:id, :type_id, :name, :description, :price)
};

SELECT문은 INSERT문과 결합되어 사용되기도 한다. SELECT문이 조회한 행들은 INSERT문이 테이블에 저장할 수 있다. SQLJ 프로그램에서 INSERT문과 SELECT문이 함께 사용될 경우, 호스트 표현은 SELECT문의 컬럼 항목이나 WHERE절에 사용될 수 있다.

int id = 14;
double new_price = 10.95;
int prod_id = 4;

#sql {
  INSERT INTO products
    SELECT
      :id, type_id, name, description, :new_price
    FROM
      products
    WHERE
      id = :prod_id
};

데이터베이스 널(NULL)값 처리

데이터베이스 테이블의 컬럼은 널(NULL)을 허용하거나 널을 허용하지 않도록(NOT NULL) 설정된다. NULL은 테이블에 행이 입력될 때 컬럼에 아무런 값이 저장되지 않아도 된다는 것을 의미한다. 반면에 NOT NULL은 반드시 값이 저장되어야 한다는 것을 의미한다. 오라클 데이터베이스는 사용자가 널에 대한 설정을 하지 않으면 널를 허용한다.

안타깝게도 자바의 숫자 타입, 논리 타입, 비트 타입(예를 들어 int, float, Boolean, byte 등)은 데이터베이스의 널 값을 조회하는데 사용할 수 없다. SQLJ 프로그램에서 조회하고자 하는 컬럼에 널이 있으면 어떻게 할 것인가? 이에 대한 해답은 "자바 래퍼(Wrapper) 클래스를 사용해야 한다"는 것이다. 자바 래퍼(Wrapper) 클래스는 자바 기본 변수를 대신할 수 있는 자바 클래스이며, 이것은 데이터베이스 널 값을 조회할 수 있다. 이러한 래퍼(Wrapper) 클래스는 java.lang 패키지에 포함되어 있으며, 정의된 래퍼(Wrapper) 클래스를 나열하면 다음과 같다.

java.lang.Boolean
java.lang.Byte
java.lang.Short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double

래퍼 클래스는 논리 타입 뿐만 아니라 다양한 숫자 타입의 데이터베이스 널 값을 표현하는데 사용된다. 다음 예제는 java.lang.Double 래퍼 클래스를 사용하여 호스트 변수를 선언하고 있다.

java.lang.Double price_var;

일단 래퍼 클래스 타입의 변수를 선언하면, 자바 기본 변수와 동일하게 사용할 수 있다. 다음 예제는 UPDATE문을 사용하여 products 테이블에서 id가 1인 행의 price 컬럼에 널을 저장하고, 다시 SELECT문을 사용하여 저장된 price 컬럼의 값을 조회하여 price_var에 저장하는 것을 보여준다.

// set the price to null
#sql {
  UPDATE
    products
  SET
    price = NULL
  WHERE
    id = 1
};

// retrieve the null price into price_var
#sql {
  SELECT
    price
  INTO
    :price_var
  FROM
    products
  WHERE
    id = 1
};

자바 기본 변수인 double에 데이터베이스 널 값을 조회하여 저장하려고 하면, SQLJ문장이 실행될 때 SQL Exception이 발생할 것이다. SQL Exception이 발생하는 이유는 double 타입의 변수가 데이터베이스 널 값을 표현할 수 없기 때문이다.

래퍼 클래스로 선언된 변수는 값이 널이 아닐 경우에 그 값을 자바 기본 변수 값으로 변환할 수 있는 메소드를 제공한다. 위 예제에서 price 컬럼에 저장된 값이 NULL이 아니라 실제 값이었다면, SELECT문은 래퍼 클래스로 선언된 변수 price_var에 그 값을 저장했을 것이다. 또한 래퍼 클래스의 doubleValue() 메소드를 통해 price_var에 저장된 값을 자바 double 변수에 저장할 수 있다. 아래 코드는 price_var의 값의 널 여부를 확인하고, 값이 널이 아니면 doubleValue() 메소드를 사용하여 그 값을 자바 기본 변수형인 double로 선언된 price_var2에 저장하고 있다.

double price_var2 = 0.0;
if (price_var != null) {
  price_var2 = price_var.doubleValue();
}

다른 래퍼 클래스도 유사한 메소드를 제공한다. Java.lang.Float는 floatValue() 메소드를, java.lang.Byte는 byteValue() 메소드를 제공한다.

예외 처리

SQLJ 실행(#sql 토근) 문장은 try/catch 안에 들어 있든지, 발생할 수 있는 예외를 다른 클래스로 위임할 수 있도록 허용하는 선언된 메소드에 있어야 한다. try절 안에서 SQLJ 실행 문장에 의해 예외가 발생하면, 이 예외는 catch절로 넘겨진다. 자바 소프트웨어는 예외를 처리할 적절한 핸들러를 찾으려고 시도한다. 만약 catch절에서 적절한 핸들러를 찾지 못한 경우, 예외는 핸들러를 찾을 때까지 계속해서 호출한 클래스를 따라 올라가게 된다.

SQLJ 실행 문장은 java.sql.SQLException 클래스를 유발할 수 있기 때문에 try/catch절은 다음과 같은 형태가 될 것이다.

try {
  ...
} catch (SQLException e) {
  ...
}

try 절에는 java.sql.SQLException이 발생할 수 있는 SQLJ문장이 포함되어 있을 것이며, catch 절에는 java.sql.SQLException이 발생했을 때 수행되어야 하는 문장이 포함되어 있을 것이다. 아래 예제는 try절 안에서 DELETE문이 예외를 발생시킬 경우 단순히 이 메시지를 보여주는 catch절이다.

try {
  #sql {
    DELETE FROM
      customers
  };
} catch (SQLException exception) {
  System.out.println("SQLException " + exception);
}

더욱 세부적으로 데이터베이스의 널 값을 처리하는데 사용할 수 있는 java.sql.SQLException의 하위 클래스가 있다.

java.sql.SQLNullException

이 예외는 자바 기본 변수(primitive)에 데이터베이스 널 값을 저장하려고 할 때 발생한다. java.sql.SQLException의 하위 클래스를 사용하고자 한다면 SQLException 핸들러 이전에 하위 클래스의 핸들러를 지정해야 한다. 만약 SQLException 핸들러가 먼저 지정되어 있으면, SQLException 핸들러가 SQLNullException를 처리하게 될 것이다. 이것은 SQLException 클래스가 상위클래스이고, 하위 클래스는 상위 클래스의 객체로 인식되어 있기 때문이다.

아래 try/catch절은 SQLNullException과 SQLException를 위한 핸들러를 모두 포함하고 있다. 주목해야 할 점은 SQLNullException이 SQLException 이전에 있다는 점이다.

try {
  ...
} catch (SQLNullException null_exception) {
  System.out.println("SQLNullException " + null_exception);
} catch (SQLException exception) {
  System.out.println("SQLException " + exception);
}

하위클래스를 앞에 둠으로써 적용되어야 할 핸들러를 정확하게 찾을 수 있다. 처리할 수 있는 핸들러가 없는 경우에만 SQLException 핸들러가 담당하게 될 것이다.

필자는 "SQL를 배우자"의 시리즈 기사에서 독자들이 재미있고 유익한 시간을 보냈기를 바란다. 다음 기사는 SQLJ 프로그램에서 SQL DDL(데이터 정의 언어)를 사용하여 데이터베이스 테이블을 생성하는 법과 데이터베이스 트랜잭션을 관리하는 방법에 대해 살펴볼 것이다.

Jason Price는 현재 오라클 제품 관리자로 재직하고 있으며, 데이터베이스 서버, 애플리케이션 서버, 몇몇 CRM 애플리케이션을 포함한 많은 오라클 제품 개발에 참여하고 있다.