JDBC 개요

자바는 데이터베이스(DB)와 연결해서 데이터 입출력 작업을 할 수 있도록 JDBC(Java Database Connectivity) 라이브러리(java.sql 패키지)를 제공한다. JDBC는 데이터베이스 관리 시스템(DBMS)의 종류와 상관 없이 동일하게 사용할 수 있는 클래스와 인터페이스로 구성되어 있다.

JDBC 인터페이스를 통해 실제로 DB와 작업하는 것은 JDBC Driver이다. JDBC Driver는 JDBC 인터페이스를 구현한 것으로, DBMS마다 별도로 다운로드받아 사용해야 한다.
JDBC에 포함되어 있는 클래스와 인터페이스들의 연관 관계는 다음과 같다.

DriverManager

DriverManager 클래스는 JDBC Driver를 관리하며 DB와 연결해서 Connection 구현 객체를 생성한다.

Connection

Connection 인터페이스는 Statement, PreparedStatement, CallableStatement 구현 객체를 생성하며, 트랜잭션(Transaction) 처리 및 DB 연결을 끊을 때 사용한다.

Statement

Statement 인터페이스는 SQL의 DDL(Data Definition Language)과 DML(Data Manipulation Language)을 실행할 때 사용한다. 주로 변경되지 않는 정적 SQL 문을 실행할 때 사용한다.

PreparedStatement

PreparedStatement는 Statement와 동일하게 SQL의 DDL, DML 문을 실행할 때 사용한다. 차 이점은 매개변수화된 SQL 문을 사용할 수 있기 때문에 편리성과 보안성이 좋다. 그래서 Statement 보다는 PreparedStatement를 주로 사용한다.

CallableStatement

CallableStatement는 DB에 저장되어 있는 프로시저(procedure)와 함수(function)를 호출할 때 사용한다.

ResultSet

ResultSet은 DB에서 가져온 데이터를 읽을 때 사용한다.

DB 연결

클라이언트 프로그램에서 DB와 연결하려면 해당 DBMS의 JDBC Driver가 필요하다.
또한 연결에 필요한 다음 네 가지 정보가 있어야 한다.

1. DBMS가 설치된 컴퓨터의 IP 주소
2. DBMS가 허용하는 포트(Port) 번호
3. 사용자(DB 계정) 및 비밀번호
4. 사용하고자 하는 DB 이름

IP 주소는 컴퓨터를 찾아가기 위해, Port 번호는 DBMS로 연결하기 위해 필요하다.
DBMS는 여러 개의 DB를 관리하므로 실제로 사용할 DB 이름이 필요하며, 어떤 사용자인지 인증받기 위한 계정 및 비밀번호가 필요하다.

데이터 저장

JDBC를 이용해서 INSERT 문을 실행하는 방법을 알아보자.

users 테이블에 새로운 사용자 정보를 저장하는 INSERT 문은 다음과 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail)
VALUES ('winter', '한겨울', '12345', 25, 'winter@mycompany.com')

값을 ?(물음표)로 대체한 매개변수화된 INSERT 문으로 변경하면 다음과 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail)
VALUES (?, ?, ?, ?, ?)

그리고 INSERT 문을 String 타입 변수 sql에 문자열로 대입한다.

String sql = new StringBuilder()
    .append("INSERT INTO users (userid, username, userpassword, userage, useremail)")
    .append("VALUES (?, ?, ?, ?, ?)")
    .toString();
// 또는

String sql = "" +
    "INSERT INTO users (userid, username, userpassword, userage, useremail) " + 
    "VALUES (?, ?, ?, ?, ?)";

매개변수화된 SQL 문을 실행하려면 PreparedStatement가 필요하다.
다음과 같이 Connection의 prepareStatement() 메소드로부터 PreparedStatement를 얻는다.

PreparedStatement pstmt = conn.prepareStatement(sql);

그리고 ?에 들어갈 값을 지정해주는데, ?는 순서에 따라 1번부터 번호가 부여된다.
값의 타입에 따라 Setter 메소드를 선택한 후 첫 번째에는 ? 순번, 두 번째에는 값을 지정해 준다.

pstmt.setString(1, "winter");
pstmt.setString(2, "한겨울");
pstmt.setString(3, "12345");
pstmt.setInt(4, 25);
pstmt.setString(5, "winter@mycompany.com");

값을 지정한 후 executeUpdate() 메소드를 호출하면 SQL 문이 실행되면서 users 테이블에 1개 의 행이 저장된다.
executeUpdate() 메소드가 리턴하는 값은 저장된 행 수인데, 정상적으로 실행 되었을 경우 1을 리턴한다.

int rows = pstmt.executeUpdate();

PreparedStatement를 더 이상 사용하지 않을 경우에는 close() 메소드를 호출해서 PreparedStatement가 사용했던 메모리를 해제시킨다.

