Spring support for Select for Update

[bill_bailey]

Is the following a true statement?

Spring JDBCTemplate supports the SQL syntax select ... for update, but it provides no mechanism for actually updating the current row.

We have a scenario where we want to select a set of rows for update and retrieve all the rows, updating a status column to 'Processing' as each row is loaded. We currently use Spring JDBC named parameter templates so we had hoped to find a way to do this using the same mechanism.

Assuming JDBCTemplates do not support this directly, I have been able to come up with three other possible ways to do it.

1) Extract the underlying JDBC connection and do it using raw JDBC, effectively bypassing Spring and most of its benefits.

2) Write a stored procedure (PL/SQL ... we are using Oracle) to query all the data and do the updates using its built in support for 'select for update' and 'update where current of '. Then use JDBCTemplate to call the stored procedure and return the data as a collection.

3) Use UpdatableSqlQuery. However, the documentation and examples for this class are pretty scarce. The one example I found show how it is used to update a row, but doesn't demonstrate how to actually retrieve the row. Is it just as simple as implementing mapRow ... i.e. it works pretty much like the MapSqlQuery class except for the addition of the update capability?

Comments?

Thanks.

Bill

[bill_bailey]

I just realized after looking more closely at the javadoc that one of my questions was kind of dumb. :P It appears that updateRow() is passed most of the same parameters as mapRow() ... so I assume this one method can be used to create the List that is returned by execute as well as to perform the updates. The name threw me momentarily I guess. Is this right?

I believe Oracle's implementation of updatable result sets probably uses the 'select for update' syntax under the covers but what we really need is to also use the NOWAIT clause. Is there any way to get this behavior using the UpdatableSqlQuery class?

I seem to be in a catch 22. If I use the UpdatableSqlQuery, it appears I will be stuck with a blocking 'select for update' which I don't want. If I use the JDBCTemplate, I can pass it the 'select for update nowait' sql, but I have no way to update the rows. Am I missing something? Am I going to need to step outside of Spring to do this?

Thanks again.

Bill

[bill_bailey]

If I'm using a NamedParameterJDBCTemplate and I give my RowMapper access to the same template is there any potential problem from a Spring perspective of doing something like the following.

In the row mapper:

1) Retrieve the current row and map to the object as usual.
2) Use the JDBCTemplate to do a separate update() on the row using the primary key just retrieved.

I think this will do what I need from the database perspective, but I'm not sure about the advisability of calling another JDBCTemplate method from in the RowMapper.mapRow() methos. Thoughts?

Bill

[bill_bailey]

One last addition to my last question. Does Spring expose a way to set the cursor name on a result set (i.e. setCursorName). I'm not even sure yet if Oracle's JDBC driver supports this, but if it does I thought I might even be able to use the WHERE CURRENT OF cursorname in my update statement. In any event, I thought I'd ask the question while I continue my research.

Thanks again.

Bill

[bill_bailey]

Sorry for the flurry of posts, but I believe in posting my questions as I have them to maximize the time others have to respond ... but I also believe in posting my own findings as I continue to research the answers on my own.

Oracle JDBC does not support setCursorName(), but selecting ROWID in the query and using that WHERE ROWID = ? in the update serves the same purpose. So this is an option assuming there isn't a Spring-related problem with calling another JDBCTemplate method (update) from within mapRow.

Also, it appears that one can get the NOWAIT behavior from updatable result sets simply including the NOWAIT in the SQL ... so UpdatableSqlQuery begins to look more and more like the most appealing alternative.

Bill

[jacob2221]

Hi Bill,

I also am trying to use "select for update" via JDBCTemplate but not getting the desired results. I am using JDBCTemplate in a multithreaded environment. I have scheduled spring trigger in such a way that 2 worker threads are launched after every trigger frequency. The thread is supposed to do the following -

1) Get a batch of records (say 50 at a time) from a table using "select for update" using jdbctemplate.execute method.
2) Then use jdbctemplate.batchUpdate() on the list of records retrieved in step 1 to put them in intermediate status so that other threads do not get hold of these records.

When the application is started, both the threads launched first pick up the same batch of records. This seems to happen only on application start up.

Could you advice on this?

Regards,
Jacob