I posted this several days ago to pgsql-jdbc but have had no response. I am posting it here (with minor changes in the
wording).
I have developed some code that works, I'm just not sure I have the "best" solution.
I have applications in which the user can create a read-only resultset with multiple rows. For example, customers who
are90 days in arrears might be brought up for review.
The user might scroll through the rows reviewing the data, and then he might decide to update one of them. A second
queryis used to update that one row. At the time of the update, the current contents of that row is reread FOR UPDATE
andcompared against the original row. If they differ, someone else has altered the row after the resultset was created.
The user is informed that another user has changed the row in question; he can then decide to accept the changes he has
madeor leave in place the changes made by the other user. In either case, that row in the original resultset has to be
madeto match the current contents in the table, because the user might scroll back and forth and revisit it.
I am using refreshRow() to make that row current, but the problem is that refreshRow() can be extremely slow.
I create the read-only, multiple row resultset (ie "viewResultSet") like this:
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
viewResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select multiple rows");
Scroll through the resultset to view rows as desired.
When positioned at a row, can update that row. See below.
This is the logic I use for updating a single row.
jdbcConn.setAutoCommit(false);
createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
updateResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select same row FOR UPDATE");
Compare viewResultSet to updateResultSet to see if changes have been made by another user.
Ask user what he wants to do. The following code updates the database row and the
resultset row with the user's changes.
resultSet.updateString(colname,colvalue); // one or more column updates go here
currentDatabaseSRS.resultSet.updateRow();
jdbcConn.commit();
jdbcConn.setAutoCommit(true);
viewResultSet.refreshRow(); // Refresh the original resultset. This can be very slow.
This all seems to work well. The biggest question I have is the very last statement, which refreshes the multi-row
read-onlyresultset. Sometimes this operation is very slow.
Specific question: why is refreshRow() slow, can I make it faster or should I perhaps execute the original query again.
General question: any problems evident with this approach?
John