pstmt.close();

데이터 수정

JDBC를 이용해서 UPDATE 문을 실행하는 방법을 알아보자.

boards 테이블에 저장된 게시물 중에서 bno가 3인 게시물의 btitle, bcontent, bfilename, bfiledata를 변경하는 SQL 문은 다음과 같다.

UPDATE boards SET
    btitle = '눈사람',
    bcontent='눈으로 만든 사람',
    bfilename = 'snowman.jpg',
    bfiledata = binaryData
WHERE bno=3

값을 ?로 대체한 매개변수화된 UPDATE 문으로 변경한다.

UPDATE boards SET
    btitle = ?,
    bcontent = ?,
    bfilename = ?,
    bfiledata = ?
WHERE bno=?

String 타입 변수 sql에 매개변수화된 UPDATE 문을 저장한다.

String sql = new StringBuilder()
    .append("UPDATE boards SET ")
    .append("btitle=?, ")
    .append("bcontent=?, ")
    .append("bfilename=?, ")
    .append("bfiledata=? ")
    .append("WHERE bno=?")
    .toString();

매개변수화된 UPDATE 문을 실행하기 위해 다음과 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻고, ?에 해당하는 값을 지정한다.

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "눈사람");
pstmt.setString(2, "눈으로 만든 사람");
pstmt.setString(3, "snowman.jpg");
pstmt.setBlob(4, new FileInputStream("src/ch20/mysql/sec07/snowman.jpg"));
pstmt.setInt(5, 3);

값을 모두 지정하였다면 UPDATE 문을 실행하기 위해 executeUpdate() 메소드를 호출한다.
성공적으로 실행되면 수정된 행의 수가 리턴된다.
만약 0이 리턴되면 조건에 맞는 행이 없어 수정된 내용이 없음을 의미한다.

int rows = pstmt.executeUpdate();

데이터 삭제

JDBC를 이용해서 DELETE 문을 실행하는 방법을 알아보자.

boards 테이블에서 bwriter가 winter인 모든 게시물을 삭제하는 DELETE 문은 다음과 같다.

DELETE FROM boards WHERE bwriter='winter'

조건절의 값을 ?로 대체한 매개변수화된 DELETE 문으로 변경한다.

DELETE FROM boards WHERE bwriter=?

매개변수화된 DELETE 문을 String 타입 변수 sql에 대입한다.

String sql = "DELETE FROM boards WHERE bwriter=?";

매개변수화된 DELETE 문을 실행하기 위해 prepareStatement() 메소드로부터 PreparedStatement를 얻고 ?에 값을 지정한 후, executeUpdate로 SQL 문을 실행한다.
리턴 값은 삭제된 행 수이다.

String sql = "DELETE FROM boards WHERE bwriter=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "winter");
int rows = pstmt.executeUpdate();

데이터 읽기

PreparedStatement를 생성할 때 SQL 문이 INSERT, UPDATE, DELETE일 경우에는 executeUpdate() 메소드를 호출하지만, 데이터를 가져오는 SELECT 문일 경우에는 executeQuery() 메소드를 호출해야 한다. executeQuery() 메소드는 가져온 데이터를 ResultSet에 저장하고 리턴한다.

ResultSet rs = pstmt.executeQuery();

ResultSet 구조

ResultSet은 SELECT 문에 기술된 컬럼으로 구성된 행(row)의 집합이다.
예를 들어 다음 SELECT 문은 userid, username, userage 컬럼으로 구성된 ResultSet을 리턴한다.

SELECT userid, username, userage FROM users

위의 SELECT 문이 가져온 데이터 행이 4개라면 ResultSet의 내부 구조는 다음과 같다.

ResultSet의 특징은 커서(cursor)가 있는 행의 데이터만 읽을 수 있다는 것이다.
여기서 커서는 행을 가리키는 포인터를 말한다.

ResultSet은 실제 가져온 데이터 행의 앞과 뒤에 beforeFirst 행과 afterLast 행이 붙는데, 최초 커서는 beforeFirst를 가리킨다.
따라서 첫 번째 데이터 행인 first 행을 읽으려면 커서를 이동시켜야 한다. 이때 next() 메소드를 사용한다.

boolean result = rs.next();

next() 메소드는 커서를 다음 행으로 이동시키는데, 이동한 행에 데이터가 있으면 true를, 없으면 false를 리턴한다.
앞의 그림을 보면 last 행까지는 true를 리턴하고 afterLast 행으로 이동하면 false를 리턴하는 것을 볼 수 있다.

만약 SELECT 문으로 가져온 데이터 행이 없다면 beforeFirst 행과 afterLast 행이 붙어있기 때문에 첫 번째 next() 결과는 false가 된다.

다음은 SELECT 문으로 가져온 행의 수에 따라서 커서를 이동시키는 코드이다.

