The Artima Developer Community
This article is sponsored by the Java Community Process.

Leading-Edge Java
Upcoming Features in JDBC 4
by Frank Sommers
September 2, 2005

<<  Page 2 of 3  >>

Advertisement

SQL 2003 support

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);

XML as a First-Class SQL Data Type

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:

create table user (int userid, firstname varchar(128), lastname varchar(128))

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

<<  Page 2 of 3  >>


This article is sponsored by the Java Community Process.

Sponsored Links



Google
  Web Artima.com   
Copyright © 1996-2014 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use - Advertise with Us