Using SQLXML Objects使用SQLXML对象

The Connection interface provides support for the creation of SQLXML objects using the method createSQLXML. Connection接口支持使用createSQLXML方法创建SQLXML对象。The object that is created does not contain any data. 创建的对象不包含任何数据。Data may be added to the object by calling the setString, setBinaryStream, setCharacterStream or setResult method on the SQLXML interface.可以通过调用SQLXML接口上的setString、setBinaryStream、setCharacterStream或setResult方法将数据添加到对象中。

The following topics are covered:涵盖以下主题：

• Creating SQLXML Objects创建SQLXML对象
• Retrieving SQLXML Values in ResultSet在ResultSet中检索SQLXML值
• Accessing SQLXML Object Data访问SQLXML对象数据
• Storing SQLXML Objects存储SQLXML对象
• Initializing SQLXML Objects初始化SQLXML对象
• Releasing SQLXML Resources释放SQLXML资源
• Sample Code示例代码

Creating SQLXML Objects创建SQLXML对象

In the following excerpt, the method Connection.createSQLXML is used to create an empty SQLXML object. The SQLXML.setString method is used to write data to the SQLXML object that was created.

Connection con = DriverManager.getConnection(url, props);
SQLXML xmlVal = con.createSQLXML();
xmlVal.setString(val);

Retrieving SQLXML Values in ResultSet在ResultSet中检索SQLXML值

The SQLXML data type is treated similarly to the more primitive built-in types. A SQLXML value can be retrieved by calling the getSQLXML method in the ResultSet or CallableStatement interface.

For example, the following excerpt retrieves a SQLXML value from the first column of the ResultSet rs:

SQLXML xmlVar = rs.getSQLXML(1);

SQLXML objects remain valid for at least the duration of the transaction in which they are created, unless their free method is invoked.

Accessing SQLXML Object Data访问SQLXML对象数据

The SQLXML interface provides the getString, getBinaryStream, getCharacterStream, and getSource methods to access its internal content. The following excerpt retrieves the contents of an SQLXML object using the getString method:

SQLXML xmlVal= rs.getSQLXML(1);
String val = xmlVal.getString();

The getBinaryStream or getCharacterStream methods can be used to obtain an InputStream or a Reader object that can be passed directly to an XML parser. The following excerpt obtains an InputStream object from an SQLXML Object and then processes the stream using a DOM (Document Object Model) parser:

SQLXML sqlxml = rs.getSQLXML(column);
InputStream binaryStream = sqlxml.getBinaryStream();
DocumentBuilder parser = 
    DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document result = parser.parse(binaryStream);

The getSource method returns a javax.xml.transform.Source object. Sources are used as input to XML parsers and XSLT transformers.

The following excerpt retrieves and parses the data from a SQLXML object using the SAXSource object returned by invoking the getSource method:

SQLXML xmlVal= rs.getSQLXML(1);
SAXSource saxSource = sqlxml.getSource(SAXSource.class);
XMLReader xmlReader = saxSource.getXMLReader();
xmlReader.setContentHandler(myHandler);
xmlReader.parse(saxSource.getInputSource());

Storing SQLXML Objects存储SQLXML对象

A SQLXML object can be passed as an input parameter to a PreparedStatement object just like other data types. The method setSQLXML sets the designated PreparedStatement parameter with a SQLXML object.

In the following excerpt, authorData is an instance of the java.sql.SQLXML interface whose data was initialized previously.

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO bio " +
                              "(xmlData, authId) VALUES (?, ?)");
pstmt.setSQLXML(1, authorData);
pstmt.setInt(2, authorId);

The updateSQLXML method can be used to update a column value in an updatable result set.updateSQLXML方法可用于更新可更新结果集中的列值。

If the java.xml.transform.Result, Writer, or OutputStream object for the SQLXML object has not been closed prior to calling setSQLXML or updateSQLXML, a SQLException will be thrown.

Initializing SQLXML Objects初始化SQLXML对象

The SQLXML interface provides the methods setString, setBinaryStream, setCharacterStream, or setResult to initialize the content for a SQLXML object that has been created by calling the Connection.createSQLXML method.

The following excerpt uses the method setResult to return a SAXResult object to populate a newly created SQLXML object:

