서울대학교 이상구 교수님의 SNUON 강의 데이타베이스: 빅데이터 시대의 필수 정보관리 개론와 Database System Concepts 7th Edition의 내용을 바탕으로 정리한 내용입니다.

강의에서는 다루지 않는 내용으로, Database System Concepts의 Chapter 5를 번역 및 정리해서 포스팅합니다.

이전 장들에서 SQL의 기본 구조에 대해 다뤘으니, 이번 장에서는 어떻게 범용 프로그래밍 언어에서 SQL에 접근할 수 있는지에 대해 다뤄본다.

1. Accessing SQL from Programming Language

SQL에서 쿼리를 짜는 건 범용 프로그래밍 언어에서 같은 쿼리를 코딩하는 것보다 보통 더 쉽다. 하지만 DB 프로그래머는 적어도 다음의 두 이유로 범용 프로그래밍 언어에도 접근할 수 있어야 한다.

1. 모든 쿼리가 SQL로 표현될 수 있는 것은 아니다.
   • C, Java, Python 등으로 표현될 수는 있지만, SQL로는 그렇지 못하는 경우가 있을 수 있다.
2. 비-선언적인 행동들은 SQL로 처리될 수 없다.
   • 사용자와 상호작용하거나, 쿼리 결과를 사용자 GUI에 나타내는 등의 일은 SQL로 할 수 없다.
   • 애플리케이션은 여러 구성 요소를 가지며, 쿼리를 날리거나 데이터를 업데이트 하는 일은 그 중 한 부분에 불과하다. 다른 부분들은 범용 프로그래밍 언어들로 쓰인다.

범용 프로그래밍 언어에서 SQL에 접근하는 방법에는 두 방법이 있다.

동적 SQL(Dynamic SQL)

범용 프로그램은 함수, 또는 메서드들을 통해 DB 서버와 통신한다. 동적 SQL은 프로그램이 런타임에 SQL 쿼리를 문자열로 만들고, 쿼리를 보내고, 그 결과를 프로그램 변수에 담을 수 있게 한다. 이 장에서는 SQL 데이터베이스에 연결하기 위한 표준들 중 하나를 알아보고 쿼리와 업데이트를 수행해본다. 교재에서는 자바의 JDBC, C에서 개발되어 C++, C#, Ruby, Go, PHP, Visual Basic 등에서도 쓰이는 ODBC를 모두 다루고 있지만, 이 글에서는 JDBC만 살펴 본다.

임베디드 SQL(Embedded SQL)

동적 SQL과 마찬가지로 임베디드 SQL은 프로그램이 데이터베이스 서버와 상호작용할 수 있는 수단을 제공한다. 하지만 임베디드 SQL에서 SQL문은 전처리기를 이용해 컴파일 타임에 결정된다. 임베디드 SQL로 쓰인 요청을 함수로 변환하는 식이다. 이 함수들은 런타임에 동적 SQL 기능을 제공하는 API를 통해 DB에 연결된다.

SQL과 범용 언어를 혼합하는 데 있어서의 가장 큰 문제는, 이 두 언어들이 데이터를 조작하는 방식에 차이가 있다는 것이다. SQL에서 데이터의 가장 기본적인 타입은 릴레이션이다. SQL문은 릴레이션들을 연산하고, 그 결과로도 릴레이션을 내놓는다. 프로그래밍 언어에서는 보통 변수를 다루며, 그 변수들은 릴레이션 튜플의 애트리뷰트 값에 대응한다. 이 두 방식을 하나의 애플리케이션으로 통합하는 일은 쿼리의 결과를 프로그램이 다룰 수 있는 방식으로 반환하는 메커니즘을 제공해야 한다.

(1) JDBC(Java Database Connectivity)

JDBC는 자바 프로그램들이 DB 서버와 연결할 때 쓸 수 있는 API를 정의한다. JDBC를 사용하려면 java.sql.*를 임포트해야 한다.

public static void JDBCexample(String userid, String passwd)
{
	try (
		Connection conn = DriverManager.getConnection(
		"jdbc:oracle:thin:@db.yale.edu:1521:univdb", userid, passwd);
		Statement stmt = conn.createStatement();
	){
		try {
			stmt.executeUpdate(
			"insert into instructor values(’77987’,’Kim’,’Physics’,98000)");
		}
		catch (SQLException sqle) {
			System.out.println("Could not insert tuple. " + sqle);
		}

		ResultSet rset = stmt.executeQuery(
			"select dept name, avg (salary) "+
			" from instructor "+
			" group by dept name");

		while (rset.next()) {
			System.out.println(rset.getString("dept name") + " " +
			rset.getFloat(2));
		}
	}
	catch (Exception sqle)
	{
		System.out.println("Exception : " + sqle);
	}
}

