JDBC 2.0

Date: 21/Nov/2002

OracleResultSetMetaData

•   use OracleResultSetMetaData/ResultSetMetaData object to get information about columns in the ResultSet.

Introduction

ResultSetMetaData implementation provides information about the ResultSet and implements the java.sql.ResultSetMetaData interface. The column name, column type etc. are provided by the ResultSetMetaData object. oracle.jdbc.OracleResultSetMetaData class is JDBC 2.0-compliant Oracle specific implementation of  the ResultSetMetaData interface.

Pre-requisites for running the example 

To run the sample provided below,
1. Include Oracle JDBC2.0 classes in the classpath.
2. Specify the <hostname>, <port> and <SID> of the database you are connecting to.

Description

The ResultSetMetaData object can be obtained by calling the getMetaData() method in the ResultSet object obtained when a SQL query is executed. The returned ResultSetMetaData object can be type cast to get OracleResultSetMetaData object. The following code shows how to use OracleResultSetMetaData.

import java.sql.*;
import oracle.jdbc.OracleResultSetMetaData;
public class OracleResultSetMetaDataSample  {
static final String dbURI = "jdbc:oracle:thin:@<hostname>:<port>:<SID>";

  public static void main(String[] args) throws Exception{

    // Connection reference
    Connection conn = null;
    try {

      // Load database driver
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

      // Make connection
      conn = DriverManager.getConnection(dbURI,"scott","tiger");

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * from emp");

      // Get the ResultSet meta data
      OracleResultSetMetaData rmd = (OracleResultSetMetaData)rs.getMetaData();

      if (rmd == null) {

        System.out.println("ResultSet meta data not available");

      } else {

        int columnCount = rmd.getColumnCount();

        // Display number of Columns in the ResultSet
        System.out.println("Number of Columns in the table : " + columnCount);

        for(int i=1; i<=columnCount; i++) {

          // Display number of Column name
          System.out.print("Column Name :" + rmd.getColumnName(i));

          // Display number of Column Type
          System.out.print("   Column Type :" + rmd.getColumnTypeName(i));

          // Display if Column can be NOT NULL
          switch (rmd.isNullable(i)) {
          
            case OracleResultSetMetaData.columnNoNulls :
              System.out.println("  NOT NULL");
              break;
            case OracleResultSetMetaData.columnNullable :
              System.out.println("  NULLABLE");
              break;
            case OracleResultSetMetaData.columnNullableUnknown :
              System.out.println("  NULLABLE Unkown");
          }
        }
      }
    } finally {
    
      // Close connection
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException ex) {
           System.out.println("Error in closing Conection");
        }
      }
    }
  }
}

After getting a OracleResultSetMetaData object, you can use the methods in it to get details about the columns in the ResultSet.All the methods in OracleResultSetMetaData return boolean, int or String.
getColumnCount() method returns number of columns in the ResultSet as an int.
getColumnName() method returns the column name as a String.
getColumnTypeName() method returns the column type as a String.
isNullable() method returns if the the column can be NULL. This method returns an int which can be any one of the following constants:

columnNoNulls                - indicates that a column does not allow NULL values

columnNullable                - indicates that a column allows NULL values

columnNullableUnknown - indicates that the nullability of a column's values is unknown

OracleResultSetMetaData interface does not implement the getSchemaName() and getTableName() methods because underlying protocol does not make this feasible.

Resources

•   using OracleResultSetMetaData