| |
What's New in JDBC 4.0?ResultSet Becomes More FlexibleROWID Data TypeArrayConnection and PooledConnectionDatabaseMetaDataStatement, PreparedStatement, and CallableStatementAmong many new features and enhancements in Java SE 6 is the Java Database Connectivity (JDBC) API library, which is updated to JDBC 4.0 in this version of Java SE. JDBC 4.0, specified as JSR-221 under the Java Community Process, provides better code manageability and flexibility, and more complex data type support. In this article I will explore some new features and enhancements available in JDBC 4.0 from the perspective of programming ease and flexibility.
The JDBC 4.0 specification lists some twenty new features or enhancements, some of which are major, others minor. Because it is not possible to discuss all of them in this article, I've attempted to group them in following categories based on functionality they provide or the area(s) the changes fall into.
For the remainder of this article, I will be explaining these categories in more details.
The use of drivers--connection and result set management--sees a number of new features and enhancements in different JDBC aspects. The following three aspects are the most significant.
If you have been programming JDBC for some time, then I'm sure
you still have the typical laundry list of tasks to follow for
establishing a connection to the target database. The first in the
list is to load a suitable driver. Did you think this process
should have been improved, or ever would be? They did exactly that
in this version of JDBC. You don't have to explicitly load the
driver by calling Class.forName anymore. When your
application attempts to connect the database for the first time,
DriverManager automatically loads the driver found in
the application CLASSPATH. This is one of the great
additions in this version of JDBC.
Although DriverManager now automatically loads the
driver, creating an appropriate DataSource object is
still the preferred way to retrieve a connection. It allows
transparency and portability because the properties of the data
source instance can be modified to retrieve a connection to a
different database. You don't need to change any application code
to connect to a different database instance, or a completely
different type of database that requires loading a different
driver.
ResultSet Becomes More FlexibleThe hierarchy of the ResultSet interfaces now offers
several important features for programming flexibility. The
RowSet sub-interface provides a scrollable, updatable,
and offline-editable ResultSet. The
WebRowSet sub-interface provides the ability to read
data from database tables, serialize them to an XML document, and
deserialize from XML back to result set. I've discussed these
features in more detail in the OnJava article "
Making the Most of JDBC with WebRowSet." Although
the RowSet interface hierarchy was introduced in previous
version of JDBC, the current version's support for the SQLXML data
type (discussed later) along with these features makes JDBC
programming easier and more flexible.
More APIs have been added to this version of JDBC to provide access to commonly implemented features of SQL:2003. In addition, several new methods have been added to commonly used interfaces to support easier and better data manipulation.
Now, let's review some working code and discuss the result of
the Example1 class below. It will connect to an embedded
Apache Derby database and display the query results in the console.
Although JDBC 4.0 has been out for several months now, I found
Apache Derby is the only database to provide a driver that even
partially supports the JDBC 4.0 specification at the time of this
writing (March 2007). All examples in this article are developed
using JDK 1.6 and
Apache Derby database
version 10.2.2.0. For all code examples, I will show only snippets
of the actual Java source file that are relevant to the discussion.
See the Resources section for complete
sample code discussed in this article.
public class Example1 {
public static void main(String[] args) {
...
String dbName = "example1";
String tableName = "stu1";
ds = new EmbeddedDataSource40();
ds.setDatabaseName(dbName);
String connectionURL = "jdbc:derby:"+dbName+";create=true";
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select * from "+tableName);
int colCount= rs.getMetaData().getColumnCount();
for (int j=0; j< colCount; j++){
System.out.print(rs.getMetaData().getColumnName(j+1)
+ "\t");
}
while (rs.next()) {
System.out.print("\n");
for (int i = 0; i < colCount; i++) {
System.out.print(rs.getString(i + 1) + "\t");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
//close connections
}
}
}
If you have some data in the stu1 table of the example1 database,
then compiling and running Example1.java should print
contents in the console. Here is what I got.
ID NAME COURSE
1001 John Doe Statistics
1002 Jack McDonalds Linear Algebra
If you want to see the DriverManager automatically
load the JDBC driver, then you can replace the
con=ds.getConnection() call in Example1
with con=DriverManager.getConnection(connectionURL).
The class should produce exactly the same result. As you can see
from this example, you no longer have to load the driver with an
explicit Class.forName() call.
How do you differentiate a robust Java program from those that aren't? In my view, exception handling is one of the most important factors. A robust Java program handles exceptions very well, giving the program an ability to recover itself from such conditions. On the other hand, poor exception handling may result in a program producing the wrong result or it may ultimately break the application!
JDBC 4.0 adds some simple but powerful features to handle
exceptions, including support for chained exception and use of the
enhanced for-each loop to retrieve such chained exceptions, if any.
Example2 below shows part of a Java class that handles
chained exceptions in this new way.
public class Example2 {
public static void main(String[] args) {
String dbName = "example";
String tableName = "student4";
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select * from " + tableName);
} catch (SQLException sx) {
for(Throwable e : sx ) {
System.err.println("Error encountered: " + e);
}
}
finally{
//close connections
}
}
}
I ran the class in Example2.java, specifying student4
as a table name that does not exist in the database. It raised a
chained exception in following call.
rs = stmt.executeQuery("select * from " + tableName);
In a real application, you need to catch such chained exceptions, inspect them, and take a suitable action. In this example, however, I will show how to print them to the error console. Here is how to do just that.
for(Throwable e : sx ) {
System.err.println("Error encountered: " + e);
}
Here's the output running the class Example2:
Error encountered: java.sql.SQLSyntaxErrorException:
Table/View 'STUDENT4' does not exist.
Error encountered: java.sql.SQLException:
Table/View 'STUDENT4' does not exist.
Exception in thread "main" java.lang.NullPointerException
at ex.Examlpe2.main(Examlpe2.java:51)
With JDBC 4.0, you now can retrieve and iterate over chained
exceptions with a small amount of code. In previous versions, you
had to retrieve individual chained exceptions iteratively by
calling getNextException in the caught exception.
This version of JDBC adds some new data types and enhances
support for others. I like the fact that the need for XML support has
rightly been identified, and a new interface, SQLXML, has been added. In
my view this interface deserves a separate section for discussion.
SQLXML is a mapping in the Java programming language for the XML
data type in SQL. XML is a built-in type that stores an XML value
as a column value in a row of the target table. By default, drivers
implement an SQLXML object as a logical pointer to the XML data
rather than the data itself. An SQLXML object is valid for the
duration of the transaction in which it was created.
In the Example3 class below, I show how to create
an SQLXML object from the current connection and update the
underlying table value.
public class Example3 {
public static void main(String[] args) {
...
con = ds.getConnection();
SQLXML sx= con.createSQLXML();
sx.setString("Math is Fun");
String psx ="insert into "+tableName+
" ( id, textbook) values(?,?) ";
PreparedStatement pstmt = con.prepareStatement(psx);
pstmt.setString(1,"1000");
pstmt.setSQLXML(2,sx);
pstmt.executeUpdate();
...
}
}
This example shows just a very basic thing you can do. Things
will get interesting if we can explore further. But before going
too far, let me show what happened when I ran the
Example3.java. Unfortunately, I was unable to retrieve the
SQLXML object and the program exited with following disappointing
message:
java.sql.SQLFeatureNotSupportedException: Feature not
implemented: no details.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.
getSQLException(Unknown Source)
... ... ... ...
at ex.Example3.main(Example3.java:62)
It turns out that this version of Apache Derby does not provide
support for creating an SQLXML object from a
Connection. But you should still be able to see what
I'm trying to do in Example3: I wanted to insert a new
row with an id column value of 1000 and a
textbook column (which is SQLXML type) value of Math
is Fun in the target table.
I want to conclude this SQLXML section by showing the following code
snippet that would read an XML value from a table and parse it into a
Document object.
SQLXML sqlxml = rs.getSQLXML(column);
InputStream binaryStream = sqlxml.getBinaryStream();
DocumentBuilder parser =
DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document doc = parser.parse(binaryStream);
Isn't it nice to be able to read a column value from underlying table and build a XML document directly? I think it is a very nice feature.
ROWID Data TypeSQL ROWID identifies a row within a table and is the fastest way
to access it. A new RowId interface has been added in
this version to provide access to the ROWID SQL type from a Java
class.
Version 2.0 of JDBC provided support for large SQL objects such
as CLOB, BLOB, ARRAY, and STRUCT by adding corresponding interfaces:
Clob, Blob, Array, and Struct. More
methods are now added in this version of JDBC to support
manipulation of such objects. I will discuss some such methods in
the API Changes section.
SQL:2003 provides support for SQL data types of NCHAR, NVARCHAR,
LONGNVARCHAR, and NCLOB. They are analogous to CHAR, VARCHAR,
LONGVARCHAR, and CLOB except for the fact that the values are
encoded using different character sets, namely the national
character set (NCS). You may want to choose the NCS data types over the
regular character data types if your data need extensive character
processing operations. This version of JDBC adds or enhances NCS
support in the following APIs.
PreparedStatement, CallableStatement, and
ResultSet interfaces to support NCS conversion.
Methods such as
setNString, setNCharacterStream, and setNClob are a few
examples.SQLInput and SQLOutput interfaces to
support NClob and NString objects.Most of the enhancements in JDBC 4.0 have been at the API level, which I will briefly discuss in this section. For more details, please refer to the JSR 221:JDBC 4.0 API specification.
ArrayA new free method has been added to the
Array interface to free the array object and release
the resources that it holds.
Connection and PooledConnectionConnection interface now provides a bunch of methods
to support the creation of large objects such as createClob,
createBlob, etc. Other additions are overloaded versions of
getter and setter methods for the client info, and methods to check
the validity of the current connection.
The PooledConnection interface now provides
addStatementEventListener and
removeStatementEventListener methods that come in handy if you
need to register or deregister the StatementEventListener
interface, which is introduced in this version of JDBC. An instance
of this interface registers to be notified of events occurring on
PreparedStatements that are in the
Statement pool. For example, if registered, the driver
calls the statementClosed method on all
StatementEventListeners when it detects that a
PreparedStatement is closed on the statement.
DatabaseMetaDataDifferent relational DBMSs often support different features,
implement features in different ways, and may use different data
types. This may cause portability issues because a portion of code
that works for one database may fail on another, based on the
underlying implementation. Such issues may be addressed to some
extent using information returned by calling methods in this
interface. For example, suppose you are writing some code that will
pass a SQL statement to create a table. You may want to find out
what data types can be used in a CREATE TABLE statement by calling
the getTypeInfo method in this interface.
This version of JDBC adds several methods to support querying
database metadata info. In Example4, I show a code
snippet to demonstrate a new feature that allows retrieving a list
of database schemas that satisfies a typical search pattern.
con = ds.getConnection();
DatabaseMetaData dmd = con.getMetaData();
rs=dmd.getSchemas("TABLE_CAT", "SYS%");
//iterate over the rs and print to console
First I called dmd.getCatalogs and iterated over
the result set, which gave me only the catalog value as TABLE_CAT. I
then used this value to ask what schemas in the
database have names that start with SYS by calling
rs=dmd.getSchemas("TABLE_CAT", "SYS%"). Here is the
list of table schema I got:
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
A scalar function operates on a predefined set of input values
and returns some result. For example, the result of the scalar function
call ABS(number) returns the absolute value of the
number. These scalar functions can be used as part of
SQL strings from the Java code. This version of JDBC requires that
the driver should support them if the underlying database supports
the following new scalar functions: CHAR_LENGTH,
CHARACTER_LENGTH, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,
EXTRACT, OCTET_LENGTH, and POSITION.
Statement, PreparedStatement, and CallableStatementThe Statement interface now provides an
isClosed method to query if the statement is closed,
setPoolable to set if you want this statement to be
poolable or non-poolable, and isPoolable to
check if the current statement is poolable.
The PreparedStatement and
CallableStatement interfaces now provide more methods
for inserting large objects, using InputStream and
Reader respectively.
This version of the API adds a new Wrapper interface
that provides a mechanism for accessing an instance of a resource,
which may have been wrapped for architectural reasons. The Wrapper
pattern, a.k.a. the Adapter pattern,
is employed by many JDBC driver implementations to provide
extensions beyond the traditional JDBC API that are specific to a
data source. The main intention of this interface is to eliminate
non-standard means to access vendor-specific resources. You can find out an object that implements the given interface
by calling the unwrap method in the instance of this
interface. Because the unwrap method call is a heavyweight
operation, you should use the isWrapperFor method of this
interface to check if the class that implements this interface is
either a direct or indirect wrapper for an object before actually
unwrapping it.
It would have been really nice to wrap up with another example on how to unwrap the JDBC adapter but the "JDBC 4.0-only features" section of the Apache Derby reference manual mentions following about the Wrapper: "JDBC 4.0 introduces the concept of wrapped JDBC objects ... For Derby, this is a vacuous exercise because Derby does not expose any of these extensions." There is no point on trying to do a vacuous exercise!
I've discussed some new features and enhancements in JDBC 4.0 in areas like driver and connection management, exception handling, support for additional data types (including XML), and API changes providing more flexibility for data manipulation. These new features are meant to provide ease of use and programmer productivity. Although the API specification has been out for several months now, most commercial database vendors have yet to provide drivers that support this version of JDBC at the time of writing. When more vendors start supporting for JDBC 4.0--and of course that should include the vendor of your preferred database--I'm sure you will enjoy all these great new features that provide lot of flexibility and ease of programming to you as a programmer.
Finally, I feel that it will be nice have a single web page that lists the database vendors and the most recent version of JDBC they support. There is a searchable JDBC Data Access API page on the Sun Developer Network (SDN) that attempts to provide this, but the information available there does not seem to be up to date.
Sharad Acharya has more than eight years of experience in the software engineering field in multiple business domains including supply chain, insurance, banking, and mortgage.
 |
 java.net RSS Feeds
|
