I have code that works, I'm just not sure I have the "best" solution.
I have applications in which the user can open multiple rows at a time.
The user might scroll through the rows and not update some of the rows but might update other rows. Until a row is to
beupdated, nothing should be locked. At the time of the update, the user should be informed if another user has change
therow in question; he can then decide to accept the changes he has made or to leave in place the changes made by the
otheruser.
I create a result set for viewing the multiple rows 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.
To update one of those rows, it is necessary to update that row, then refresh that row in the original multiple row
resultsetso that if the user scrolls away from the row and then back to it, it shows the updated information.
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");
Can compare viewResultSet to updateResetSet to see if changes have been made by another user.
resultSet.updateString(colname,colvalue); // one or more column updates
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