JavaData & JavaDiscovering Database Metadata with the JDBC Metadata API

Discovering Database Metadata with the JDBC Metadata API

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Metadata basically means the data that provide a structured description about some other data. From a programmer’s point of view, database metadata refers to data about database data or, more elaborately, the information about tables, views, column types, column names, result sets, stored procedures, and databases. Java‘s JDBC metadata API provides the means to retrieve that information through Java code. Java Metadata information are particularly useful for writing code that can adapt to several database system or to the content of any database. That means creating a sort of generic interface that uses advanced database features to discover database metadata at runtime. This article is an attempt to explore some of the key features of the JDBC metadata API in view of discovering database metadata.

Use of Metadata

JDBC Metadata API can be used to retrieve the following information about the database:

  • Database users, tables, views, stored procedures
  • Database schema and catalog information
  • Table, view, column privileges
  • Information about primary key, foreign key of a table

JDBC Overview

JDBC provides the necessary APIs to the client application to discover metadata information about the database and any given ResultSet with the help of metadata classes.

Meta
Figure 1: A JDCB overview

The API involves several interfaces that provide metadata information service to the client. The client retrieves this information via the JDBC Driver Manager. The Driver Manager acts as a interface between the actual database and the APIs. Vendor-specific databases provide their own Java driver (Type 4 driver). For example, MySQL provides Connector/J; other databases, such as Oracle or PostgreSQL, have their respective JDBC driver. In cases where the database vendor does not provide any driver (Type 4), a Java IDBC/ODBC Bridge can act as a intermediary between the underlying ODBC driver and the Driver Manager in Java. In all cases, the technique of retrieving database metadata information is the same.

JDBC Metadata API

The JDBC API provides two key interfaces to retrieve metadata information: DatabaseMetaData and ResultSetMetaData. These two interfaces are part of the java.sql package. DatabaseMetaData is primarily used to retrieve information about the database, such as its capabilities and structure. ResultSetMetaData, on the other hand, is used to obtain information about SQL queries such as information about its size and column types. So, the key players of the JDBC metadata APIs are as follows:

DatabaseMetaData

This interface is generally implemented by the database vendor and provided along with the native JDBC driver. Native JDBC drivers are built on top of the database. By implementing this interface, database vendors provide comprehensive information about the database as a whole, such as table name, indexes, product name, version, and so forth. There are a number of methods declared in this interface to retrieve various metadata information associated with the database.

Quick Example

package org.mano.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseMetaDataDemo {

   private static final String URL =
      "jdbc:mysql://localhost:3306/addressbook?
      zeroDateTimeBehavior=convertToNull";
   private static final String USERNAME = "testuser";
   private static final String PASSWORD = "secret";

   public static void main(String[] args) {
      Connection conn = null;
      DatabaseMetaData dbmd = null;
      try {
         conn = DriverManager.getConnection(URL, USERNAME,
            PASSWORD);
         dbmd = conn.getMetaData();
         if (dbmd != null) {
            System.out.println("Database Version: " +
               dbmd.getDatabaseProductVersion());
            System.out.println("Driver Name: " +
               dbmd.getDriverName());
            System.out.println("Driver Version: " +
               dbmd.getDriverVersion());
            System.out.println("URL: " +
               dbmd.getURL());
            System.out.println("User Name: " +
               dbmd.getUserName());
            System.out.println(
               (dbmd.supportsANSI92FullSQL() ?
               "ANSI92FullSQL supported." :
               "ANSI92FullSQL not supported."));
            System.out.println(
               (dbmd.supportsTransactions() ?
               "Transaction supported." :
               "Transaction not supported."));
         } else {
            System.out.println("Metadata not supported");
         }
      } catch (SQLException ex1) {
         System.err.println(ex1);
      } finally {
         try {
            conn.close();
         } catch (SQLException ex2) {

         }
      }
   }
}

ResultSetMetaData

This interface provides metadata information about the structure of the ResultSet object, such as finding out number of columns, its name, type and length, table names, whether a column is readable/writable, searchable, and so on.

Quick Example

package org.mano.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetMetaDataDemo {

   private static final String URL =
      "jdbc:mysql://localhost:3306/addressbook?
      zeroDateTimeBehavior=convertToNull";
   private static final String USERNAME = "testuser";
   private static final String PASSWORD = "secret";
   private static final String SQL =
      "SELECT * FROM Addresses";

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      ResultSetMetaData rsmd = null;

      try {
         conn = DriverManager.getConnection(URL,
            USERNAME, PASSWORD);
         stmt = conn.createStatement();
         rs = stmt.executeQuery(SQL);
         rsmd = rs.getMetaData();

         if (rsmd != null) {
            int cols = rsmd.getColumnCount();
            System.out.println("Number of Columns: " + cols);
            System.out.println("Table Name: " +
               rsmd.getTableName(1));
            System.out.println("Catalog Name: " +
               rsmd.getCatalogName(1));
            System.out.println
               ("------------------------------------------");
            for (int i = 1; i <= cols; i++) {
               System.out.println("Class Name: " +
                  rsmd.getColumnClassName(i));
               System.out.println("Column Name: " +
                  rsmd.getColumnName(i));
               System.out.println("Column Type Name: " +
                  rsmd.getColumnTypeName(i));
               System.out.println
                  ("--------------------------------------");
            }
         } else {
            System.out.println("ResultSetMetadata not supported");
         }

      } catch (SQLException ex1) {
         System.err.println(ex1);
      } finally {
         try {
            stmt.close();
            rs.close();
            conn.close();
         } catch (SQLException ex2) {

         }
      }
   }
}

