|
|
|
|
This article is sponsored by the Java Community Process.
|
|
Advertisement
|
After a less-than-enthusiastic reception of object-relational database features in the late 90s, the relational database research community has focused on accommodating new data types used in recent applications, and on making access to vast data stores faster and more convenient. The result of that effort is SQL 2003, the latest SQL standard [2]. SQL 2003 is not simply an incremental upgrade to the database standard, but also introduces many new features in response to database users' needs.
JDBC 4 provides support for those SQL 2003 features most likely to find
their way into commercial products in the near future. In addition to the XML
data types discussed in the next section, JDBC introduces support for ROWID, a
data type that had been in use in database products even before it became part
of the SQL standard. The best way to understand ROWID is as a reference, or
address, unique for each table row. For example, you can use a ROWID as a parameter in a PreparedStatement:
Connection c = myDataSource.getConnection();
PreparedStatement st = c.prepareStatement("insert into siteusers (userid, username) values
(?, ?);
st.setRowId(1, rowId1);
The above code would set the user's userid to the value referred to by the
rowId1 ROWID, which you could have obtained from a previous select statement.
The ResultSet and PreparedStatement classes were
updated in JDBC 4 to handle ROWIDs.
In addition to ROWID, JDBC 4 provides mappings for the following SQL 2003 data types:
| SQL 2004 type | JDBC 4 mapping |
|---|---|
| BLOB | java.sql.Blob |
| CLOB | java.sql.Clob |
| NCLOB (Character Large Object using the National Character Set) | java.sql.NClob |
| ARRAY | java.sql.Array |
| Structured types | java.sql.Struct |
| REF (reference to SQL structured type in the database) | java.sql.Ref |
| DISTINCT | Type to which base type is mapped, e. g., Based on SQL's NUMERIC type maps to java.math.BigDecimal. |
| DATALINK | java.net.URL |
As databases become increasingly capable of storing large amounts of binary
and textual data, JDBC 4 offers extended support for the CLOB (character large
object) and BLOB (binary large object) SQL data types. In order to make it
easier to work with large binary or text objects that could be inserted into a
database, the Connection, Statement and
ResultSet interfaces in JDBC 4 offer methods for the creation,
insertion, and retrieval of CLOBs and BLOBs, respectively.
When creating a new BLOB or CLOB object through Connection's
appropriate create() method, the resulting object does not contain
the actual binary or character data. Rather, you would add the "real" data to
the CLOB or BLOB objects:
Connection c = myDataSource.getConnection(); Blob myBlob = c.createBlob(); OutputStream outStream = myBlob.setBinaryStream(0);
Having obtained the BLOB's OutputStream, you can write your
actual data, such as an image or a binary document, to that stream. The
parameter to getBinaryStream() denotes the byte offset where the
new OutputStream starts writing its data. Once you've created and
populated a BLOB or CLOB, you can use those objects in
PreparedStatements as well:
PreparedStatement st =
c.prepareStatement("update sitesusers set user_photo=? where userid=?");
st.setBlob(1, myBlob);
Perhaps the most significant enhancement in SQL 2003 is support for XML as a native database data type [3]. Many database products have offered native XML support prior to the emergence of SQL 2003, and in many ways the standard is a codification of common practice. But until JDBC 4, Java clients could access XML data elements only as BLOBs, CLOBs, or even TEXT types.
The best way to understand SQLXML, the new JDBC 4 mapping of the SQL XML type, is with an example. Suppose you store a user's blog posts in an XML document format, and you'd like to iterate over those posts and display them in a Web page. The following database table would hold this information:
create table user_has_blog(userid int, blog_entry xml);
Using JDBC 4's SQLXML data type, you could retrieve a user's blog entries as follows:
Connection c = myDataSource.getConnection();
PreparedStatement st = c.prepareStatement("select userid, blog_entry from user_has_blog");
ResultSet rs = st.executeQuery();
while (rs.next()) {
SQLXML blog = st.getSQLXML("blog_entry");
javax.xml.stream.XMLStreamReader reader = blog.createXMLStreamReader();
//read data here
blog.free();
}
SQLXML's createXMLStreamReader() returns a StAX stream reader,
which is a low-level interface allowing access to an XML stream [4]. The Streaming API for XML (StAX) is
currently being developed as JSR 173 [5].
See the reference at the end of this article about using StAX to read and write
XML data. Whenever you've exhausted the XML stream, you need to invoke
close() on SQLXML to free resources associated with the XML data
stream.
While not strictly related to JDBC 4, databases that support SQL 2003's XML
extensions also allow you to employ the new XMLELEMENT operator.
XMLELEMENT is used to create an XML element. The first required
argument to XMLELEMENT is the name of the element to be
constructed, specified as an SQL NAME. An optional second argument
can specify attributes for the newly created element. Arguments following that
make up the content of the XML element. To illustrates the XMLELEMENT operator,
consider the following table:
The following SQL selects all users with their first and last names into
XML User elements:
select e.userid,
XMLELEMENT(NAME
"user", e.firstname || e.lastname) as "result" from employee e;
The result of that query will be a two-column relation:
| userid | result |
|---|---|
| 1 | <user>George Washington</user> | 2 | <user>Thomas Jefferson</user> |
Creating and inserting into the database a new SQLXML value is similar to
working with BLOBs. You invoke Connection's
createSQLXML() method, and then populate the resulting SQLXML's
input stream with the XML content:
Connection c = myDataSource.getConnection();
PreparedStatement st = c.prepareStatement("insert into user_has_blog (userid, blog_entry) values (?, ?)");
SQLXML blogEntry = c.createSQLXML();
Writer writer = blogEntry.createXMLSteamWriter();
//write XML content to writer
st.setInt(1, 1); //User id
st.setSQLXML(2, blogEntry); //User's blog
//Commit transaction
|
This article is sponsored by the Java Community Process.
|
|
Sponsored Links
|