1

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

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

2 Comments

thanks Krisz :) .i have a question. '@Query' most replace on the interface method or class method in service layer? Because i have interfaces with '@Repository' annotation and classes with '@Service' annotation.
You would have to add a method with a Query in the repository layer. See docs.spring.io/spring-data/jpa/docs/current/reference/html/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.