Newsletter sign-up

View all newsletters

Enterprise Java Newsletter
Stay up to date on the latest tutorials and Java community news posted on JavaWorld

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

Suppose you need to write a program that populates a table in a database with data from a text file. Say the table contains ticket information for an airline, including ticket number, date of purchase, date of travel, departure and arrival location, and the ticket's price. Each piece of information has a specific data type associated with it in the database, such as number, text, date, or currency. The program must read information for several such tickets from a text file, translate each piece of information to the appropriate data type in the database, and load the table in the database with this translated information. In order to perform such a translation, the program needs to know the database-specific type of each piece of information. A simple approach would be to hardcode the type conversion in your program -- treating data types as static information known at programming time, for example. By adopting this simple approach, you would need to write similar code in another program to populate a second table of the same database, such as a customer table with customer information. You can repeat a lot of boilerplate code from the previous program, but you would need to redo all the database-specific data-type-handling code.

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

How can you avoid all this rewriting? The code you write to populate the table of a database from a text file can make the database name, table name, and column names into parameters. The question is, how can you do the same for the data types, so that you can write a class, or collection of classes, just once to populate any set of tables in any database? The answer is to postpone the determination of data types until runtime, using the DatabaseMetaData interface in the java.sql package. By writing to this interface, you avoid the hardcoding of data types and develop generic, reusable code. The DatabaseMetaData interface provides metadata information for a database. Metadata is data that describes data. For instance, our airline database table contains ticketing information. This is data. In this scenario, metadata would contain information on things like the number of columns in the table, the data types of these columns, whether a column can have null values, and so on. This is data about data. The specific focus of this article is on the data types of columns. In the following discussion, I will show you how to develop a reusable library of three Java classes that are organized in layers, from closest to the database to farthest. The farthest layer from the database becomes the nearest layer to the application that uses the library. Although this library is meant for populating a database table, you can use the techniques illustrated in building it, as well as some of the code, to build libraries for data-type-independent database queries and updates.

Follow the scenario

Suppose you need to populate a table called emp in an organization database. Here is a sample data file that contains the table name on the first line, and a set of column names in the second. Every subsequent line contains row data for the specified columns.

emp  hiredate	sal	ename	empno
1996-09-01	1250.00	jackson	7123
1980-01-01	2500.50	walsh	7124
1985-01-01	12345.67	gates	7125

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

 

XpoLog Center Correlate application logs with server metrics - XpoLog

About Us | Advertise | Contact Us | Terms of Service/Privacy

Copyright, 2006-2008 Network World, Inc. All rights reserved.