SQL-J and JDBC ResultSet/Cursor Mechanisms
A cursor provides you with the ability to step through and process the rows in a ResultSet one by one. A java.sql.ResultSet object constitutes a cursor. You do not need to use a language construct, such as SQL-92's DECLARE CURSOR, to work with cursors in a Java application. In Cloudscape, any SELECT statement generates a cursor.
Simple Non-Updatable ResultSets
Here is an excerpt from a sample JDBC application that generates a ResultSet with a simple SELECT statement and then processes the rows.
Connection conn = DriverManager.getConnection(
"jdbc:cloudscape:toursDB");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(
"SELECT group_id, fixed_rate, number_seats FROM FlightBookings");
while (rs.next()) {
int gid = rs.getInt("GROUP_ID");
BigDecimal cost = rs.getBigDecimal("FIXED_RATE");
int number_seats = rs.getInt("NUMBER_SEATS");
System.out.println(gid + "; per seat rate: " + cost +
"; total cost: " +
cost.multiply(new BigDecimal(
(double)number_seats)).setScale(2));
}
rs.close();
s.close();
Updatable Cursors
Cursors are read-only by default. For a cursor to be updatable, you must specify SELECT . . . FOR UPDATE. Use FOR UPDATE only when you will be modifying rows to avoid excessive locking of tables.
Requirements for Updatable Cursors
Only specific SELECT statements--simple accesses of a single table--allow you to update or delete rows as you step through them.
For more information, see SELECT statement and FOR UPDATE clause of the Cloudscape Reference Manual.
Naming or Accessing the Name of a Cursor
There is no SQL-J language command to assign a name to a cursor. You can use the JDBC setCursorName method to assign a name to a ResultSet that allows positioned updates and deletes. You assign a name to a ResultSet with the setCursorName method of the Statement interface. You assign the name to a cursor before executing the Statement that will generate it.
Statement s3 = conn.createStatement();
s3.setCursorName("UPDATABLESTATEMENT");
PreparedStatement ps2 = conn.prepareStatement(
"UPDATE FlightBookings SET number_seats = ? " +
"WHERE CURRENT OF UPDATABLESTATEMENT");
More typically, you do not assign a name to the cursor, but let the system generate one for you automatically. You can determine the system-generated cursor name of a ResultSet generated by a SELECT statement using the ResultSet class's getCursorName method.
PreparedStatement ps2 = conn.prepareStatement(
"UPDATE FlightBookings SET number_seats = ? WHERE CURRENT OF "+
otherResult.getCursorName());
Updatable Cursors and Cloudconnector
When using Cloudconnector, turn off the server's prefetching and caching in order to work with updatable cursors. For more information, see the Cloudscape Server and Administration Guide.
Extended Updatable Cursor Example
String URL = "jdbc:cloudscape:toursDB";
if (CloudconnectorClient)
URL += "&weblogic.t3.cacheRows=0";
Connection conn = DriverManager.getConnection(
URL);
conn.setAutoCommit(false);
int anygroup = 2;
Statement s3 = conn.createStatement();
s3.setCursorName("UPDATABLESTATEMENT");
ResultSet Updatable = s3.executeQuery(
"SELECT group_id, fixed_rate, number_seats FROM FlightBookings "+
"FOR UPDATE of number_seats");
PreparedStatement ps2 = conn.prepareStatement("UPDATE FlightBookings "+
"SET number_seats = ? WHERE CURRENT OF UPDATABLESTATEMENT");
while (Updatable.next()) {
int thegroup = Updatable.getInt("GROUP_ID");
int ns = Updatable.getInt("NUMBER_SEATS");
if (thegroup < anygroup) {
ps2.setInt(1, 5);
ps2.executeUpdate();
System.out.println("Updating number_seats in FlightBookings"
" table for group number " + thegroup);
}
}
Updatable.close();
s3.close();
ps2.close();
conn.commit();
ResultSets and Auto-Commit
This example demonstrates how auto-commit can affect the behavior of a ResultSet.
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(
"SELECT City.getName(), budget FROM groups, Cities "+
"WHERE groups.city_id = Cities.city_id " +
"AND group_id = ?");
Statement s2 = conn.createStatement();
int bookingcount = 0;
ResultSet outerRs;
ResultSet innerRs;
outerRs = s2.executeQuery(
"SELECT group_id, fixed_rate, number_seats FROM FlightBookings");
while (outerRs.next()) {
bookingcount++;
BigDecimal threshold = new BigDecimal(950.00).setScale(2);
BigDecimal cost = outerRs.getBigDecimal("FIXED_RATE");
cost.setScale(2);
if (cost.compareTo(threshold) == 1) {
ps.setInt(1, outerRs.getInt("GROUP_ID"));
innerRs = ps.executeQuery();
innerRs.next();
System.out.println(innerRs.getString(1) + ", " +
innerRs.getBigDecimal("BUDGET").setScale(2));
innerRs.close();
}
}
outerRs.close();
conn.commit();
s2.close();
ps.close();
Scrolling Insensitive ResultSets
JDBC 2.0 adds a new kind of ResultSet, one that allows you to scroll in either direction or to move the cursor to a particular row.
Cloudscape implements only scrolling insensitive ResultSets that are not updatable.
conn.setAutoCommit(false);
Statement s4 = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet scroller= s4.executeQuery(
"SELECT group_id, fixed_rate FROM FlightBookings " +
"ORDER BY fixed_rate DESC");
if (scroller.first())
System.out.println("The largest rate for a flight is " +
scroller.getBigDecimal("FIXED_RATE"));
else
System.out.println("There are no rows.");
scroller.beforeFirst();
scroller.afterLast();
scroller.absolute(3);
if (!scroller.isAfterLast()) {
System.out.println("The third highest rate for a flight is " +
scroller.getBigDecimal("FIXED_RATE") +
" held by by group number = " + scroller.getInt("GROUP_ID"));
}
if (scroller.isLast()) {
System.out.println("There are only three rows.");
}
if (scroller.last())
System.out.println("The smallest fixed_rate in the table is: " +
scroller.getBigDecimal("FIXED_RATE"));
scroller.close();
conn.commit();
s4.close();
conn.close();
System.out.println("Committed transaction and closed connection");
|