Hello I'm trying to display the Student data with his corresponding subject based on the subject_id foreign key and displaying the result on GET REQUEST. I don't know how I need to rewrite the SQL command to remove the error. Here is the 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 'INNER JOIN subject ON student.subject_id=subject.id  WHERE user_id=3' at line 1Retrieve not successful

Here is my DB schema:

Here is my code:

public ArrayList<Object> getStudentSubject(int id) throws Exception {
        Connection connection = null;

        ArrayList<Student> data = new ArrayList<>();
        ArrayList<Subject> data2=new ArrayList<>();
        ArrayList<Object> data3 = new ArrayList<>();

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


            String select ="SELECT student.user_id, student.username, student.password, student.fullname,student.email, subject.id,subject.name"  
                    "FROM student INNER JOIN subject ON student.subject_id=subject.id  WHERE user_id=?";

            PreparedStatement ps = connection.prepareStatement(select);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();

            Student model = new Student();
            Subject model2 = new Subject();

            while (rs.next()) {

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

                model2.setId(rs.getInt("id"));
                model2.setName(rs.getString("username"));

                data.add(model);
                data2.add(model2);

                data3.add(data);
                data3.add(data2);
            }
        } catch (Exception e) {
            System.out.println(e   "Retrieve not successful");
        }

        return data3;


    }

Jersey Code:

   @Path("subject/{id}")
    @GET
    public Response getStudentwithSubject(@PathParam("id") int id) throws Exception {

        return Response.ok(new Gson().toJson(studentService.getStudentSubject(id))).build();
    }

Student Model:

package com.common.db.domain;


import com.google.gson.annotations.SerializedName;

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;



    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;
    }
}

Subject Model:

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;
    }



}

CodePudding user response：

It is a simply wrong SQL formed because of string concatenation, if you observe there is no space between subject.name and FROM student. Add space either after subject.name or before FROM like below.

String select ="SELECT student.user_id, student.username, student.password, student.fullname,student.email, subject.id,subject.name "  
                    " FROM student INNER JOIN subject ON student.subject_id=subject.id  WHERE user_id=?";

Let me know if this helps.