Database Metadata

Different RDBMS databases in combination with the database-specific JDBC drivers usually support, and implement features differently. It also supports different SQL data types. An application that is used with different databases would be required to obtain database-specific information. For example, an application could be required to retrieve information about all the SQL data types, which are being supported with a database. An application that implements batch updates would be required to find out if a database supports batch updates. The DatabaseMetaData interface represents the database metadata. The database metadata is obtained from the Connection object:

DatabaseMetaData metadata = currentConnection.getMetaData();

The SQL data type supported by a database can be obtained using the getTypeInfo() method:

ResultSet resultSet=metadata.getTypeInfo();

To find out if a database supports batch update, invoke the supportsBatchUpdates() method:

metadata.supportsBatchUpdates();

To find out if a database supports transactions, invoke the supportsTransactions() method, and to find out if a database supports savepoints, invoke the supportsSavepoints() method:

metadata.supportsTransactions();
metadata.supportsSavepoints();

Support for a ResultSet type can be checked using the supportsResultSetType() method, while support for a concurrency type, in combination with a result set type, can be checked with the supportsResultSetConcurrency() method. Support for a result set holdability can be checked with the supportsResultSetHoldability() method:

metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
metadata.supportsResultSetConcurrency(ResultSet. TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
metadata.supportsResultSetHoldability(ResultSet. CLOSE_CURSORS_AT_COMMIT);

The database metadata also includes information about the different SQL clauses supported by the database. Support for the GROUP BY clause is checked with the supportsGroupBy() method; support for SELECT FOR UPDATE is checked with the supportsSelectForUpdate() method; support for UNION clause is checked with the supportsUnion() method; support for ALTER TABLE with add column is checked with the supportsAlterTableWithAddColumn() method; and support for mixed case SQL identifiers is checked with the storesMixedCaseIdentifiers() method. Also, the maximum number of columns that can be specified in a SELECT statement is obtained with the getMaxColumnsInSelect() method.

The database metadata also provides information about the JDBC driver and the database. The database product name, the database major version, the driver major version, the driver name, the driver version, and the JDBC major version supported by the driver are obtained as follows:

String database=metadata.getDatabaseProductName();
int databaseMajorVersion=metadata.getDatabaseMajorVersion();
int driverMajorVersion=metadata.getDriverMajorVersion();
String driverName=metadata.getDriverName();
int driverVersion=metadata.getDriverVersion();
int jdbcMajorVersion=metadata.getJDBCMajorVersion();

Metadata about a database table is obtained with the getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types) method. The parameter, catalog, is a catalog name in the database. SchemaPattern is the Schema pattern. TableNamePattern is the table name pattern and the types represents the table type. Table types include TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPERORY, LOCAL TEMPERORY, ALIAS, and SYNONYM. Obtain all the tables of type, TABLE:

String[] names = {"TABLE"};
ResultSet tables = metadata.getTables(null,"%", "%", names);

Obtain the table name and table schema from the table's metadata:

while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
String tableSchema = tables.getString("TABLE_SCHEM");
}

Metadata about the columns can be obtained with the getColumns(String catalog,String schemaPattern,String tableNamePattern,String columnNamePattern) method. Obtain the column's metadata for the table name obtained from the table's metadata:

ResultSet columns = metadata.getColumns(null, "%", tableName, "%");

Obtain the column name, column type, column size, and column nullable:

while (columns.next()) {
String columnName = columns.getString("COLUMN_NAME");
String datatype = columns.getString("TYPE_NAME");
int datasize = columns.getInt("COLUMN_SIZE");
int nullable = columns.getInt("NULLABLE");
}

The procedures in the database can be obtained from the getProcedures(String catalog,String schemaPattern, String procedureNamePattern) method:

ResultSet procedures=metadata.getProcedures(null,"%", "%");

Obtain the procedure name, procedure type, and procedure schema:

while (procedures.next())
{
String procedureName = procedures.getString("PROCEDURE_NAME");
String procedureSchema = procedures.getString("PROCEDURE_SCHEM");
String procedureType = procedures.getString("PROCEDURE_TYPE");
}

In JDBC 4.0, the methods discussed in the following table have been added to the DatabaseMetaData interface:

The getSchemas() method in the DatabaseMetaData interface has been overloaded to support a catalog name and a schema pattern.