ParameterMetaData

ParameterMetadata objects are used to retrieve information about the parameter markers in a PreparedStatement object. The metadata information refers to the types and properties of the parameter used, such as getting the fully qualified Java class name, parameter count, its types, designated precision specified by the column size, and the like.

Quick Example

package org.mano.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ParameterMetaDataDemo {

   private static final String URL = "jdbc:mysql://localhost:3306/
      addressbook?zeroDateTimeBehavior=convertToNull";
   private static final String USERNAME = "testuser";
   private tatic final String PASSWORD = "secret";
   private static final String SQL = "SELECT id, firstName, lastName
      FROM Addresses WHERE id=? AND firstName LIKE ?";

   public static void main(String[] args) {

      Connection conn = null;
      PreparedStatement pstmt = null;
      ParameterMetaData pmd = null;

      try {
         conn = DriverManager.getConnection(URL,
            USERNAME, PASSWORD);
         pstmt = conn.prepareStatement(SQL);
         pmd = pstmt.getParameterMetaData();
         if (pmd != null) {
            System.out.println("Parameter Count: " +
               pmd.getParameterCount());
         } else {
            System.out.println("ParameterMetadata not
               supported by the database");
         }

      catch (SQLException ex1) {
         System.err.println(ex1);
      } finally {
         try {
            pstmt.close();
            conn.close();
         } catch (SQLException ex2) {

         }
      }
   }
}

RowSetMetaData

The RowSetMetaData interface is a part of the javax.sql package and is a sub-implementation of the ResultSetMetaData interface. An object of this class provides information about columns in a RowSet object. The RowSet interface provides support for the JDBC API for JavaBeans component model. As a result, it has properties and supports the technique of JavaBeans event notification. RowSetMetadata provides the information about the columns in a RowSet object and can be used to find out about the number of columns contained in a rowset or the type of data each column contains. It is crucial to understand the working mechanism of the RowSet object to fully appreciate the use of RowSetMetaData. Delineating the rowset mechanism is outside the scope of this article; however, readers may refer the article “Working with the JDBC RowSet API” to get an idea of the Rowset API. However, this is not going to stop us from getting a glimpse of RowSetMetaData in action.

Quick Example

package org.mano.example;

import java.sql.SQLException;

import javax.sql.RowSetMetaData;
import javax.sql.rowset.WebRowSet;

import com.sun.rowset.WebRowSetImpl;

public class RowSetMetaDataDemo {

   private static final String URL = "jdbc:mysql://localhost:3306/
      addressbook?zeroDateTimeBehavior=convertToNull";
   private static final String USERNAME = "testuser";
   private static final String PASSWORD = "secret";
   private static final String SQL = "SELECT * FROM Addresses";

   public static void main(String[] args) {
      WebRowSet rowSet = null;
      RowSetMetaData rsmd = null;

      try {
         Class.forName("com.mysql.jdbc.Driver");
         rowSet = new WebRowSetImpl();
         rowSet.setUrl(URL);
         rowSet.setUsername(USERNAME);
         rowSet.setPassword(PASSWORD);
         rowSet.setCommand(SQL);
         rowSet.execute();

         rsmd = (RowSetMetaData) rowSet.getMetaData();
         if (rsmd != null) {

            int count = rsmd.getColumnCount();
            for (int i = 1; i <= count; i++)
               System.out.println("Column Name: " +
                  rsmd.getColumnName(i) +
                  " Type: " + rsmd.getColumnTypeName(i));
         } else {
            System.out.println("RowSetMetadata not supported");
         }

      } catch (ClassNotFoundException | SQLException ex1) {
         System.err.println(ex1);
      } finally {
         try {
            rowSet.close();
         } catch (SQLException ex2) {

         }
      }
   }
}

Conclusion

Take these examples as a starting point to experiment with the JDC Metadata APIs. JDBC Metadata APIs are indispensable for creating most commercial database applications. They are particularly useful for creating customized database solutions such as generating a GUI-based data browser application. For example, one can create dynamic a GUI-based table structure according to the metadata information retrieved from the database. Refer to the Java API documentation for more information on the APIs and their functionality.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories