JPA group by query

Question

I have a table EMPLOYEES

EMP_ID	DEPT_ID	NAME
1	1	FSJFJF
2	1	GRFGFF
3	2	SFFKSF
4	2	DFSFSF

I want to create a map of EmployeeDTO by grouping employee data based on deptId.

public class EmployeeDTO {

    private Integer deptId;

    private List<Employee> employeeList;
}

Currently I am using

SELECT a FROM Employee a

and using groupingBy

Map<Integer, List<Employee>> map = employees.stream()
                .collect(groupingBy(employee -> employee.getDeptId()));

But it is inefficient for a very large data as it using in memory grouping instead of using database group by query. Do I need to use Projections here? Any examples? Please help.

Answer 1

I am assuming you are using MySQL.

MySQL has JSON_OBJECTAGG, JSON_OBJECT

Then your query will look like following

SELECT dept_id,
json_arrayagg(JSON_OBJECT('name', name, 'emp_id', empId)) as employees
from EMPLOYEE group by dept;

It will return following result set

dept_id employees

1       [{"name": "FSJFJF", "emp_id": 1}, {"name": "GRFGFF", "emp_id": 1}]
2       [{"name": "SFFKSF", "emp_id": 3}, {"name": "DFSFSF", "emp_id": 4}]

Now how do we map this result into JPA.

JpaConverterJson.java

@Converter(autoApply = true)
public class JpaConverterJson implements AttributeConverter<Object, String> {

  private final static ObjectMapper objectMapper = new ObjectMapper();

  @Override
  public String convertToDatabaseColumn(Object meta) {
    try {
      return objectMapper.writeValueAsString(meta);
    } catch (JsonProcessingException ex) {
      return null;
      // or throw an error
    }
  }

  @Override
  public Object convertToEntityAttribute(String dbData) {
    try {
      return objectMapper.readValue(dbData, Object.class);
    } catch (IOException ex) {
      // logger.error("Unexpected IOEx decoding json from database: " + dbData);
      return null;
    }
  }

}

DepartmentWiseEmployees.java

class DepartmentWiseEmployees {

    private Long deptId;

    @Convert(converter = JpaConverterJson.class)
    private List<Employee> employees;

    // omitting getter and setter
}

Then a simple JPQL inside the Repository

public interface EmployeeRepository extends JPARepository<Employee, Long> {
    
    @Query(value = "SELECT dept_id, json_arrayagg(JSON_OBJECT('name', name, 'emp_id', empId)) as employees from EMPLOYEE group by dept;", native = true)
    public List<DepartmentWiseEmployees> findDepartmentWiseEmployees();
}

Reference:

• How to map a map JSON column to Java Object with JPA