java - java.sql.SQLSyntaxErrorException：“一个错误......靠近'？，？，？，？，？，？）'”

问题描述

我不知道为什么会收到此错误。(?,?,?,?,?,?) 的路径有什么问题？

Exception in thread "main" 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 '?,?,?,?,?,?)' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1355)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2128)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1264)
at com.theanh.dao.StudentMySQLDAO.createStudent(StudentMySQLDAO.java:46)
at com.theanh.dao.StudentMySQLDAO.main(StudentMySQLDAO.java:26)

我在Javatpoint上查看了正确的语法：

String sql="插入 emp 值(?,?,?)";

我的语法是：

String createSQL = "插入学生值(?,?,?,?,?,?)";

基于我的数据库脚本：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
    `id`       int(40)      NOT NULL AUTO_INCREMENT,
    `name`     varchar(255) NOT NULL,
    `birthday` DATE         NOT NULL,
    `sex`      bool         NOT NULL, # zero is considered as false, and non-zero value is considered as true.
    `gpa`      float,
    `rank`     varchar(255),
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = latin1;

现在，我无法在我的语法中发现任何错误。我知道我们只能使用 String 来构建 SQL 查询，但我想使用PreparedStatementclass。与数据库的连接很好

有什么建议吗？

以下是详细信息：这是 mysql jdbc 版本：

 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>

我的班级运行并抛出异常：

public class StudentMySQLDAO implements StudentDAO {
    public static void main(String[] args) throws Exception {
            new StudentMySQLDAO().createStudent(new Student(300, "Steven",
                    LocalDate.of(1995, 10, 20), true, 9.0f, "VERY GOOD"));
        }
    
        public void createStudent(Student student) throws Exception {
            // Codes to do real operations in MySQL database, assuming that student existent has been checked in db
            // Create SQL to add the student to database
            String createSQL = "insert into student values(?,?,?,?,?,?)";
            // Open a connection
            Connection conn = new DBContext().getConnection();
            conn.setAutoCommit(false);
            // Execute a query
            PreparedStatement ps = conn.prepareStatement(createSQL);
            ps.setInt(1, student.getId());
            ps.setString(2, student.getName());
            ps.setString(3, student.getBirthday().format(formatter));
            ps.setInt(4, student.getSex() ? 1 : 0);
            ps.setFloat(5, student.getGpa());
            ps.setString(6, student.getRank());
            ps.executeUpdate(createSQL);
            conn.rollback();
            // Close all
            conn.close();
            ps.close();
        }
}

基本的，没什么花哨的 Student.class：

public class Student {
    private int id;
    private String name;
    private LocalDate birthday;
    private boolean sex; // True = male, False = female
    private float gpa;
    private String rank; // Only has values "Very good", "Good", "Average"

    public Student() {
    }

    public Student(int id, String name, LocalDate birthday, boolean sex, float gpa, String rank) {
        this.id = id;
        this.name = name;
        this.birthday = birthday;
        this.sex = sex;
        this.gpa = gpa;
        this.rank = rank;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public LocalDate getBirthday() {
        return birthday;
    }

    public void setBirthday(LocalDate birthday) {
        this.birthday = birthday;
    }

    public boolean getSex() {
        return sex;
    }

    public void setSex(boolean sex) {
        this.sex = sex;
    }

    public Float getGpa() {
        return gpa;
    }

    public void setGpa(Float gpa) {
        this.gpa = gpa;
    }

    public String getRank() {
        return rank;
    }

    public void setRank(String rank) {
        this.rank = rank;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Student student = (Student) o;
        return id == student.id &&
                sex == student.sex &&
                Float.compare(student.gpa, gpa) == 0 &&
                name.equals(student.name) &&
                birthday.equals(student.birthday) &&
                rank.equals(student.rank);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, birthday, sex, gpa, rank);
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", birthday=" + birthday +
                ", sex=" + sex +
                ", gpa=" + gpa +
                ", rank='" + rank + '\'' +
                '}';
    }
}

我的 MySQL 工作台版本：8.0.16

标签： javamysql

java - java.sql.SQLSyntaxErrorException：“一个错误......靠近'？，？，？，？，？，？）'”

问题描述

解决方案

推荐阅读