Hibernate, HSQL, and Update w/ Limits

Question

Is it possible to limit the number of rows that are updated using Hibernate/HQL? For instance:

Query q = em.createQuery("UPDATE MyObj o Set o.prop = :prop");
q.setParameter("prop", "foo");
q.setMaxResults(myLimit);

int res = q.executeUpdate();
if (res > myLimit) {
    // This is entering here and I don't want it to!
}

I've been Googling around and such, and I am trying to use HQL so that I can do some unit tests using HSQL DB in memory dbs, as well as using MySql in deployment. MySql supports the Limit clause on Update statements, but HSQL does not, and doing an UPDATE with an inner select in HSQL required an order by, which seemed like a bad idea. Is there a way for me to achieve limiting the number of rows in an update?

Thanks.

Answer 1

Take a look at this:

How do you do a limit query in HQL

Answer 2

Try HSQLDB 2.0 (the latest snapshot jars, not the GA) with Hibernate 3.5.5

It does require an inner select with LIMIT at the end but this no longer requires an ORDER BY. Also, ORDER BY can use an index if it is the same index as the one used for SELECT.