Use DOM and XSL to format data extracted by Java

Java can extract data from any JDBC-compatible database, convert the data into a DOM object, and then format the data in the desired form using XSL. In the previous article, we demonstrated how to extract data programmatically from the database. Now let's discuss how to generate a DOM object and format the data with an XSL style sheet. In this way, the final output can be used for any application, as long as you provide the required input for them.



Generate DOM document


The latest Java version supports jaxp xml processing and implements DOM APIs defined by the W3C. To use these versions compatible with JAXP, you only need three lines of code to create a DOM Document Object. The JAXP factory and builder methods are used:


DocumentBuilderFactory factory = DocumentBuilderFactory. newInstance ();
DocumentBuilder builder = factory. newDocumentBuilder ();
Document document = builder. newDocument ();

Create a root element named resultset and add it to the Document Object.


Element root = document. createElement ("resultset ");
Document. appendChild (root );

When the resultset cursor traverses the result set, add a new element containing row data to the root element:


Element nextRow = document. createElement ("row ");

The column count must be read from the ResultSetMetaData object. In the database data extraction phase of this project, it must be defined as follows:

Int columnCount = rsmd. getColumnCount ();

The column name must be retrieved in a for loop. for each column name, a name node is added with a TextNode that contains the name value used for a names element:


String [] columnNames = new String [columnCount];
Element names = document. createElement ("names ");
For (int I = 0; I <columnCount; I ++ ){
/* The first column is 1, the second is 2 ,...*/
ColumnNames [I] = rsmd. getColumnName (I + 1 );
Element nextNameNode = document. createElement ("name ");
Text nextName = document. createTextNode (columnNames [I]);
NextNameNode. appendChild (nextName );
Names. appendChild (nextNameNode );
}

The column index starts from 1, not from 0. When reading each row of data, the column values are generalized and retrieved as strings in a for loop. This for Loop reads each column value:



/* Move the cursor through the data one row at a time .*/
While (resultSet. next ()){
/* Create an Element node for each row of data .*/
Element nextRow = document. createElement ("row ");
If (debug) System. out. println ("new row ");
For (int I = 0; I <columnCount; I ++ ){
/* Create an Element node for each column value .*/
Element nextNode = document. createElement (columnNames [I]);
/* The first column is 1, the second is 2 ,...*/
/* GetString () will retrieve any of the basic SQL types */
Text text = document. createTextNode (resultSet. getString (I + 1 ));
NextNode. appendChild (text );
NextRow. appendChild (nextNode );
}
Root. appendChild (nextRow );
}

After all data is converted to a DOM Document Object, the connection can be closed. DataBaseHandler never requires file operations. The XML document is created in memory.
A specific DataBaseHandler object


Use a few lines of code to construct a generalized DefaultDataBaseHandler:



Public class DefaultDataBaseHandler extends actdatabasehandler {

Public defadatabdatabasehandler (String urlString, String userName,
String password, String driverName ){

SetUrlString (urlString );
SetUserName (userName );
SetPassword (password );
SetDriverName (driverName );
}
}

The OracleDataBase processing program is a little complicated:

Public class OracleDataBaseHandler extends actdatabasehandler {

Private String thinOraclePrefix = "jdbc: oracle: thin :@";
Private String urlString;
Private String userName;
Private String password;
Private String driverName = "oracle. jdbc. OracleDriver ";
Private String host;
Private String port;
Private String sid;

Public OracleDataBaseHandler (String host, String sid,
String userName, String password ){

This. host = host;
This. sid = sid;
/* A valid url connection string format is: "host: port: sid "*/
SetUrlString (thinOraclePrefix + host + ": 1521:" + sid );
SetUserName (userName );
SetPassword (password );
}

Public OracleDataBaseHandler (String host, String port, String sid,
String userName, String password ){

This. host = host;
This. sid = sid;
This. port = port;
/* A valid url connection string format is: "host: port: sid "*/
SetUrlString (thinOraclePrefix + host + ":" + port + ":" + sid );
SetUserName (userName );
SetPassword (password );
}
}

