# JPA Criteria query and pagination

Hi,

I wonder what the correct way is to gather data required for pagination with JPA and the criteria API.

What I need is the total number of rows to calculate the position and total number of pages in the view - in the past I added a 'count' to the select part. (Maybe there is a better way?)

Now I have the problem that I am aggregating data with groupBy, leading to the result that the count is no longer representing the total number of rows, but the number of collapsed rows per row.

For example:

I have a Customer entity and in the criteria query I add a

groupBy(Customer_.city, Customer_.age);

The result will then show something like

City Age Count

London 20 5

London 21 2

London 22 10

...

I came up with the idea to have a subquery - but this is per JPA spec not allowed in the select part.

I could write a second query which uses distinct values to calculate what should be the outcome of the query - this works as long as I am not using a HAVIN in the WHERE part, since then the result will depend on the aggregation.

After having finished reading the complete google and stackoverflow corpus ;) I wonder if I am doing something completely wrong and there is in fact an easy solution for pagination with JPA in general. I would be very happy if someone could point out that solution.

Thank you!

(My current 'solution' is in fact to make a second query which just selects a unique field of the aggregation query and then counting the size of the result set: query.getResultList().size(); -- I really consider this a clumsy workaround, but I want to stick to the criteria query in order to avoid creating queries manually.)

I wonder what the correct way is to gather data required for pagination with JPA and the criteria API.

What I need is the total number of rows to calculate the position and total number of pages in the view - in the past I added a 'count' to the select part. (Maybe there is a better way?)

Now I have the problem that I am aggregating data with groupBy, leading to the result that the count is no longer representing the total number of rows, but the number of collapsed rows per row.

For example:

I have a Customer entity and in the criteria query I add a

groupBy(Customer_.city, Customer_.age);

The result will then show something like

City Age Count

London 20 5

London 21 2

London 22 10

...

I came up with the idea to have a subquery - but this is per JPA spec not allowed in the select part.

I could write a second query which uses distinct values to calculate what should be the outcome of the query - this works as long as I am not using a HAVIN in the WHERE part, since then the result will depend on the aggregation.

After having finished reading the complete google and stackoverflow corpus ;) I wonder if I am doing something completely wrong and there is in fact an easy solution for pagination with JPA in general. I would be very happy if someone could point out that solution.

Thank you!

(My current 'solution' is in fact to make a second query which just selects a unique field of the aggregation query and then counting the size of the result set: query.getResultList().size(); -- I really consider this a clumsy workaround, but I want to stick to the criteria query in order to avoid creating queries manually.)