ResultSetMetaData

1. Metadata

  (1) Information of dataStructure

  (2) ResultSet : real Data

  (3) ResultSetMetaData : structure of Data

 

2. Example

ResultSet rs = stmt.executeQuery("SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE");
ResultSetMetadata rsmd = rs.getMetaData();

int count = rsmd.getColumnCount(); // 컬럼 갯수
String name = rsmd.getColumnName(2); // 2번째 컬럼의 이름
Strng type = rsmd.getColumnTypeName(3); // 3번째 컬럼의 DB에서의 타입

3. Major Methods

(1) getColumnCount() : Return the number of columns in this ResultSet object

(2) getColumnName(int column) : Get the designated column's name

(3) getColumnType(int column) : Retrieves the designated column's SQL type 

(4) isNullable(int column) : indicated the nullability of values in the designated column
(5) getPrecision(int column) : Get the designated column's specified column size

 

 

4. Notes

(1) Oracle 

    -  Notorious for performance hits when accessing metadata

    -  To Provide Metadata, the Oracle driver often queries complex internal data dictionary views or 

       re-parses the SQL.

    - You should check about the includeSynonyms at DB connection property

    - Cache the metadata : Call it once before result set loop and store the values in a local variable or DTO

(2) MySQL / MariaDB

    - Depends heavily on the driver version and the type of Statement used

    - There are possiblity of the driver appeding WHERE 1=0 Clause internally and run it against the server 

       to get column structure , if you call getMetaData() on a PreparedStatement before calling execute() 

(3) PostgreSQL

    - Case Sensitivity

    - PostgreSQL handles identifiers differently than most traditional RDBMS

    - PostgreSQL folds unquoted identifiers to lowercase

    - Always use getColumnLabel() and be consistent with your alias naming conventions

(4) getColumnName() vs  getColumnLabel()

    - getColumnName() : Returns the actual column name in the DB table

    - getColumnLabel() : Returns the Alias (the name defined in the AS clause)