SQL syntax Error on Java Jersey GET CRUD operation

Question

I'm trying to retrieve the information of the faculty with the students attending it and the subjects corresponding to the student.

Here is the SQL error:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM faculty f
         inner join student_faculty sf on sf.student_id=s.user_id'

Here is the picture in line 116 the s.user_id is not on yellow color indicating something not right:

Here is it how I want it to look like:

{
  "fid": "1",
  "name": "Mathematcis",
  "enrolled_students": [
    {
      "id": "student1",
      "username": "user",
      "full_name": "userstudent",
      "subjects": [
        {
          "id": 1,
          "name": "Programim 1"
        },
        {
          "id": 2,
          "name": "Programim 2"
        },
        {
          "id": 3,
          "name": "Calculus"
        },
        {
          "id": 4,
          "name": "Discrete mathematics"
        }
      ]
    }
  ]

Here is how postman shows it:

Here is my code:

 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.student_id=s.user_id\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.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);

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

        }

        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<String> 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<String> studentList) {
        this.studentList = studentList;
    }

    public List<Object> getStudentList()
    {
        return Collections.singletonList(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;
    }



}

Here is my ER Diagram:

Answer 1

I was reading through the code and the error and it appears to be just your sentence.

"subj.name , \n" + "FROM faculty f\n" +

That comma after the column name and before the FROM clause is causing that error. Try to remove it and test it again