Returning nested custom classes from Spring JPA, Hibernate-CodePudding

I have a Student class.

@Entity
public class Student {
  private long id;
  private String name;
  private String department;
  private String subDepartment;
  private int marks;

  //getters, setters and constructors.
}

I want to be able to get min, max and average marks grouped by department and then subDepartment.

My repository:

public class StudentRepository extends JpaRepository<Student,Long> {

 @Query("Select s.department, s.subDepartment , min(marks), max(marks), avg(marks) from Student as s groupby s.department, s.subDepartment")
 List<Object[]> getStatsByDepartmentAndSubDepartment()
}

Of course, this works but I want to avoid using List<Object[]> and instead get something like:

Map<String, Map<String,Stats>> class

Where first key is Department, second key is subDepartment and Stats class encapsulates min,max,average

Is there a way to do this with Spring JPA. ? I checked and nested classes are not possible.

How about Hibernate or any other solution? I am a newbie when it comes to Hibernate.

I am using Spring boot and can add any dependency needed. Any database is ok, even H2 for a start.

CodePudding user response：

You cannot return a map. You can convert the rows using a class DTO.

Class DTO with constructor

You can also create a class DTO:

class StudentDTO {

    public StudentDTO(String dep, String subDep, Integer min, Integer max, Double avg ) {
       this.stats = new Stats(min, max, avg);
       ...
    }
}

And then call it in the select clause:

select new StudentDTO(s.department, s.subDepartment, avg(s.marks), ...) from ...

This will return a List<StudentDTO>. As long as the constructor matches the select clause, it will work fine.

Interface DTO

You can also define DTO using interfaces, but I don't think it works if you have nested classes.

This will work though:

interface StudentViev {
   String getDepartment();
   String getSubdepartment();
   Integer getMinMark();
   Integer getMaxMark();
   Double getAvgMark();
}

and now you can use it as type for the list:


 @Query("Select s.department as department, s.subDepartment as subDepartment, min(marks) as minMark, max(marks) as maxMark, avg(marks) as avgMark from Student as s groupby s.department, s.subDepartment")
 List<StudentView> getStatsByDepartmentAndSubDepartment()

Note that I've used aliases in the select cluase so that the column names returned match the getters in the interface