JDBC DatabaseMetadata 기반 정보 조회 / 스키마, 테이블, 컬럼, PK, Index / 오라클, MySQL, PostgreSQL, SQL Server, ...

Oracle, MySQL, PostgreSQL, SQL Server, Sybase IQ, SAP HANA 등의 DBMS 종류는 우리가 인지하고 있는 것보다 모르는 것이 더 많을 것이다.

 

이러한 데이터베이스에서 스키마 목록, 테이블 목록, 테이블의 컬럼 목록, 테이블의 Primary Key, 테이블의 인덱스, Foreign Key 등의 데이터를 조회하기 위해서 어떻게 해야 할까?? 가장 먼저 떠오르는 방법은 해당 데이터베이스에 SQL 쿼리를 작성하여 각각의 딕셔너리 테이블(혹은 뷰)을 조회하는 방법일 것이다.

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='T1'

이렇게 SQL 쿼리를 기반으로 작성된 코드는 다른 데이터베이스와는 호환이 되지 않으며(데이터베이스마다 조회 쿼리가 모두 다름), 하나의 프로젝트에서 여러 데이터베이스를 접근하는 경우라면 데이터베이스별로 해당 쿼리와 접근을 위한 로직들을 별도로 개발해야 할 것이다. 결국, 이러한 코드들은 재사용과 유지보수 측면에서 비효율 적이다.

 

하지만, JDBC를 사용하는 경우라면 얘기가 조금 달라진다. JDBC에서 제공하는 DatabaseMetadata 인터페이스를 잘 활용한다면 데이터베이스의 딕셔너리를 조회하는 방법을 일반화 할 수 있다. A 데이터베이스의 컬럼 목록을 조회하는 로직을 B, C, D, E, … 데이터베이스에서도 재사용이 가능하다는 말이다.

 

JDBC의 DatabaseMetadata는 데이터베이스 전체에 대한 종합적인 정보를 조회하기 위한 인터페이스며 각 DBMS 벤더마다 해당 인터페이스에 정의된 각종 메서드와 속성 필드들을 구현하도록 한다. (참고)

 

지금부터 JDBC의 DatabaseMetadata를 사용하여 데이터베이스의 테이블의 각종 정보를 조회하는 방법에 대해서 알아보도록 하자.

 

스키마 목록 조회

스키마(혹은 유저)의 목록을 조회하기 위해서는 DatabaseMetadata의 getSchema(), getSchema(catalog, pattern) API를 사용하면 된다. 

Connection conn = DriverManager.getConnection(url, user, pwd);
ResultSet rs = Conn.getMetadata().getSchemas();
while(rs.next()) {
    String catalog = rs.getString("TABLE_CATALOG");
    String schema = rs.getString("TABLE_SCHEM");
    System.out.println("Catalog : " + catalog + ", Schema : " + schema);
}

DatabaseMetadata의 정보 조회용 API를 호출하면, ResultSet이 반환되고 조회 가능한 정보(컬럼)의 이름은 API 문서를 통해서 확인 가능하다. getSchemas() API는 카탈로그와 스키마 이름을 조회할 수 있는 필드가 있다.

 

카탈로그(Catalog)는 무엇을 의미하는가? Catalog는 데이터베이스 제품마다 그 의미를 다르게 정의하고 있는데, DatabaseMetadata의 getCatalogTerm() API를 사용하여 의미를 알 수 있다.

 

참고로, 오라클의 경우 Catalog는 Database 이름을 의미한다.

 

테이블 목록 조회

테이블의 목록을 조회하기 위해서는 getTables() API를 사용한다.

Connection conn = DriverManager.getConnection(url, user, pwd);
ResultSet rs = conn.getMetaData().getTables(null, "OWN1", null, new String[]{"TABLE"});
while(rs.next()) {
    String table = rs.getString("TABLE_NAME");
    System.out.println("Table Name : " + table);
}

 테이블 카탈로그, 스키마, 패턴, 테이블 종류를 필터링 조건으로 사용하여 원하는 테이블 목록을 조회 가능하다.

 

테이블의 컬럼 목록 조회

테이블에 속한 컬럼의 목록과 각 컬럼의 속성을 조회하기 위해서는 getColumns() API를 사용한다.

