嘗試 GET REQUEST 時遇到 SQL 語法錯誤

Question

您好，我正在嘗試根據 subject_id 外鍵顯示學生資料及其相應主題，並在 GET REQUEST 上顯示結果。我不知道如何重寫 SQL 命令來消除錯誤。 錯誤如下：

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

這是我的資料庫架構：

這是我的程式碼：

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;


    }

球衣程式碼：

#

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

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

學生模型：

#

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

主題模型：

#

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



}

Answer 1

如果您觀察到 subject.name 和 FROM Student 之間沒有空格，則這是由於字串連接而形成的完全錯誤的 SQL。在 subject.name 之後或 FROM 之前新增空格，如下所示。

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=?";

請告訴我這是否有幫助。