Java Tip 82: Build data-type-independent JDBC applications

Use JDBC metadata and precompiled SQL to turn data into database data types

By Sesh Venugopal, JavaWorld.com, 11/05/99

Page 3 of 5

The following is the invocation of the getColumns method in the TableColumns constructor, where dbMeta is a DatabaseMetaData instance for a given database:

    ResultSet rset = dbMeta.getColumns(null, null, 
                    table.toUpperCase(), "%");

Notice that the values of the catalog name and schema pattern parameters are null. A null value indicates that a parameter should be dropped from the search criteria. Also note that we specifically send in the uppercase version of the given table name for the table name pattern. It is not clear whether this is a JDBC requirement or a JDBC driver preference. I spent some frustrating moments looking at all kinds of other bugs before I discovered that the uppercase version of the table name makes my code work. Finally, the catch-all pattern "%" is specified for the column name pattern. This all translates to: "Get ALL columns in the GIVEN table."

The java.sql.ResultSet returned by the getColumns method consists of one row per matching column. Each row consists of 18 descriptive fields, which are the result-set columns. The relevant fields for our class are the column name and the column type code, which are the fields numbered four and five, respectively. The column type code is a constant in the java.sql.Types class that represents the SQL type of the column. In this example, the getColumns invocation with emp for the table name would return a description of all eight columns, one per row of the result set. The type codes for the columns are extracted and stored in the array called columnTypeCodes for the TableMediator class to use. We'll examine this process next.

Layer 2: The TableMediator class

• It constructs a prepared statement (a precompiled SQL statement with parameters for which values are supplied at runtime) to populate a table row. The statement parameters correspond to the values of the active columns.
  
  
• It executes the prepared statement for a given set of active columns.

The TableMediator class uses the TableColumns class to get the complete column information for the given table, and then matches this information against the given set of active columns to arrive at the configuration illustrated in Figure 1.

The prepareRowInserts method constructs the required prepared statement. In this example, the prepared statement would look like this:

 
    insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
               values (?, ?, ?, ?, ?, ?, ?, ?)

All the columns in the table will be populated for every row, with values for the inactive columns (those that do not appear in the input file) set to null.

The first key to making your library portable is to use the DatabaseMetaData interface to discover column types, as seen in the TableColumns class. The second is to use the generic setObject method of the java.sql.PreparedStatement interface:

    ps.setObject(activeColumnOrder[i], columnValues[i]);

This line of code appears in the insertRow method of the TableMediator class, and ps is the PreparedStatement instance constructed earlier.