(1-1) Connecting to the Database

자바 프로그램에서 DB에 접근하려면 우선 DB 커넥션을 열어야 한다. 이 단계에서는 어떤 DB를 사용할지를 선택해야 한다. 반드시 커넥션을 연 후에야 JAVA 프로그램에서 SQL문을 수행할 수 있다.

커넥션을 열기 위해서는 DriveManager 클래스의 getConnection() 메서드를 사용해야 한다. 이 메서드는 세 개의 파라미터를 가진다.

public static Connection getConnection(String url,
                                       String user,
                                       String password)
                                throws SQLException;

첫 번째 파라미터 url은 데이터베이스의 url이다. 이 파라미터에는 DB와 통신할 때 사용할 프로토콜, DB가 사용하는 포트 번호, 해당 서버에서 사용할 구체적인 데이터베이스가 무엇인지 등의 정보들이 포함되어있다. JDBC는 API를 제공할 뿐, 어떤 특정한 통신 프로토콜을 제공하는 것은 아님에 유의하자. JDBC 드라이버는 여러 프로토콜들을 지원할 수 있으며, 따라서 개발자는 그 중 DB와 드라이버 모두에서 사용될 수 있는 하나를 명시해야 한다.

두 번째 파라미터는 DB의 사용자 아이디 문자열이고, 세 번째는 그 비밀번호다.

JDBC를 지원하는 DB 제품들은 각각 자바에서 DB에 접근할 수 있게 하기 위해 동적으로 로드되는 JDBC 드라이버를 제공한다. 사실 커넥션을 여는 것 이전에 드라이버의 로딩이 먼저 일어나야 한다. 이 드라이버는 특정 DBMS와는 독립적인 JDBC 콜을, 특정 DBMS에 대한 것으로 변환하는 역할을 한다. DB와 정보를 교환하기 위해 사용되는 실제 프로토콜은 사용되는 드라이버에 달려있으며, JDBC 표준에 의해 정의되는 것은 아니다. 어떤 드라이버는 하나보다 많은 프로토콜을 제공하기도 하므로, 데이터베이스 제품이 지원하는 프로토콜이 무엇인가에 따라 적절한 프로토콜을 정해야 한다.

(1-2) Shipping SQL Statements to the Database System

DB 커넥션이 열리면, 프로그램은 이를 이용해 SQL문을 DB 시스템에 보내 실행시킬 수 있다. 이는 Statement의 인스턴스에 의해 이뤄진다. Statement 객체는 SQL문 그 자체가 아니라, 자바 프로그램에서 DB 시스템이 SQL문을 실행하도록 하는 메서드를 호출할 수 있게 하기 위한 객체다.

이를 실행하려면 executeQuery() 메서드나 executeUpdate() 메서드를 사용해야 한다. 이들은 각각 실행하고자 하는 SQL문이 쿼리인지, 혹은 update, insert, delete, create table 등과 같은 것인지에 따라 달라진다.

ResultSet executeQuery(String sql) throws SQLException;
int executeUpdate(String sql) throws SQLException;

executeQuery()는 쿼리 결과를 담은 ResultSet 객체를 반환하며, executeUpdate는 해당 SQL문에 영향을 받은 행의 개수를 반환한다. DDL인 경우는 0을 반환한다.

(1-3) Exceptions and Resource Management

SQL 메서드 실행은 예외를 던질 수도 있다. try{...} catch{...}문을 이용해서 처리하도록 하자.

커넥션이나 스테이트먼트, 혹은 그 외의 다른 JDBC 객체들을 여는 일은 모두 시스템 자원을 소모한다. 따라서 이것들은 모두 사용 후에 닫아줘야 한다. 그렇게 하지 않으면 DB 시스템의 리소스 풀이 고갈되어 시스템을 접근 불가능하게 만들거나, 혹은 시간 제한이 끝날 때까지 작동하지 않게 만들 수 있기 때문이다. 이를 닫기 위한한 방법은 그냥 커넥션과 스테이트먼트를 닫는 코드를 명시적으로 쓰는 것이다. 그런데 이 방법은 예외가 발생해 전체 메서드를 나가게 되는 경우, 제대로 작동하지 않을 수 있다. 그러므로 좀 더 선호되는 것은 자바의 try-with-resources 방식이다. 위의 예제 코드와 같이 커넥션과 스테이트먼트를 try의 바디가 아니라, try 바로 뒤의 괄호 안에서 열면, 이것들은 try 블럭이 끝나면 자동으로 닫히게 된다. 이를 통해 커넥션이나 스테이트먼트를 열린 채로 두는 일을 방지할 수 있다.

(1-4) Retrieving the Result of a Query

