Menu
I am using a JPA distinct projection to get some data:
select distinct o.f1, o.f2, o.f3 from SomeEntity o where ...
This works fine with setFirstResult and setMaxResults to page data.
However I need to count the total number of rows without fetching all of them. I have tried:
select count(distinct o.f1, o.f2, o.f3) from SomeEntity o where ...
This does not work (with EclipseLink anyway) and it doesn't seem to be allowed by the JPA spec. Is there another way? I don't want to have to write an SQL query to do this.
839
The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.
NULL values are not included in COUNT DISTINCT counts.
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
Projection is one of the first things you're probably thinking about when implementing a query with Spring Data JPA. This is because projection defines the entity attributes and the database columns returned by your query. So, selecting the right columns is important for your business logic.
Try this:
select count(distinct o) from SomeEntity o where ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
