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 are 90 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 query is used to update that one row. At
the time of the update, the current contents of that row is reread FOR UPDATE
and compared 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 made or leave in place the changes
made by the other user. In either case, that row in the original resultset has
to be made to 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-only resultset. 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
