title: Spring boot jpa table hierarchy
body: <p>I have just one table in the database named <strong>Student</strong>. Below is the jpa entity against it in my code:</p>
  
  <pre><code>@Entity
  @Table(name = "student")
  public class Student{
  
  @Id
  private Long id;
  
  @Column
  private String firstName;
  
  @Column
  @Embedded
  @JsonUnwrapped
  private School school;
  }
  
  public class School{
  
  private Integer grade;//eg. 2nd, 3rd
  
  private String type; //eg. primary, secondary
  }
  </code></pre>
  
  <p>Until now, the code was only to fetch all the students and their data or to fetch a particular student. So, the DB schema was such. But now, we have a new functionality wherein we need to search based on a particular grade and fetch all students for that particular grade. OR fetch all students for a particular school type eg. all students in the primary school. So, the requirement has totally reversed such that we need to send the return the below schema to the front end:</p>
  
  <pre><code>class SchoolResponseDTO{
  
  private String schoolType;
  
  private List&lt;Integer&gt; grades;
  }
  
  class Grade{
  
  private Integer id;
  
  private List&lt;Integer&gt; studentId;
  }
  </code></pre>
  
  <p>To be a bit verbose, from now on, we need to find all the school types, then, all the grades in that school types, and then, all the students in that school type and grade.</p>
  
  <p>Until now, we were using the Spring JpaRepository for our requirements. I feel this new requirement would require custom queries and I dont think this can be handled in a straight forward manner using the JPARepository. I just need to know what do you think. Can this be done without custom sql queries?</p>