Using SQLXML data types

xml| Data | data type

Overview

Java EE developers typically need to store XML documents in relational databases and retrieve XML documents from the database for element values and attribute values. Currently, only vendor-specific technology and data types can be used to store XML documents in a relational database. For example, the IBM DB2 Universal database™ (UDB) provides DB2 XML Extender and XML user-defined types (UDT) to store XML documents. The standard data types used for XML type columns and the Java data types that correspond to XML type columns facilitate the storage and retrieval of XML documents in relational databases. The SQL:2003 standard supports such a data type: A new XML data type to store XML documents in a relational database. With XML data types, you can store XML documents in database table columns similar to other data types. DB2 Viper (that is, DB2 UDB Version 9.1) supports XML data types. The Java Database Connectivity (JDBC) 4.0 specification supports SQL:2003 standards.

Preliminary setup

Before you start, make sure you have the necessary components. SQLXML is a JDBC 4.0 data type. Because the SQLXML data type is implemented in the Sun-provided Java SE 6 Development Kit (JDK) 6.0 Beta, you need to install it. Another requirement is to create a SQLXML type object that is a relational database that supports XML data types. DB2 Viper (that is, DB2 UDB Version 9.1) supports XML data types. To run the sample application Sqlxmldb.java included in this article, you need to install the DB2 Viper database and create a sample DB2 database instance sample. JDK 6.0 Beta, DB2 Viper, and Sqlxmldb.java can be obtained from resources and downloads, respectively.

You also need a JDBC 4.0 driver, but DB2 UDB does not currently provide a JDBC 4.0 driver to run the Sqlxmldb.java with the DB2 JDBC 3.0 driver and determine if the DB2 database supports XML data types, you need to add the J AR file C:/Program Files/ibm/sqllib/java/db2jcc.jar and C:/Program Files/ibm/sqllib/java/db2jcc_license_cu.jar added to In Classpath. When the JDBC 4.0 driver becomes available, it is possible to run Sqlxmldb.java using the SQLXML API. This article will be updated when the JDBC 4.0 driver becomes available.

Begin

SQLXML Java data types allow you to map XML type database columns to Java data types. In the JDBC 4.0 specification, the Java.sql.Connection interface can create a SQLXML object that initially does not have any data, but you can use the SetString (String) method or the Createxmlstreamwriter () method to add data. The SQLXML object can be obtained from a columnName or ResultSet object using either the Getsqlxml (int columnindex) method or the Getsqlxml (String callablestatement) method. You can retrieve data from a SQLXML object using either the GetString () method or the Createxmlstreamreader () method, and you can also use the setsqlxml of the PreparedStatement interface (int parameterindex, S Qlxml SqlXML) method stores a SqlXML object in a database table column that resembles a type XML of any other data type.

Listing 1 shows an example of an XML document that you can create, namely Catalog.xml:

Listing 1. Sample XML Document Catalog.xml

<catalog title= "XML Zone" publisher= ' IBM developerWorks ' >
<journal date= "2006" >
<article>
<title>managing XML Data:tag uris</title>
<author>elliotte harold</author>
</article>
<article>
<title>practical data Binding:xpath as data binding tool, part 2</title>
<author>brett mclaughlin</author>
</article>
</journal>
</catalog>

Select Database

A relational database is needed to support XML data types to store XML documents, and not all databases support XML data types. To determine whether your database supports the database type, you need to obtain database metadata from the Connection object. For example, to test whether the DB2 Viper supports XML data types, you need to load and register the Com.ibm.db2.jcc.DB2Driver JDBC driver, that is, the DB2 JDBC Type 4 driver:

Class.forName ("Com.ibm.db2.jcc.DB2Driver");

Specifies the connection URL for the DB2 UDB database. The format of the DB2 UDB Type 4 driver is jdbc:db2://<server>:<port>/<database>. In this connection URL,<server> is DB2 UDB server name,<port> is DB2 database port, and <database> is a DB2 database instance. The connection URL for the DB2 UDB database is:

