带有限制的Hibernate，HSQL和更新 [英] Hibernate, HSQL, and Update w/ Limits

问题描述

是否可以限制使用Hibernate/HQL更新的行数?例如:

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!
}

我一直在使用Google搜索，因此我尝试使用HQL，以便可以在内存dbs中使用HSQL DB以及在部署中使用MySql进行一些单元测试. MySql在Update语句上支持Limit子句，但是HSQL不支持，在HSQL中使用内部选择进行UPDATE时需要按顺序排序，这似乎是个坏主意.我可以通过一种方法来限制更新中的行数吗?

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?

谢谢.

推荐答案

在Hibernate 3.5.5中尝试HSQLDB 2.0(最新的快照jar，而不是GA)

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

它确实需要在内部以LIMIT结尾，但不再需要ORDER BY.另外，如果ORDER BY与SELECT使用的索引相同，则可以使用索引.

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.

示例如下:

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

这篇关于带有限制的Hibernate，HSQL和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！