hen developers connect to a database with JDBC, they receive a plethora of information about the database and any ResultSet objects that they might create using the database connection. This data, provided as database metadata, has been around since JDBC 1.0 and it can take different forms.

This article explores the information JDBC metadata provides by demonstrating how to create an IBM DB2 database and then invasively peek into it via Java. It focuses on two forms of metadata: one describing a given database connection, the other describing a ResultSet object. Each of these forms will have an associated Java interface: the java.sql.DatabaseMetaData interface, which provides information about the database connection, and the ResultMetaData interface, which provides information about the ResultSet object.

Some database veterans may opt for querying a database server’s system catalog tables to retrieve some of the information that they can discover via the DatabaseMetaData and ResultSetMetaData interfaces. However, this option ties them to a specific database, as each database server has a different set of system catalog tables. JDBC enables a database-agnostic approach to gathering database metadata information.

Set Up a DB2 Laboratory
As this article uses IBM DB2 Universal Database as the underlying database, your first step is downloading a trial version of IBM DB2 Universal Database Version 8.1. Of course, the methodologies for acquiring information via the DatabaseMetaData and ResultSetMetaData interfaces apply for any other JDBC-compliant database you might use.

Begin by creating a DB2 database using the DB2 Command Line Processor. Create a database named EMPDB to store employee data:

db2 => create db EMPDB

Next, connect to your database (assuming you have a user named db2admin with a password of db2admin):

db2 => connect to EMPDB user db2admin using db2admin

Next, create a table called EMPLOYEE, which will store some information about your imaginary employees:

db2 => create table EMPLOYEE(SSN integer not null primary key,
     FIRSTNAME varchar(30) not null, LASTNAME varchar(30) not null,
     SALARY decimal (8,2) NOT NULL)

Now, populate your EMPLOYEE table with some sample records:

db2 => insert into EMPLOYEE values (123456789,'James','Smith',79345.00)db2 => insert into EMPLOYEE values (123456790,'Bob','Jeffries',65321.23)db2 => insert into EMPLOYEE values (123456791,'Amy','Keppler',49998.56)

The DatabaseMetaData Interface
Through your java.sql.DatabaseMetaData interface you can obtain a huge amount of information about any database connection you establish. You can create a DatabaseMetaData object with the following call:

DatabaseMetaData databaseMetaData  = connection.getMetaData();

From your DatabaseMetaData object, you can use methods that the JDBC API provides to get information about your database connection. This article doesn’t cover every method, as the API offers more than 150. It covers just a few to whet your appetite. (The MetaDataStudy class contains all the code associated with this article.)

In many setups, developers might not even see the database they are accessing. From the DatabaseMetaData object, you can gather information about the database with which you are working:

String databaseProductName =databaseMetaData.getDatabaseProductName();String databaseProductVersion =	databaseMetaData.getDatabaseProductVersion();System.out.println("Database Product Name: " + databaseProductName);System.out.println("Database Product Version: " + databaseProductVersion);

The code above informs you about which database product and version you are using. In my case, I got back this response:

Database Product Name: DB2/NTDatabase Product Version: SQL08020

Your DatabaseMetadata object can also provide information about your database’s internals. For example, the following code retrieves the maximum number of characters allowed for database columns and tables, respectively:

int maximumColumnNameChars = databaseMetaData.getMaxColumnNameLength();System.out.println("Max number of chars for a column name: " + maximumColumnNameChars);int maximumTableNameChars = databaseMetaData.getMaxTableNameLength();System.out.println("Max number of chars for a table name: " + maximumTableNameChars);

All database systems are not created equal. The DatabaseMetaData object provides information about which database types (VARCHAR, INTEGER, BLOB, etc.) the database you have established a connection to supports:

System.out.println("Supported Database Types:");while (databaseTypes.next()){String typeName = databaseTypes.getString("TYPE_NAME");	System.out.println(typeName);}

For my connection, the object told me DB2 supported the following database types:

Supported Database Types:BIGINTLONG VARCHAR FOR BIT DATAVARCHAR () FOR BIT DATACHAR () FOR BIT DATALONG VARCHARLONG VARGRAPHICCHARGRAPHICDECIMALINTEGERSMALLINTREALDOUBLEVARCHARVARGRAPHICDATALINKDATETIMETIMESTAMPDISTINCTBLOBCLOBDBCLOB

If you are connecting to a different database, your list of supported database types might differ.

The DatabaseMetaObject can also tell you if your database and the database driver you’re using support advanced operations. For example, some databases and JDBC drivers can support batch updates while some cannot. (Batch updates allow you to group multiple update statements and send them to the database server to be processed as a batch.) With the following code, I can find out whether DB2 Version 8.2 and the JDBC driver that ships with it support batch updates:

databaseMetaData.supportsBatchUpdates()