예제 코드는 stmt.executeQuery()로 쿼리를 실행하고 ResultSet 객체 rset 안에 담긴 쿼리 결과 튜플들을 한 번에 하나씩 조회한다. next() 메서드는 ResultSet 객체에 아직 페치하지 않은 튜플이 하나 이상 남아있는지를 확인하고, 만약 그렇다면 그 튜플을 페치한다. 그 반환값은 Boolean으로, 튜플을 페치 했는지의 여부를 가리킨다. 페치된 튜플의 애트리뷰트들은 'get'으로 시작하는 여러 메서드들을 통해 조회할 수 있다. getString()은 어떤 기본 SQL 자료형이든 조회할 수 있으며, 이를 자바 String 객체로 변환한다. 다른 메서드에는 getFloat(), getInt(), getLong() 등이 있다. 이 get 메서드들의 인자는 애트리뷰트 이름에 해당하는 문자열이 될 수도 있고, 또는 튜플 내의 해당 애트리뷰트의 위치를 가리키는 정수도 될 수 있다.

(1-5) Prepared Statements

몇몇 값들이 "?"로 대치된 prepared statement를 만들어, 나중에 그 실제 값을 제공해 명시할 수도 있다. DB 시스템은 쿼리가 준비되었을 때 컴파일하고, 쿼리가 실행될 때마다 이전에 컴파일된 쿼리 형태를 재활용하면서 새 값들을 파라미터처럼 적용한다.

Connection 클래스의 prepareStatement() 메서드는 파라미터 값들을 포함하는 쿼리를 정의한다. 어떤 JDBC 드라이버들은 이때 쿼리를 데이터베이스에 제출해 컴파일하도록 하기도 하지만, 어떤 다른 드라이버들은 지금 당장은 데이터페이스에 연결하지 않기도 한다. 이 메서드는 PreparedStatment 클래스의 객체를 반환한다. SQL문은 이때 실행되지 않고, 이후 PreparedStaement의 executeQuery()나 executeUpdate()문이 불릴 때 실행된다. 하지만 이 두 메서드들을 호출하기 전에는 "?"로 표시한 곳의 파라미터들을 채워줘야 한다. 이를 채우기 위한 메서드에는 setString() 및 그것과 유사한 것(setInt(), setTime(), setShort() 등)들이 있다. 이 메서드들은 두 개의 파라미터를 가지는데, 첫 번째는 여러 개의 "?" 중 어떤 것을 채울지, 두 번째는 그 구체적인 값이 무엇인지다. 단, 이때 "?"의 위치는 보통 사용하는 것과 달리 0이 아니라 1부터 시작한다.

void	setString(int parameterIndex, String x);
void    setInt(int parameterIndex, int x);
void	setTime(int parameterIndex, Time x);
void	setShort(int parameterIndex, short x);
void    setObject(int parameterIndex, Object x);
//...

prepared statement는 같은 쿼리가 파라미터 값만 바뀌어 여러 번 호출되는 경우에 효율적이며, 사용자가 입력한 값이 사용되는 SQL 쿼리를 실행하는 데에도 유용하다. 유저가 입력한 값에 "\"와 같은 특수 문자가 들어간 경우를 생각해보자. prepared statement를 사용하지 않고 그냥 String을 이어붙여 쿼리를 만든다면, 정말 세심하게 주의를 기울이는 경우가 아니라면 SQL문은 제대로 작동하지 않을 확률이 높다. 이를 악용한 공격을 가리켜 SQL 인젝션(SQL injection)이라 부른다. 하지만 prepared statement를 사용한다면 setString() 메서드가 이런 것들을 알아서 자동으로 처리해주기 때문에, 문법적 오류나 SQL 인젝션과 같은 것에는 덜 신경을 써줘도 된다. 그러므로 사용자 입력 문자열을 DB에 보낼 때에는 반드시 prepared statement를 사용하도록 하자.

(1-6) Callable Statements

JDBC는 CallableStatement 인터페이스도 제공하는데, 이는 프로시저나 함수에 저장된 SQL을 호출할 수 있게 한다. 이는 PrepareStatement가 쿼리에 대해 그랬던 것처럼, 함수나 프로시저도 그럴 수 있게 한다.

CallableStatement cStmt1 = conn.prepareCall("{? = call some function(?)}");
CallableStatement cStmt2 = conn.prepareCall("{call some procedure(?,?)}");

함수 반환값의 타입 및 프로시저의 출력 파라미터는 registerOutParameter() 메서드를 통해 등록해야 한다. 그 결과들은 result set에서 그랬던 것처럼 여러 get 메서드들을 통해 조회할 수 있다.

(1-7) Metadata Features

