he new version of the Java Database Connectivity API (JDBC 4) is getting a major face-lift with many great new features. Perhaps the most important update is the support for the XML data type, defined by the SQL 2003 standard. While storing XML in the database and having your application make updates to it isn’t anything new, this is the first time that JDBC offers a mapping interface (java.sql.SQLXML) to support the SQL/XML database data type. Of course other interfaces, such as java.sql.Connection and java.sql.ResultSet, were updated as well to reflect this new addition.

Before the XML data type was introduced with the SQL 2003 standard, developers had to store XML as BLOB, CLOB, or TEXT types. Today, many database vendors already support the XML type as part of their products, but prior to JDBC 4, Java applications still had to do conversions between the XML data type on the database side and the types supported by JDBC. The new JDBC interfaces define Java native bindings for XML, thus making the handling of any database XML data easier and more efficient.

This article covers how to store and retrieve the XML data type with the new JDBC interfaces, and then drills down into source code examples for demonstration.

Storing and Retrieving XML Data
In order to store XML data to an XML type column in a table, you first call java.sql.Connection.createSQLXML(). You get back an instance of the newly introduced type, java.sql.SQLXML. You then have to add the XML data to the SQLXML object by calling either setOutputStream(), setCharacterStream(), or simply setString(String xml). Note that this functionality is very similar to the support of BLOB and CLOB types.

One of the key features of the new API is that you can also call setResult(Class resultClass) on java.sql.SQLXML and get back an instance of a class that implements javax.xml.transform.Result, which includes DOMResult, JAXBResult, and SAXResult among others. In other words, without any conversions you can simply:

1. Take your XML.
2. Create a DOMResult off it.
3. Set DOMResult to your java.sql.SQLXML object.
4. Directly store your XML into a database column by binding it to java.sql.Statement.

In order to retrieve the SQLXML type from java.sql.ResultSet, you would simply call getSQLXML by indicating the column name or index, as you would for any type. You then can retrieve the actual XML data from java.io.InputStream, java.io.Reader, or the plain old String by calling getBinaryStream(), getCharacterStream(), or getString(), respectively on the java.sql.SQLXML instance retrieved from ResultSet. Similarly to storing XML, you can also call getSource(Class sourceClass) on the SQLXML instance and thus be able to directly access the XML data from any class that implements javax.xml.transform.Source.

The Sample Program
Because JDBC 4 was officially released on December 11, 2006, very few databases offer drivers for it yet and none offer full support at this time. This example uses the latest Apache Derby version, 10.2, to introduce you to storing and querying XML data (see Listing 1. Sample Program Storing and Querying XML). Derby, however, does not yet support java.sql.SQLXML, which means that you cannot directly bind into or retrieve XML values from a result set. This may seem like a major shortcoming considering the purpose of this article, but since Derby is SQL 2003-compliant and can easily be used in the embedded mode, it still works for demonstrating how XML data retrieval would happen if you had a fully compliant driver available.

Derby’s XML operators, such as XMLPARSE and XMLSERIALIZE, help you convert data to character streams or strings for use in your program. Following each sample task in this example, I will also show how the task would be accomplished with a fully SQL/XML-compliant driver. In fact, you could substitute each task in the sample code with example snippets that use java.sql.SQLXML and the code still will successfully compile. However, running the program would result in a Derby-specific error, such as “Binding directly to an XML value is not allowed.” In short, the sample code primarily shows how to interact with an SQL/XML-compliant database. I will separately list a code snippet for each task that uses java.sql.SQLXML to do the same as the sample program.

First, create a simple table that includes an XML data type:

Statement s = c.createStatement();s.execute("CREATE TABLE ARTICLE(ID INTEGER, DATA XML)");

Insert XML Data
Because Derby does not yet support the java.sql.SQLXML type, when you insert data into the DATA column, you have to bind to some other type that can be parsed as XML. I use a CLOB type:

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) VALUES "+   "(?, XMLPARSE (DOCUMENT CAST (? AS CLOB) PRESERVE "+   "WHITESPACE))");			ps.setInt(1, id++);ps.setClob(2, new StringReader(insert));

Now, if you had a fully JDBC 4-compliant driver, you could accomplish the same by using java.io.Writer (remember, you can still compile the code with this change):

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) values (?, ?)");SQLXML article = c.createSQLXML();Writer writer = article.setCharacterStream();writer.write(insert);writer.close();ps.setInt(1, id++);ps.setSQLXML(2, article);

Or by using javax.xml.transform.dom.DOMSource:

ps = c.prepareStatement("INSERT INTO ARTICLE (ID, DATA) values (?, ?)");SQLXML article = c.createSQLXML();DOMResult dom = (DOMResult)article.setResult(DOMResult.class);dom.setNode(doc); // doc is instance of org.w3c.dom.Documentps.setInt(1, id++);ps.setSQLXML(2, article);

Retrieve XML Data
Similarly, when you retrieve the XML type from Derby, you have to convert the XML database type to character type:

ResultSet rs = s.executeQuery("SELECT XMLSERIALIZE (DATA AS CLOB) "+   "FROM ARTICLE WHERE ID = 2");

With support for java.sql.SQLXML, you can accomplish the same task by simply selecting the column with the XML database type. You would get the XML data directly. Suppose you would like to use the DOM parser to evaluate the XML retrieved from the result set:

PreparedStatement st = c.prepareStatement("SELECT ID, DATA FROM ARTICLE");ResultSet rs = st.executeQuery();while (rs.next()){   SQLXML article = rs.getSQLXML("DATA");   InputStream stream = article.getBinaryStream();   DocumentBuilder parser =    DocumentBuilderFactory.newInstance().newDocumentBuilder();   Document doc = parser.parse(stream);   // Do something...}

Instead of calling getBinaryStream(), you could have called getSource(Class sourceClass) and get back an instance of DOMSource, SAXSource, or any other class type that implements javax.xml.transform.Source.

XPath with XMLEXISTS
The final example of the sample program shows how to use XPath with the new XMLEXISTS predicate of SQL 2003:

Statement s = c.createStatement();ResultSet rs = s.executeQuery("SELECT ID FROM ARTICLE WHERE "+   "XMLEXISTS('//author[text()="John Smith"]' PASSING BY REF "+   "DATA)");

Note that you can also use the XMLQUERY function to execute any XQuery expressions. However, since both the XMLEXISTS predicate and XMLQUERY function are more SQL 2003 features than JDBC 4 features, this article doesn’t cover these features further.

Endless Possibilities
Support for SQL/XML can improve the clarity of your code and shorten development time. Usually there is a compelling reason why one would store XML data in a database. Perhaps one of the most common approaches to display different media online, such as articles, event listings, or product information, is to store the entity data as XML and use transformations to display that data online. The java.sql.SQLXML API documentation provides a plethora of examples of how to make transformations from the new data type, but consider the following, also listed in the Java SE 6 documentation:

File xsltFile = new File("transformer.xslt");File xhtmlFile = new File("xhtml.xml");Transformer xslt =   TransformerFactory.newInstance().newTransformer(new      StreamSource(xsltFile)); Source source = sqlxml.getSource(null);Result result = new StreamResult(xhtmlFile);xslt.transform(source, result);

The sqlxml variable of course is an instance of java.sql.SQLXML that you retrieved from the database. No conversions are required. In essence, you have been able to transform your XML content stored in the database into XHTML?in only five lines.