CRUD GET operation not displaying the faculty students and their subjects properly

Question

When I do GET REQUEST to display faculty data with the students and subjects that the student has the student data gets lost and the subject data is added to one student instead of separating the students and their subjects.

Here is how it looks like in Postman:

Here is how I want the GET REQUEST output to look like:

{
    "id": 1,
    "university_id": 1,
    "name": "FSHMN",
    "Enrolled Students:": [
        {
            "id": "5",
            "username": "Johnny",
            "password": "d123",
            "fullname": "Depp",
            "email": "[email protected]",
            "subjects": [
                {
                    "id": 1,
                    "name": "Programim 1"
                },
                {
                    "id": 2,
                    "name": "Programim 2"
                },
                
                 {
                    "id": 3,
                    "name": "Calculus"
                },
                
                {
                    "id": 4,
                    "name": "Discrete mathematics"
                },
             ]
                
            "id": "4",
            "username": "Student2",
            "password": "d123",
            "fullname": "studentfull",
            "email": "[email protected]", 
            "subjects": [
          
                {
                    "id": 1,
                    "name": "Programim 1"
                },
                {
                    "id": 2,
                    "name": "Programim 2"
                },
                {
                    "id": 3,
                    "name": "Calculus"
                },
                {
                    "id": 4,
                    "name": "Discrete mathematics"
                }
            ]
        }
    ]
}

I want the enrolled students to be separated along with their subjects, so what concepts do I need to know here to make this work properly.

Here is my code:

The method that the GET REQUEST is calling:

 public Faculty  getFacultyStudent(int id) throws Exception {
        Connection connection = null;

        Faculty faculty = new Faculty();
        Student student = new Student();
        student.setSubjectList(new ArrayList<>());
        faculty.setStudentList(new ArrayList<>());

        try {
            connection = new MysqlDbConnectionService().getConnection();


            String select = "SELECT f.fid,\n" +
                    "      f.fname,\n" +
                    "      f.university_id,\n" +
                    "       s.user_id,\n" +
                    "       s.username,\n" +
                    "       s.password,\n" +
                    "       s.fullname,\n" +
                    "       s.email,\n" +
                    "       subj.id,\n" +
                    "       subj.name\n" +
                    "FROM faculty f\n" +
                    "         INNER JOIN student_faculty sf ON sf.faculty_id=f.fid \n" +
                    "         INNER JOIN student s ON sf.student_id=s.user_id\n" +
                    "         INNER JOIN faculty_subject fs ON f.fid = fs.faculty_id\n" +
                    "         INNER JOIN subject subj ON fs.subject_id = subj.id\n" +
                    "WHERE fid = ?";



            PreparedStatement ps = connection.prepareStatement(select);

            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();


            while (rs.next()) {
                if(faculty.getFid()==0) {
                   faculty.setFid(rs.getInt("fid"));
                    faculty.setUniversityid(rs.getInt("university_id"));
                   faculty.setFname(rs.getString("fname"));


                }

                student.setId(rs.getString("user_id"));
                student.setUsername(rs.getString("username"));
                student.setPassword(rs.getString("password"));
                student.setFullName(rs.getString("fullname"));
                student.setEmail(rs.getString("email"));

                Subject subject=new Subject();
                subject.setId(rs.getInt("id"));
                subject.setName(rs.getString("name"));
                student.getSubjectList().add(subject);


            }

        } catch (Exception e) {
            System.out.println(e + " Retrieve not successful");

        }

        faculty.getStudentList().add(student);

        return faculty;


    }

Faculty Class:

package com.common.db.domain;
import com.google.gson.annotations.SerializedName;

import java.util.Collections;
import java.util.List;


public class Faculty {


    @SerializedName("id")
    private int fid;

    @SerializedName("university_id")
    private int university_id;

    @SerializedName("name")
    private String fname;

    @SerializedName("Enrolled Students:")
    private List<Object> studentList;


    public Faculty() {
        this.fid = fid;
        this.university_id=university_id;
    }

    public  void setFid(int fid)
    {

        this.fid = fid;
    }

    public int getFid()
    {

        return fid;
    }

    public  void setUniversityid(int university_id)
    {

        this.university_id=university_id;
    }

    public int getUniversityid()
    {

        return university_id;
    }

    public void setFname(String fname)
    {
        this.fname = fname;
    }

    public String getFname()
    {
        return fname;
    }

    public void setStudentList(List<Object> studentList) {
        this.studentList = studentList;
    }

    public List<Object> getStudentList()
    {
        return studentList;
    }

}

Student Class:

package com.common.db.domain;


import com.google.gson.annotations.SerializedName;

import java.util.List;

public class Student {


    @SerializedName("id")
    private String id;

    @SerializedName("username")
    private String username;

    @SerializedName("password")
    private String password;

    @SerializedName("fullname")
    private String fullName;

    @SerializedName("email")
    private String email;

    @SerializedName("subjects")
    private List<Subject> subjectList;


    public Student() {

    }


    public Student(String id, String username, String password, String fullName, String email) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
        this.fullName = fullName;
        this.email = email;
    }


    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public List<Subject> getSubjectList() {
        return subjectList;
    }

    public void setSubjectList(List<Subject> subjectList) {
        this.subjectList = subjectList;
    }
}

Subject Class:

package com.common.db.domain;
import com.google.gson.annotations.SerializedName;
public class Subject {




    @SerializedName("id")
    private int id;

    @SerializedName("name")
    private String name;


    public Subject() {
        this.id = id;
        this.name=name;
    }

    public  void setId(int id)
    {
      this.id=id;
    }

    public int getId()
    {
        return id;
    }

    public void setName(String name)
    {
        this.name=name;
    }

    public String getName()
    {
        return name;
    }



}

ER DIAGRAM:

What changes or additions do I need to make in my method in order to separate the students with their corresponding subjects.

Answer 1

        Map<String, Student> studentMap = new HashMap<>();
        while (rs.next()) {
            if(faculty.getFid()==0) {
               faculty.setFid(rs.getInt("fid"));
                faculty.setUniversityid(rs.getInt("university_id"));
               faculty.setFname(rs.getString("fname"));


            }
            String userID = rs.getString("user_id");

            if(!studentMap.containsKey(userID)){
                student = new Student();
                student.setId(rs.getString("user_id"));
                student.setUsername(rs.getString("username"));
                student.setPassword(rs.getString("password"));
                student.setFullName(rs.getString("fullname"));
                student.setEmail(rs.getString("email"));
                student.setSubjectList(new ArrayList<>());
                studentMap.put(userID, student);
            }else{
                student = studentMap.get(userID);

            }
            

            Subject subject=new Subject();
            subject.setId(rs.getInt("id"));
            subject.setName(rs.getString("name"));
            student.getSubjectList().add(subject);


        }
        faculty.getStudentList().addAll(studentMap.values());