Metadata (Java Enterprise in a Nutshell)

Most JDBC programs are designed to work with a specific database and particular tables in that database; the program knows exactly what kind of data it is dealing with. Some applications, however, need to dynamically discover information about result set structures or underlying database configurations. This information is called metadata, and JDBC provides two classes for dealing with it: DatabaseMetaData and ResultSetMetaData. If you are developing a JDBC application that will be deployed outside a known environment, you need to be familiar with these interfaces.

2.9.1. DatabaseMetaData

You can retrieve general information about the structure of a database with the java.sql.DatabaseMetaData interface. By making thorough use of this class, a program can tailor its SQL and use of JDBC on the fly, to accommodate different levels of database and JDBC driver support.

Database metadata is associated with a particular connection, so DatabaseMetaData objects are created with the getMetaData() method of Connection:

DatabaseMetaData provides an overwhelming number of methods you can call to get actual configuration information about the database. Some of these return String objects (getURL()), some return boolean values (nullsAreSortedHigh()), and still others return integers (getMaxConnections()). The full list is given in Chapter 17, "The java.rmi.server Package".

A number of other methods return ResultSet objects. These methods, such as getColumns(), getTableTypes(), and getPrivileges(), generally encapsulate complex or variable-length information. The getTables() method, for instance, returns a ResultSet that contains the name of every table in the database and a good deal of extra information besides.

Many of the DatabaseMetaData methods take string patterns as arguments, allowing for simple wildcard searching. A percent sign (%) substitutes for any number of characters, and an underscore ( _ ) calls for a single character match. Thus, %CUSTOMER% matches NEW_CUSTOMERS, CUSTOMER, and CUSTOMERS, while CUSTOMER% matches only CUSTOMER and CUSTOMERS. All of these patterns are case-sensitive.

Example 2-2 shows a simple program that displays some basic database characteristics, a list of tables, and a list of indexes on each table. The program assumes a JDBC driver with full support for all the DatabaseMetaData commands.

Example 2-2. DBViewer Program

import java.sql.*;
import java.util.StringTokenizer;

public class DBViewer {

  final static String jdbcURL = "jdbc:odbc:customerdsn";
  final static String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";

  public static void main(java.lang.String[] args) {

    System.out.println("--- Database Viewer ---");
   
    try {
      Class.forName(jdbcDriver);
      Connection con = DriverManager.getConnection(jdbcURL, "", "");

      DatabaseMetaData dbmd = con.getMetaData();

      System.out.println("Driver Name: " + dbmd.getDriverName());
      System.out.println("Database Product: " + dbmd.getDatabaseProductName());
      System.out.println("SQL Keywords Supported:");
      StringTokenizer st = new StringTokenizer(dbmd.getSQLKeywords(), ",");
      while(st.hasMoreTokens())
        System.out.println(" " + st.nextToken());
 
      // Get a ResultSet that contains all of the tables in this database
      // We specify a table_type of "TABLE" to prevent seeing system tables,
      // views and so forth
      String[] tableTypes = { "TABLE" };
      ResultSet allTables = dbmd.getTables(null,null,null,tableTypes);
      while(allTables.next()) {
        String table_name = allTables.getString("TABLE_NAME");
        System.out.println("Table Name: " + table_name);
        System.out.println("Table Type: " + allTables.getString("TABLE_TYPE"));
        System.out.println("Indexes: ");

        // Get a list of all the indexes for this table
        ResultSet indexList = dbmd.getIndexInfo(null,null,table_name,false,false);
        while(indexList.next()) {
          System.out.println(" Index Name: "+indexList.getString("INDEX_NAME"));
          System.out.println(" Column Name:"+indexList.getString("COLUMN_NAME"));
        }
        indexList.close(); 
      }

      allTables.close();
      con.close();
    } 
    catch (ClassNotFoundException e) {
      System.out.println("Unable to load database driver class");
    }
    catch (SQLException e) {
      System.out.println("SQL Exception: " + e.getMessage());
    }
  }
}

Here's some sample output when this program is run against a Microsoft Access database via the JDBC-ODBC bridge (snipped slightly to prevent several pages of uninteresting text):

2.9.2. ResultSetMetaData

The ResultSetMetaData interface provides information about the structure of a particular ResultSet. Data provided by ResultSetMetaData includes the number of available columns, the names of those columns, and the kind of data available in each. Example 2-3 shows a short program that displays the contents of a table and shows the data type for each column.

Example 2-3. TableViewer Program

import java.sql.*;
import java.util.StringTokenizer;

public class TableViewer {

  final static String jdbcURL = "jdbc:oracle:customerdb";
  final static String jdbcDriver = "oracle.jdbc.OracleDriver";
  final static String table = "CUSTOMERS";

  public static void main(java.lang.String[] args) {
 
    System.out.println("--- Table Viewer ---");
   
    try {
      Class.forName(jdbcDriver);
      Connection con = DriverManager.getConnection(jdbcURL, "", "");
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);

      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();
      for(int col = 1; col <= columnCount; col++) {
        System.out.print(rsmd.getColumnLabel(col));
        System.out.print(" (" + rsmd.getColumnTypeName(col)+")");
        if(col < columnCount) 
          System.out.print(", ");
      }
      System.out.println();
  
      while(rs.next()) {
        for(int col = 1; col <= columnCount; col++) {
          System.out.print(rs.getString(col));
          if(col < columnCount) 
            System.out.print(", ");
        } 
        System.out.println();
      }  
 
      rs.close();
      stmt.close();
      con.close();
    }
    catch (ClassNotFoundException e) {
      System.out.println("Unable to load database driver class");
    }
    catch (SQLException e) {
      System.out.println("SQL Exception: " + e.getMessage());
    }
  }
}

The key methods used here are getColumnCount(), getColumnLabel(), and getColumnTypeName(). Note that type names returned by getColumnTypeName() are database-specific (e.g., Oracle refers to a string value as a VARCHAR; Microsoft Access calls it TEXT). Here's some sample output for TableViewer:

2.9. Metadata

2.9.1. DatabaseMetaData

Example 2-2. DBViewer Program

2.9.2. ResultSetMetaData

Example 2-3. TableViewer Program