This chapter is from the book
This chapter is from the book
This chapter is from the book
ResultSet to XML
RStoXML is a class that will return a JDOM representation of a database ResultSet upon the invocation of the build() method. Before this method is used, properties of the returned JDOM document can be specified. Some of the properties available include changing the default element names from the record column names and declaring columns to be created as attributes instead of elements. Another important property available is the maximum number of records that will be converted into the JDOM representation. The use of this property will be demonstrated in the next chapter when a paging report is created.
This class is a starting point for the creation of a more versatile production-oriented object. The capability to filter which columns of the ResultSet get included in the JDOM creation can easily be added with another HashMap, some more property setting methods, and some additional logic in the build() method.
What It Does
RStoXML.java at its simplest will create a JDOM document from a ResultSet object. At its default setting, RStoXML will create sibling Element nodes for each field of data found in a record. These nodes will be placed together as children of an Element node, each of which represents a record. This causes the record elements to be children of the root node, and all data containing elements to be grandchildren.
For example, the default XML structure returned from RStoXML is shown following Table 11.1, which contains two sample records.
Table 11.1. Two Records from the ReportClientData Table
| RCD_index | RCD_lname | RCD_fname | RCD_dob | RCD_clinic | RCD_color |
|---|---|---|---|---|---|
| 1 | Nowitz | Kerry | 1970-08-29 | 1 | yellow |
| 2 | Muller | Todd | 1981-02-08 | 2 | green |
Using all the defaults of RStoXML, the sample records found in Table 11.1 would result in a JDOM representation of the XML document shown in Listing 11.2.
Listing 11.2 Default JDOM Document Returned from RStoXML
<ROOT>
<RECORD>
<RCD_index>1</RCD_index>
<RCD_lname>Nowitz</RCD_lname>
<RCD_fname>Kerry</RCD_fname>
<RCD_dob>1970-08-29</RCD_dob>
<RCD_clinic>1</RCD_clinic>
<RCD_color>yellow</RCD_color>
</RECORD>
<RECORD>
<RCD_index>2</RCD_index>
<RCD_lname>Muller</RCD_lname>
<RCD_fname>Todd</RCD_fname>
<RCD_dob>1981-02-08</RCD_dob>
<RCD_clinic>2</RCD_clinic>
<RCD_color>green</RCD_color>
</RECORD>
</ROOT>
RStoXML is not limited to outputting Elements to represent the structure of the ResultSet. Methods such as setAsAttribute() and setAllAttribute() exist to set one or all columns of data to attributes of the RECORD element. For example, if setAllAttribute() were invoked before the JDOM representation were built through the build() method, the JDOM structure as represented in Listing 11.3 would result.
Listing 11.3 JDOM Document Returned from RStoXML
<ROOT>
<RECORD RCD_index="1" RCD_lname="Nowitz" RCD_fname="Kerry"
RCD_dob="1970-08-29" RCD_clinic="1" RCD_color="yellow" />
<RECORD RCD_index="2" RCD_lname="Muller" RCD_fname="Todd"
RCD_dob="1981-02-08" RCD_clinic="2" RCD_color="green" />
</ROOT>
Nor is RStoXML limited to the default column names that exist in the database table definition. Sometimes these names can be very long, depending upon the database schema. As a result, methods such as setName() permit the setting of the attribute or element name with which a ResultSet data column will be labeled.
The source code to this class follows in Listing 11.4. It will not be explained because it is straightforward Java. Save and compile this file as webapps\xmlbook\WEB-INF\classes\xmlbook\chapter11.
Listing 11.4 RStoXML.java
package xmlbook.chapter11;
import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import org.jdom.*;
public class RStoXML {
private SQLException exception;
private ResultSet rs;
private int intColCount;
private ResultSetMetaData rsmd;
//key is original column name keyed to replacement name
private Map newNames = new HashMap();
//key is original db column, true or false
private Map attribs = new HashMap();
int maxRows = Integer.MAX_VALUE; // default to all
private String rootName = "ROOT";
private String rowName = "RECORD";
public RStoXML(ResultSet rs) {
this.rs = rs;
try {
rsmd = rs.getMetaData();
intColCount = rsmd.getColumnCount();
}
catch (SQLException e) {
// Hold the exception until build() is called
exception = e;
}
}
public RStoXML(ResultSet rs, String rootName, String rowName) {
this(rs);
setRootName(rootName);
setRowName(rowName);
}
public Document build() throws JDOMException {
if (exception != null) {
throw new JDOMException("Database problem", exception);
}
try {
Element root = new Element(rootName);
Document doc = new Document(root);
int rowCount = 0;
// get column names for the record set
String[] columnName = new String[intColCount];
for (int index = 0; index < intColCount; index++) {
columnName[index] = rsmd.getColumnName(index+1);
}
// build the org.jdom.Document out of the result set
String name;
String value;
Element record;
Element child;
while (rs.next() && (rowCount++ < maxRows)) {
record = new Element(rowName);
for (int col = 1; col <= intColCount; col++) {
if (newNames.isEmpty() ||
(newNames.get(columnName[col-1].toLowerCase()) == null))
name = columnName[col-1];
else
name = lookupName(columnName[col-1]);
value = rs.getString(col);
if ( attribs.containsKey(columnName[col-1].toLowerCase())) {
//if the value of the column is in attribs
//and notnull create attribute
record.setAttribute(name, value);
}
else {
//other wise create child element
child = new Element(name);
if (!rs.wasNull())
child.setText(value);
record.addContent(child);
}
}
root.addContent(record);
}
return doc;
}
catch (SQLException e) {
throw new JDOMException("Database problem", e) ;
}
}
//origName is column name in db, if name exists in the newNames hashmap
//return it, else returned passed in name.
private String lookupName(String origName) {
String name = (String) newNames.get(origName.toLowerCase());
if (name != null) {
return name;
}
else {
return origName;
}
}
public void setRootName(String rootName) {
this.rootName = rootName;
}
public void setRowName(String rowName) {
this.rowName = rowName;
}
public void setMaxRows(int maxRows) {
this.maxRows = maxRows;
}
public void setAsAttribute(String columnName, String attribName) {
String name = columnName.toLowerCase();
attribs.put(name, "t");
newNames.put(name, attribName);
}
public void setAllAttribute() throws JDOMException{
try{
int intColCount = rsmd.getColumnCount();
for (int index = 0; index < intColCount; index++) {
attribs.put(rsmd.getColumnName(index+1).toLowerCase(), "t");
}
} catch (SQLException e) {
throw new JDOMException("Database problem", e);
}
}
public void setName(String columnName, String name){
newNames.put(columnName.toLowerCase(), name);
}
//set a database column name to a specific element name
public void setAsName(String columnName, String elemName) {
String name = columnName.toLowerCase();
newNames.put(name, elemName);
}
}