Java 有没有办法使用结果集获取 JPA 命名查询的计数大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2285270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to get the count size for a JPA Named Query with a result set?
提问by Tim
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:
我喜欢 JPA 中用于静态查询的命名查询的想法，但我经常想获得查询的计数结果以及查询的某个子集的结果列表。我宁愿不写两个几乎相同的 NamedQueries。理想情况下，我想要的是：
@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();
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:
因此，假设 m 是 10，s 是 0，并且 Account 中有 400 行。我希望 r 中有一个包含 10 个项目的列表，但我想知道总共有 400 行。我可以写第二个@NamedQuery：
@NamedQuery(name = "getAccountCount", query = "SELECT COUNT(a) FROM Account")
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.
但如果我总是想要计数的话，这样做似乎是一种 DRY 违规。在这种简单的情况下，很容易让两者保持同步，但如果查询发生变化，我必须更新两个 @NamedQueries 以保持值一致似乎不太理想。
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").
这里的一个常见用例是获取项目的一些子集，但需要某种方式来指示总计数（“显示 1-10 个，共 400 个”）。
采纳答案by Tim
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:
所以我最终使用的解决方案是创建两个@NamedQuerys，一个用于结果集，一个用于计数，但在静态字符串中捕获基本查询以保持 DRY 并确保两个查询保持一致。所以对于上述，我有类似的东西：
@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();
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.
显然，在这个例子中，查询主体是微不足道的，这是矫枉过正的。但是对于更复杂的查询，您最终会得到查询正文的单一定义，并且可以确保两个查询同步。您还可以获得预编译查询的优势，并且至少使用 Eclipselink，您可以在启动时而不是在调用查询时获得验证。
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.
通过在两个查询之间进行一致的命名，可以仅通过基于查询的基本名称来包装代码主体以运行两个集合。
回答by Pascal Thivent
Using setFirstResult/setMaxResultsdo notreturn a subset of a result set, the query hasn't even been run when you call these methods, they affect the generated SELECT query that will be executed when calling getResultList. If you want to get the total records count, you'll have to SELECT COUNTyour entities in a separate query (typically before to paginate).
使用setFirstResult/setMaxResults千万不能返回结果集的一个子集，查询甚至没有被运行时调用这些方法，它们会影响调用时将执行的生成SELECT查询getResultList。如果您想获得总记录数，您必须SELECT COUNT在单独的查询中查询您的实体（通常在分页之前）。
For a complete example, check out Pagination of Data Sets in a Sample Application using JSF, Catalog Facade Stateless Session, and Java Persistence APIs.
有关完整示例，请查看使用 JSF、Catalog Facade Stateless Session 和 Java Persistence APIs 的示例应用程序中的数据集分页。
回答by alessandro
oh well you can use introspection to get named queries annotations like:
哦，您可以使用自省来获取命名查询注释，例如：
String getNamedQueryCode(Class<? extends Object> clazz, String namedQueryKey) {
NamedQueries namedQueriesAnnotation = clazz.getAnnotation(NamedQueries.class);
NamedQuery[] namedQueryAnnotations = namedQueriesAnnotation.value();
String code = null;
for (NamedQuery namedQuery : namedQueryAnnotations) {
if (namedQuery.name().equals(namedQueryKey)) {
code = namedQuery.query();
break;
}
}
if (code == null) {
if (clazz.getSuperclass().getAnnotation(MappedSuperclass.class) != null) {
code = getNamedQueryCode(clazz.getSuperclass(), namedQueryKey);
}
}
//if not found
return code;
}
