Database Metadata

The DatabaseMetaData interface is implemented by JDBC drivers to provide
information about their underlying data sources. It is used primarily by application servers and tools to determine how to interact with a given data source. Applications may also use DatabaseMetaData methods to get information about a data source, but this is less typical.
The DatabaseMetaData interface includes over 150 methods, which can be categorized according to the types of information they provide:

• general information about the data source
• whether or not the data source supports a given feature or capability
• data source limits
• what SQL objects the data source contains and attributes of those objects
• transaction support offered by the data source

The DatabaseMetaData interface also contains over 40 fields, which are constants used as return values for various DatabaseMetaData methods. This chapter presents an overview of the DatabaseMetaData interface, gives examples to illustrate the categories of metadata methods, and introduces some new methods. For a comprehensive listing, however, the reader should consult the JDBC

API specification.

JDBC also defines the ResultSetMetaData interface, which is discussed in “Result Sets”.

Creating a DatabaseMetadata Object

A DatabaseMetaData object is created with the Connection method getMetaData. Once created, it can be used to dynamically discover information about the underlying data source. CODE EXAMPLE creates a DatabaseMetadata object and uses it to determine the maximum number of characters allowed for a table name.

// con is a Connection object
DatabaseMetaData dbmd = con.getMetadata();
int maxLen = dbmd.getMaxTableNameLength();

CODE EXAMPLE Creating and using a DatabaseMetadata object

Retrieving General Information

Some DatabaseMetaData methods are used to dynamically discover general
information about a data source as well as some details about its implementation.
Some of the methods in this category are:

• getURL
• getUserName
• getDatabaseProductVersion, getDriverMajorVersion and getDriverMinorVersion
• getSchemaTerm, getCatalogTerm and getProcedureTerm
• nullsAreSortedHigh and nullsAreSortedLow
• usesLocalFiles and usesLocalFilePerTable
• getSQLKeywords

Determining Feature Support

A large group of DatabaseMetaData methods can be used to determine whether a given feature or set of features is supported by the driver or underlying data source. Beyond this, some of the methods describe what level of support is provided. Some of the methods that describe support for individual features are:

• supportsAlterTableWithDropColumn
• supportsBatchUpdates
• supportsTableCorrelationNames
• supportsPositionedDelete
• supportsFullOuterJoins
• supportsStoredProcedures
• supportsMixedCaseQuotedIdentifiers

Methods to describe a level of feature support include:

• supportsANSI92EntryLevelSQL
• supportsCoreSQLGrammar

Data Source Limits

Another group of methods provides the limits imposed by a given data source. Some of the methods in this category are:

• getMaxRowSize
• getMaxStatementLength
• getMaxTablesInSelect
• getMaxConnections
• getMaxCharLiteralLength
• getMaxColumnsInTable

Methods in this group return the limit as an int. A return value of zero means that there is no limit or the limit is unknown.

SQL Objects and Their Attributes

Some DatabaseMetaData methods provide information about the SQL objects that populate a given data source. This group also includes methods to determine the attributes of those objects. Methods in this group return ResultSet objects in which each row describes a particular object. For example, the method getUDTs returns a ResultSet object in which there is a row for each UDT that has been defined in the data source. Examples of this category are:

• getSchemas and getCatalogs
• getTables
• getPrimaryKeys
• getProcedures and getProcedureColumns
• getUDTs

Transaction Support

A small group of methods provides information about the transaction semantics
supported by the data source. Examples of this category include:

• supportsMultipleTransactions
• getDefaultTransactionIsolation

New Methods

The JDBC 3.0 API introduces the following new DatabaseMetaData methods:

• getSuperTypes — returns a description of the user-defined type hierarchies defined in a given schema in the underlying data source
• getSuperTables — returns a description of the table hierarchies defined in a given schema in the underlying data source
• getAttributes — returns a description of user-defined type attributes available from a given catalog in the underlying data source
• getSQLStateType — returns the type of SQLSTATEs that will be returned by the method SQLException.getSQLState, described in “SQLException”.
  
• supportsSavepoints — returns true if the driver or underlying data source supports savepoints, described in “Savepoints” .
  
• supportsNamedParameters — returns true if the driver or underlying data source supports named parameters for CallableStatement objects, described in “Setting Parameters”.
  
• supportsMultipleOpenResults — returns true if the driver or underlying data source supports multiple open result sets for CallableStatement objects, described in “Returning Unknown or Multiple Results”.
  
• supportsGetGeneratedKeys — returns true if the driver or underlying data source supports the retrieval of automatically generated keys, described in “Retrieving Auto Generated Keys” A complete definition of these methods may be found in the JDBC 3.0 API specification (javadoc).
  

Modified Methods

The JDBC 3.0 API modifies the definitions of these existing DatabaseMetaData
methods, adding support for type hierarchies:

• getTables — returns descriptions of the tables that match the given catalog, schema, table name, and type criteria
• getColumns — returns descriptions of the columns that match the given catalog, schema, table name, and column name criteria
• getUDTs — returns descriptions of the user-defined types that match the given catalog, schema, type name, and type criteria
• getSchemas — now returns catalog for each schema as well as the schemata. The JDBC 3.0 API specification includes updated definitions of these methods.