Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Gather Crucial JDBC Connection Information with Database Metadata : Page 2

JDBC database connections can provide useful database and ResultSet object information through metadata. Learn how to use JDBC's database-agnostic approach for gathering database metadata.


advertisement
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/NT Database 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: BIGINT LONG VARCHAR FOR BIT DATA VARCHAR () FOR BIT DATA CHAR () FOR BIT DATA LONG VARCHAR LONG VARGRAPHIC CHAR GRAPHIC DECIMAL INTEGER SMALLINT REAL DOUBLE VARCHAR VARGRAPHIC DATALINK DATE TIME TIMESTAMP DISTINCT BLOB CLOB DBCLOB

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap