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

As the number of tables that need to be populated increases, you'll need to write more code. But even this is only the case for a single database. If the data is ported to a different database system, you must rewrite all the existing code to account for the different data types in the new database. The issue of cross-database data typing has earned well-deserved notoriety in database programming.

Create reusable code

Follow the scenario

While all input data is ASCII text, the data in these columns will be translated to the following database-specific types:

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.

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.

The setObject method takes two parameters: the first is the parameter position in the associated prepared statement, and the second is the value of this parameter. (Recall that these parameters stand for columns in a table.) In general, the second parameter can be any type of object; in this code, it is a string. The JDBC driver is responsible for converting this string into the appropriate database type.

In this statement, we have not used the column types discovered earlier. However, we do need the column types for values that are null -- to populate a column for which no value is supplied, for example. In that case, we would use a supporting setNull method in the PreparedStatement interface. Here is how it is used in the insertRow method of the TableMediator class:

    ps.setNull(i, tc.columnTypeCodes[i]);

This statement sets the value of prepared statement parameter i to null, but this setting requires that the type of the corresponding column be sent as the additional, second parameter.

Layer 3: The TableBuilder class

The TableBuilder class encapsulates the following functionality:

• It reads the table name from the first line of the text file, and creates an instance of the TableMediator class
  
  
• It reads the column names from the second line of the text file, and asks the TableMediator instance to set the active column list to these columns
  
  
• It asks the TableMediator instance to prepare for inserting rows into the table
  
  
• It reads the column data from each subsequent line of the text file, and asks the TableMediator instance to insert this data into the next row of the table

SUBHEAD_BREAK: Using the tablebuild library Given the tablebuild library, it is very easy to write an application that populates the table of a database. The following is such a sample application.

  
    import java.sql.*; 
    import java.io.*; 
    import tablebuild.*;
    public class PopulateTable 
        {
        public static final String usage = "usage: java PopulateTable " +
                       "<tabledatafile>";
        public static void main(String[] args)
        throws SQLException, ClassNotFoundException, IOException
        {             
            if (args.length != 1) {
                System.err.println(usage);
                System.exit(1);
            }
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection(
                        "jdbc:odbc:orgdb");
                  
            BufferedReader br = new BufferedReader(
                        new FileReader(args[0]));
            // tab-delimited input data file
            TableBuilder tb = new TableBuilder(conn, br, "\t");
            tb.buildTableInfo();
            tb.buildActiveColumns();
            tb.buildTable();
        }
        }

The example uses the JDBC-ODBC bridge to drive a Microsoft Access database, whose data source is named orgdb. I chose to illustrate this bridge mainly because it serves as a reference implementation for JDBC drivers for most JDBC capabilities.

To complete the example, you can run the above application to populate the emp table of the orgdb data source. If the input file name is empfile, you run the application as follows:

java PopulateTable empfile 

This populates the hiredate, sal, ename, and empno columns with the given data, translating each input data value to the required database-specific type.

If you have a new data source, you don't need to modify any of the tablebuild classes or the application, so long as the same input format is maintained -- that is, the first line contains the table name, the second line contains the column names, and every subsequent line contains the data for all the columns of some row. The column delimiters can be anything, since they are set as a parameter to the TableBuilder constructor.

If the database itself changes, all you need to do is change the lines of code in the application in which the connection to the database is established. Again, none of the tablebuild classes need to be touched; the TableBuilder constructor is simply passed the new connection as a parameter.

Test your JDBC driver

There is one important caveat: the ability to use dynamic type discovery, as well as type translation, ultimately depends on the capabilities of the JDBC driver you use. For instance, some JDBC drivers may not support the type-translation capability of the PreparedStatement's setObject method. Also, different JDBC drivers implement the DatabaseMetaData interface to different extents. As a general rule in JDBC programming, when capabilities promised by the JDBC specifications do not materialize, suspect the JDBC driver. More often than not, a good driver that is JDBC-compliant should solve the problem, overcoming apparent limitations in portability or capability.

Author Bio

Free Download - 5 Minute Product Review. When slow equals Off: Manage the complexity of Web applications - Symphoniq

Free Download - 5 Minute Product Review. Realize the benefits of real user monitoring in less than an hour. - Symphoniq