자바 응용 프로그램은 DB에 저장된 데이터 선언을 포함하지 않는다. 그런 선언은 SQL DDL문의 역할이다. 따라서 JDBC를 쓰는 자바 프로그램은 프로그램에 하드코딩된 DB 스키마를 사용하거나, 혹은 런타임에 직접 DB 시스템을 통해 해당 정보를 결정해야 한다. 후자의 방식이 더 선호되는데, 응용 프로그램을 DB 스키마의 변화에 대해 좀 더 견고하게 만들기 때문이다.

ResultSet 인터페이스는 getMetaData() 메서드를 가지고 있는데, 이는 result set에 대한 메타데이터를 담은 ResultSetMetaData 객체를 반환한다. 이 클래스는 결과의 컬럼 수, 컬럼의 이름, 컬럼의 타입등의 메타데이터 정보를 찾기 위한 메서드들을 가지고 있다. 이를 이용하면 결과의 스키마에 대한 이해가 없더라도 쿼리를 실행하는 코드를 작성할 수 있다.

DatabaseMetaData 인터페이스를 이용하면 데이터베이스의 메타데이터를 찾을 수도 있다. Connection 인터페이스에는 getMetaData() 메서드가 있으며, 이 메서드가 해당 인터페이스의 객체를 반환한다. 이 인터페이스는 데이터베이스 및 연결된 데이터베이스 시스템의 메타데이터를 얻기 위한 아주 많은 메서드들을 가지고 있다.

예를 들면 데이터베이스 시스템의 제품명 및 버전을 얻을 수도 있고, 해당 시스템이 제공하는 기능들에 대한 쿼리도 보낼 수도 있다. getColumns() 메서드를 사용하면 전달된 인자들에 따라 해당하는 컬럼들의 정보를 가져올 수 있다.

ResultSet getColumns(String catalog,
                     String schemaPattern,
                     String tableNamePattern,
                     String columnNamePattern)
              throws SQLException;

getTables() 메서드는 데이터베이스 안에 있는 모든 테이블의 리스트를 가져오는 메서드다.

ResultSet getTables(String catalog,
                    String schemaPattern,
                    String tableNamePattern,
                    String[] types)
             throws SQLException;

앞의 세 파라미터는 getColumns()와 동일하며, 마지막 파라미터는 반환되는 테이블 타입을 제한하기 위해 쓰인다. 널로 설정되면 시스템 내부의 테이블을 포함한 모든 테이블이 반환되며, 파라미터를 설정하면 사용자가 생성한 테이블들로만 반환되도록 할 수도 있다.

(1-8) Other Features

JDBC는 updatable result set과 같은 다른 여러 기능들도 제공한다. 이를 이용하면 result set 내에 있는 튜플들을 갱신할 때, DB 릴레이션에 있는, 대응하는 튜플들도 업데이트된다.

트랜잭션은 여러 액션들을 하나의 원자적 단위로 취급할 수 있게 하는데, 기본적으로 각 SQL문은 자동으로 커밋되는, 별개의 트랜잭션으로 다뤄진다. Connection 인터페이스의 setAutoCommit() 메서드를 이용하면 이러한 설정을 켜거나 끌 수 있다. 만약 conn.setAutoCommit(false)로 해당 옵션을 끄면, 트랜잭션은 conn.commit()이나 conn.rollback()으로 명시적으로 커밋, 혹은 롤백되어야 한다.

JDBC는 또한 전체 큰 객체를 메모리에 생성하지 않고도 큰 객체들을 다루는 인터페이스를 제공한다. 큰 객체들을 페치하기 위해, ResultSet 인터페이스는 getBlob()과 getClob() 메서드들을 제공한다. 이들은 각각 Blob, Clob 타입의 객체들을 반환한다. 이 객체들은 큰 객체 전체를 저장하지 않고, 실제로 DB내에 있는 해당 객체들에 대한 논리적 포인터들만을 저장한다. 이 객체들로부터 데이터를 페치해오는 것은 파일, 또는 인풋 스트림에서 데이터를 가져오는 것과 유사하며, getBytes()나 getSubString() 메서드를 통해 이루어진다.

반대로 큰 객체를 DB에 저장하기 위해, PreparedStatement 클래스는 setBlob() 메서드를 통해 타입이 blob인 DB 컬럼이 인풋 스트림에 연결될 수 있도록 한다.

void setBlob(int parameterIndex,
             InputStream inputStream,
             long length)
      throws SQLException;

이 prepared statement가 실행될 때, 데이터는 인풋 스트림에서 읽혀 데이터베이스의 blob에 적힌다. 비슷하게 clob 컬럼을 위해서는 setClob() 메서드를 사용할 수 있다.

JDBC에는 row set 기능도 있다. row set은 DB와 연결하지 않고도 작동할 수 있으며, 순방향의 조회만 가능한 result set과는 달리 앞뒤 양방향으로도 조회할 수 있다.