Java Enterprise in a Nutshell (In a Nutshell (OReilly))
8.8. Metadata
Most JDBC programs are designed to work with a specific database and particular tables in that database; the program knows exactly what kind of data it is dealing with. Some applications, however, need to dynamically discover information about result set structures or underlying database configurations. This information is called metadata , and JDBC provides two classes for dealing with it: DatabaseMetaData and ResultSetMetaData. If you are developing a JDBC application that will be deployed outside a known environment, you need to be familiar with these interfaces. 8.8.1. DatabaseMetaData
You can retrieve general information about the structure of a database with the java.sql.DatabaseMetaData interface. By making thorough use of this class, a program can tailor its SQL and use of JDBC on the fly to accommodate different levels of database and JDBC driver support. Database metadata is associated with a particular connection, so DatabaseMetaData objects are created with the getMetaData( ) method of Connection: DatabaseMetaData dbmeta = con.getMetaData( );
DatabaseMetaData provides an overwhelming number of methods you can call to get actual configuration information about the database. Some of these return String objects (getURL( )), some return boolean values (nullsAreSortedHigh( )), and still others return integers (getMaxConnections( )). A number of other methods return ResultSet objects. These methods, such as getColumns( ), getTableTypes( ), and getPrivileges( ), generally encapsulate complex or variable-length information. The getTables( ) method, for instance, returns a ResultSet that contains the name of every table in the database as well as a good deal of extra information. Many of the DatabaseMetaData methods take string patterns as arguments, allowing for simple wildcard searching. A percent sign (%) substitutes for any number of characters, and an underscore (_) calls for a single character match. Thus, %CUSTOMER% matches NEW_CUSTOMERS, CUSTOMER, and CUSTOMERS, while CUSTOMER% matches only CUSTOMER and CUSTOMERS. All of these patterns are case-sensitive. 8.8.2. ResultSetMetaData
The ResultSetMetaData interface provides information about the structure of a particular ResultSet. Data provided by ResultSetMetaData includes the number of available columns, the names of those columns, and the kind of data available in each. Example 8-3 is a short program that displays the contents of a table and shows the data type for each column. Example 8-3. TableViewer program
import java.sql.*; public class TableViewer { final static String jdbcURL = "jdbc:oracle:customerdb"; final static String jdbcDriver = "oracle.jdbc.driver.OracleDriver"; final static String table = "CUSTOMERS"; public static void main(java.lang.String[] args) { System.out.println("--- Table Viewer ---"); try { Class.forName(jdbcDriver); Connection con = DriverManager.getConnection(jdbcURL, "", ""); Statement stmt = con.createStatement( ); ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table); ResultSetMetaData rsmd = rs.getMetaData( ); int columnCount = rsmd.getColumnCount( ); for(int col = 1; col <= columnCount; col++) { System.out.print(rsmd.getColumnLabel(col)); System.out.print(" (" + rsmd.getColumnTypeName(col)+")"); if(col < columnCount) System.out.print(", "); } System.out.println( ); while(rs.next( )) { for(int col = 1; col <= columnCount; col++) { System.out.print(rs.getString(col)); if(col < columnCount) System.out.print(", "); } System.out.println( ); } rs.close( ); stmt.close( ); con.close( ); } catch (ClassNotFoundException e) { System.out.println("Unable to load database driver class"); } catch (SQLException e) { System.out.println("SQL Exception: " + e.getMessage( )); } } }
The key methods used here are getColumnCount( ), getColumnLabel( ), and getColumnTypeName( ). Note that type names returned by getColumnTypeName( ) are database-specific (e.g., Oracle refers to a string value as a VARCHAR; Microsoft Access calls it TEXT). Here's some sample output for TableViewer: --- Table Viewer --- CustNo (SHORT), CustName (VARCHAR), CustAddress (VARCHAR) 1, Jane Markham, 12 Stevens St 2, Louis Smith, 45 Morrison Lane 3, Woodrow Lang, 4 Times Square
|