XML and Java: Developing Web Applications, Second Edition
| [ directory ] |
|
11.5 Program ExamplesIn Section 11.3.6, we discussed two approaches for mapping from an XML document to relational tables. This section introduces the examples for these approaches. The techniques described in this section are also useful for mapping relational tables to an XML document. 11.5.1 Mapping MethodListing 11.4 shows a sample program to decompose an XML document (po.xml, shown in Listing 11.1) into predefined tables. This is a typical program for the mapping approach. Listing 11.4 Archiving an XML document, chap11/XMLTableArchiver
package chap11;
/**
* XMLArchiver.java
*
* This program assumes the following database schema
*
* TABLE "PO_TBL" (
* "invoice_ID" VARCHAR(32) NOT NULL,
* "country" VARCHAR(32) NOT NULL,
* "name" VARCHAR(128) NOT NULL,
* "street" VARCHAR(128) NOT NULL,
* PRIMARY KEY ("invoice_ID") ) ;
*
* TABLE "ITEM_TBL" (
* "item_ID" VARCHAR(32) NOT NULL,
* "item" VARCHAR(128) NOT NULL,
* "qty" INTEGER NOT NULL,
* "invoice_ID" VARCHAR(32) NOT NULL,
* PRIMARY KEY ("item_ID"),
* FOREIGN KEY ("invoice_ID")
* REFERENCES PO_TBL ("invoice_ID")
* ON DELETE CASCADE) ;
*/
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.rmi.server.UID;
import java.util.Hashtable;
import java.io.File;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.apache.xml.serialize.XMLSerializer;
import org.apache.xml.serialize.OutputFormat;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import share.util.MyErrorHandler;
public class XMLTableArchiver {
// Names of database, tables, and elements
static final String XML_DB = "XMLDB";
static final String PO_TBL = "PO_TBL";
static final String ITEM_TBL = "ITEM_TBL";
static final String JDBC_DRIVER_CLASS =
"COM.ibm.db2.jdbc.app.DB2Driver";
static final String ELT_PO = "purchaseOrder";
static final String ELT_SHIPTO = "shipTo";
static final String ELT_STREET = "street";
static final String ELT_NAME = "name";
static final String ELT_ITEMS = "items";
static final String ELT_ITEM = "item";
static final String ATTR_COUNTRY = "country";
static final String ATTR_QTY = "qty";
static final String ATTR_INVOICE_ID = "invoiceNo";
static final String COLUMN_INVOICE_ID = "INVOICE_ID";
static final String COLUMN_QTY = "QTY";
static final String COLUMN_ITEM = "ITEM";
// Connection with database
private Connection con = null;
// Prepared statement for storing data into PO_TBL
private Statement stmt = null;
// Prepared statement for storing data into ITEM_TBL
[71] private PreparedStatement stmtForItem = null;
// Register the driver with DriverManager
static {
try {
Class.forName(JDBC_DRIVER_CLASS);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Constructor
*
*/
public XMLTableArchiver (String userid, String password) {
try {
// Creates connection
[89] con = DriverManager.getConnection("jdbc:db2:"+XML_DB,
userid,
password);
[92] con.setAutoCommit(false);
[93] stmt = con.createStatement();
[94] stmtForItem = con.prepareStatement(
"INSERT INTO " + ITEM_TBL + " VALUES (?, ?, ?, ?)");
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
}
/**
* Store data into database
*
* @param doc a <code>Document</code> to be stored
*/
public void store(Document doc) {
try {
String invoiceNo = null;
String country = null;
String name = null;
String street = null;
// Gets information for PO_TBL
Element poElt =
(Element)doc.getElementsByTagName(ELT_PO).item(0);
invoiceNo = poElt.getAttribute(ATTR_INVOICE_ID);
Element shipToElt = (Element)
doc.getElementsByTagName(ELT_SHIPTO).item(0);
country = shipToElt.getAttribute(ATTR_COUNTRY);
Element nameElt = (Element)
doc.getElementsByTagName(ELT_NAME).item(0);
name = nameElt.getFirstChild().getNodeValue();
Element streetElt = (Element)
doc.getElementsByTagName(ELT_STREET).item(0);
street = streetElt.getFirstChild().getNodeValue();
// Submits an insert query
String query = "INSERT INTO " + PO_TBL +
" VALUES ('" + invoiceNo + "', '" +
country + "', '" + name + "', '" +
street + "')";
// Submits query
[134] stmt.executeUpdate(query);
// Gets information for ITEM_TBL
[136] Element itemsElt =
(Element)doc.getElementsByTagName(ELT_ITEMS).item(0);
NodeList nl = itemsElt.getChildNodes();
Element itemElt = null;
try {
for (int i = 0; i < nl.getLength(); i++) {
if (nl.item(i).getNodeType() ==
Node.ELEMENT_NODE) {
// Gets item node
itemElt = (Element)nl.item(i);
String qty =
itemElt.getAttribute(ATTR_QTY);
String itemName =
itemElt.getFirstChild().
getNodeValue();
String itemID = new UID().toString();
[152] stmtForItem.setString(1, itemID);
stmtForItem.setString(2, itemName);
stmtForItem.setInt(3,
Integer.parseInt(qty));
stmtForItem.setString(4, invoiceNo);
[157] stmtForItem.executeUpdate();
}
}
} catch (SQLException se) {
// Rolls back the first insertion
System.out.println("Insertion Error: all the " +
"operation to database is canceled");
[164] con.rollback();
}
// Commits the insertions to tables
[167] con.commit();
System.out.println("Stored with ID "+invoiceNo);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] argv) {
if (argv.length != 1) {
System.err.println("Usage: XMLTableArchiver " +
"filename");
System.exit(1);
}
try {
// Userid and password are specified as
// System properties
[183] String userid = System.getProperty("chap11.userid");
String password =
System.getProperty("chap11.password");
XMLTableArchiver arc =
new XMLTableArchiver(userid, password);
// Parses input document
[189] DocumentBuilderFactory factory =
DocumentBuilderFactory.newInstance();
DocumentBuilder builder =
factory.newDocumentBuilder();
// Sets an ErrorHandler
builder.setErrorHandler(new MyErrorHandler());
// Parses the document
Document doc = builder.parse(new File(argv[0]));
// Stores the document
arc.store(doc);
} catch (Exception e) {
e.printStackTrace();
}
}
}
This program, XMLTableArchiver, assumes that the two tables PO_TBL and ITEM_TBL have been defined. Samples of the tables are shown in Tables 11.1 and 11.2. This program works as follows:
We have already shown how to parse an XML document (see Chapter 2), how to extract information from a DOM tree (see Chapter 2), and how to connect to a database with JDBC (see Appendix D). Now we describe some important techniques that appeared for the first time in this program. Prepared StatementIn the first sample program shown in Section D.5 of Appendix D,a Statement object is first created to submit an SQL query. Then, the query string is passed to the method executeQuery() and compiled into an internal representation when the method is invoked. On the other hand, as shown in Listing 11.4, the PreparedStatement object is created with a template for an SQL query. The template contains placeholders represented by "?". The actual values for the placeholders are determined at runtime; therefore, the template can be reused for multiple queries. The related code fragment in Listing 11.4 is as follows:
[71] private PreparedStatement stmtForItem = null;
...
[94] stmtForItem = con.prepareStatement(
"INSERT INTO " + ITEM_TBL + " VALUES (?, ?, ?, ?)");
First, a PreparedStatement object is created by using the preparedStatement() method of the Connection class. A template SQL query with four placeholders is passed to the method to precompile the template.
[152] stmtForItem.setString(1, itemID);
stmtForItem.setString(2, itemName);
stmtForItem.setInt(3, Integer.parseInt(qty));
stmtForItem.setString(4, invoiceNo);
[157] stmtForItem.executeUpdate();
Before the query is submitted, the placeholders are filled with the content of the ITEM_ID, ITEM, QTY, and INVOICE_ID elements. The PreparedStatement class provides a set of methods for setting values with the appropriate datatypes (for example, the setString() and setInt() methods). Finally, the query is submitted. In the example in Listing 11.4, the executeUpdate() method is used, while the executeQuery() method is used in Appendix D.The former method does not return the ResultSet object and can be used for INSERT and DELETE operations. For SELECT operations, the latter method is used to get the search result. In the program shown in Listing 11.4, the insertion for the PO_TBL is called once. However, the insertion for the ITEM_TBL tables is called multiple times. When the same type of query is called repeatedly, using the PreparedStatement object is efficient. Rollback of OperationsIn this program, the insertion operation is applied to two tables. If the insertion operation to the first table succeeds but the insertion to the second table fails, it causes an inconsistency between the tables. The sequence of the insertion process can be called as a transaction. A transaction should be atomic, consistent, isolated, and durable (these features are abbreviated as ACID). To learn more about transaction processing, see the references in Appendix B.If a process in a transaction fails, the transaction as whole should fail and the state should be returned to the point before starting the transaction. For example, if the insertion to the second table failed, the insertion to the first table should be canceled. The cancellation process is called as a rollback, which is very important for developing a reliable business system (for example, the two tables might show bank accounts for money transfer). Most RDBMSs provide a rollback mechanism. In JDBC, the rollback() method in the Connection class is prepared. In the program shown in Listing 11.4, the rollback() command is called when the insertion process for the ITEM_TBL table fails after the insertion process for the PO_TBL table (line 164). After the insertion to the two tables succeeds, the commit() method is called (line 167). To enable this function, you should execute the setAutoCommit(false) method before starting the insertion process (line 92). Now it is time to execute the program. First, we should create a database and tables by loading the setupXPathDB.ddl script, shown in Listing 11.5. The script is written in Data Definition Language (DDL) format, which is a common syntax to define database schemas. Assume that we can execute the DB2 command (see the Readme file on the CD-ROM). Listing 11.5 DDL file for creating tables (mapping method), chap11/setupMappingDB.ddl
------------------------------------------------
-- Create database "XMLDB"
------------------------------------------------
CREATE DATABASE XMLDB;
CONNECT TO XMLDB user db2admin using db2admin;
------------------------------------------------
-- Create table "PO_TBL"
------------------------------------------------
CREATE TABLE "PO_TBL" (
"INVOICE_ID" VARCHAR(32) NOT NULL,
"COUNTRY" VARCHAR(32) NOT NULL,
"NAME" VARCHAR(128) NOT NULL,
"STREET" VARCHAR(128) NOT NULL,
PRIMARY KEY ("INVOICE_ID") ) ;
------------------------------------------------
-- Create table "ITEM_TBL"
------------------------------------------------
CREATE TABLE "ITEM_TBL" (
"ITEM_ID" VARCHAR(32) NOT NULL,
"ITEM" VARCHAR(128) NOT NULL,
"QTY" INTEGER NOT NULL,
"INVOICE_ID" VARCHAR(32) NOT NULL,
PRIMARY KEY ("ITEM_ID"),
FOREIGN KEY ("INVOICE_ID") REFERENCES PO_TBL
("INVOICE_ID") ON DELETE
CASCADE) ;
R:\samples>db2 -tvf chap11\setupMappingDB.ddl
...
The script creates a database (XMLDB) and two tables (PO_TBL and ITEM_TBL). Next, we execute the following command. A userid and a password are specified as system property values (line 183 in Listing 11.4). If the properties are not specified, the default userid (logon userid) and password are used. R:\samples>java -Dchap11.userid="db2admin" -Dchap11.password= "db2admin" chap11.XMLTableArchiver chap11\po.xml Stored with ID 2001-08-31-12345 The output indicates that the document po.xml has been stored in the database with the primary key 2001-08-31-12345. The next example, shown in Listing 11.6, is the program XMLTableRetriever, which searches for and deletes purchase order documents with invoice_ID (the primary key). Listing 11.6 Retrieving an XML document, chap11/XMLTableRetriever
package chap11;
/**
* XMLRetriever.java
*
*/
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.rmi.server.UID;
import java.util.Hashtable;
import java.util.Enumeration;
import org.apache.xml.serialize.XMLSerializer;
import org.apache.xml.serialize.OutputFormat;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Document;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import java.io.StringWriter;
public class XMLTableRetriever {
// Names of database, tables, and elements
static final String XML_DB = "XMLDB";
static final String PO_TBL = "PO_TBL";
static final String ITEM_TBL = "ITEM_TBL";
static final String JDBC_DRIVER_CLASS =
"COM.ibm.db2.jdbc.app.DB2Driver";
static final String ELT_PO = "purchaseOrder";
static final String ELT_SHIPTO = "shipTo";
static final String ELT_STREET = "street";
static final String ELT_NAME = "name";
static final String ELT_ITEMS = "items";
static final String ELT_ITEM = "item";
static final String ATTR_COUNTRY = "country";
static final String ATTR_QTY = "qty";
static final String ATTR_INVOICE_ID = "invoiceNo";
static final String COLUMN_INVOICE_ID = "INVOICE_ID";
static final String COLUMN_QTY = "QTY";
static final String COLUMN_ITEM = "ITEM";
// Connection with database
Connection con = null;
// Query statement
Statement stmt = null;
static {
try {
// Register the driver with DriverManager
Class.forName(JDBC_DRIVER_CLASS);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Constructor
*
*/
XMLTableRetriever(String userid, String password) {
try {
// Creates connection
con = DriverManager.getConnection("jdbc:db2:"+XML_DB,
userid,
password);
// Creates statement
stmt = con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Search an XML document with document id
*
* @param docId document ID assigned at registration process
* @see XMLArchiver
*/
[82] public Document searchByDocumentID(String docId) {
String queryForPOTbl = "SELECT * FROM " +
PO_TBL + " WHERE " + COLUMN_INVOICE_ID + "='" +
docId + "'";
String invoiceID = null;
String country = null;
String name = null;
String street = null;
try {
// Get result of the query
ResultSet rs = stmt.executeQuery(queryForPOTbl);
if (rs.next()) {
invoiceID = rs.getString(1);
country = rs.getString(2);
name = rs.getString(3);
street = rs.getString(4);
} else {
System.out.println("Not found");
return null;
}
// Creates DOM tree from PO_TBL
Document doc =
createPoTree(invoiceID, country, name, street);
// Appends items to the tree
doc = appendItems(invoiceID, doc);
// Serializes it
System.out.println(toString(doc));
rs.close();
return doc;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* Create DOM Tree from PO_TBL
*
* @param invoiceID invoiceID
* @param country country
* @param name name
* @param street street
* @return generated <code>Document</code> object
*/
[127] Document createPoTree(String invoiceID,
String country,
String name,
String street) {
Document doc = null;
try {
// Creates document object
DocumentBuilderFactory factory =
DocumentBuilderFactory.newInstance();
DocumentBuilder builder =
factory.newDocumentBuilder();
doc = builder.newDocument();
// Constructs DOM tree
Element rootElt = doc.createElement(ELT_PO);
rootElt.setAttribute(ATTR_INVOICE_ID, invoiceID);
doc.appendChild(rootElt);
Element shipToElt = doc.createElement(ELT_SHIPTO);
rootElt.appendChild(shipToElt);
rootElt.appendChild(doc.createElement(ELT_ITEMS));
Element nameElt = doc.createElement(ELT_NAME);
shipToElt.setAttribute(ATTR_COUNTRY, country);
shipToElt.appendChild(nameElt);
nameElt.appendChild(doc.createTextNode(name));
Element streetElt = doc.createElement(ELT_STREET);
shipToElt.appendChild(streetElt);
streetElt.appendChild(doc.createTextNode(street));
} catch (Exception e) {
e.printStackTrace();
} finally {
return doc;
}
}
/**
* Delete an XML document with document id
*
* @param docId document ID assigned at registration process
* @see XMLArchiver
*/
public void deleteByDocumentID(String docId) {
String SQLquery = "DELETE " + " FROM " +
PO_TBL + " WHERE " + COLUMN_INVOICE_ID
+ " = '" + docId + "'";
try {
int deletedLineNo = stmt.executeUpdate(SQLquery);
// Only one document should be deleted since
// document id is unique
if (deletedLineNo == 1) {
System.out.println("Document " +docId +
" has been deleted");
} else {
System.out.println("deletion failed");
}
} catch (Exception e) {
e.printStackTrace();
}
}
public Document appendItems(String invoiceID, Document doc) {
String item = null;
int qty = 0;
String queryForItemTbl = "SELECT * FROM " +
ITEM_TBL + " WHERE " + COLUMN_INVOICE_ID + "='" +
invoiceID + "'";
Element itemsElt =
(Element)doc.getElementsByTagName(ELT_ITEMS).item(0);
try {
ResultSet rs = stmt.executeQuery(queryForItemTbl);
while (rs.next()) {
item = rs.getString(COLUMN_ITEM);
qty = rs.getInt(COLUMN_QTY);
Element itemElt = doc.createElement(ELT_ITEM);
itemElt.setAttribute(ATTR_QTY,
new Integer(qty).toString());
itemElt.appendChild(doc.createTextNode(item));
itemsElt.appendChild(itemElt);
}
} catch (Exception e) {
e.printStackTrace();
}
return doc;
}
/**
* Convert <code>Document</code> object to string
*
* @param doc a <code>Document</code> object
* @return its serialization
*/
public static String toString(Document doc) {
// Serializes the DOM tree as an XML document
OutputFormat formatter = new OutputFormat();
formatter.setPreserveSpace(true);
// The XML document will be serialized as string.
StringWriter out = new StringWriter() ;
XMLSerializer serializer =
new XMLSerializer(out, formatter);
try {
serializer.serialize(doc);
} catch (Exception e) {
e.printStackTrace();
}
out.flush();
return out.toString();
}
public static void main(String[] argv) {
if (argv.length != 2) {
System.err.println("Usage: XMLTableRetriever [-s|-d]"
+" docid");
System.exit(1);
}
// Userid and password are specified as
// System properties
String userid = System.getProperty("chap11.userid");
String password =
System.getProperty("chap11.password");
XMLTableRetriever arc =
new XMLTableRetriever(userid, password);
if (argv[0].equals("-s"))
arc.searchByDocumentID(argv[1]);
else if (argv[0].equals("-d"))
arc.deleteByDocumentID(argv[1]);
else
System.out.println("Not supported: " + argv[1]);
}
}
Comments embedded in the program show its procedure. It takes two arguments: a flag that means search (-s) or delete (-d) and an invoice ID. The method searchByDocumentID() (line 82) constructs an SQL query for the PO_TBL table. For example, if invoice_ID is 2001-08-31-12345, the SQL query is as follows: SELECT * FROM PO_TBL WHERE invoice_ID='2001-08-31-12345' The createPoTree() method (line 127) generates a purchase order document from the search result for the query. Recall that we showed various techniques to generate a DOM tree and serialize it in Chapter 3. This program submits two queries to PO_TBL and ITEM_TBL. We can get all the needed data with a single call by joining the tables. The SQL query looks like this. SELECT A.COUNTRY, A.NAME, A.STREET, B.ITEM, B.QTY from PO_TBL AS A, ITEM_TBL AS B where (A.invoice_ID='2001-08-31-12345') AND (A.invoice_ID = B.invoice_ID) The result for the query contains the data for two tables. However, each record for items contains duplicated COUNTRY, NAME, and STREET information. Further more, the join operation involves a cost in general. Therefore, we should determine which method is better based on the actual environment where this kind of program runs. For example, if our database is located on a remote machine and network latency is serious, the cost for two query submissions is more than the cost for the table joining and the redundant data transmission. Note that in the deletion process, a record is deleted only from the PO_TBL table. We defined a constraint for the tables in the schema shown in Listing 11.5. The constraint automatically deletes related records in ITEM_TBL. If there is no such constraint, you should manage the deletion processes for the two tables. The following is the result for the searching and deletion processes. R:\samples>java -Dchap11.userid="db2admin" -Dchap11.password="db2admin" chap11.XMLTableRetriever -s 2001-08-31-12345 <?xml version="1.0" encoding="UTF-8"?> <purchaseOrder invoiceNo="2001-08-31-12345"><shipTo country="US"> <name>Alice Smith</name><street>123 Maple Street</street></shipTo> <items><item qty="2">ThinkPad X21</item><item qty="1">ThinkPad T22</item></items></purchaseOrder> R:\samples>java chap11.XMLTableRetriever -d 2001-08-31-12345 Document 2001-08-31-12345 has been deleted 11.5.2 XPath MethodSection 11.5.1 described a sample program that decomposed an XML document and stored data in two tables by mapping schemas for XML and a database (the mapping method). In this section, we introduce another approach (the XPath method). In this approach, a set of an XPath expression and its contents is stored in a table. In the mapping method, we defined a table for a specific schema. In the XPath approach, the table is independent from any specific schema, so any XML document can be stored in the table. We use the following three classes.
Listing 11.7 shows the source code for IndexCreator. Listing 11.7 Creating an XPath index, chap11/IndexCreator.java
package chap11;
/**
* IndexCreator.java
*
*/
import java.util.Hashtable;
import org.apache.xerces.parsers.DOMParser;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Document;
import org.w3c.dom.Attr;
import org.w3c.dom.NodeList;
import org.w3c.dom.NamedNodeMap;
public class IndexCreator {
// Stores xpath-content relationship
private Hashtable pathValueTbl = new Hashtable();
public Hashtable getTable() {
return pathValueTbl;
}
/**
* Constructor
*
*/
public IndexCreator () {
}
/**
* Search text nodes with XPath and store them to hash table
*
* @param node node to be indexed
*/
public void makeIndexTbl (Node node) {
makeIndexTbl(node, "/"+node.getNodeName()+"[1]");
}
[41] public void makeIndexTbl (Node node, String xpath) {
// Searches Text nodes and stores them with xpath
[43] if (node.getNodeType() == Node.TEXT_NODE) {
if (!isWhitespaces(node.getNodeValue())) {
pathValueTbl.put(xpath, node.getNodeValue());
System.out.println(xpath+"="+node.getNodeValue());
}
// Searches attribute values and stores them with xpath
} else if (node.getNodeType() == Node.ELEMENT_NODE) {
NamedNodeMap nmap = node.getAttributes();
for (int i = 0; i < nmap.getLength(); i++) {
String attrXpath = xpath + "/@" +
nmap.item(i).getNodeName();
System.out.println(attrXpath+"="+
nmap.item(i).getNodeValue());
pathValueTbl.put(xpath, nmap.item(i).getNodeValue());
}
}
// Called recursively
for (Node child = node.getFirstChild();
child != null;
child = child.getNextSibling()) {
if (child.getNodeType() == Node.ELEMENT_NODE) {
makeIndexTbl(child, xpath + "/" + child.getNodeName()
+ "[" +
getPosition(child) + "]");
} else if (child.getNodeType() == Node.TEXT_NODE) {
makeIndexTbl(child, xpath);
}
}
}
/**
* Return the position of the node.
*
* @param node <code>Node</code>
* @return The position of the node in the all siblings
* that has same element name
*/
int getPosition(Node node) {
if (node.equals(node.getOwnerDocument())) {
return 0;
}
NodeList nl = node.getParentNode().getChildNodes();
int j = 1;
for (int i = 0; i < nl.getLength(); i++) {
if (node.getNodeName().equals(
nl.item(i).getNodeName())) {
if (node.equals(nl.item(i))) {
return j;
} else {
j++;
}
}
}
return 0;
}
boolean isWhitespaces (String str) {
for (int i = 0; i < str.length(); i++) {
if (str.charAt(i) != '\n' &&
str.charAt(i) != '\t' &&
str.charAt(i) != '\r' &&
str.charAt(i) != ' ') {
return false;
}
}
return true;
}
}
The procedure of this program is as follows:
The method makeIndexTable() (line 41) is the heart of this program. The method visits all child nodes for a given node and checks whether they are text or attribute nodes. When a text node or an attribute node is found, the method stores an XPath expression with the text content or attribute value in the hash table pathValueTable. The makeIndexTable() method is called recursively to construct an XPath expression. Section 7.3.2 in Chapter 7 introduces another method to get an XPath expression for a given node. The class XMLPathArchiver extracts a set of the XPath expression and its contents from the hash table. Before explaining the program, we define a schema for two tables used for the program. We use the XMLDB database used in the previous section and create two tables: DOCUMENT_TBL and NODE_VALUE_TBL. Listing 11.8 shows a configuration file to create the tables and set constraints. Listing 11.8 DDL file for creating tables (XPath method), chap11/setupXPathDB.ddl
------------------------------------------------
-- Create database "XMLDB"
------------------------------------------------
CREATE DATABASE XMLDB;
CONNECT TO XMLDB user db2admin using db2admin;
------------------------------------------------
-- Create table "DOCUMENT_TBL"
------------------------------------------------
CREATE TABLE "DOCUMENT_TBL" (
"DOCUMENT_ID" VARCHAR(32) NOT NULL,
"STORED_TIME" TIMESTAMP NOT NULL WITH
DEFAULT CURRENT
TIMESTAMP,
"ROOT_ELEMENT" VARCHAR(128) NOT NULL,
"DOCUMENT_CONTENT" BLOB(102400) LOGGED COMPACT NOT
NULL,
PRIMARY KEY ("DOCUMENT_ID") ) ;
------------------------------------------------
-- Create table "NODE_VALUE_TBL"
------------------------------------------------
CREATE TABLE "NODE_VALUE_TBL" (
"NODE_ID" VARCHAR(32) NOT NULL,
"PATH_STRING" VARCHAR(2048) NOT NULL,
"CONTENT_STRING" VARCHAR(256) NOT NULL,
"DOCUMENT_ID" VARCHAR(32) NOT NULL,
PRIMARY KEY ("NODE_ID"),
FOREIGN KEY ("DOCUMENT_ID") REFERENCES
DOCUMENT_TBL
("DOCUMENT_ID")
ON DELETE CASCADE) ;
The DOCUMENT_TBL table contains the following columns.
In the schema shown in Listing 11.8, the BLOB type is used for storing an XML document. CLOB is another solution for a datatype in which to store an XML document. When the CLOB type is used, the string to be stored is converted with a predefined encoding. It sometimes causes a problem, as mentioned in Chapter 3, Section 3.6. The advantage of using CLOB is that it can search with an SQL query or other vendor-specific searching methods. The DB2 XML Extender provides a special datatype called XMLCLOB that can be searched for by using an XPath expression. The NODE_VALUE_TBL table contains the PATH_STRING column for an XPath expression, CONTENT_STRING for its content, DOCUMENT_ID for the foreign key of DOCUMENT_TBL, and NODE_ID for the unique identifier (the primary key). An example of NODE_VALUE_TBL is shown in Table 11.3. To create the tables, we need to load the script file setupXPathDB.ddl. R:\samples>db2 -tvf chap11\setupXPathDB.ddl ... The source code for XMLPathArchiver, which extracts XPath expressions by using the IndexCreator class and stores them, is shown in Listing 11.9. Listing 11.9 Archiving XPath expressions, chap11/XPathArchiver.java
package chap11;
/**
* XPathArchiver.java
*
*/
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.rmi.server.UID;
import java.util.Hashtable;
import java.util.Enumeration;
import java.io.File;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.apache.xml.serialize.XMLSerializer;
import org.apache.xml.serialize.OutputFormat;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Document;
import java.io.StringWriter;
import share.util.MyErrorHandler;
public class XPathArchiver {
// Names of database, tables, and elements
static final String JDBC_DRIVER_CLASS =
"COM.ibm.db2.jdbc.app.DB2Driver";
static final String XML_DB = "XMLDB";
static final String DOCUMENT_TBL = "DOCUMENT_TBL";
static final String NODE_VALUE_TBL = "NODE_VALUE_TBL";
static final String COL_DOCUMENT_ID = "DOCUMENT_ID";
static final String COL_DOCUMENT_CONTENT= "DOCUMENT_CONTENT";
static final String COL_ROOT_ELEMENT = "ROOT_ELEMENT";
// Connection with database
Connection con = null;
// Prepared statement for storing data into DOCUMENT_TBL
PreparedStatement stmtForDocTbl = null;
// Prepared statement for storing data into NODE_VALUE_TBL
PreparedStatement stmtForNodeTbl = null;
// Register the driver with DriverManager
static {
try {
Class.forName(JDBC_DRIVER_CLASS);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Constructor
*
*/
public XPathArchiver (String userid, String password) {
try {
// Creates connection
con = DriverManager.getConnection("jdbc:db2:"+XML_DB,
userid,
password);
// Explicit commit should be needed
con.setAutoCommit(false);
// Creates prepared statement to insert data to
// DOCUMENT_TBL
// A Timestamp (STORED_DATE) will be set at database.
stmtForDocTbl = con.prepareStatement(
"INSERT INTO " + DOCUMENT_TBL +
"(" + COL_DOCUMENT_ID + ", "
+ COL_DOCUMENT_CONTENT + ", "
+ COL_ROOT_ELEMENT
+ ") VALUES(?, ?, ?)");
// Creates prepared statement to insert data to
// NODE_TBL
stmtForNodeTbl = con.prepareStatement("INSERT INTO "
+ NODE_VALUE_TBL +
" VALUES(?, ?, ?, ?)");
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
}
/**
* Store data into database
*
* @param doc a <code>Document</code> to be stored
*/
[90] public void store(Document doc) {
try {
// Generates an unique id
UID documentId = new UID();
// Sets data to the prepared statement for
// DOCUMENT_TBL
stmtForDocTbl.setString(1, documentId.toString());
[97] stmtForDocTbl.setBytes (2, toString(doc).getBytes());
stmtForDocTbl.setString(3, doc.getDocumentElement().
getTagName());
// Executes it
try {
[102] stmtForDocTbl.executeUpdate();
System.out.println("Stored with ID " +
documentId.toString());
// Sets data to the prepared statement
// for NODE_TBL
IndexCreator idx = new IndexCreator();
// Creates XPath-content table
[109] idx.makeIndexTbl(doc.getDocumentElement());
[110] Hashtable tbl = idx.getTable();
Enumeration enum = tbl.keys();
// For all xpath expressions with contents,
while (enum.hasMoreElements()) {
String path = (String)enum.nextElement();
String content = (String)tbl.get(path);
// Generates an unique id for each xpath
[117] UID nodeId = new UID();
// Sets data
stmtForNodeTbl.setString(1,
nodeId.toString());
stmtForNodeTbl.setString(2, path);
stmtForNodeTbl.setString(3, content);
stmtForNodeTbl.setString(4,
documentId.toString());
// Executes it
[126] stmtForNodeTbl.executeUpdate();
}
} catch (SQLException e) {
// Rolls back the insertion processes
System.out.println("SQL Error: all the operation"
+ "to database is canceled");
con.rollback();
System.exit(1);
}
// Commits all operation
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Convert <code>Document</code> object to string
*
* @param doc a <code>Document</code> object
* @return its serialization
*/
public static String toString(Document doc) {
// Serializes the DOM tree as an XML document
OutputFormat formatter = new OutputFormat();
formatter.setPreserveSpace(true);
// The XML document will be serialized as string
StringWriter out = new StringWriter() ;
XMLSerializer serializer =
new XMLSerializer(out, formatter);
try {
serializer.asDOMSerializer().serialize(doc);
} catch (Exception e) {
e.printStackTrace();
}
out.flush();
return out.toString();
}
public static void main(String[] argv) {
if (argv.length != 1) {
System.err.println("Usage: XPathArchiver filename");
System.exit(1);
}
try {
// Userid and password are specified as
// System properties
String userid = System.getProperty("chap11.userid");
String password =
System.getProperty("chap11.password");
XPathArchiver arc =
new XPathArchiver(userid, password);
IndexCreator idx = new IndexCreator();
// Parses input document
[180] DocumentBuilderFactory factory =
DocumentBuilderFactory.newInstance();
DocumentBuilder builder =
factory.newDocumentBuilder();
// Sets an ErrorHandler
builder.setErrorHandler(new MyErrorHandler());
// Parses the document
Document doc = builder.parse(new File(argv[0]));
// Stores the document
arc.store(doc);
} catch (Exception e) {
e.printStackTrace();
}
}
}
The program works as follows:
The most important process in this program is storing the set to the tables (DOCUMENT_TBL and NODE_VALUE_TBL). First, two prepared statement objects are created for the two tables. The reason the object is used for the (single) insertion operation to the DOCUMENT_TBL table is that a BLOB datatype should be embedded in the query. This is done by using the setBytes() method (line 97). The store() method first extracts information from a DOM tree to submit an insertion query to the DOCUMENT_TBL table (line 90). It is similar to the process shown in Listing 11.4.
[117] UID nodeId = new UID();
// Sets data
stmtForNodeTbl.setString(1, nodeId.toString());
stmtForNodeTbl.setString(2, path);
stmtForNodeTbl.setString(3, content);
stmtForNodeTbl.setString(4,
documentId.toString());
// Executes it
stmtForNodeTbl.executeUpdate();
To generate a unique ID, the java.rmi.UID class is used. The UID object generates a string value that is unique on a machine where JVM is running. The XML document itself is converted to a byte array and set in the PreparedStatement object. After values in the object are set, the executeUpdate() method is called (line 126). Next, the store() method creates an IndexCreator object and calls the makeIdxTbl() method (line 109), and gets a hash table that contains a set of an XPath and its contents (line 110). By using data in the hash table, an SQL query for the NODE_VALUE_TBL table is created and submitted (line 126). The following is the result of executing XPathArchiver. The userid and password for accessing the database XMLDB are specified as system properties. R:\samples>java -Dchap11.userid="db2admin" -Dchap11.password= "db2admin" chap11.XPathArchiver chap11\po.xml Stored with ID 35b39e75:e8b91faee7:-8000 /purchaseOrder[1]/@orderDate=1999-10-20 /purchaseOrder[1]/shipTo[1]/@country=US /purchaseOrder[1]/shipTo[1]/name[1]=Alice Smith /purchaseOrder[1]/shipTo[1]/street[1]=123 Maple Street /purchaseOrder[1]/items[1]/item[1]/@qty=2 /purchaseOrder[1]/items[1]/item[1]=ThinkPad X21 /purchaseOrder[1]/items[1]/item[2]/@qty=1 /purchaseOrder[1]/items[1]/item[2]=ThinkPad T22 The value "35b39e75:e8b91faee7:-8000" is the identifier for the XML document (po.xml) generated by the java.rmi.UID class. After we execute the program, the database contains an XML document (po.xml). The next program, shown in Listing 11.10, retrieves it. Listing 11.10 Retrieving an XML document, chap11/XPathRetriever.java
package chap11;
/**
* XPathRetriever.java
*
*/
import java.sql.*;
import java.rmi.server.UID;
import java.util.Hashtable;
import java.util.Enumeration;
import java.io.File;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.apache.xml.serialize.XMLSerializer;
import org.apache.xml.serialize.OutputFormat;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;
import java.io.StringWriter;
import share.util.MyErrorHandler;
public class XPathRetriever {
// Names of database, tables, and elements
static final String XML_DB = "XMLDB";
static final String LOGIN_USER_ID = "db2admin";
static final String LOGIN_PASSWORD = "db2admin";
static final String DOCUMENT_TBL = "DOCUMENT_TBL";
static final String NODE_VALUE_TBL = "NODE_VALUE_TBL";
static final String COL_DOCUMENT_ID = "DOCUMENT_ID";
static final String COL_DOCUMENT_CONTENT = "DOCUMENT_CONTENT";
static final String JDBC_DRIVER_CLASS =
"COM.ibm.db2.jdbc.app.DB2Driver";
// Connection with database
Connection con = null;
// Query statement
Statement stmt = null;
static {
try {
// Register the driver with DriverManager
Class.forName(JDBC_DRIVER_CLASS);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Constructor
*
*/
XPathRetriever(String userid, String password) {
try {
// Creates connection
con = DriverManager.getConnection("jdbc:db2:"+XML_DB,
userid,
password);
// Creates statement
stmt = con.createStatement();
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
}
/**
* Search an XML document with document id
*
* @param docId document ID assigned at registration process
* @see XMLArchiver
*/
[71] public void searchByDocumentID(String docId) {
String SQLquery = "SELECT "+ COL_DOCUMENT_CONTENT +
" FROM " + DOCUMENT_TBL + " WHERE " +
COL_DOCUMENT_ID +" = '" + docId + "'";
try {
// Get result of the query
ResultSet rs = stmt.executeQuery(SQLquery);
// Checks the existence of result
if (!rs.next()) {
System.out.println("Not found");
return;
}
// Display Result
// Parses input document
DocumentBuilderFactory factory =
DocumentBuilderFactory.newInstance();
DocumentBuilder builder =
factory.newDocumentBuilder();
// Sets an ErrorHandler
builder.setErrorHandler(new MyErrorHandler());
// Only one document should be retrieved since
// document id is unique
// Gets data stream from results set
Document doc = builder.parse(
new InputSource(rs.getBinaryStream(1)));
System.out.println(toString(doc));
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Delete an XML document with document id
*
* @param docId document ID assigned at registration process
* @see XMLArchiver
*/
[109] public void deleteByDocumentID(String docId) {
String SQLquery = "DELETE " + " FROM " +
DOCUMENT_TBL + " WHERE "+
COL_DOCUMENT_ID +" = '" + docId + "'";
try {
int deletedLineNo = stmt.executeUpdate(SQLquery);
// Only one document should be deleted since document
// id is unique
if (deletedLineNo == 1) {
System.out.println("Document " +docId +
" has been deleted");
} else {
System.out.println("deletion failed");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Convert <code>Document</code> object to string
*
* @param doc a <code>Document</code> object
* @return its serialization
*/
public static String toString(Document doc) {
// Serializes the DOM tree as an XML document
OutputFormat formatter = new OutputFormat();
formatter.setPreserveSpace(true);
// The XML document will be serialized as string.
StringWriter out = new StringWriter() ;
XMLSerializer serializer = new XMLSerializer(out,
formatter);
try {
serializer.asDOMSerializer().serialize(doc);
} catch (Exception e) {
e.printStackTrace();
}
out.flush();
return out.toString();
}
public static void main(String[] argv) {
if (argv.length != 2) {
System.err.println("Usage: XPathRetriever " +
"[-s|-d] docid");
System.exit(1);
}
try {
// Userid and password are specified as
// System properties
String userid = System.getProperty("chap11.userid");
String password =
System.getProperty("chap11.password");
XPathRetriever arc = new XPathRetriever(userid,
password);
if (argv[0].equals("-s"))
arc.searchByDocumentID(argv[1]);
else if (argv[0].equals("-d"))
arc.deleteByDocumentID(argv[1]);
else
System.out.println("Not supported: "
+ argv[1]);
} catch (Exception e) {
e.printStackTrace();
}
}
}
The XPathRetriever program retrieves or deletes an XML document with an identifier assigned when it is stored. The program retrieves an XML document from the DOCUMENT_TBL table and returns it. The following is the result of the searching and deletion process.
R:\samples>java -Dchap11.userid="db2admin" -Dchap11.password=
"db2admin" chap11.XPathRetriever -s 35b39e75:e8b91faee7:-8000
<purchaseOrder orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
</shipTo>
<items>
<item qty="2">ThinkPad X21</item>
<item qty="1">ThinkPad T22</item>
</items>
</purchaseOrder>
R:\samples>java -Dchap11.userid="db2admin" -Dchap11.password=
"db2admin" chap11.XPathRetriever -d 35b39e75:e8b91faee7:-8000
Document 35b39e75:e8b91faee7:-8000 has been deleted
The searching process is handled by searchByDocumentID() (line 71) and the deletion process is done by the deleteByDocumentID() method (line 109). The details are not covered here, but it is easy to read the source code and determine what these methods do. The last sample program in this section searches for an XML document in a database with XPath. The program, XMLRetrieverWithXPath, is included on the CD-ROM. The class takes two arguments. The first is (a subset of) an XPath expression, and the second is its value. For example, a pair of "/purchaseOrder/items/item" and "ThinkPad X21" means the program should search an XML document that has an item element that contains "ThinkPad X21", and the element is the child of an item element that is a child of a purchaseOrder element. In the NODE_VALUE_TBL table, an XPath expression is stored with a position index like /purchaseOrder[1]/shipTo[1]/name[1]. It is included by the expression /purchaseOrder/items/item, but it is not matched literally. Our program converts an XPath expression into an SQL string. For example:
The following is an example of using this program. R:\samples>java -Dchap11.userid=db2admin -Dchap11.password=db2admin chap11.XPathArchiver chap11\po.xml Stored with ID 35b3a6e1:e8b961b433:-8000 /purchaseOrder[1]/@orderDate=1999-10-20 /purchaseOrder[1]/shipTo[1]/@country=US /purchaseOrder[1]/shipTo[1]/name[1]=Alice Smith /purchaseOrder[1]/shipTo[1]/street[1]=123 Maple Street /purchaseOrder[1]/items[1]/item[1]/@qty=2 /purchaseOrder[1]/items[1]/item[1]=ThinkPad X21 /purchaseOrder[1]/items[1]/item[2]/@qty=1 /purchaseOrder[1]/items[1]/item[2]=ThinkPad T22 R:\samples>java -Dchap11.userid=db2admin -Dchap11.password=db2admin chap11.XPathArchiver chap02\department.xml Stored with ID 3a6c26ef:e8b961df3b:-8000 /department[1]/employee[1]/@id=J.D /department[1]/employee[1]/name[1]=John Doe /department[1]/employee[1]/email[1]=John.Doe@foo.com /department[1]/employee[2]/@id=B.S /department[1]/employee[2]/name[1]=Bob Smith /department[1]/employee[2]/email[1]=Bob.Smith@foo.com /department[1]/employee[3]/@id=A.M /department[1]/employee[3]/name[1]=Alice Miller /department[1]/employee[3]/url[1]/@href=http://www.foo.com/~amiller/ R:\samples>java -Dchap11.userid=db2admin -Dchap11.password=db2admin chap11.XMLRetrieverWithXPath "/purchaseOrder/items/item" "ThinkPad X21" 35b3a6e1:e8b961b433:-8000 R:\samples>java -Dchap11.userid=db2admin-Dchap11.password=db2admin chap11.XMLRetrieverWithXPath "@id" "J.D" 3a6c26ef:e8b961df3b:-8000 R:\samples>java -Dchap11.userid=db2admin -Dchap11.password=db2admin chap11.XMLRetrieverWithXPath "/department[1]/employee[3]/name[1]" "Alice Miller" 3a6c26ef:e8b961df3b:-8000  |
| [ directory ] |
|