Hi. This is my first query in this forum. Thanks in advance to all who helps me resolve my problem.

The Problem:
I want to implement sql for update in hibernate. I know that I can set the LockMode for Query or for a Criteria.
To be specific, in normal JDBC I can do a "select ... for update" and I can update the resultset while rs.next() and set some values required and call rs.update() method which will update the row that is currently pointed by the resultset by setting the Resultset.CONCURRENT_UPDATABLE in the prepared statement. I need the same implementation in Hibernate.

I tried the following.

1. Using org.hibernate.Query
created a query by using a Bean class for select. and then added the query.setLockMode(alias, LockMode.UPGRADE);
Whatever I give as alias, it gives me an error saying unknown alias.

2. Using Criteria
Created a Criteria using the Bean class and set some Restrictions to it.
now called the criteria.setLockMode(LockMode.UPGRADE);
This implementation throws error saying.. this_my_key is not a valid identifier.

If i remove the setLockMode method call, Then everything works fine in a normal way without the db level lock..


I actually require a db level lock as there will be multiple instances of my application running in different jvm.

My database is Oracle 10g.

Please let me know if any additional info is required.

Hi,



I've done a test and it works as expected, please copy/paste query and java code.

Anthony

Thanks Anthony,

my problem in this approach is, I don't know what exactly I should give Alias. What does it mean. How can I create it.

My Code looks something like this..

Query query = session.createQuery("select batchSeq,recNum from BatchMasterBean where status = 'U'");

Now I would like to lock this select query.

query.setLockMode("alias", LockMode.UPGRADE);

Can you tell me what should be given as alias here. DO i need to create an alias. If so then how?

select b.batchSeq,b.recNum from BatchMasterBean b where status = 'U'
query.setLockMode("b", LockMode.UPGRADE);

Hi Anthony,

tried as per your suggestion.

SELECT b.recordNumber, b.status, b.referenceId, b.counter, b.recordType FROM SelectBatchDetailBean b WHERE b.batchSequenceNumber = ? AND b.status = 'U'

query.setLockMode("b", LockMode.UPGRADE);

throwing error. stacktrace as follows..

Exception in thread "RBS/RBS-PR-1" java.lang.IllegalArgumentException: alias not found: b
at org.hibernate.loader.hql.QueryLoader.applyLocks(QueryLoader.java:319)
at org.hibernate.loader.Loader.preprocessSQL(Loader.java:183)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1153)
at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:410)
at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:281)
at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:935)
at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
at com.equifax.ntg.shaolin.batchprocessor.database.abstraction.BatchDatabaseAbstraction.getNextRequest(BatchDatabaseAbstraction.java:502)
at com.equifax.ntg.shaolin.batchprocessor.worker.Worker.run(Worker.java:151)
at java.lang.Thread.run(Thread.java:595)

and with
SELECT b FROM SelectBatchDetailBean b WHERE b.batchSequenceNumber = ? AND b.status = 'U'
?

Hi Anthony,

This also give me the same exception. Just wanted to know if this kindof implementation is possible using hibernate or not. If not then I can force to use jdbc in that case..

Thanks
Praveen