The DatabaseMetaData interface’s supportsBatchUpdates() method simply returns a boolean value of true or false, telling you if your database connection supports batch updates. If the suspense is killing you, yes, the DB2 Universal JDBC driver that ships with DB2 Version 8 supports batch updates.

The DatabaseMetaData interface contains a number of other supportsXXXXXX methods for a developer to verify database facility support. I find these methods to be particularly useful and informative, as digging into the thousands of pages of your database server’s manual to figure out if the JDBC driver you are using supports save points or not isn’t very fun (if you ever do find the support information).

With the getPrimaryKeys method of your DatabaseMetaData object, you can discover what the primary key(s) are for a given table. You can narrow your search by providing a catalog name and a schema name (which the code below does not do) as the first two parameters. The third parameter of the method represents the name of the table, whose primary key you are trying to find. What you get back is a ResultSet object, which contains descriptions about the primary key(s) of the table being scrutinized. Each primary key column description has a number of columns, one of which, called COLUMN_NAME, contains the name of the primary key column.

The following code programmatically discovers the primary key column name of the EMPLOYEE table, the SSN column:

ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null,"EMPLOYEE");while (primaryKeys.next()){String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");System.out.println("Primary Key Column: " + primaryKeyColumn);}

The DatabaseMetadata object documentation offers a lot more information than I just covered.

The ResultSetMetaData Interface
Through your java.sql.ResultMetaData interface, you can obtain information about a ResultSet object returned via execution of a query. This information includes the types of columns and the column names resident in a ResultSet object. The following code shows the creation of a ResultSetMetaData object using the getMetaData method of a ResultSet object that was created by performing a “select * from EMPLOYEE” query:

Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery("select * from EMPLOYEE");ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

The payload of your ResultSetMetaData object contains a large amount of information about your underlying ResultSet object. Again, I won’t cover everything you can get from the ResultSetMetaData object, I’ll highlight just some of the information you can ascertain. You can always reference the java.sql.ResultSetMetaData API to see all of the information you can get.

The following code showcases some of the facilities the ResultSetMetaData API provides:

int numColumns = resultSetMetaData.getColumnCount();for (int i=1; i<(numColumns+1); i++){String columnName = resultSetMetaData.getColumnName(i);	String columnTypeName = resultSetMetaData.getColumnTypeName(i);	int columnPrecision = resultSetMetaData.getPrecision(i);	int columnScale = resultSetMetaData.getScale(i);	System.out.println(columnName + ": " +  columnTypeName + " - " 		+ " Precision: " + columnPrecision + ", " + " Scale: " 		+ columnScale); }

First, it grabs the number of columns housed in the ResultSet object with the getColumnCount method. This in turn allows you to see what the column names, column data types, column precisions, and column scales are for each column housed in your ResultSet object. This information is ascertained by using the getColumnName, getColumnTypeName, getColumnPrecision, and getColumnScale methods, respectively. Each of these methods takes in an integer argument, which specifies which column in the ResultSetMeta is being studied. A column’s precision refers to the designated column’s number of decimal digits, and a column’s scale refers to the designated column’s number of digits to the right of the decimal point.

The code above produces the following output:

SSN: INTEGER -  Precision: 10,  Scale: 0FIRSTNAME: VARCHAR -  Precision: 30,  Scale: 0LASTNAME: VARCHAR -  Precision: 30,  Scale: 0SALARY: DECIMAL -  Precision: 8,  Scale: 2

Developers might use programmatic acquisition of column information like this in a graphical application to help build customized GUIs on the fly. For example, using the information above, I can build a GUI at runtime that has the appropriate text field sizes for first and last name entries. If a database administrator changes the underlying database table that my GUI feeds data to (e.g., by expanding or contracting the precision of my columns), I don’t have to modify my application’s GUI if I built it dynamically using precision data gathered from my ResultSetMetaData object.

As exemplified in the previous code, the ResultSetMetaData object exposes various methods, which let you discover the structure of your underlying ResultSet’s columns. Covering all the available methods is beyond the scope of this article, but Table 1 singles out some methods that might be particularly interesting when studying a ResultSet via its ResultSetMetaData.

As stated before, you can study all the offerings of the ResultSetMetaData interface by studying its API documentation.

JDBC Metadata Is Packed with Info
You should now feel much more comfortable with using the DatabaseMetaData and ResultSetMetaData classes to collect metadata. JDBC metadata lets you gather a lot of useful information. However, rather than providing information about regular Java objects as the Java reflection API does, the MetaData classes let you discover information about a JDBC database connection and the ResultSet objects you may have created using your connection. Unlike Java reflection, gathering metadata is not time consuming. Also, it does not place stress on the database server. In fact, you can use the metadata APIs you learned about in this article with disconnected objects like the FilteredRowSet object.