Data Management

Extract XML/DOM-friendly data from a JDBC-compliant database

Java can programmatically extract data from any JDBC-compliant database, but performing this task so that both XML documents and DOM objects can use the data is a bit tricky. See how to create a Java interface to produce XML- and DOM-friendly ResultSets.


The current version of Java includes all the tools to translate the output from a relational database query into XML as a Document Object Model (DOM) document object, which can then be easily translated using the power of Extensible Style Language (XSL) into any number of output formats. This article will show you how to build an interface that can read any JDBC-compatible relational database, creating a ResultSet that can later be translated into almost any type of character output.

The interface
To begin, you must define an interface that, when implemented, will handle the details of establishing a database connection, passing a SQL query to it, reading the results, and creating a DOM document object. The DataBaseHandler interface shown below defines a desired behavior for handling or dealing with a database. (Note: This example lacks import statements and, therefore, won't compile.) Specifically, the document returned by a getDocument() query will be transformed to the desired output type by other processes independent of the database.
public interface DataBaseHandler{

Document getDocument(String sqlQuery);
String getUrlString();
void setUrlString(String urlString);
String getUserName();

void setUserName(String userName);
String getPassword();
void setPassword(String password);
String getDriverName();
void setDriverName(String driverName);
}

This is the minimum behavior required to do the job of handling or interfacing with a database. It includes several get and set methods for ensuring that the database driver name, username, and password are set or can be later retrieved. The DataBaseHandler needs are summarized below:
  • urlString
  • userName
  • password
  • sqlQuery string
  • driverName

The getDocument() method does all the real work after the set methods are invoked:
Document getDocument(String sqlQuery);

All the inputs passed to the set methods could have been passed to the getDocument() method. From a reusability standpoint, you should separate the methods so that the implementing classes can simply and flexibly take advantage of this multiple input-passage functionality.

An abstract implementation of the interface
Next, an abstract class is defined that implements most of the behavior and actions required, with the notable exception of those behaviors needed for a specific database type. The AbstractDataBaseHandler performs the generic actions of the set and get methods and, once all database-handling parameters have been set, AbstractDataBaseHandler performs the generic actions of interacting with the database. The only details left are the specifics of a given database type, as well as the setup that must be defined to obtain a concrete implementation of the DataBaseHandler.

The get and set methods set the private class variables that will vary with the database handler object created by extending AbstractDataBaseHandler. This process is straightforward, as Listing A demonstrates.

The actions in the getDocument() method are in a try catch block in case any errors occur during the database transaction or any of the required input parameters are not set. It is worth noting that a SQLException could have multiple exceptions chained together. You must iterate through the SQLException to see the full set of problems that may occur when interacting with databases.
} catch (SQLException sqle) {
while(sqle != null) {
System.err.println ("**** SQLException ****\n");
System.err.println ("** SQLState: " + sqle.getSQLState() + "\n");
System.err.println ("** Message: " + sqle.getMessage() + "\n");
System.err.println ("** Error Code: " + sqle.getErrorCode() + "\n");
System.err.println ("***********\n");
/* There could be multiple exception messages chained together. */
sqle= sqle.getNextException();
}

As with any exception, a Java logging implementation of exception handling could have been used to log any problems that occur. It is important to ensure that all possible exceptions are included in the try catch block so that appropriate action is taken to notify to the user of any problem occurring during execution. The list below summarizes the information that a SQLException contains:
  • A string describing the error
  • A SQLstate string
  • An integer error code that is specific to each vendor
  • A chain to a next exception

The getDocument() method first checks for all required parameters or throws an exception:
if(getUrlString() == null){
throw new Exception("Database URL String is not set.");
}
if(getUserName() == null){
throw new Exception("Database User Name is not set.");
}
if (getPassword() == null){
throw new Exception("Database Password is not set.");
}
if (getDriverName() == null){
throw new Exception("Database Driver Name is not set.");
}

A SQL query string is passed as input. This can be any SQL string desired, such as:
SELECT * from mytable;
SELECT overdue, name, duedate, description from myactiontable;

Next, an instance of a JDBC driver is created, which automatically registers the driver with the DriverManager:
Class.forName(getDriverName());

The DriverManager can now create a database connection with the registered driver and handle all the database work.

The URL connection string varies from database to database. For an Oracle database, this is a string of the form:
host:port:sid

The exact values required are defined in the tsnames configuration file, called tnsnames.ora, which has entries that look like this:
mydb =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(Host = SERVERX.www.mycompany.com)
(Port = 1521)
)
(CONNECT_DATA =
(SID = examplesid)
(GLOBAL_NAME = exampledb)
)
)

A Connection object is created from a URL string, username, and password set previously. A Statement object is created using the Connection object and executes the query on the database returning a ResultSet object. The details are handled seamlessly by JDBC.

The statement execution may have no return values to work with, depending on the SQL statement used. This class checks for an absence of returned values and adds Element nodes to the document only when there is at least one row of data returned. Column names are retrieved from a ResultSetMetaData object obtained through a get method of ResultSet:
ResultSetMetaData rsmd = resultSet.getMetaData();

The cursor or row number of the ResultSet is iterated over the whole ResultSet in a while loop, and a DOM document object is built simultaneously.
while(resultSet.next()){
// add Element and TextNode objects which define each row and each data column.
}

Processing the ResultSet and its metadata completes the interaction with the database. From here, you can employ XML/DOM objects to house, and XSL to format, the data.

End of the beginning
This article has described a process of creating an interface that defines a generic behavior for a DataBaseHandler and implements it in an abstract class that can be extended to a concrete custom-built database handler. This behavior allows you to connect transparently to a relational database, execute a SQL query, and return a ResultSet whose data can be translated into a DOM document object.


Editor's Picks

Free Newsletters, In your Inbox