Accessing multiple entity join query resultset with Spring JPA

Question

I have below 2 entity classes 1.student 2. resluts and I have to return a resultset by executing the below customized query

 select s.roll_no , s.first_name, s.age ,r.subject_name , r.marks from student s , results r where s.roll_no= : rollNo and r.marks >70

which gives the result set with combination of both student and result entity. In such scenario how do i write my implementation. I have tried below two approaches

Approach 1 :

public interface GetStudentDetail extends CrudRepository<Student, String> {
    @Transactional(readOnly=true)
    @Query("select s.roll_no , s.first_name, s.age ,r.subject_name , r.marks from student s , results r where s.roll_no= : rollNo and r.marks >70")
    public List<student> getStudentDetails(@Param("rollNo")String rollNo);
}

With the above i was able to get only the student entity values from the resultset and results entity objects were not visible here.

Approach 2:

public interface GetStudentDetail extends CrudRepository<Student, String> {
    @Transactional(readOnly=true)
    @Query("select s.roll_no , s.first_name, s.age ,r.subject_name , r.marks from student s , results r where s.roll_no= : rollNo and r.marks >70")
    public List<Object[]> getStudentDetails(@Param("rollNo")String rollNo);
}

By this, i created one more VO class with both the entity variable and i manually set to those object by accessing to it position like below

List<StudentResultVo>studtVoObjList = new ArrayList<StudentResultVo>();
    for (Object[] resObj : resultSetList) {
        StudentResultVo studtVO = new StudentResultVo();
        if (resObj[0] != null) {
            studtVO.setRollNo(resObj[0].toString().trim());
        }
        //.First name
        //.Age

        if (resObj[3] != null) {
            studtVO.setSubName(resObj[3].toString().trim());
        }
        //.Marks
        studtVoObjList.add(studtVO);
    }

i knew the above approach is not a good coding practice. Is there any way to achieve solution to this problem.

Thanks.

score 2 · Answer 1 · edited May 23 '17 at 11:45

first create new interface and declare your method

public interface GetStudentDetailCustom {
  public List<Object[]> getStudentDetails(String rollNo);
}

and second create class GetStudentDetailImpl and implement the interface

public class GetStudentDetailImpl implements GetStudentDetailCustom {

    @PersistenceContext
    private EntityManager entitymanager; // I use entity manager also you want to use inject SessionFactory etc..

    @Override
    public List<Object[]> getStudentDetails(String rollNo) {
        String queryString = "SELECT s.roll_no , s.first_name, s.age ,r.subject_name , r.marks FROM student s "
                + ", results r WHERE s.roll_no= :rollNo and r.marks > 70";

        Query query = entitymanager.createQuery(queryString);
        query.setParameter("rollNo", rollNo);

        return query.getResultList();
    }

}

and refactor your repository like this

public interface GetStudentDetail extends CrudRepository<Student, String>, GetStudentDetailCustom  {}

and finaly use inject GetStudentDetail in your service class and call getStudentDetails method

Example use in your service layer :

@Autowired
private GetStudentDetail getStudentDetail;

getStudentDetail.getStudentDetails(String rollNo);

reference answer : How to add custom method to Spring Data JPA

and spring reference : http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behaviour

Accessing multiple entity join query resultset with Spring JPA

1 Answers1