String url = "Jdbc:db2://localhost:50000/sample";

Using DriverManager static method Getconnection () to obtain the Connection with the database:

Connection Connection = drivermanager.getconnection (URL,
"username", "password");

Next, get the database meta data from the Connection object:

DatabaseMetaData metadata = Connection.getmetadata ();

Then, use the GetTypeInfo () method to obtain the data types supported by the database:

ResultSet rs = Metadata.gettypeinfo ();

Iterate over the data type result set and output the Type_name column:

System.out.println ("Type_name:" +rs.getstring ("type_name"));

If the database supports XML data types, the XML type_name gets the output. Because the DB2 Viper database supports XML data types, the output from running the Sqlxmldb.java application includes the XML data type, as follows:

Type_name:xml

Creating SQLXML Objects

This section discusses the process of creating a SQLXML object. First, import java.sql package and Javax.xml.stream packages. The Javax.xml.stream package has the Xmlstreamwriter and Xmlstreamreader interfaces needed to instantiate the SQLXML object and obtain the data in the SQLXML object.

Import java.sql.*;
Import javax.xml.stream.*;

The Java representation of an XML document in a database table column of type XML is SQLXML. To create an XML document that will be stored in an XML type column, you need to create a SQLXML object from the Connection object using the Createsqlxml () method:

SQLXML SQLXML = Connection.createsqlxml ();

The SQLXML object created with the Createsqlxml () method does not contain any data. The next section shows how to add data to the SQLXML object.

Initializing the SQLXML object

You can use the Xmlstreamwriter object to create an XML document. You can use the Createxmlstreamwriter () method to create a Xmlstreamwriter object from a SQLXML object:

Xmlstreamwriter xmlstreamwriter = Sqlxml.createxmlstreamwriter ();

If the Createxmlstreamwriter () method is invoked instead of the same SQLXML object, the SQLException is thrown, and the previously returned Xmlstreamwriter object is unaffected.

Adds the beginning portion of an XML document with the WriteStartDocument (string encoding, string version) method. The encoding specified in the WriteStartDocument () method does not encode the output of the XML document; The specified encoding sets only the encoding in the XML declaration. The Xmlstreamwriter interface also includes the WriteStartDocument () method and the WriteStartDocument (String version) method, which is used to create an XML document without specifying the encoding and the version. The latter is used to create an XML document when the version is provided:

Xmlstreamwriter.writestartdocument ("UTF-8", "1.0");

Add the beginning of the root element catalog with the WriteStartElement (String localname) method, as shown in the following code fragment. Use the WriteStartElement (string prefix, String localname, String NamespaceURI) method to create an element based on the namespace and prefix. Use the Writeemptyelement (String localname) method to generate an empty element:

Xmlstreamwriter.writestartelement ("Catalog");

Add the Title property with the WriteAttribute (string localname, String value) method, as shown in the following code fragment. Similarly, you can add Publisher properties. If the attribute has a namespace prefix, the method writeattribute (string prefix, String NamespaceURI, String localname, String value) is used:

Xmlstreamwriter.writeattribute ("title", "XML Zone");
Xmlstreamwriter.writeattribute ("publisher", "IBM developerWorks");

Similarly, you can add journal elements and their properties date:

Xmlstreamwriter.writestartelement ("journal");
Xmlstreamwriter.writeattribute ("Date", "2006");

Add a article element and a TITLE element:

Xmlstreamwriter.writestartelement ("article");
Xmlstreamwriter.writestartelement ("title");

Use the Writecharacters (String text) method to add text to the title element, as shown in the following code fragment. In addition, you can add text from the char[] array using the Writecharacters (char[] text,int start,int len) method.

Xmlstreamwriter.writecharacters ("Managing XML data:tag URIs");

Make sure that the start element has a corresponding end element tag. Add an end element using the WriteEndElement () method:

Xmlstreamwriter.writeendelement ();

