1

I'm writing a module that provides data for a UI table, where a user can pass a JPA Criteria query. Fetching the data is fine. However I couldn't find a solution to count the rows from the query to specify the "length" for the table.

What I want to do is to pass the query as a subquery in the FROM part of a new query, but I don't know if this even possible with Criteria.

The resulting SQL should be something like this:

SELECT count(1) FROM <user specified query as subquery>

All the examples that I found said I should recreate the query with the same root, the same joins...etc. And change the selection to contain the count, but thats much more complicated than this and also, if I do that I have to think of stuff like: what if the original query contained a group by clause, how do I copy that to a new query without getting multiple rows in the result.

I'd tried to do this:

    CriteriaQuery countingQuery = criteriaBuilder.createQuery(Long.class);
    Root from = countingQuery.from(userSpecifiedQuery.subquery(entityClass));
    countingQuery.select(criteriaBuilder.count(criteriaBuilder.literal(1L)));

But there is no from method that accepts Subquery as a parameter.

The Subquery class has the correlate method which would create a Root from the Subquery, which could be used instead of the countingQuery.from(), but I couldn't figure out what root to pass to the correlate method.

The java-doc sais:

/**
 * Create a subquery root correlated to a root of the 
 * enclosing query.
 * @param parentRoot  a root of the containing query
 * @return subquery root
 */

It's confuses me more. Isn't the "enclosing" query's root in my case would be the root created by this method? (Or maybe this method does something completely different what I thought? )

cs.peter
  • 11
  • 1
  • 3
  • Check this, I this you'll find your answer : https://stackoverflow.com/questions/43468272/java-hibernate-count-rows/43468343#43468343 – Zorglube Jul 05 '18 at 09:44
  • @Zorglube In that answer the query put into the where clause, not into the from. In cases where the original query contains the id of a single entity could be useful, but I cannot assume this. My code could get a query with multiple joins, or even without a way to get the ids. (E.g.: aggregated query) – cs.peter Jul 05 '18 at 10:54
  • If I understood well you just look for an solution to count the rows returned from a query to specify the "length" of an table on your UI ? – Zorglube Jul 05 '18 at 11:22
  • @Zorglube Yes thats exactly what i try to do. – cs.peter Jul 07 '18 at 08:49
  • You just have to read the response size from you response object. – Zorglube Jul 09 '18 at 12:46
  • @Zorglube Yes thats an option. However, it will fetch the rows from the database to the app server, which can be slow, memory consuming, and in some cases can kill the application. So I need a better solution. (For the time being this is what we've done, but I'm not happy with that solution.) – cs.peter Jul 19 '18 at 07:51
  • Te better solution is an equivalent of `Select Count(*) From XXX Where XXX.yyy = ...` – Zorglube Jul 19 '18 at 08:05

0 Answers0