Ralat sintaks SQL ditemui semasa mencuba GET REQUEST

Question

Hai, saya cuba memaparkan data pelajar dan subjek yang sepadan berdasarkan kekunci asing subject_id dan memaparkan keputusan pada GET REQUEST. Saya tidak tahu bagaimana untuk menulis semula arahan SQL untuk menghapuskan ralat. Ralatnya adalah seperti berikut:

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

Ini adalah skema pangkalan data saya:

Ini kod saya:

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;


    }

Kod jersi:

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

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

Model pelajar:

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

Model tema:

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

Jika anda melihat tiada ruang di antara subject.name 和 FROM Student maka ini adalah SQL yang salah sama sekali kerana penggabungan rentetan. Tambahkan ruang selepas subjek.nama atau sebelum FROM seperti yang ditunjukkan di bawah.

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

Sila beritahu saya jika ini membantu.