SQLXML sqlxml = con.createSQLXML();
SAXResult saxResult = sqlxml.setResult(SAXResult.class);
ContentHandler contentHandler = saxResult.getXMLReader().getContentHandler();
contentHandler.startDocument();
    
// set the XML elements and
// attributes into the result
contentHandler.endDocument();

The following excerpt uses the setCharacterStream method to obtain a java.io.Writer object in order to initialize a SQLXML object:以下摘录使用setCharacterStream方法获取java.io.Writer对象以初始化SQLXML对象：

SQLXML sqlxml = con.createSQLXML();
Writer out= sqlxml.setCharacterStream();
BufferedReader in = new BufferedReader(new FileReader("xml/foo.xml"));
String line = null;
while((line = in.readLine() != null) {
    out.write(line);
}

Similarly, the SQLXML setString method can be used to initialize a SQLXML object.

If an attempt is made to call the setString, setBinaryStream, setCharacterStream, and setResult methods on a SQLXML object that has previously been initialized, a SQLException will be thrown. If more than one call to the methods setBinaryStream, setCharacterStream, and setResult occurs for the same SQLXML object, a SQLException is thrown and the previously returned javax.xml.transform.Result, Writer, or OutputStream object is not affected.

Releasing SQLXML Resources释放SQLXML资源

SQLXML objects remain valid for at least the duration of the transaction in which they are created. SQLXML对象至少在创建它们的事务期间保持有效。This could potentially result in an application running out of resources during a long running transaction. 这可能会导致应用程序在长时间运行的事务中耗尽资源。Applications may release SQLXML resources by invoking their free method.应用程序可以通过调用其free方法来释放SQLXML资源。

In the following excerpt, the method SQLXML.free is called to release the resources held for a previously created SQLXML object.在下面的摘录中，调用了SQLXML.free方法来释放为先前创建的SQLXML对象保留的资源。

SQLXML xmlVar = con.createSQLXML();
xmlVar.setString(val);
xmlVar.free();

Sample Code示例代码

MySQL and Java DB and their respective JDBC drivers do not fully support the SQLXML JDBC data type as described on in this section. However, the sample RSSFeedsTable demonstrates how to handle XML data with MySQL and Java DB.

The owner of The Coffee Break follows several RSS feeds from various web sites that cover restaurant and beverage industry news. 咖啡休息时间的负责人关注来自不同网站的几个RSS源，这些网站涵盖了餐厅和饮料行业的新闻。An RSS (Really Simple Syndication or Rich Site Summary) feed is an XML document that contains a series of articles and associated metadata, such as the date of publication and author for each article. RSS(Really Simple Syndication或Rich Site Summary）提要是一个XML文档，其中包含一系列文章和相关元数据，例如每篇文章的发布日期和作者。The owner would like to store these RSS feeds into a database table, including the RSS feed from The Coffee Break's blog.所有者希望将这些RSS提要存储到一个数据库表中，包括咖啡休息的博客中的RSS提要。

The file rss-the-coffee-break-blog.xml is an example RSS feed from The Coffee Break's blog.

Working with XML Data in MySQL在MySQL中处理XML数据

The sample RSSFeedsTable stores RSS feeds in the table RSS_FEEDS, which is created with the following command:

create table RSS_FEEDS
    (RSS_NAME varchar(32) NOT NULL,
    RSS_FEED_XML longtext NOT NULL,
    PRIMARY KEY (RSS_NAME));

MySQL does not support the XML data type. Instead, this sample stores XML data in a column of type LONGTEXT, which is a CLOB SQL data type. MySQL has four CLOB data types; the LONGTEXT data type holds the greatest amount of characters among the four.

The method RSSFeedsTable.addRSSFeed adds an RSS feed to the RSS_FEEDS table. The first statements of this method converts the RSS feed (which is represented by an XML file in this sample) into an object of type org.w3c.dom.Document, which represents a DOM (Document Object Model) document. This class, along with classes and interfaces contained in the package javax.xml, contain methods that enable you to manipulate XML data content. For example, the following statement uses an XPath expression to retrieve the title of the RSS feed from the Document object:

Node titleElement =
    (Node)xPath.evaluate("/rss/channel/title[1]",
        doc, XPathConstants.NODE);

The XPath expression /rss/channel/title[1] retrieves the contents of the first <title> element. For the file rss-the-coffee-break-blog.xml, this is the string The Coffee Break Blog.

The following statements add the RSS feed to the table RSS_FEEDS:以下语句将RSS提要添加到表RSS_FEEDS中：

// For databases that support the SQLXML
// data type, this creates a
// SQLXML object from
// org.w3c.dom.Document.

System.out.println("Adding XML file " + fileName);
String insertRowQuery =
    "insert into RSS_FEEDS " +
    "(RSS_NAME, RSS_FEED_XML) values " +
    "(?, ?)";
insertRow = con.prepareStatement(insertRowQuery);
insertRow.setString(1, titleString);

System.out.println("Creating SQLXML object with MySQL");
rssData = con.createSQLXML();
System.out.println("Creating DOMResult object");
DOMResult dom = (DOMResult)rssData.setResult(DOMResult.class);
dom.setNode(doc);

insertRow.setSQLXML(2, rssData);
System.out.println("Running executeUpdate()");
insertRow.executeUpdate();

The RSSFeedsTable.viewTable method retrieves the contents of RSS_FEEDS. For each row, the method creates an object of type org.w3c.dom.Document named doc in which to store the XML content in the column RSS_FEED_XML. The method retrieves the XML content and stores it in an object of type SQLXML named rssFeedXML. The contents of rssFeedXML are parsed and stored in the doc object.

Working with XML Data in Java DB在JavaDB中使用XML数据

Note: See the section "XML data types and operators" in Java DB Developer's Guide for more information about working with XML data in Java DB.：有关在Java DB中使用XML数据的更多信息，请参阅《Java DB开发人员指南》中的“XML数据类型和运算符”一节。

The sample RSSFeedsTable stores RSS feeds in the table RSS_FEEDS, which is created with the following command:样例RSSFeedsTable将RSS提要存储在表RSS_FEEDS中，该表是使用以下命令创建的：

create table RSS_FEEDS
    (RSS_NAME varchar(32) NOT NULL,
    RSS_FEED_XML xml NOT NULL,
    PRIMARY KEY (RSS_NAME));

Java DB supports the XML data type, but it does not support the SQLXML JDBC data type. JavaDB支持XML数据类型，但不支持SQLXMLJDBC数据类型。Consequently, you must convert any XML data to a character format, and then use the Java DB operator XMLPARSE to convert it to the XML data type.因此，必须将任何XML数据转换为字符格式，然后使用Java DB运算符XMLPARSE将其转换为XML数据类型。

The RSSFeedsTable.addRSSFeed method adds an RSS feed to the RSS_FEEDS table. The first statements of this method convert the RSS feed (which is represented by an XML file in this sample) into an object of type org.w3c.dom.Document. This is described in the section Working with XML Data in MySQL.

The RSSFeedsTable.addRSSFeed method converts the RSS feed to a String object with the method JDBCTutorialUtilities.convertDocumentToString.

Java DB has an operator named XMLPARSE that parses a character string representation into a Java DB XML value, which is demonstrated by the following excerpt:Java DB有一个名为XMLPARSE的运算符，该运算符将字符串表示形式解析为Java DB XML值，如下摘录所示：

String insertRowQuery =
    "insert into RSS_FEEDS " +
    "(RSS_NAME, RSS_FEED_XML) values " +
    "(?, xmlparse(document cast " +
    "(? as clob) preserve whitespace))";

The XMLPARSE operator requires that you convert the character representation of the XML document into a string data type that Java DB recognizes. In this example, it converts it into a CLOB data type. See Getting Started and the Java DB documentation for more information about Apache Xalan and Java DB requirements.有关ApacheXalan和JavaDB需求的更多信息，请参阅入门和JavaDB文档。

The method RSSFeedsTable.viewTable retrieves the contents of RSS_FEEDS. Because Java DB does not support the JDBC data type SQLXML you must retrieve the XML content as a string. Java DB has an operator named XMLSERIALIZE that converts an XML type to a character type:

String query =
    "select RSS_NAME, " +
    "xmlserialize " +
    "(RSS_FEED_XML as clob) " +
    "from RSS_FEEDS";

As with the XMLPARSE operator, the XMLSERIALIZE operator requires that Apache Xalan be listed in your Java class path.与XMLPARSE运算符一样，XMLSERIALIZE运算符要求在Java类路径中列出Apache Xalan。