Scrollable, Sensitive, Updateable ResultSets
| In the past, it was possible to retrieve records from a result set only in order, one by one, primarily because databases like Oracle retrieved data piece-wise into a buffer of fixed size, and this limitation allowed for a more efficient implementation. This limitation is reflected in the implementation of the JDBC 1.0 ResultSet class, which had a single method for navigating through a ResultSet, next(). But, like other limitations, this has been overcome, and new releases of Oracle and JDBC feature scrollable ResultSets. Scrollability lets you traverse a result set forward and backward. This is furthermore associated with random access of a result set the ability to access a row by absolute or relative position. In addition to being scrollable, JDBC 2.0 ResultSets can be sensitive, which means that they can reflect changes made to the database while the ResultSet is open; ResultSets can also be updateable, which means that rows in the ResultSet can be changed, added, or deleted. These features, particularly sensitivity, need to be used with caution, however. The underlying mechanism for obtaining results sets by groups of rows has not changed navigating between a few widely scattered rows can cause the database to fetch many rows behind the scenes, severely impairing performance. Scrolling through ResultSetsBy default, a ResultSet is not scrollable, and the only way to navigate between rows is to start at the beginning and continue one at a time with the next() method. Sometimes, such as when a user is browsing information interactively, it's useful to be able to reverse direction or to skip forward or backward. To create a scrollable ResultSet, we need to specify this when we call Connection's createStatement() or createPreparedStatement() method to get a Statement. It's Statement or PreparedStatement that returns ResultSets to us, after all, when we call the executeQuery() method. We can't just specify scrollability, however. If you specify one of the new ResultSet features, you must specify them all. The default createStatement() and createPreparedStatement() methods have no parameters. The simplest methods that we can use to specify scrollability have two parameters. createStatement(int resultSetType, int resultSetConcurreny) createPreparedStatement(int resultSetType, int resultSetConcurreny) (There are two more methods that have, in addition, a third parameter to specify holdability which we won't use here.) The first parameter, resultSetType, actually controls two settings: scrollability and sensitivity. It can take one of the following three values: ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE TYPE_FORWARD_ONLY is the default no-scrolling option we get when we use no parameters; INSENSITIVE is implied. The next two allow our ResultSets to be scrollable and let us select whether they are sensitive or not, as well. The second parameter, resultSetConcurrency, lets us select whether our ResultSet is updateable. The two options are: ResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATABLE Assuming that we've already got a Connection, conn, and we want a Statement, we can select scrollable, insensitive, read-only ResultSets like this: Statement stmt = createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); Now, when we execute a query, we'll be able to navigate it in many different ways. Methods available are:
We can easily imagine an application where a user can submit a query and get a window full of results. The user then may have the option to scroll up or down a line or a page at a time (within the limits of the results). The query might be: ResultSet rs = stmt.executeQuery( "SELECT NAME, DESCRIPTION, MODEL, PRICE " + "FROM PRODUCTS " + "WHERE TYPE=' "+ prod_type + "' AND PRICE <= " + price); We'll probably need to tell the user how many pages of results were retrieved. To do that, we need to find out how many rows were returned and divide that number by the number of rows we can display per page, plus one if there is a partial page at the end. There is no direct way to find out the number of rows in a ResultSet. We need to move to the last record and call getRow(), which returns the current row's number. We'll assume that ROWS_PER_PAGE is a symbolic constant set to the number of rows per page and that rowCount is a class attribute. rs.last(); int rowCount = rs.getRow(); int pages = rowCount/ROWS_PER_PAGE; if(pages * ROWS_PER_PAGE < rowCount) { pages++; } Let's assume that we cache a page's worth of rows locally in a vector as strings. For the query above, we could store each row as a string array of four elements, one for each column in the query. Even though the PRICE column is likely a NUMBER column, calling getString() will provide the necessary conversion and is perfectly adequate for display purposes (although we might want to do something about the format in our SQL query). Perhaps we would pass this vector to a display function that formats it as an HTML table for output to a browser. We'll get the number of columns like this: ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); We'll create two methods for updating the vector. One will add a row at the beginning or end, then adjust the vector size back to ROWS_PER_PAGE. Another will refresh all the rows in the vector. Both of these methods will start retrieving data from the current row they are not responsible for navigation. In fact, these low-level methods are responsible for leaving the cursor where they found it so that the higher level method isn't confused. First, here's our method for reading a row from the database into an array of strings; both of the methods that update the vector will call it. private String [] readRow() throws SQLException { String [] sArray = new String [columnCount]; for(int i=1; i<=columnCount; i++) { sArray[i - 1] = rs.getString(i); } return sArray; } Next is our method for filling in the vector with the next ROWS_PER_PAGE rows (or until the next() method stops returning rows). Notice that, because we want to start at the current row but use next() in the loop, we must first move back one row by calling previous(). private void fillVector() throws SQLException { v.clear(); int startRow = rs.getRow(); rs.previous(); for(int i=0; i < ROWS_PER_PAGE && rs.next(); i++) { v.add(readRow()); } rs.absolute(startRow); } Our other method for updating the vector will "scroll" it up or down by inserting a row at the beginning and removing one at the end, or by adding a row at the end and removing the row at the beginning. We'll define symbolic constants to indicate the direction of the scroll. private static final int SCROLLUP = -1; private static final int SCROLLDOWN = 1; This method will get a row of data, add it, and adjust the vector appropriately. private void scrollVector(int scrollDirection) throws SQLException { System.out.println("scrolling: " + scrollDirection); String [] sArray; if((sArray = readRow())!=null); { switch(scrollDirection) { case SCROLLUP: if((sArray = readRow())!=null) { v.removeElementAt(v.size()-1); v.insertElementAt(sArray, 0); } break; case SCROLLDOWN: int startRow = rs.getRow(); rs.relative(ROWS_PER_PAGE - 1); if((sArray = readRow())!=null) { v.removeElementAt(0); v.add(sArray); } rs.absolute(startRow); break; } } } To navigate the vector in response to the user, we need to move the cursor to the appropriate row and call fillVector() if we are displaying a new page as a result of PageUp, PageDown, Home, or End, or call scrollVector() if we are moving one row up or down. First, we'll define some symbolic constants to indicate direction. The calling class will provide one of these as a result of user input: public static final int PAGEUP = 1; public static final int PAGEDOWN = 2; public static final int HOME = 3; public static final int END = 4; public static final int UP = 5; public static final int DOWN = 6; To navigate the ResultSet, we need to move the cursor by translating these into the appropriate call: previous() or next() for UP and DOWN; relative() for PAGEUP or PAGEDOWN; and absolute() for HOME and END. We also need to determine the parameters correctly, based on the direction of the move and boundary conditions. The following public method responds to the navigation request and returns the updated vector: public Vector getDataVector(int move) throws SQLException { /* Note: Only REFRESH is valid if the query * returned too few results */ if(pages==1) { move = REFRESH; } int startRow = rs.getRow(); switch(move) { case PAGEUP: if(startRow >= ROWS_PER_PAGE) { rs.relative(-ROWS_PER_PAGE); } break; case PAGEDOWN: if(startRow < (rowCount-(ROWS_PER_PAGE*2))) { rs.relative(ROWS_PER_PAGE); } else { rs.absolute(-ROWS_PER_PAGE); } break; case HOME: rs.first(); break; case END: rs.absolute(-ROWS_PER_PAGE); break; case UP: if(startRow > 1) { rs.previous(); } break; case DOWN: if(startRow <= rowCount - ROWS_PER_PAGE) { rs.next(); } break; case REFRESH: startRow = -1; break; } switch(rs.getRow() - startRow) { case 0: break; // no move, no action case -1: scrollVector(SCROLLUP); break; case 1: scrollVector(SCROLLDOWN); break; default: fillVector(); break; } return v; } To use these methods in an application, we'd put them in a class for example, ScrollWindow. Depending on whether the application wants to manage the database connection and whether the class knows how to determine the database URL, we could have the following constructors:
In addition, we'd want to have some public get methods for obtaining rowCount, as well as pages. We might also want a set method to allow the user to set the number of rows per page. Sensitive ResultSetsOur last example was not sensitive to changes in the data. Once the user submits the query, the entire ResultSet is effectively cached. If we want to be able to see changes as they are made by others, we can create our Statement object with the ResultSet.TYPE_SCROLL_SENSITIVE option. The database will provide the updated record, if it's been changed, when we move to a new row. Statement stmt = createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); In addition, in order for our ResultSet to be scroll-sensitive, we must observe the following restrictions on our queries:
If we don't comply with these restrictions, the ResultSet will be automatically and silently downgraded to scroll-insensitive. If we attempt to perform operations that are specific to scroll-sensitive results sets, such as refreshRow(), we will get an error indicating that the feature is not supported. Suppose that the following query retrieves a hundred rows, which we display to the user: ResultSet rs = executeQuery("SELECT COL1 FROM TEST"); While(rs.next()) { System.out.println(rs.getString(1)); } If someone elsewhere changes a row and we retrieve the hundred rows by moving back to the beginning of the result set and displaying them again, the change will appear. rs.first(); While(rs.next()) { System.out.println(rs.getString(1)); } Updates are not always immediately visible, however. By default, Oracle's JDBC driver fetches 10 rows of data at a time. The fetch obtains the most recent data for those 10 rows at that time. As long as we navigate within these 10 rows, Oracle will not perform another fetch; hence, no updates to those rows will be visible. Not until we navigate to a row outside these 10 prefetched rows will Oracle perform another fetch. At that time, Oracle will once again fetch 10 more rows, including any updates, starting with the new current row. We can also update the data by calling the refreshRow() method, which will fetch 10 rows, starting with the current row.We can also change the fetch size by calling setFetchSize() with the number of rows we want fetched at a time. There is no maximum fetch size, but Oracle recommends 10 as the most effective setting. Poor use of either of the refreshRow() or setFetchSize() methods can cause serious performance problems, due to either an increased number of round trips to the database or unnecessarily large amounts of data being returned from the database. Let's take a look at an example of a sensitive ResultSet. We'll open two ResultSets from two different Statements, one sensitive, one not. Using a third Statement, we'll change the data and see whether the changes can be seen in the other two. import java.sql.*; public class Sensitive { public static void main(String [] vars) { Connection conn; String sql; try { OracleDataSource ods = new OracleDataSource(); ods.setDriverType("thin"); ods.setServerName("noizmaker"); ods.setNetworkProtocol("tcp"); ods.setDatabaseName("osiris"); ods.setPortNumber(1521); ods.setUser("david"); ods.setPassword("bigcat"); Statement stmtWrite = conn.createStatement(); Statement stmtInsensitive = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); Statement stmtSensitive = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Create and populate a table stmtWrite.executeUpdate("CREATE TABLE TEST (COL1 NUMBER)"); for(int i=0; i<20; i++) { sql = "INSERT INTO TEST VALUES( " + i +")"; stmtWrite.executeUpdate(sql); } // Query: get sensitive and insensitive ResultSets sql = "SELECT COL1 FROM TEST"; ResultSet rsInsensitive = stmtInsensitive.executeQuery(sql); ResultSet rsSensitive = stmtSensitive.executeQuery(sql); // Change table after query sql = "UPDATE TEST SET COL1 = COL1 * 10"; stmtWrite.executeUpdate(sql); // Now print out result sets System.out.println("Insensitive Sensitive"); while(rsInsensitive.next()&&rsSensitive.next()) { System.out.print(rsInsensitive.getString(1)); System.out.print(" "); System.out.println(rsSensitive.getString(1)); } rsSensitive.close(); rsInsensitive.close(); stmtWrite.close(); stmtInsensitive.close(); stmtSensitive.close(); conn.close(); } catch (SQLException e) { System.out.println("Caught:" + e); } } } The output from this program is Insensitive Sensitive 0 0 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 100 11 110 12 120 13 130 14 140 15 150 16 160 17 170 18 180 19 190 The sequence of events was:
The first column, the results from the insensitive ResultSet, are what we expected. They are the values the table held when we queried it. The second column is not what we might naively expect. The first 10 values are the old values, and the last 10 are the new values. But we know, of course, that 10 values were fetched at the time of the query the default fetch size and a new fetch wasn't triggered until we requested a row that wasn't in the first 10 rows. One experiment you may wish to try is changing the fetch size, to 2 for example, prior to the query to see that only the first two rows keep their stale values. stmtSensitive.setFetchSize(2); ResultSet rsSensitive = stmtSensitive.executeQuery(sql); You might also try to reverse the order in which you retrieve the rows. rsSensitive.afterLast(); rsInsensitive.afterLast(); while(rsInsensitive.previous()&&rsSensitive.previous()) { System.out.print(rsInsensitive.getString(1)+" "); System.out.println(rsSensitive.getString(1)); } In this case, because the database doesn't know any better, it fetches the first 10 rows, but right at the start we ask for the last row, so it immediately needs to fetch again, and all the values are the latest. Updateable ResultSetsA user interface that uses a scrollable ResultSet invites the user to browse through the data interactively; depending on the type of application, we may want to allow the user to change the data. Perhaps we have an on-line store that allows users to place orders. When a user is reviewing an existing order and wants to change it, it's much easier to update the database by changing the row in the ResultSet rather than identifying the row, then creating and executing a separate UPDATE statement. In addition to updating existing rows, updateable ResultSets also allow us to delete rows and to insert new rows. It's not required that an updateable ResultSet be scrollable, as well, but generally it's easier to manage updates if you can scroll too. As with scroll-sensitive ResultSets, there are important restrictions on the types of queries that are allowed for updateable ResultSets.
The ResultSet will be automatically downgraded if our query doesn't meet these restrictions and will cause an error when we attempt to perform an update, insert, or delete. In addition to these general restrictions, we will be able to perform inserts only if we meet the following condition:
The first step in updating a row is to position the cursor on it by using the next() method or if we're using a scrollable ResultSet, using any of the methods, such as previous(), absolute(), or relative(). Next, we set the new value by calling one of the many updateXXX() methods, according to the type of column we want to update. The updateXXX() methods correspond to the setXXX() methods that we use for PreparedStatements, and as with the setXXX() methods, we can address a column by either its index or its column name. These methods include: updateInt(int columnIndex, int value) updateInt(String columnName, int value) updateLong(int columnIndex, long value) updateLong(String columnName, long value) updateFloat(int columnIndex, float value) updateFloat(String columnName, float value) updateDouble(int columnIndex, double value) updateDouble(String columnName, double value) updateString(int columnIndex, String value) updateString(String columnName, String value) updateDate(int columnIndex, Date value) updateDate(String columnName, Date value) updateTime(int columnIndex, Time value) updateTime(String columnName, Time value) Once we've updated a column or columns in a ResultSet row, we update the database by calling the updateRow() method. Assuming that we have a valid connection, we can create a Statement, query to obtain a ResultSet, and change a value in a row as follows: Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate( "CREATE TABLE UPDATE_TEST(COL1 VARCHAR2(25))"); stmt.executeUpdate("INSERT INTO UPDATE_TEST VALUES('Hello')"); stmt.executeUpdate("INSERT INTO UPDATE_TEST VALUES('Goodbye')"); ResultSet rs = stmt.executeQuery("SELECT COL1 FROM UPDATE_TEST"); if(rs.next()) { System.out.println(rs.getString(1)); } rs.updateString(1, "Howdy"); rs.updateRow(); rs.first(); System.out.println(rs.getString(1)); To delete an existing row, we position the cursor on the row and call deleteRow(). Assuming that we're still on the first row, we can delete the second row as follows: rs.next(); rs.deleteRow(); After the delete, the cursor moves to the previous row. (If the cursor was on the first row, it will end up before the first row.) If the ResultSet is scrollable, the row numbers of the rows after the deleted row will be adjusted to reflect the deletion. In addition to changing and deleting existing rows, an updateable ResultSet also allows us to insert new rows. In this case, of course, it makes no sense to move to an existing row first. Instead, JDBC provides a special insert row and a method, moveToInsertRow(), to move there. Once there, we can call any of the updateXXX() methods to set the columns, then at the end call insertRow() to perform the actual insert into the database. Suppose that we've already obtained an updateable ResultSet, as in the example above. We can insert a row like this: rs.moveToInsertRow(); rs.updateString(1, "This is a test"); rs.insertRow(); To move back to the row we were on before moving to the insert row, we can call: rs.moveToCurrentRow(); Inserted rows are not visible in the ResultSet. Updating a row and inserting a row, you may have noticed, require two steps: first updating the values in the row, then updating the database by calling either updateRow() or insertRow(), as appropriate. If you move to another row before calling updateRow() or insertRow(), the changes are discarded. You can also discard the changes explicitly by calling cancelRowUpdates(). Deleting a row takes just one step: calling deleteRow(). This immediately causes the row to be deleted from the database. All three cases updating, inserting, and deleting rows are subject to the normal rules for transactions. It can still be rolled back if it has not been committed but remember that, by default in JDBC, auto-commit is set to true, so as soon as updateRow(), insertRow(), or deleteRow() are called, the operation is committed. | ||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 71