RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Gather Crucial JDBC Connection Information with Database Metadata : Page 3

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.

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: 0
FIRSTNAME: VARCHAR -  Precision: 30,  Scale: 0
LASTNAME: VARCHAR -  Precision: 30,  Scale: 0
SALARY: 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.

Boolean isCaseSensitive(int column)
Indicates whether a column's case matters
Boolean isCurrency(int column)
Indicates whether the designated column is a cash value
Int isNullable(int column)
Indicates the nullability of values in the designated column
Boolean isSearchable(int column)
Indicates whether the designated column can be used in a where clause
Table 1. Relevant Methods 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.

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing J2EE-centric solutions at customer sites across the nation.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date