JPQL/HSQL update with a limit?

I want to update a @Version like column as an application managed pessimistic lock.

These are the steps I want to take:

1. Get the next number of the sequence
2. Select the first 50 records and update a @version like column with the number of the sequence.
3. Now select back those 50 records matching that sequence.

How can one write a JPQL or HSQL query whi开发者_如何学编程ch updates a column but limits itself to a fixed number of records?

One cannot. In fact, one cannot write such a query in SQL either unless one happens to be working with RDBMS that supports update ... limit X notation - not all RDBMS do.

Possible workarounds are:

• Select first 50 records (you can use limit or, rather, setMaxResults() here) and update them one by one - within the same transaction, of course.
• Select PK of the 50th record (using both setMaxResults() and setFirstResult()) and execute bulk update with entity.pk <= :pk condition. This assumes that you're fine with ordering select query by PK.

With recent versions of HSQLDB you can:

UPDATE atable SET ... WHERE ROWNUM() <=50 [AND ...]

With version 2.3.3, you can:

UPDATE atable SET ... WHERE ... LIMIT 50