使用Hibernate Criteria选择前n行和后n行

Question

假设我有两个表，Books和Reviews。 Reviews有一个列stars，可以有1到5的值。一本书可以有多个评论。

如何使用Criteria API选择所有书籍，使得每本书只返回其前3篇和后3篇评论（而不是所有评论）？

如果Criteria API无法实现，我可以尝试其他建议，例如HQL、SQL等。

- SingleShot

Answer 1

你可以始终设置升序/降序的排序方式，然后限制结果数量。

例如：

criteria.addOrder(Order.desc("id"));
criteria.setMaxResults(1);

不确定它在效率方面的表现如何。我假设它是在执行Select *之后进行过滤的？

- user969452

Answer 2

请尝试以下方法，如果对你有用请让我知道：

  // you should have the DAO class containing this queries 
  // extend HibernateDaoSupport

  List<Tag> topList = getSession().createQuery("FROM Reviews r WHERE r.book = " 
          + book + " ORDER BY r.stars asc").setMaxResults(3).list();

  List<Tag> bottomList = getSession().createQuery("FROM Reviews r WHERE r.book = " 
          + book + " ORDER BY r.stars desc").setMaxResults(3).list();

- MatBanik

---

谢谢。是的，最终我在我的Review DAO中添加了类似这样的内容，但我更喜欢使用Criteria API，所以我用了它。问题是，我正在查询与某个条件匹配的所有书籍，并且每一本书都需要其顶部和底部的评论。运行该查询然后手动浏览每个评论，运行这两个查询并调整响应将非常低效和笨拙。我已经采取了不同的解决方案。 - SingleShot

请勿将 SQL 作为字符串连接方式。这种方法非常容易引发注入攻击。 考虑到有人以变量书籍（book）输入此内容："any; drop table Reviews" - Esben Andersen

Answer 3

回答自己的问题...

我很惊讶这有多么困难。所有涉及查询图书和每本书都有其顶部和底部评论列表的解决方案在某些数据库上会导致每本书N次查询，在我的情况下（MySql）需要3N次查询。当然，有人可能能够找到聪明的方法绕过这个问题，但是通过玩弄代码、原始SQL、研究他人所做的事情、数据库限制等等，似乎总是回到那个点。

所以...我最终将问题颠倒了过来。我并不是在查询书籍时计算顶部和底部评论（这是一个非常频繁的查询 - FYI Books/Reviews仅用于举例，实际域名不同），而是在提交新评论时计算顶部和底部评论。这将把提交评论从一个“查询”转变为三个查询，但相比查询书籍，提交评论是相对不频繁的。

为此，我向Book添加了两个新属性，topReviews和bottomReviews，并将它们映射为Review上的一对多列表。然后，我更新了保存新评论的代码，以查询有关问题的书籍的顶部和底部评论（2个查询），设置书籍的顶部/底部评论属性（覆盖以前的属性），并保存该书籍。任何书籍查询现在都返回这些“缓存”的顶部和底部评论。我仍然有一个懒惰的“reviews”属性，如果需要，可以拥有所有评论（而不仅仅是顶部/底部） - 另一个一对多映射。

我愿意听取其他建议。这里的大多数答案都接近问题，但由于数据库限制或需要太多查询而无法解决问题。

- SingleShot

Answer 4

如何将Review和Book的映射设置为多对多呢？然后在Book的reviews集合中指定：order-by="rating desc"，并且lazy="true"。

假设使用了懒加载方法，数据只有在需要从集合中获取对象时才会被获取，但我不确定这一点。为了确认，您可以打开Hibernate SQL日志记录并监视运行时进行了哪些查询。

- user591593

---

我需要顶部和底部的评论，所以这并不完全适用。请查看我的答案。谢谢！ - SingleShot

Answer 5

我认为你不能使用单个查询来实现你想要的功能。但是，你可以使用两个查询来实现（使用带有命名参数的 EJBQL）：

SELECT r FROM Reviews r WHERE r.book = :book ORDER BY r.stars ASC LIMIT 3;
SELECT r FROM Reviews r WHERE r.book = :book ORDER BY r.stars DESC LIMIT 3;

当然，您可以编写一个简单的帮助方法，使这两个调用并将结果汇总到您喜欢的任何数据结构中。

- aroth

---

LIMIT 是特定于关系型数据库管理系统的。不要使用它。请使用 setMaxResults。 - Alex Gitelman

@Alex - LIMIT 也是EJBQL规范的一部分。 - aroth

你说得对。我从未使用过它。不过他想要条件查询。 - Alex Gitelman

似乎问题略有不同，他想要书籍而不是评论。我暂时撤回了我的答案，因为我不确定自己是否回答了正确的问题。 - Alex Gitelman

@Alex - 很好的观点，他想要“选择所有书籍，以便仅返回每本书的前三篇和后三篇评论”，我认为这不是直接可能的。通常，当我希望一个实体提供对另一个实体集合的“自动”自定义过滤时，我会在第一个实体中添加一个@Transient方法，该方法在支持集合上执行所需的过滤，然后返回结果（在新的集合实例中）。 - aroth

谢谢你的回答，但我没有使用EJBQL，并且正在寻找一份书籍列表，其中包含其评论集合的前三和后三。您的选择将作为子查询或类似于我的书籍查询的有用信息，但我还没有想出如何使这样的事情起作用。如果我成功了，我会发布它。 - SingleShot

Answer 6

这通常可以使用类似于 union 的东西来完成，但在 HQL 中无法实现。

不过，在 HQL 中你可以这样做。

WHERE id in ([SELECT top 3]) or id in ([SELECT bottom 3])

我没有测试这个的方式，但这个方法也许可行。

DetachedCriteria topN = [ your criteria ] 
DetachedCriteria bottomN = [ your criteria ] 

session.createCriteria(..._
    .add( Property.or(Property.forName("id").in(topN),Property.forName("id").in(bottomN) )
    .list();

- dfb

---

谢谢。我很确定我不能使用您的Criteria建议，因为DetachedCriteria没有setMaxResults()方法，而我需要这个方法来将结果限制为前/后3个（实际上，在Hibernate的JIRA网站上有一个请求添加此功能）。不过，我会尝试使用您的HQL。 - SingleShot

看起来你提出的建议，以及我想要的一般情况，在使用Criteria、HQL甚至SQL（至少在MySQL上）时都是不可能的。我可以将这个问题反过来，在保存评论时进行一些预处理，这样每本书的顶部/底部评论就会被保存在一个特殊的表中。 - SingleShot

我非常确定在MySQL中可以实现，发表你的查询，我会看一下。 - dfb

MySQL不支持带有限制的子查询 :-( - SingleShot

好的，发现得不错。大多数其他方言都支持这个，我在MSSQL中尝试过了。一个可怕的解决方案是使用临时表。如果你找到更好的方法，请务必发布。 - dfb

Answer 7

使用 org.springframework.data.domain.Pageable 的两个 HQL 查询：

在中间层服务中，您可以创建 pageable 并调用 repo：

Pageable pageableTop = new PageRequest(0, 3, Direction.ASC, "yourFieldToSortBy");
yourRepository.findTopOrderByYourEntity(pageableTop);

Pageable pageableBottom = new PageRequest(0, 3, Direction.DESC, "yourFieldToSortBy");
yourRepository.findTopOrderByYourEntity(pageableBottom);

代码库：

public interface YourRepository extends CrudRepository<YourEntity, String> {

    @Query("FROM YourEntity")
    List<YourEntity> findTopOrderByYourEntity(Pageable pageable);
}

- izy