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:
- Take your XML.
- Create a DOMResult off it.
- Set DOMResult to your java.sql.SQLXML object.
- 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.