CODEWITHSUNDEEP
javamysqljdbc
Omen
Omen

Reputation: 313

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

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

Upvotes: 0

Views: 2367

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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

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.

Change: 

ResultSet rs = preparedStatement.executeQuery(selectSQL);

To: 

ResultSet rs = preparedStatement.executeQuery();

And it should be working.

Refer:

PreparedStatement.html#executeQuery

Upvotes: 1

Abhi
Abhi

Reputation: 341

Change:

ResultSet rs = preparedStatement.executeQuery(selectSQL);

to

ResultSet rs = preparedStatement.executeQuery();

Upvotes: 0

Related Questions