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 2 of 5

The structure of emp is shown in the top row of Figure 1.

Figure 1. Mapping from input columns to database table columns

Not all columns in a table need to be populated at once. In this example, only those columns marked as true are to be populated; the active columns are empno, ename, hiredate, and sal.

Also, the order in which the columns appear in the input file need not be the same as the order in which they are arranged in the database table. An index array maintains a mapping from the columns in the input to their respective positions in the table. In this example, the active column order array is the index array. If i is the position of a column in the input file (hiredate is 0, sal is 1, and so forth), the activeColumnOrder[i] is the position of this column in the table (activeColumn[0] is 5, which means hiredate is the fifth column in the emp table).

SUBHEAD_BREAK: Building library class layers Our library consists of three layers:

• A TableColumns class, closest to the database, which discovers and manages table column information
  
  
• A TableMediator class, which prepares for populating the table using information managed in TableColumns
  
  
• A TableBuilder class, farthest from the database and thus closest to the application, which reads the data from input and uses the TableMediator class to populate the given table.

Figure 2 illustrates this layering.

Figure 2. Application layers

These classes are all collected into a package called tablebuild.

The Java source files for the tablebuild package are available in the Resources section below. In order to avoid clutter, only relevant portions of the code appear in this article.

Layer 1: The TableColumns class

    public abstract ResultSet getColumns(String catalog,
                                  String schemaPattern,
                                  String tableNamePattern,
                                  String columnNamePattern)
        throws SQLException

The getColumns method takes four parameters: a catalog name, a schema name, a table name, and a column name. Of these, the last three, which carry the Pattern suffix (see the code above), are interesting in that they let you search for all target strings that match a pattern expression. In other words, you specify search criteria through these parameters. This is what is being said:

Find all columns that satisfy the following criteria: they belong to the given catalog, to ANY of the schemas that match the given schema pattern, and to ANY of the tables that match the given table name pattern. Their names also match the given column name pattern.

The pattern is specified with syntax similar to that which is applied in SQL statements, in which the LIKE phrase is used to match names. In particular, the underscore character (_) in a pattern expression matches any character of a target string, and the percent character (%) matches any number of consecutive characters in a target string. For instance, the pattern expression j_b would match the target strings "job" and "jab," while the pattern expression j%b would match any target string starting with "j" and ends with "b," with any number (including zero) of intervening characters.