线程"main"中的异常com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:SQL语法有错误 [英] Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

问题描述

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

I am trying to execute the above query through java but I get the following error in line 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)

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

I can't find what is the problem with the syntax... Any suggestion? This is my query:

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

推荐答案

对于PreparedStatement，没有必要将sql语句变量传递给executeQuery.这是您所遇到的主要问题.

There is no necessity to pass sql statement variable to executeQuery for PreparedStatement. That is the main issue in your case.

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的实例，该实例在字符串等的引号之间寻找有效的输入.不存在.因此就是语法错误.

As you are resetting the sql to be executed, the statement is not recognized as prepared statement but an instance of parent Statement which is looking for valid inputs in between quotes for stings etc. which are not present. And hence is the Syntax error.

更改:

ResultSet rs = preparedStatement.executeQuery(selectSQL);

收件人:

ResultSet rs = preparedStatement.executeQuery();

它应该可以正常工作.

引用:

PreparedStatement.html#executeQuery

这篇关于线程"main"中的异常com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:SQL语法有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！