java - 线程 "main"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : You have an error in your SQL syntax 中出现异常

Question

我尝试通过 java 执行上述查询，但在第 6 行出现以下错误:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '? AND lexicon_lemma = ?)' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)

我找不到语法问题是什么...有什么建议吗？这是我的查询:

public static void InsertEntry(String lexiconType, String lemma, String lang) throws SQLException {
    String selectSQL = "SELECT id FROM lexicons WHERE (lexicon_type = ? AND lexicon_lemma = ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
    preparedStatement.setString(1, lexiconType);
    preparedStatement.setString(2, lemma);
    ResultSet rs = preparedStatement.executeQuery(selectSQL);
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
        System.out.println(id);
    } else {
        String insertSQL = "INSERT INTO lexicons "
    + "(lexicon_type,lexicon_lemma,lexicon_lang) VALUES"
    + "(?,?,?)";
        PreparedStatement prepStatement = connection.prepareStatement(insertSQL);
        prepStatement.setString(1, lexiconType);
        prepStatement.setString(2, lemma);
        prepStatement.setString(3, lang);
        prepStatement .executeUpdate();
        prepStatement.close();
    }
}

Answer 1

没有必要将 SQL 语句变量传递给 PreparedStatement 的 executeQuery。这是您案例中的主要问题。

PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
preparedStatement.setString(1, lexiconType);
preparedStatement.setString(2, lemma);
// error is in the following statement
ResultSet rs = preparedStatement.executeQuery(selectSQL);

当您重置要执行的sql时，该语句不会被识别为准备好的语句，而是父Statement的实例，该实例正在寻找引号之间的有效输入对于不存在的刺痛等。因此是语法错误。

更改:

ResultSet rs = preparedStatement.executeQuery(selectSQL);

致:

ResultSet rs = preparedStatement.executeQuery();

它应该可以工作。

引用:

PreparedStatement.html#executeQuery