[FIXED] Couldn't join two tables on Spring boot

Issue

I am a beginner of spring boot application. I want to join the course table and the student table together. What I tried so far I attached code below. I didn't get any errors. When the student page is loaded I show course id only I need to display the name instead of the id. I attached the screenshot image below.

Above screenshot image only displayed the course id I need to display the course name.

Student Controller

@RequestMapping(value = "/student", method = RequestMethod.GET)
public String viewStudentPage(Model model) {
     List<Student> liststudent = services.listAll();
     model.addAttribute("liststudent", liststudent);
     System.out.print("Get / ");    
     return "Student";
}

I made the relation the below. What I tried so far now.

Course.java

@Entity
public class Course {
    
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;
    private String coursename;
    private int duration;
    
    @ManyToOne
    private Student student;

    public Course()
    {       
        
    }
 
    public Course(Long id, String coursename, int duration) {
        
        this.id = id;
        this.coursename = coursename;
        this.duration = duration;
    }
    

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getCoursename() {
        return coursename;
    }
    public void setCoursename(String coursename) {
        this.coursename = coursename;
    }
    public int getDuration() {
        return duration;
    }
    public void setDuration(int duration) {
        this.duration = duration;
    }


    @Override
    public String toString() {
        return "Course [id=" + id + ", coursename=" + coursename + ", duration=" + duration + "]";
    }
}

Student.java

@Entity
@Table(name="student")
public class Student {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;
    private String stname;
    private int course;
    private int fee;
    
    @OneToMany(mappedBy = "course")
    private List<Student> student;

    public Student() {
        
        
    }
   
    public Student(Long id, String stname, int course, int fee) {
    
        this.id = id;
        this.stname = stname;
        this.course = course;
        this.fee = fee;
    }

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getStname() {
        return stname;
    }
    public void setStname(String stname) {
        this.stname = stname;
    }
    public int getCourse() {
        return course;
    }
    public void setCourse(int course) {
        this.course = course;
    }
    public int getFee() {
        return fee;
    }
    public void setFee(int fee) {
        this.fee = fee;
    }


    @Override
    public String toString() {
        return "Student [id=" + id + ", stname=" + stname + ", course=" + course + ", fee=" + fee + "]";
    }
}

StudentRepository

@Repository
public interface StudentRepository extends JpaRepository<Student, Long>{ }

Student.html

<table class="table">
  <thead class="thead-dark">
    <tr>
            <th>Student ID</th>
            <th>Student Name</th>
            <th>Course Name</th>
             <th>Fee</th>
             <th>edit</th>
             <th>delete</th>
    </tr>
  </thead>
  <tbody>
      <tr  th:each="student : ${liststudent}">
        <td th:text="${student.id}">Student ID</td>
        <td th:text="${student.stname}">Student Name</td>
        <td th:text="${student.course}">Course</td>
         <td th:text="${student.fee}">Fee</td>
        <td>
            <a th:href="@{'/Student/edit/' + ${student.id}}">Edit</a>
        </td>                               
        <td>
            <a th:href="@{'/Student/delete/' + ${student.id}}">Delete</a>
        </td>           
        </tr> 
   
  </tbody>
</table>

Custom Code i wrote it to Join

@Repository

public interface StudentRepository extends JpaRepository<Student, Long>{

    @Query(value="select student.id, student.stname, course.coursename from student Inner JOIN course ON  student.course= course.id", nativeQuery=true)
    List<Object[]> findStudent();
    
    
}

Solution

You have to add a custom query to get the course name. Your listAll() return all student object without course, the payload doesn't have any variable like name and you have course id in your entity that's why ID appearing in your UI.

Your student object have course objects also you can get like below.

you have the wrong relationship on your entity correct as below. It should come under ManyToMany relationship because one user have many courses and one course belong to many student anyway you started as oneToMany then follow as below.

within Student Entity.

 @OneToMany(fetch = FetchType.LAZY,
            cascade = CascadeType.ALL,mappedBy = "student")
    private List<Course> course;

within Course entity

 @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "id", nullable = false)
    private Student student;

then try you access the course object as below.

th:field="*{student.course.name}"

if you want to try a custom query then try to as below.

@Query(value="select s.id, s.name, c.name from Student s left JOIN Course c on student.course_id= c.id", nativeQuery=true)
List<Object[]> findStudent();

Answered By - S. Anushan