Data Management

Use JDBC to examine the structure of a database

The next time you need to programmatically obtain information about the structure of your database, remember that JDBC has a solution. Follow these examples and learn about two objects that will allow you to examine your database's structure.

No matter what development hats you wear, you're bound to run into situations when you need information about the structure of the database you are working with. Luckily, Java's database access API, Java Database Connectivity (JDBC), provides two objects you can use to examine a database's structure. In this article, I'll introduce you to these two objects.

JDBC uses the term metadata to refer to information that describes a database and the objects it contains. To examine a database or database object's metadata, you use the DatabaseMetaData or ResultSetMetaData object. The former retrieves information about the capabilities of the database system, while the latter gives you information about the structure of a particular database object. Let's examine DatabaseMetaData first.

What's under the hood?
The DatabaseMetaData object can retrieve information about the database system to which you are connected and the capabilities of the driver that is handling your connection. Here is some of the more important information you can retrieve using this object:
  • The getMaxConnections method returns the maximum number of connections supported by the database.
  • The getNumericFunctions method returns a comma-delimited list of all the numeric functions usable in a Statement.
  • The getCatalogs method retrieves a ResultSet listing of all the catalogs found in the database.
  • The getTables method returns a list of all tables found in a catalog; getProcedures returns a list of all stored procedures.
  • The getSchemas method retrieves information about the schemas used by the database.
  • The supportsOuterJoins method indicates whether the database supports outer join queries.
  • The nullsAreSortedHigh, nullsAreSortedLow, nullsAreSortedAtStart, and nullsAreSortedAtEnd methods describe how the database handles null values when sorting rows.

The dbBrowser application uses the DatabaseMetaData object to retrieve a handful of these properties in the ConnectButtonActionEventPerformed method of the MainFrame class. This method handles ActionEvent events from ConnectButton.

First, the code connects to the specified database and opens a DatabaseMetaData object using the Connection's getMetaData method. It then retrieves the names of all catalogs in the database, the name and version of the database system, the maximum number of connections supported, and a list of all numeric functions natively supported by the database.

Let's see what you're made of
The ResultSetMetaData object examines the structure of a ResultSet. It's not important how the ResultSet was created. It could be the result of calling a stored procedure, executing a query, or executing a SQL statement. You call ResultSet's getMetaData method to create a ResultSetMetaData object and then use the methods of this object to retrieve information like:
  • The number of columns in the set using getColumnCount.
  • The name of the catalog (getCatalog), table (getTable), and schema (getSchema) associated with the object that generated the ResultSet.

ResultSetMetaData supports a variety of methods for describing the columns in a set. These methods all accept the index of the column as an argument. Remember that unlike array indexes, column indexes are one-based; otherwise, you'll wind up in exception city. Some of the column properties that can be retrieved include:
  • The SQL type of the column, either as a numeric identifier (getColumnType) or as descriptive name (getColumnTypeName).
  • Whether the column accepts Null values (isNullable).
  • The preferred display name of the column (getColumnLabel) and its internal name (getColumnName).
  • Whether a column can be used in a SQL WHERE clause (isSortable).
  • For numeric fields, the number of significant digits to the right of the decimal (getPrecision) and to the left of the decimal (getScale).
  • Whether a column is case sensitive (isCaseSensitive).

MainFrame.FieldsListValueChanged handles ListSelectionEvents from FieldList. After opening a connection to the specified database, the code opens a ResultSet by executing a SELECT * statement on the table selected in TableCombo. It then opens a ResultSetMetaData object from the resulting ResultSet and retrieves a handful of properties about the column selected in FieldList.

Because of its generic nature, JDBC can't tell you everything there is to know about your database system. It can, however, provide you with a lot of useful information about your system. If your application must be able to work with multiple database back ends, this metadata can prove to be invaluable.





Editor's Picks