javaspring-boothibernatespring-data-jpardbms

Spring Data JPA, use GROUP BY to return a list of rows grouped by a column


@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.


Solution

  • 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:

    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();
    }
    
    1. You cannot group by dept and select the full entity e directly. That’s invalid SQL.

    2. Instead, select aggregated fields (like e.dept and COUNT(e)).

    3. Spring Data JPA supports interface-based projections (DeptEmployeeCount) to map partial query results cleanly.

    4. 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:

    enter image description here

    generated hibernate query

    enter image description here

    References:

    - https://docs.spring.io/spring-data/jpa/reference/repositories/projections.html
    - https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions