Learning about a data source using DatabaseMetaData methods

The DatabaseMetaData interface contains methods that retrieve information about a data source. These methods are useful when you write generic applications that can access various data sources.

About this task

In generic applications that can access various data sources, you need to test whether a data source can handle various database operations before you execute them. For example, you need to determine whether the driver at a data source is at the JDBC 4.0 level before you invoke JDBC 4.0 methods against that driver.

DatabaseMetaData methods provide the following types of information:
  • Features that the data source supports, such as the ANSI SQL level
  • Specific information about the JDBC driver, such as the driver level
  • Limits, such as the maximum number of columns that an index can have
  • Whether the data source supports data definition statements (CREATE, ALTER, DROP, GRANT, REVOKE)
  • Lists of objects at the data source, such as tables, indexes, or procedures
  • Whether the data source supports various JDBC functions, such as batch updates or scrollable ResultSets
  • A list of scalar functions that the driver supports

Procedure

To invoke DatabaseMetaData methods, you need to perform these basic steps:

  1. Create a DatabaseMetaData object by invoking the getMetaData method on the connection.
  2. Invoke DatabaseMetaData methods to get information about the data source.
  3. If the method returns a ResultSet:
    1. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.
    2. Invoke the close method to close the ResultSet object.

Examples

Example: The following code demonstrates how to use DatabaseMetaData methods to determine the driver version, to get a list of the stored procedures that are available at the data source, and to get a list of datetime functions that the driver supports. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 1. Using DatabaseMetaData methods to get information about a data source
Connection con;
DatabaseMetaData dbmtadta;
ResultSet rs;
int mtadtaint;
String procSchema;
String procName;
String dtfnList;
…
dbmtadta = con.getMetaData();     // Create the DatabaseMetaData object  1 
mtadtaint = dbmtadta.getDriverVersion();                                 2 
                                  // Check the driver version           
System.out.println("Driver version: " + mtadtaint);
rs = dbmtadta.getProcedures(null, null, "%");
                                  // Get information for all procedures
while (rs.next()) {               // Position the cursor                 3a 
 procSchema = rs.getString("PROCEDURE_SCHEM");
                                  // Get procedure schema
 procName = rs.getString("PROCEDURE_NAME");
                                  // Get procedure name
 System.out.println(procSchema + "." + procName);
                                  // Print the qualified procedure name
}
dtfnList = dbmtadta.getTimeDateFunctions();
                                  // Get list of supported datetime functions
System.out.println("Supported datetime functions:");
System.out.println(dtfnList);     // Print the list of datetime functions
rs.close();                       // Close the ResultSet                 3b