重新使用部分查询进行计数的Java编码最佳实践 [英] Java coding best-practices for reusing part of a query to count

问题描述

实现 - 结果 - 分页 - hibernate-getting-total-number-of-rows 问题引发了另一个问题，关于一些实现问题：

现在你知道你必须重用部分HQL查询来完成计数，如何有效地重用？

两个HQL查询之间的区别是：

1. 选择是 count（？），而不是pojo或属性（或列表）


2. 提取不应该发生，所以某些表不应该被加入


3. 顺序应该消失

是否还有其他区别？您有编码最佳实践以有效实现此重用（关注：努力，清晰度和性能）？

一个简单的HQL查询示例：

 从a中选择a a a fetch ab ab其中a.id = 66按a.name排序
从a a中选择count（a.id）其中a.id = 66 
  

---

更新 p>

我使用 Criteria 收到了以下答案：

• （但我们主要使用HQL）


• 操纵String 查询（但大家都认为它看起来很复杂并且不是很安全）


• 包装查询，依靠数据库优化（但有一种感觉，这是不安全的）

我希望有人会提供另一条路径的选项，更多与字符串连接有关。

我们可以使用公共部分构建两个HQL查询吗？

？

解决方案

不错的问题。以下是我过去所做的事情（您已经提到很多事情）：

1. 检查 SELECT 子句存在。
   
   
   
   
   1. 如果不是，请添加 select count（*）
   
   
   2. 否则，检查它是否包含 DISTINCT 或聚合函数。如果你使用ANTLR来解析你的查询，那么可以解决这些问题，但是它很重要。您可能更愿意使用 select count（*）from（）来包装所有内容。
   
   



2. 删除获取所有属性


3. 删除 fetch 来自连接，如果你正在将HQL解析为字符串。如果你真的使用ANTLR解析查询，你可以完全移除 left join ;检查所有可能的引用是相当混乱的。


4. 删除顺序


5. 你已经在1.2中完成了，你需要通过 / 来移除/调整 group。。



以上内容适用于HQL，当然。对于Criteria查询，你可以做的事情非常有限，因为它不适合轻松操作。如果你在Criteria之上使用了某种包装层，那么你最终将得到ANTLR解析结果的（有限的）子集，并且可以应用上面的大部分。

由于您通常会持续抵消您当前的页面和总计数，所以我通常首先运行实际查询并给定限制/偏移量，并且只运行 count（* ）查询结果返回的数量是否大于或等于极限AND偏移量为零（在所有其他情况下，我运行 count（*）



更新（手工组装HQL）

我并不特别喜欢这种方法。当被映射为命名查询时，HQL具有构建时错误检查的优势（在技术上运行良好，因为SessionFactory必须被构建，尽管通常在集成测试期间完成）。当在运行时生成时，它在运行时会失败:-)做性能优化也不是很容易。

当然，同样的推理适用于Criteria，但它有点难度由于定义良好的API，而不是字符串连接，因此而搞砸了。并行构建两个HQL查询（分页和一个全局计数）也会导致代码重复（可能会出现更多的错误），或者迫使您在顶部编写某种包装器层来为您执行此操作。两种方式都远非理想。如果你需要从客户端代码（比如在API上）这样做，问题会变得更糟。

其实我已经对此问题进行了相当多的回应。从 Hibernate-Generic-DAO 搜索API似乎是一种合理的折衷方案;我在回答上述链接问题时有更多细节。

The implementing-result-paging-in-hibernate-getting-total-number-of-rows question trigger another question for me, about some implementation concern:

Now you know you have to reuse part of the HQL query to do the count, how to reuse efficiently?

The differences between the two HQL queries are:

1. the selection is count(?), instead of the pojo or property (or list of)
2. the fetches should not happen, so some tables should not be joined
3. the order by should disappear

Is there other differences?

Do you have coding best-practices to achieve this reuse efficiently (concerns: effort, clarity, performance)?

Example for a simple HQL query:

    select       a     from A a join fetch a.b b where a.id=66 order by a.name
    select count(a.id) from A a                  where a.id=66

---

UPDATED

I received answers on:

• using Criteria (but we use HQL mostly)
• manipulating the String query (but everybody agrees it seems complicated and not very safe)
• wrapping the query, relying on database optimization (but there is a feeling that this is not safe)

I was hoping someone would give options along another path, more related to String concatenation.
Could we build both HQL queries using common parts?

解决方案

Nice question. Here's what I've done in the past (many things you've mentioned already):

1. Check whether SELECT clause is present.
   1. If it's not, add select count(*)
   2. Otherwise check whether it has DISTINCT or aggregate functions in it. If you're using ANTLR to parse your query, it's possible to work around those but it's quite involved. You're likely better off just wrapping the whole thing with select count(*) from ().

2. Remove fetch all properties
3. Remove fetch from joins if you're parsing HQL as string. If you're truly parsing the query with ANTLR you can remove left join entirely; it's rather messy to check all possible references.
4. Remove order by
5. Depending on what you've done in 1.2 you'll need to remove / adjust group by / having.

The above applies to HQL, naturally. For Criteria queries you're quite limited with what you can do because it doesn't lend itself to manipulation easily. If you're using some sort of a wrapper layer on top of Criteria, you will end up with equivalent of (limited) subset of ANTLR parsing results and could apply most of the above in that case.

Since you'd normally hold on to offset of your current page and the total count, I usually run the actual query with given limit / offset first and only run the count(*) query if number of results returns is more or equal to limit AND offset is zero (in all other cases I've either run the count(*) before or I've got all the results back anyway). This is an optimistic approach with regards to concurrent modifications, of course.

Update (on hand-assembling HQL)

I don't particularly like that approach. When mapped as named query, HQL has the advantage of build-time error checking (well, run-time technically, because SessionFactory has to be built although that's usually done during integration testing anyway). When generated at runtime it fails at runtime :-) Doing performance optimizations isn't exactly easy either.

Same reasoning applies to Criteria, of course, but it's a bit harder to screw up due to well-defined API as opposed to string concatenation. Building two HQL queries in parallel (paged one and "global count" one) also leads to code duplication (and potentially more bugs) or forces you to write some kind of wrapper layer on top to do it for you. Both ways are far from ideal. And if you need to do this from client code (as in over API), the problem gets even worse.

I've actually pondered quite a bit on this issue. Search API from Hibernate-Generic-DAO seems like a reasonable compromise; there are more details in my answer to the above linked question.

这篇关于重新使用部分查询进行计数的Java编码最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！