XML Support in JDBC 4.0: The SQLXML Interface

March 1, 2010

The SQL language standard includes XML as one of the SQL data types, which allows you to have columns of type XML in tables and result sets. The latest JDBC standard (JDBC 4.0) now includes an interface and data type called SQLXML that correspond to the XML data type in the database server.   

Of course you can use JDBC 3.0 to send and retrieve XML data to/from a database as a String, Clob, BinaryStream, CharacterStream, etc. with the traditional getter and setter methods. In addition to that, the new SQLXML interface in JDBC 4.0 enables you to use a DOM or SAX representation of your XML data when you send or receive it to/from the database. This can be very helpful if your application already uses the DOM or SAX API to manipulate XML.  

The SQLXML interface in JDBC 4.0 works for both SQL/XML and XQuery. So even if you use XQuery without any SQL involved, you can use JDBC to conveniently retrieve and process the result sequence that is produced by an XQuery. And if you use SQL/XML to insert, update, or retrieve XML and relational data at the same time, then JDBC is also very well suited for your needs. 

Let’s look at an example. First, be aware that JDBC 4.0 requires Java 6. To use JDBC 4.0 with DB2 you also need JCC4 (Java Common Client 4, db2jcc4.jar).  

 Assume you have a target table such as the following:  

create table mycustomer(info XML)

With JDBC 3.0 you would insert serialized XML from your Java application, using setBinaryStream(), setBlob(), or setString, etc. to bind your XML document to the parameter marker of an INSERT statement, like this:  

String sql = "INSERT INTO mycustomer(info) VALUES (?)";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setBinaryStream(....);
stmt.execute();

But, assume your application already holds an XML document as a DOM in a variable called “xmldocumentDOM”. Then you can bind the DOM to the parameter marker of the insert statement as follows:  

String sql = "INSERT INTO mycustomer(info) VALUES (?)";
PreparedStatement stmt = connection.prepareStatement(sql); 

SQLXML sqlxml = con.createSQLXML();
DOMResult domResult=sqlxml.setResult(DOMResult.class)
domResult.setNode(xmldocumentDOM); 

stmt.setSQLXML(1, sqlxml);
stmt.execute();

You can also use the SQLXML interface to fetch an XML column value (a document) from a JDBC resultSet directly into a DOM or SAX for further manipulation in your application, if needed.  You can call the getSQLXML() method on the resultSet object and then use the DOM or SAX API to operate on the retrieved XML document: 

ResultSet resultSet
    = statement.executeQuery("SELECT info FROM mycustomer WHERE ....");
resultSet.next(); 

// retrieve an SQLXML object from the ResultSet:
SQLXML sqlxml = resultSet.getSQLXML(1); // 1 is the column index 
// obtain DOM tree from SQLXML object:
DOMSource source = sqlxml.getSource(DOMSource.class); 

// create document object from DOMSource:
Document document = (Document) source.getNode(); 

// now you can process the DOM tree as usual...

You can find more information and examples here: 

About these ads

3 Responses to “XML Support in JDBC 4.0: The SQLXML Interface”

  1. toto Says:

    well it doesn’t work well for me :

    java.lang.ClassCastException: org.postgresql.jdbc4.Jdbc4SQLXML cannot be cast to [B
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRowBuffer(AbstractJdbc2ResultSet.java:1729)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateRow(AbstractJdbc2ResultSet.java:1291)

    when i use that :

    SQLXML sqlxml = con.createSQLXML();
    DOMResult domResult=sqlxml.setResult(DOMResult.class)
    domResult.setNode(xmldocumentDOM);

    stmt.setSQLXML(1, sqlxml);
    stmt.execute();

    Do you have an idea, why ?

  2. toto Says:

    thanks for the answer


  3. Hi…
    which database and which JDBC driver are you using? The exception that you posted shows “org.postgresql.jdbc2.”. Not every database and not every JDBC driver supports all the new features that the JDBC 4.0 standard has introduced. Maybe Postgress does not (yet) support it. Did you try it on DB2 9.7 Express? It’s available for free:

    http://www-01.ibm.com/software/data/db2/express/

    http://www.ibm.com/developerworks/downloads/im/udbexp/

    You also need to make sure that you use Java 1.6.

    Thanks,

    Matthias


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: