how map join query columns to a class using hibernate?

Question

I tried to create a query that returns different columns from two tables, and I want the query columns to be mapped to the user definition class.

my Student Model :

package com.example.demo.models;

import javax.persistence.*;
import java.util.List;

@Entity
@Table
public class Students {
    public Students() {
    }

    public Students(String firstName, String lastName, String age) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
    }

    @Column
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column
    private String firstName;

    @Column
    private String lastName;

    @Column
    private String age;

    @ManyToMany(cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinTable(name = "Student_Course",
            joinColumns = @JoinColumn(name="studentID"),
            inverseJoinColumns = @JoinColumn(name="courseID"))
    private List<Course> courses;


    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public List<Course> getCourses() {
        return courses;
    }

    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }
}

my Course Model :

package com.example.demo.models;

import javax.persistence.*;
import java.util.List;

@Entity
@Table
public class Course {
    public Course() {
    }

    public Course(String courseName, String unitCount) {
        this.courseName = courseName;
        this.unitCount = unitCount;
    }

    @Column
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "CourseName")
    private String courseName;

    @Column
    private String unitCount;

    @ManyToMany(fetch = FetchType.LAZY, cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinTable(name = "Student_Course",
            joinColumns = @JoinColumn(name="courseID"),
            inverseJoinColumns = @JoinColumn(name="studentID"))
    private List<Students> students;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "teacherID")
    private Teachers teachers;

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public String getUnitCount() {
        return unitCount;
    }

    public void setUnitCount(String unitCount) {
        this.unitCount = unitCount;
    }

    public List<Students> getStudents() {
        return students;
    }

    public void setStudents(List<Students> students) {
        this.students = students;
    }

    public Teachers getTeachers() {
        return teachers;
    }

    public void setTeachers(Teachers teachers) {
        this.teachers = teachers;
    }
}

my Query in Service Layer:

    @Transactional
    public List<StudentInfo> getStudentInfo(){
        Session session = sf.openSession();
        Query hql = session.createQuery("select std.firstName, std.lastName, c.courseName from Students std join std.courses c");
        var data = hql.list();
        session.close();

        return data;
    }

and i want map query columns to this simple class :

package com.example.demo.ViewModels;

public class StudentInfo {
    private String firstName;
    private String lastName;
    private String courseName;

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }
}

and in finally..
my controller Class :

    @RequestMapping(value = "/", method = RequestMethod.GET)
    public ModelAndView Index() {

       List<StudentInfo> data = studentRepository.getAll();


        return new ModelAndView("indexView", "data", data);
    }

notice : i`m using thymeleaf in this project.
please help me.
thanks.:D

Answer 1

If you use Spring Data JPA you should be able to do it in the repository using the @Query annotation:

@Query(value = "SELECT new com.path.to.StudentInfo(std.firstName, " + 
    "std.lastName, c.courseName) " + 
    "FROM Students std join std.courses c"
List<StudentInfo> getAllStudentInfo();

Make sure you have an all-args constructor in StudentInfo though.

If you use Hibernate, it's almost the same:

entityManager.createQuery("SELECT new com.path.to.StudentInfo(std.firstName, " +
    "std.lastName, c.courseName) " + 
    "FROM Students std join std.courses c", 
StudentInfo.class)

Edit: I have concerns about whether it's supposed to work when using join, but give it a try regardless.