1개의 데이터 행만 가져올 경우에는 if 조건식에서 next() 메소드를 1번 호출한다.
true일 경우(첫 번째 데이터 행이 있을 경우)와 false일 경우(afterLast 행으로 이동했을 경우)에 따라서 적절한 처리를 해야 한다.

주로 SELECT 문이 기본키(primary key)를 조건으로 데이터를 가져오는 경우에 해당한다.

n개의 데이터 행을 가져올 경우에는 while 문을 이용해서 next() 메소드를 반복 호출해 true가 리턴될 동안(last 행까지 이동할 때까지) 데이터 행을 처리하고, false가 리턴되면(afterLast 행으로 이동할 때) 반복을 종료시킨다.

SELECT 문에 따라 ResultSet에는 많은 데이터 행이 저장될 수 있기 때문에 ResultSet을 더 이상 사용하지 않는다면 close() 메소드를 호출해서 ResultSet이 사용한 메모리를 해제하는 것이 좋다.

rs.close();

데이터 행 읽기

커서가 있는 데이터 행에서 각 컬럼의 값은 Getter 메소드로 읽을 수 있다.

컬럼의 데이터 타입에 따라서 getXxx() 메소드가 사용되며, 매개값으로 컬럼의 이름 또는 컬럼 순번을 줄 수 있다.
ResultSet에서 컬럼 순번은 1부터 시작하기 때문에 userid = 1, username = 2, userage = 3이 된다.

만약 SELECT 문에 연산식이나 함수 호출이 포함되어 있다면 컬럼 이름 대신에 컬럼 순번으로 읽어야 한다.
예를 들어 다음과 같은 SELECT 문에서 userage - 1 연산식이 사용되면 컬럼 순번으로만 읽을 수 있다.
userage - 1은 컬럼명이 아니기 때문이다.

(userage - 1) as userage와 같이 별명(alias)이 있다면 별명이 컬럼 이름이 된다.

트랜잭션 처리

트랜잭션(transaction)은 기능 처리의 최소 단위를 말한다. 하나의 기능은 여러 가지 소작업들로 구성될 수 있다.
최소 단위란 것은 이 소작업들을 분리할 수 없으며, 전체를 하나로 본다는 개념이다
트랜잭션은 소작업들이 모두 성공하거나 모두 실패해야 한다.

예를 들어 계좌 이체는 출금 작업과 입금 작업으로 구성된 트랜잭션이다. 출금과 입금 작업 중 하나만 성공할 수 없으며, 모두 성공하거나 모두 실패되어야 한다.

계좌 이체는 DB 입장에서 보면 두 개의 계좌 금액을 수정하는 작업이다.
출금 계좌에서 금액을 감소시키고, 입금 계좌에서 금액을 증가시킨다.

따라서 다음과 같이 두 개의 UPDATE 문이 필요하다. 두 UPDATE 문은 모두 성공하거나 모두 실패해야 하며, 하나만 성공할 수 없다.

DB는 트랜잭션을 처리하기 위해 커밋(commit)과 롤백(rollback)을 제공한다.

커밋은 내부 작업을 모두 성공 처리하고, 롤백은 실행 전으로 돌아간다는 의미에서 모두 실패 처리한다.

JDBC에서는 INSERT, UPDATE, DELETE 문을 실행할 때마다 자동 커밋이 일어난다.
이 기능은 계좌 이체와 같이 두 가지 UPDATE 문을 실행할 때 문제가 된다. 출금 작업이 성공되면 바로 커밋이 되기 때문에 입금 작업의 성공 여부와 상관없이 출금 작업만 별도 처리된다.

따라서 JDBC에서 트랜잭션을 코드로 제어하려면 자동 커밋 기능을 꺼야 한다.
자동 커밋 설정 여부는 Connection의 setAutoCommit() 메소드로 설정할 수 있다.

다음 코드는 자동 커밋 기능을 끈다.

conn.setAutoCommit(false);

자동 커밋 기능이 꺼지면, 다음과 같은 코드로 커밋과 롤백을 제어할 수 있다.

conn.commit(); // 커밋하기
conn.rollback(); // 롤백하기

트랜잭션을 위한 일반적인 코드 작성 패턴은 다음과 같다.

Connection conn = null;

try {
  // 트랜잭션 시작
    // 자동 커밋 기능 끄기
    conn.setAutoCommit(false);

    // 소작업 처리
    ...

    // 소작업 처리
    ...

    // 커밋 -> 모두 성공 처리 conn.commit();
  // 트랜잭션 종료
} catch (Exception e) {
    try {
    //롤백 -> 모두 실패 처리
    conn.rollback();
    } catch (SQLException e1) {}
} finally {
    if (conn != null) {
        try {
        // 원래대로 자동 커밋 기능 켜기
        conn.setAutoCommit(true);

        // 연결 끊기
        conn.close();
        } catch (SQLException e) {}
    }
}