Chapter 4. ResultSet
Index:
A ResultSet is a Java object that contains the results of executing an SQL query. In other words, it contains the rows that satisfy the conditions of the query. The data stored in a ResultSet object is retrieved through a set of get methods that allows access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making it the current row.
A ResultSet object maintains a cursor, which points to its current row of data. The cursor moves down one row each time the method next is called. When a ResultSet object is first created, the cursor is positioned before the first row, so the first call to the next method puts the cursor on the first row, making it the current row. ResultSet rows can be retrieved in sequence from top to bottom as the cursor moves down one row with each successive call to the method next. A scrollable result set's cursor can move both forward and backward as well as to a particular row. The following methods move the cursor backward, to the first row, to the last row, to a particular row number, to a specified number of rows from the current row, and so on: previous, first, last, absolute, relative, afterLast, and beforeFirst. As with scrollability, making a ResultSet object updatable increases overhead and should be done only when necessary. That said, it is often more convenient to make updates programmatically, and that can only be done if a result set is made updatable.
The HXTT DBF driver supports scrollable updatable result set.
The number of rows that should be fetched from the database each time new rows are needed. The number of rows to be fetched is called the fetch size, and it can be set by two different methods: Statement.setFetchSize and ResultSet.setFetchSize. The statement that creates a ResultSet object sets the default fetch size for that ResultSet object, using the Statement method setFetchSize. The following code fragment sets the fetch size for the ResultSet object rs to 10. Until the fetch size is changed, any result set created by the Statement object stmt will automatically have a fetch size of 10.
Statement stmt = con.createStatement();
stmt.setFetchSize(10);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
A result set can, at any time, change its default fetch size by setting a new fetch size with the ResultSet version of the method setFetchSize. Continuing from the previous code fragment, the following line of code changes the fetch size of rs to 50:
stmt.setFetchSize(50);
Normally the most efficient fetch size is already the default for the HXTT DBF driver. The method setFetchSize simply allows a programmer to experiment to see if a certain fetch size is more efficient than the default for a particular application.
A ResultSet object may be updated (have its rows modified, inserted, or deleted) programmatically if its concurrency type is CONCUR_UPDATABLE. The following example demonstrates show how to update, delete, and insert data.
PreparedStatement stmt = con.prepareStatement(
"select int1,float1,clob1 from test where double1<=?",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.setFetchSize(12);
stmt.setDouble(1, 0);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++) {
System.out.println(resultSetMetaData.getColumnLabel(i));
}
Object colval;
while (rs.next()) {
for (int i = 1; i <= iNumCols; i++) {
colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.first();
rs.relative(5);
rs.updateString(3, "eeees333ee3");
rs.updateFloat("float1", 11111.2111f);
rs.updateRow();
rs.absolute(6);
rs.deleteRow();
rs.relative( -2);
rs.refreshRow();
rs.moveToInsertRow();
rs.updateInt(1, 10000);
rs.updateFloat(2, 1000000.0f);
rs.updateObject(3, "abc" + (new java.sql.Time(System.currentTimeMillis())));
rs.insertRow();
rs.moveToCurrentRow();
System.out.println("After be updated:");
rs.beforeFirst();
while (rs.next()) {
for (int i = 1; i <= iNumCols; i++) {
colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
stmt.close();
con.close();
The HXTT DBF driver's result set is Serializable.
// serialize the resultSet
java.io.FileOutputStream fileOutputStream = new java.io.FileOutputStream("testrs.tmp");
java.io.ObjectOutputStream objectOutputStream = new java.io.ObjectOutputStream(fileOutputStream);
objectOutputStream.writeObject(rs);
objectOutputStream.flush();
rs.close();
rs = null;
// deserialize the resultSet
java.io.FileInputStream fileInputStream = new java.io.FileInputStream("testrs.tmp");
java.io.ObjectInputStream objectInputStream = new java.io.ObjectInputStream(fileInputStream);
rs = (ResultSet) objectInputStream.readObject();