Hibernate, HSQL, and Update w/ Limits

问题:

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.

回答1:

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.

An example would be like:

UPDATE MyObj o SET o.prop = :prop WHERE o.id IN (SELECT id FROM MyObj LIMIT 10)