I'm using Hibernate 5.2.8 with JPA, no Spring or anything like that. As I'm sure you're familiar, in order to get a total count of objects, a CriteriaQuery<Long> needs to be created. Since I've already written an entire CrtieriaQuery with an existing root / joins / predicates, and since changing the type of an existing CriteriaQuery is a no-no, it'd be great if I could just create a new CriteriaQuery re-using the join chain and conditions that I've already created:



Of course, this won't work because cntQ needs a .from() on it, pointing to a new root (since the root apparently can't be inferred simply from what's being counted?....)
But then, if I need to point cntQ to a new root, I would need to also create new joins on that root to satisfy all my predicates...
And then, if I create new joins, I have to create new predicates based on all those joins...

...and the result is just a big mess of code duplication could have been avoided by sticking with simple string splicing (i.e. just re-use a stringified FROM clause). I could break out the creation of those joins and their predicates into a separate method that is called when constructing both the count and list queries, but this would just bring things to a whole new apex of unintelligibility. So I would need to just duplicate code in order for other devs to be able to interpret this easily at all.

Unfortunately, that code duplication, not to mention the extra CPU used in translating from the criteria API to JPQL, means I'm better off going back to string splicing rather than using the criteria API. I really hope I'm wrong. What can be done about this?

If you need to build dynamic queries, then you need to use either Criteria API or a tool like jOOQ. Just don't use string concatenation because you risk SQL injection attacks, as explained in this article.

Now back to your problem. Reusability is useful only when it makes sense. Reusing joins or where clauses across distinct queries is going to hurt readability, abnd create coupling between business use case data access methods. That's worse that duplicating a join or a where clause.

We ultimately decided just to split out API calls for the list of items and the counts so that the same logic can be used for both lists and counts, rather than trying to fetch both in one go. The code is way cleaner this way, and more performant too.

It's always a good idea to split queries instead of reusing one that would otherwise grow too complex. Simpler queries generate simple execution plans, so they are faster to execute.

Re-using the query logic itself for both listing and counting was really straightforward. Pass in a "count" bool to a method that returns a typed query, then you only have to check the boolean twice:



Then when the query is used