有没有办法获得具有结果集的JPA命名查询的计数大小？ [英] Is there a way to get the count size for a JPA Named Query with a result set?

问题描述

我喜欢JPA中的命名查询的想法，我将要做的是静态查询，但我经常想要查询查询的计数结果以及查询的某个子集的结果列表。我宁愿不写两个几乎相同的NamedQueries。理想情况下，我想要的是：

I like the idea of Named Queries in JPA for static queries I'm going to do, but I often want to get the count result for the query as well as a result list from some subset of the query. I'd rather not write two nearly identical NamedQueries. Ideally, what I'd like to have is something like:

@NamedQuery(name = "getAccounts", query = "SELECT a FROM Account")
.
.
  Query q = em.createNamedQuery("getAccounts");
  List r = q.setFirstResult(s).setMaxResults(m).getResultList();
  int count = q.getCount();

因此，假设m为10，s为0且Account中有400行。我希望r有一个包含10个项目的列表，但我想知道总共有400行。我可以写第二个@NamedQuery：

So let's say m is 10, s is 0 and there are 400 rows in Account. I would expect r to have a list of 10 items in it, but I'd want to know there are 400 rows total. I could write a second @NamedQuery:

@NamedQuery(name = "getAccountCount", query = "SELECT COUNT(a) FROM Account")

但如果我总是想要这样做，那么这似乎是违反DRY的行为伯爵。在这个简单的例子中，很容易保持两者同步，但是如果查询发生变化，我必须更新两个@NamedQueries以使值保持一致，这似乎不太理想。

but it seems a DRY violation to do that if I'm always just going to want the count. In this simple case it is easy to keep the two in sync, but if the query changes, it seems less than ideal that I have to update both @NamedQueries to keep the values in line.

这里的一个常见用例是获取某些项目的子集，但需要某种方式来指示总计数（显示1-10的400）。

A common use case here would be fetching some subset of the items, but needing some way of indicating total count ("Displaying 1-10 of 400").

推荐答案

所以我最终使用的解决方案是创建两个@NamedQuerys，一个用于结果集，一个用于计数，但是在静态字符串中捕获基本查询以保持DRY并确保两个查询保持一致。所以对于上面的内容，我有类似的东西：

So the solution I ended up using was to create two @NamedQuerys, one for the result set and one for the count, but capturing the base query in a static string to maintain DRY and ensure that both queries remain consistent. So for the above, I'd have something like:

@NamedQuery(name = "getAccounts", query = "SELECT a" + accountQuery)
@NamedQuery(name = "getAccounts.count", query = "SELECT COUNT(a)" + accountQuery)
.
static final String accountQuery = " FROM Account";
.
  Query q = em.createNamedQuery("getAccounts");
  List r = q.setFirstResult(s).setMaxResults(m).getResultList();
  int count = ((Long)em.createNamedQuery("getAccounts.count").getSingleResult()).intValue();

显然，在这个例子中，查询体是微不足道的，这是过度的。但是对于更复杂的查询，您最终会得到查询正文的单一定义，并且可以确保您同时拥有两个查询。您还可以获得预编译查询的优势，至少使用Eclipselink，您可以在启动时进行验证，而不是在调用查询时进行验证。

Obviously, with this example, the query body is trivial and this is overkill. But with much more complex queries, you end up with a single definition of the query body and can ensure you have the two queries in sync. You also get the advantage that the queries are precompiled and at least with Eclipselink, you get validation at startup time instead of when you call the query.

通过在两者之间进行一致的命名在两个查询中，可以通过基于查询的基本名称来包装代码体以运行两个集。

By doing consistent naming between the two queries, it is possible to wrap the body of the code to run both sets just by basing the base name of the query.

这篇关于有没有办法获得具有结果集的JPA命名查询的计数大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！