select for update using JDBCTemplate current, example, update, where

amy

Hi all,

I'd like to know how to perform a 'select for update query' using JDBCTemplate.
Once I make the query:
Code:
select ID_MOVIE, NAME, DESCRIPTION from T_MOVIES where ID_MOVIE gt; 10 AND ID_MOVIE lt; 20 for update
Then, I have to do:
Code:
update T_MOVIES set DESCRIPTION = ? where current of ?
But I don't know how to do!!! I've tried to find an example, but I didn't get it using JDBCTemplate.

Can you help me? An example or a site explaining it will be enough... I hope

Thanks a lot!!!

Just add the where clause of your select statement to your update statement, too. That will update the previously locked column(s).

If you use Oracle (don't know about other DBs here) you may use the synthetic ROWID column to do things more efficiently.Code:
select ROWID, ID_MOVIE, NAME, DESCRIPTION from T_MOVIES where ID_MOVIE gt; 10 AND ID_MOVIE lt; 20 for update

...

update T_MOVIES set DESCRIPTION = ? where ROWID = ?

Hope that is what you are looking for,
Andreas


Originally Posted by Andreas SenftJust add the where clause of your select statement to your update statement, too. That will update the previously locked column(s).

So, I remove the 'for update' statement in select and I perform first the select query and the the update query. Is that what do you refer?

However, I think the performance is much better doing a 'select... for update' statement, instead of two separate statements. Is it right?Originally Posted by Andreas SenftIf you use Oracle (don't know about other DBs here) you may use the synthetic ROWID column to do things more efficiently.

Yes I use Oracle 10g.

I'm trying to do something like this, but I'm afraid it won't work....

Code:
public static void select(){
final String query = quot;select ID_MOVIE from T_MOVIES where ID_MOVIE gt; 10 AND ID_MOVIE lt; 20 for updatequot;;
final String updateQuery = quot;update T_MOVIES set DESCRIPTION = ? where current of ?quot;;

JDBCUtil jdbc = JDBCUtil.getInstance();

PreparedStatementCreator psc = new PreparedStatementCreator(){
public PreparedStatement createPreparedStatement(Connection conn){
PreparedStatement ps = conn.prepareStatement(updateQuery);
return ps;
}
};

RowCallbackHandler rch = new RowCallbackHandler(){
public void processRow(ResultSet rs){
String cursorName = rs.getCursorName();
ps.setString(1,quot;myDescriptionquot;);       ps.setString(2, cursorName);       ps.executeUpdate();
}
};

jdbc.getTemplate().query(psc, rch);
}
My problem is in processRow, I don't know how can I access to the PreparedStatementCreator. Maybe,  I'm doing it to much difficult.

Thanks a lot for your help.


Originally Posted by mamntc02So, I remove the 'for update' statement in select and I perform first the select query and the the update query. Is that what do you refer?

No. You should leave the quot;for updatequot; in the select statement.
As of executing first the select and then the update statement: yes, that is what I meant.Originally Posted by mamntc02However, I think the performance is much better doing a 'select... for update' statement, instead of two separate statements. Is it right?

What do you want to achieve in the first place? Do you only want to update rows? In that case a simple quot;updatequot; statement would be sufficient. If you, however, need to access the rows you wish to update, then quot;select for updatequot; is ok. However, you need two statements then.

As of quot;current ofquot;: Afaik this is PL/SQL and thus will not work in plain SQL.

Regards,
Andreas

Thanks a lot!!

Originally Posted by Andreas SenftWhat do you want to achieve in the first place? Do you only want to update rows? In that case a simple quot;updatequot; statement would be sufficient. If you, however, need to access the rows you wish to update, then quot;select for updatequot; is ok.

Yes, I need to access data, too. The update process it's only to mark a row has been treated.
Now, I'm trying to do:
Code:
public static void select(){
String query = quot;select ID_MOVIE from T_MOVIES where ID_MOVIE gt; 10 AND ID_MOVIE lt; 20 for updatequot;;

JDBCUtil jdbc = JDBCUtil.getInstance();

RowCallbackHandler rch = new RowCallbackHandler(){
public void processRow(ResultSet rs) throws SQLException{
int i = 0;
String updateQuery = quot;update T_MOVIES set DESCRIPTION = ? where ID_MOVIE = ?quot;;
while(rs.next()){
Object[] obj = new Object[]{quot;myDescriptionquot; + i, rs.getLong(1)};
JDBCUtil.getInstance().getTemplate().update(updateQuery, obj);
i++;
}
}
};

jdbc.getTemplate().query(query, rch);
}
But I doesn't work either. Is update function a bad option in this context? Because it hangs when it tries to execute that function, but no exception is thrown.

Thanks!!!

I'm not sure whether nesting template operations like that is supposed to work.

I suggest having two distinct operations:
1. Execute the select statement and store the data you need (e.g. using that  RowCallbackHandler filling a List)
2. Execute the update statement on the rows you memorized during step 1.
3. Return the data you extracted in step 1.

That way you have a cleaner separation and you are able to test the different processing steps more thoroughly.

Hope that helps,
Andreas

Yes, that's what I thought if it doesn't work what I wanted to do.
However, in this way I think I don't take advantage of performance of 'for update' statement in Oracle.
You know what I mean?

quot;For updatequot; gives no performace advantage, if anything it lowers overall concurrency of the database. quot;for updatequot; means that you want db to set write lock on selected row instead of read lock, nothing more.


Originally Posted by dejanpquot;For updatequot; gives no performace advantage, if anything it lowers overall concurrency of the database. quot;for updatequot; means that you want db to set write lock on selected row instead of read lock, nothing more.

And that's all? Ok, I thought when 'for update' was set, it took some advantage of it, to perform the update.

Thanks a lot to all!!!!

Well, it makes a huge difference when you need it. If you don't know what it does, you most probably don't need it.