ODBCDataBaseHandler is slightly different, but it processes the data source name (DSN) instead of the Host Name, port, and SID. The host name, port number, and SID related to the Oracle database are used because we process a database server rather than a database file. However, Microsoft Access relational database must use the. mdb file:

Public class ODBCDataBaseHandler extends actdatabasehandler {

Private String urlString;
Private String userName;
Private String password;
Private String driverName = "sun. jdbc. odbc. JdbcOdbcDriver ";
Private String dsn;
Private String odbcPrefix = "jdbc: odbc :";
Public ODBCDataBaseHandler (String dsn, String userName, String password ){
/* A valid url connection string format is: "jdbc: odbc: dsn "*/
This. dsn = dsn;
SetUrlString (odbcPrefix + dsn );
SetUserName (userName );
SetPassword (password );
SetDriverName (driverName );
}
}

For more information about accessing a specific database, see the product documentation. You need to use another class to call a specific DataBaseHandler and perform XSL conversion to convert the database results into a useful alternative output type.
SQLMapper

The SQLMapper class uses a DataBaseHandler class to complete its database work, and converts a document object to a required output type using a ing method. The ing method returns a string, because it is assumed that the output is composed of character data. In addition, you can also use a StringBuffer.

SQLMapper requires an SQL query string, an output type set, and a DataBaseHandler for executing specific work. They are initialized using the set method and retrieved using the get method:




If (getSQL ()! = Null)
& (GetSQL (). length ()> 0)
& (GetOutputType ()! = Null)
& (IsValidOutputType (getOutputType ()))
& (GetDataBaseHandler ()! = Null )){
Document document = dataBaseHandler. getDocument (getSQL ());

To convert to the expected output, you must specify an XSL style table in the set method. We have created a Transformer object, which provides only one private getTransformer method. This method can be used to obtain a default style table or a specified style table. If necessary, you can use the TransformerFactory method of Java to generate a style sheet:



TransformerFactorytransformerfactory = TransformerFactory. newInstance ();
Transformer = transformerfactory. newTransformer (getStylesheet ());

You only need several lines of Java code to complete the conversion:


Transformer transformer = getTransformer ();
StringWritersw = new StringWriter ();
StreamResult result = new StreamResult (sw );
If (transformer! = Null ){
Transformer. transform (new DOMSource (document. getDocumentElement (), result );
Output = sw. toString ();
System. err. println ("output:" + output );
} Else {
System. err. println ("No Transformer ");
}

The Transformer object requires a DOMSource object. To obtain this object, we pass the root element of a DOM document to the Transformer constructor.

Finally, the implementer should design his own XSL style sheet. You can also use some default style sheets to convert the original data into HTML or XML. The following is a generalized XSL style table. It can use the so-called identity transformation technology to convert the generated data into an XML document, and ensure that the output content is compatible with UTF-8 standards and has good readability.

<〈? Xml version = "1.0" encoding = "UTF-8 "? > 〉
<Xsl: stylesheet xmlns: xsl = "http://www.w3.org/1999/XSL/Transform" version = "1.0"> "〉
<Xsl: output method = "xml" edition = "1.0" encoding = "UTF-8" indent = "yes"/> "/〉
<Xsl: template match = "@ * | node ()"> ()"〉
<Xsl: copy> 〉
<Xsl: apply-templates select = "@ * | node ()"/> ()"/〉
</Xsl: copy> 〉
</Xsl: template> 〉
</Xsl: stylesheet> 〉


The following is a generalized XSL style table that converts the generated data into an HTML table:




<〈? Xml version = "1.0" encoding = "UTF-8 "? > 〉
<Xsl: stylesheet xmlns: xsl = "http://www.w3.org/1999/XSL/Tr