The WriteEndElement () method does not specify the local name of the element as the WriteStartElement () method. Similarly, you can add additional elements to create the XML document shown in Listing 1. To complete the XML document, call the WriteEndDocument () method. Also, close the Xmlstreamwriter object:

Xmlstreamwriter.writeenddocument ();
Xmlstreamwriter.close ();

You can also add an XML document from an XML string to a SQLXML object using the SQLXML interface's SetString (string) method, as shown in Listing 2. If the SetString (String) method is invoked on the previously initialized SQLXML object, the SQLException is thrown.

Listing 2. To add an XML document to a SQLXML object
Sqlxml.setstring ("<catalog title=" XML Zone "publisher= ' IBM developerWorks ' >
<journal date= "2006" >
<article>
<title>managing XML Data:tag uris</title>
<author>elliotte harold</author>
</article>
<article>
<title>practical data Binding:xpath as data binding tool, part 2</title>
<author>brett mclaughlin</author>
</article>
</journal>
</catalog> ");

The next section explains how to store the created SQLXML object in a database.

Storing SQLXML objects

SQLXML Java data types are stored in XML documents, just like any other Java data type. First, create a database table with an XML type column. You can use the SQL command-line tool or the JDBC API to create database tables with XML type columns. To create a database table with JDBC, you need to obtain a Statement object from the Connection object:

Statement stmt = Connection.createstatement ();

Create a database table with an XML type column, such as Catalog:

Stmt.executeupdate ("CREATE Table Catalog (catalogid INTEGER, Catalog XML)");

Add the SQLXML object created in the previous section to the database. Create a PreparedStatement object to add values to the database table Catalog. PreparedStatement consists of the INSERT SQL statement and the parameter markers for those values that are added to the database:

PreparedStatement statement =
Connection.preparestatement ("INSERT into CATALOG (Catalogid, CATALOG) VALUES (?,?)");

Sets the int value with the setint (int parameterindex, int value) method, using the setsqlxml (int parameterindex, SQLXML value) method of the PreparedStatement interface to set the Place the SQLXML value, as shown below. If the Xmlstreamwriter object is not closed before the SetSqlXml () method is invoked, the SQLException is thrown.

Statement.setint (1, 1);
Statement.setsqlxml (2, SqlXML);

To update a database with the Executeupdate () method:

Statement.executeupdate ();

The SQLXML object is valid at least during the creation of the SQLXML object. If you do not use a SQLXML object, you can use the free () method to release the SQLXML object resource:

Sqlxml.free ();

Update SQLXML objects

JDK 6.0 Beta also provides some updater methods in the ResultSet interface to update SQLXML values. Updater Method Updatesqlxml (int columnindex, SQLXML SQLXML) and Updatesqlxml (String columnName, SQLXML SQLXML) modify SQLXML values in the current row or ResultSet the inserted row of the object. For example, to add a new row to a ResultSet object, you need to obtain an ResultSet type of Statement object that is not updatable:

Statement stmt = connection.createstatement (
Resultset.type_scroll_insensitive,
resultset.concur_updatable);

Obtain a ResultSet from the database table Catalog, which has an XML-type column. You can move ResultSet Grand to insert a row. You can also add SQLXML column values using the Updatesqlxml () method. The Updatesqlxml () method does not update the database. To update the database, you need to invoke the InsertRow () method:

ResultSet rs = stmt.executequery ("SELECT * from Catalog");
Rs.movetoinsertrow ();
Rs.updatesqlxml (2, Xmlobject);
Rs.insertrow ();

In addition, to update ResultSet from the current row in a scrollable ResultSet, you need to move to the ResultSet row using the absolute (int) or relative (int) method. You can modify the SQLXML value using one of the updater methods. To update a database using the Updaterow () method:

Rs.absolute (5);
Updatesqlxml ("Catalog", Xmlobject)
Rs.updaterow ();

If the Xmlstreamwriter object is not closed before the updater method is called, the SQLException is thrown.

Retrieving SQLXML objects from the ResultSet object

Retrieves an XML database type column value as a SQLXML data type. Create a PreparedStatement for the SELECT SQL query to obtain ResultSet from the database table Catalog, as shown in the following code fragment. The SQL statement has a parameter marker for the Catalogid value.

PreparedStatement stmt =
Connection.preparestatement ("select * from CATALOG WHERE catalogid=?");

Specifies the Catalogid value from which the XML document will be retrieved:

Stmt.setint (1, 1);

Obtain a result set using the ExecuteQuery () method:

ResultSet rs = Stmt.executequery ();

Use the ResultSet interface's method getsqlxml (int columnindex) or getsqlxml (String columnName) to obtain the Catalog object for the XML type SQLXML column:

SQLXML SQLXML = Rs.getsqlxml ("Catalog");

To output an XML document in a SQLXML object, you may need to use the getString () method of the SQLXML interface:

System.out.println (Sqlxml.getstring ());

Accessing SQLXML objects

Reads an XML document from a SQLXML object that has an event iterator using the Xmlstreamreader interface. Create a Xmlstreamreader object from the SQLXML object that will be navigated:

Xmlstreamreader Xmlstreamreader = Sqlxml.createxmlstreamreader ();

The next parsing event is obtained using the next () method:

while (Xmlstreamreader.hasnext ()) {
int parseevent = Xmlstreamreader.next ();
}

The next () method returns an int value corresponding to the Xmlstreamconstants constant. Table 1 lists the return values for the next () method.

Table 1. Method Next () returns the value

Parsing event Description
attribute specifies a property
CDATA CDATA Parts
CHARACTERS text
Notation_declaration Specify a symbolic declaration
COMMENT XML Document annotations
Processing_instruction Specify a processing instruction
Start_document Specify the beginning of the document
Start_element at the beginning of the specified element
End_element at the end of the specified element
Entity_declaration Specify an entity declaration
Entity_reference Specify an entity reference
NAMESPACE specifies a namespace declaration
Space to specify an negligible whitespace
End_document the end of the specified document
DTD specifies a DTD

If the return value is start_element, the parsing event indicates that an element has been parsed. You can use the method Getlocalname (), Getprefix (), Getnamespaceuri () to get the local name, prefix, and namespace elements:

if (parseevent==xmlstreamconstants.start_element) {
System.out.println ("Element local Name:" +xmlstreamreader.getlocalname ());
System.out.println ("Element Prefix:" +xmlstreamreader.getprefix ());
System.out.println ("Element Namespace:" +xmlstreamreader.getnamespaceuri ());
}

You can use the Getattributecount () method to get the total number of attributes in an element. You can also iterate over the property, use the Getattributelocalname () method to get the property local name, use the Getattributevalue () method to get the property value, and use the Getattributeprefix () method to get the property prefix. and using the Getattributenamespace () method to get the property namespace:

for (int i=0; I<xmlstreamreader.getattributecount (); i++) {

System.out.println ("Attribute Prefix:" +xmlstreamreader.getattributeprefix (i));
System.out.println ("Attribute Namespace:" +xmlstreamreader.getattributenamespace (i));
System.out.println ("Attribute local Name:" +xmlstreamreader.getattributelocalname (i));
System.out.println ("Attribute Value:" +xmlstreamreader.getattributevalue (i));
}

If the type of the parsing event is CHARACTERS, you can use the GetText () method to obtain the text that resolves the event:

if (parseevent==xmlstreamconstants.characters) {
System.out.println ("CHARACTERS text:" +xmlstreamreader.gettext ());
}

Conclusion

The SQLXML data type provides a database to Java mapping tool for database type XML. You can use SQLXML data types for any database that supports XML data types and provides JDBC 4.0 drivers. Vendor-specific Xml-to-sql APIs are not required for SQLXML. The SQLXML API will be included in JDK 6.0. DB2 Viper supports XML data types.

Download: source code Download