Connection conn = DriverManager.getConnection(url, user, pwd);
ResultSet rs = conn.getMetaData().getColumns(null, "OWN1", "TABLE1", "%");
while(rs.next()) {
    String name = rs.getString("COLUMN_NAME");
    int position = rs.getInt("ORDINAL_POSITION");
    String type = rs.getString("TYPE_NAME");
    int length = rs.getInt("CHAR_OCTET_LENGTH");
    int precision = rs.getInt("COLUMN_SIZE");
    int scale = rs.getInt("DECIMAL_DIGITS");
    boolean nullable = rs.getInt("NULLABLE") == 0 ? false : true;
    
	...
}

카탈로그, 스키마, 테이블 이름, 컬럼 이름 패턴을 사용하여 사용자가 원하는 컬럼 목록과 각 컬럼의 속성을 조회 가능하다. 

 

테이블의 Primary Key 조회

테이블의 Primary Key를 조회하기 위해서는 getPrimaryKeys() API를 사용한다.

Connection conn = DriverManager.getConnection(url, user, pwd);
ResultSet rs = conn.getMetaData().getPrimaryKeys(null, "OWN1", "TABLE1");
while(rs.next()) {
    String colName = rs.getString("COLUMN_NAME");
    int keySeq = rs.getInt("KEY_SEQ");
    String pkName = rs.getString("PK_NAME");
      
	...
}

카탈로그, 스키마, 테이블 이름을 지정하면 해당 테이블의 Primary Key를 구성하는 컬럼의 목록을 확인 가능하다. 

 

테이블의 Index 조회

테이블의 인덱스 목록과 해당 인덱스를 구성하는 컬럼의 목록을 조회하기 위해서는 getIndexInfo() API를 사용한다. 해당 API를 사용할 경우 인덱스에 속한 컬럼 목록이 조회 되므로, 인덱스가 두 개 이상일 경우 주의하여 조회해야 한다. 아래의 코드(Unique Index와 구성 컬럼 조회 방법)에서 Map을 사용하여 인덱스별 컬럼 목록을 유지하는 방법을 눈여겨 보길 바란다.

Connection conn = DriverManager.getConnection(url, user, pwd);
ResultSet rs = conn.getMetaData().getIndexInfo(null, "OWN1", "TABLE1", true, false);

// Index 이름과 인덱스 구성 컬럼(List)로 구성된 해시맵
Map<String, List<String>> indexes = new HashMap<String, List<String>>();

while(rs.next()) {
    String idxName = rs.getString("INDEX_NAME");
    
    List<String> cols = indexes.get(idxName);
    
    if (cosl == null) {
    	cols = new ArrayList<String>();
        indexes.put(idxName, cols);
    }
    
    String colName = rs.getString("COLUMN_NAME");
    // 인덱스에서 컬럼의 순서, 컬럼 순서에 민감할 경우 사용
    int colSeq = rs.getInt("ORDINAL_POSITION");
    
    cols.add(colName);
      
	...
}

위의 코드는 모든 유니크 인덱스와 해당 인덱스를 구성하는 컬럼 목록을 조회하는 코드이다. 카탈로그, 스키마, 테이블 이름, Unique 여부 등을 필터링 조건으로 사용 가능하다.

 

지금까지 JDBC의 DatabaseMetadata를 사용하여 테이블의 각종 정보를 조회하는 방법에 대해서 알아보았다. 필자가 소개한 몇몇의 API는 빙산의 일각일 뿐이니 DatabaseMetadata 클래스의 문서를 잘 파악하여 필요한 기능을 활용하시기 바란다.

 

DatabaseMetadata는 DBMS의 벤더들이 구현하는 내용이기 때문에 기대하는 동작과 100% 일치하지 않는 경우도 있으니 주의해서 사용하길 바란다. 필자는 여러 Database에 접근하여 각 정보를 조회하기 위해서 기본으로 DatabaseMetadata를 이용하는 공통 클래스가 있고, DB별로 부족하거나 추가 정보가 필요할 경우 공통 클래스를 상속하여 DBMS별로 약간의 추가 기능을 구현해서 사용한다.