@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
private long salary;
.............
}
I am trying to get the result of the following query using Spring Data JPA:
SELECT e.dept, e
FROM Employee e
GROUP BY e.dept
HAVING e.dept IN ('IT', 'Admin')
We cannot have a map response using default Spring Data JPA implementations and hence the above query cannot be directly used in the repository layer.
How can I get that result using a criteria with a paginated response?
The data size is expected to be large and grouping the data in Java using stream might not be an option.
The query that i gave itself is not directly possible in sql I guess . I want to know if there is any way to achieve the same result.
SELECT e.dept, e
FROM Employee e
GROUP BY e.dept
HAVING e.dept IN ('IT', 'Admin')
is not valid SQL or JPQL. In SQL, when using GROUP BY e.dept
, you cannot select the full entity e
because it contains multiple columns (like id
, name
, salary
) that are not grouped or aggregated.
You can achieve grouped results with pagination by:
Selecting aggregated data (like dept
and COUNT(*)
)
Using Spring Data JPA interface-based projections for clean mapping
Applying Pageable
for paginated response
Here’s how you can do it:
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
@Query("SELECT e.dept AS dept, COUNT(e) AS employeeCount " +
"FROM Employee e " +
"GROUP BY e.dept " +
"HAVING e.dept IN :depts")
Page<DeptEmployeeCount> countEmployeesByDept(@Param("depts") List<String> depts, Pageable pageable);
}
public interface DeptEmployeeCount {
String getDept();
Long getEmployeeCount();
}
You cannot group by dept
and select the full entity e
directly. That’s invalid SQL.
Instead, select aggregated fields (like e.dept
and COUNT(e)
).
Spring Data JPA supports interface-based projections (DeptEmployeeCount
) to map partial query results cleanly.
Pagination works by returning Page<DeptEmployeeCount>
, which includes pagination metadata (totalPages
, totalElements
, etc.).
I got proper response as you can see in below screen shot:
generated hibernate query
References:
- https://docs.spring.io/spring-data/jpa/reference/repositories/projections.html
- https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions