Several JDBC functions were enhanced for i5/OS® Version
5 Release 2.
Enhanced JDBC functions for i5/OS Version
5 Release 2 include:
- Removal of the 'FOR UPDATE' restriction
- You no longer need to specify FOR UPDATE on your SELECT statements
in order to guarantee an updatable cursor. When connecting to V5R1
and later versions of i5/OS, IBM® Toolbox
for Java™ honors whatever concurrency you pass in
when you create statements. The default continues to be a read-only
cursor if you do not specify a concurrency.
- Get and modify columns and parameters by name
- New methods allow you to get and update information by column
name in ResultSet and to get
and set information by parameter name in CallableStatement. For example,
in ResultSet, where you previously used the following:
ResultSet rs = statement.executeQuery( SELECT * FROM MYCOLLECTION/MYTABLE );
rs.getString(1);
You can now use:
ResultSet rs = statement.executeQuery( SELECT * FROM MYCOLLECTION/MYTABLE );
rs.getString( 'STUDENTS' );
Be aware
that accessing parameters by their index results in better performance
than accessing them by their name. You can also specify parameter
names to set in CallableStatement. Where you might have used the following
in CallableStatement:
CallableStatement cs = connection.prepareCall( CALL MYPGM (?) );
cs.setString( 1 );
You can now use:
CallableStatement cs = connection.prepareCall( CALL MYPGM (?) );
cs.setString( 'PARAM_1' );
To use these
new methods, you need JDBC 3.0 or later and the Java 2
Platform, version 1.4 (either the Standard or the Enterprise Edition).
- Retrieve auto-generated keys
- The getGeneratedKeys() method on AS400JDBCStatement
retrieves any auto-generated keys created as a result of executing
that Statement object. When the Statement object does not generate
any keys, an empty ResultSet object is returned. Currently the server
supports returning only one auto-generated key (the key for the last
inserted row). The following example shows how you might insert a
value into a table then get the auto-generated key:
Statement s =
statement.executeQuery("INSERT INTO MYSCHOOL/MYSTUDENTS (FIRSTNAME) VALUES ('JOHN'");
ResultSet rs = s.getGeneratedKeys();
// Currently the server supports returning only one auto-generated
// key -- the key for the last inserted row.
rs.next();
String autoGeneratedKey = rs.getString(1);
// Use the auto-generated key, for example, as the primary key in another table
To
retrieve auto-generated keys, you need JDBC 3.0 or later, and the Java 2 Platform, version 1.4 (either the Standard
or the Enterprise Edition). Retrieving auto-generated keys also requires
connecting to a V5R2 or later version of i5/OS.
- Improved performance when running SQL insert statements in
a batch
- Performance of running SQL insert statements in a batch has been
improved. Run SQL statements in a batch by using the different addBatch()
methods available in AS400JDBCStatement,
AS400JDBCPreparedStatement, and AS400JDBCCallableStatement. Enhanced batch support
affects only insert requests. For example, using batch support to
process several inserts involves only one pass to the server. However,
using batch support to process an insert, and update, and a delete
sends each request individually.
To use batch support, you need
JDBC 2.0 or later and the Java 2
Platform, version 1.2 (either the Standard or the Enterprise Edition).
- Enhanced support for ResultSet.getRow()
- Previously, the IBM Toolbox for Java JDBC
driver was limited in its support for the getRow() method in ResultSet. Specifically, using ResultSet.last(),
ResultSet.afterLast(), and ResultSet.absolute() with a negative value
made the current row number not available. The previous restrictions
are lifted, making this method fully functional.
- Using mixed case in column names
- IBM Toolbox for Java methods
must match either column names provided by the user or column names
provided by the application with the names that are on the database
table. In either case, when a column name is not enclosed in quotes, IBM Toolbox
for Java changes the name to uppercase characters
before matching it against the names on the server. When the column
name is enclosed in quotes, it must exactly match the name on the
server or IBM Toolbox for Java throws
an exception.
- Specify holdability in created Statements, CallableStatements,
and PreparedStatements
- New methods in AS400JDBCConnection allow you to specify the
holdability for Statements, CallableStatements, and PreparedStatements
that you create. Holdability determines whether cursors are held
open or closed when committing the transaction. You can now have
a statement that has a different holdability than its connection object.
Also, connection objects can have multiple open statement objects,
each with a different specified holdability. Calling commit causes
each statement to be handled according to the holdability specified
for that statement.
Holdability is derived in the following order
of precedence:
- Holdability specified on statement creation by using the Connection
class methods createStatement(), prepareCall(), or prepareStatement().
- Holdability specified by using Connection.setHoldability(int).
- Holdability specified by the IBM Toolbox
for Java JDBC cursor hold
property (when methods in 1. or 2. are not used)
To use these methods, you need JDBC 3.0 or later, and the Java 2 Platform, version 1.4 (either the Standard
or the Enterprise Edition). Also, servers running a V5R1 or earlier
version of i5/OS are able to use only the holdability
specified by the JDBC cursor hold property.
- Enhanced transaction isolation support
- The IBM Toolbox for Java JDBC
driver now features support for switching to a transaction isolation
level of *NONE after a connection is made. Before V5R2, the IBM Toolbox
for Java JDBC driver threw an exception when switching
to *NONE after making a connection.