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

请告诉我这是否有帮助。