JDBC 4.0 Enhancements in Java SE 6

Support for National Character Set Conversion

Following is the list of new enhancements made in JDBC classes when handling the National Character Set:

1. JDBC data types: New JDBC data types, such as NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB were added.
2. PreparedStatement: New methods setNString, setNCharacterStream, and setNClob were added.
3. CallableStatement: New methods getNClob, getNString, and getNCharacterStream were added.
4. ResultSet: New methods updateNClob, updateNString, and updateNCharacterStream were added to ResultSet interface.

Enhanced Support for Large Objects (BLOBs and CLOBs)

The following is the list of enhancements made in JDBC 4.0 for handling the LOBs:

1. Connection: New methods (createBlob(), createClob(), and createNClob()) were added to create new instances of BLOB, CLOB, and NCLOB objects.
2. PreparedStatement: New methods setBlob(), setClob(), and setNClob() were added to insert a BLOB object using an InputStream object, and to insert CLOB and NCLOB objects using a Reader object.
3. LOBs: There is a new method (free()) added in Blob, Clob, and NClob interfaces to release the resources that these objects hold.

Now, let's look at some of the new classes added to the java.sql and javax.jdbc packages and what services they provide.

JDBC 4.0 API: New Classes

RowId (java.sql)

As described earlier, this interface is a representation of an SQL ROWID value in the database. ROWID is a built-in SQL data type that is used to identify a specific data row in a database table. ROWID is often used in queries that return rows from a table where the output rows don't have an unique ID column.

Methods in CallableStatement, PreparedStatement, and ResultSet interfaces such as getRowId and setRowId allow a programmer to access a SQL ROWID value. The RowId interface also provides a method (called getBytes()) to return the value of ROWID as a byte array. DatabaseMetaData interface has a new method called getRowIdLifetime that can be used to determine the lifetime of a RowId object. A RowId's scope can be one of three types:

1. Duration of the database transaction in which the RowId was created
2. Duration of the session in which the RowId was created
3. The identified row in the database table, as long as it is not deleted

DataSet (java.sql)

The DataSet interface provides a type-safe view of the data returned from executing of a SQL Query. DataSet can operate in a connected or disconnected mode. It is similar to ResultSet in its functionality when used in connected mode. A DataSet, in a disconnected mode, functions similar to a CachedRowSet. Since DataSet extends List interface, we can iterate through the rows returned from a query.

There are also several new methods added in the existing classes such as Connection (createSQLXML, isValid) and ResultSet (getRowId).

Sample Application

The sample application included with this article is a loan processing application that includes a loan search page where the user submits the form by entering a loan ID to get loan details. The loan search page calls a controller object that in turn calls a DAO object to access the back-end database to retrieve the loan details. These details include borrower name, loan amount, and loan expiration date, which are displayed on a loan details screen. In the back-end database, we have a table called LoanApplicationDetails to store the details of the loan application.

The use case of the sample application is to get loan details for a specified loan ID. The loan details are available for retrieval once a loan is registered and locked for a mortgage product and interest rate combination. The project details of the loan processing application are shown in Table 5.

The table below lists the JDBC parameters we need to connect to the loan details Apache Derby database. These parameters are stored in a text file called derby.properties, which is located in the etc/jdbc directory under the project base directory (see Table 6).

Note: Apache Derby database provides two types of JDBC drivers: Embedded Driver (org.apache.derby.jdbc.EmbeddedDriver) and Client/Server Driver (org.apache.derby.jdbc.ClientDriver). I used the Client/Server Driver version in the sample application.

The following are the commands to start the Derby database server and to create the new database using the ij tool.

To start Derby Network Server, open a command prompt and run the following commands (change DERBY_INSTALL and JAVA_HOME environment variables to reflect your local environment).

set DERBY_INSTALL=C:\dev\dbservers\db-derby-10.1.2.1-bin

set JAVA_HOME=C:\dev\java\jdk1.6.0

set DERBY_INSTALL=C:\dev\dbservers\db-derby-10.1.3.1-bin

set CLASSPATH=%CLASSPATH%;%DERBY_INSTALL%\lib\derby.jar;

                %DERBY_INSTALL%\lib\derbytools.jar;

                %DERBY_INSTALL%\lib\derbynet.jar;



cd %DERBY_INSTALL%\frameworks\NetworkServer\bin

startNetworkServer.bat

To connect to the database server and create the test database, open another command prompt and run the following commands. Make sure to change DERBY_INSTALL and JAVA_HOME environment variables to suit to your environment.

set JAVA_HOME=C:\dev\java\jdk1.6.0

set DERBY_INSTALL=C:\dev\dbservers\db-derby-10.1.3.1-bin

set CLASSPATH=%DERBY_INSTALL%\lib\derbyclient.jar;

                %DERBY_INSTALL%\lib\derbytools.jar;.



%JAVA_HOME%\bin\java org.apache.derby.tools.ij

connect 'jdbc:derby://localhost:1527/LoanDB;create=true';