preparedStatement语法错误 [英] preparedStatement syntax error

问题描述

我最近遇到了Java PreparedStatements的这个问题。我有以下代码：

I recently encountered this problem with Java PreparedStatements. I have the following code:

String selectSql1
        = "SELECT `value` FROM `sampling_numbers` WHERE `value` < (?)" ;
    ResultSet rs1 = con.select1(selectSql1,randNum);

其中 select1 方法

    public ResultSet select1(String sql, int randNum) {
    try {
        this.stmt = con.prepareStatement(sql);
        stmt.setInt(1, randNum);
        return this.stmt.executeQuery(sql);
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

但是，它不断抛出此错误：

However, it keeps throwing this error:

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 '?)' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2777)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1651)
    at util.P_DBCon.select1(P_DBCon.java:59)
    at app.RandomNumberGenerator.main(RandomNumberGenerator.java:60)

    Exception in thread "main" java.lang.NullPointerException
    at app.RandomNumberGenerator.main(RandomNumberGenerator.java:64)

当我以天真的方式做... value< + randNum 但我想这样做。

This problem does not happen when I do the naive way of doing "...value < " + randNum but I would like to do so this way.

非常感谢任何帮助。

我尝试了社区的各种推荐，例如

I tried with the various recommendations by the community, like

String selectSql1
        = "SELECT `value` FROM `sampling_numbers` WHERE value < ?" ;

String selectSql1
        = "SELECT value FROM sampling_numbers WHERE value < ?" ;

仍然出现错误信息。

推荐答案

问题的解决方案实际上非常简单，你正在调用 Statement.executeQuery（String）当你想要调用 PreparedStatement.executeQuery（） -

The solution to your problem is actually very easy, you are calling Statement.executeQuery(String) when you want to call PreparedStatement.executeQuery() -

this.stmt = con.prepareStatement(sql); // Prepares the Statement.
stmt.setInt(1, randNum);               // Binds the parameter.
// return this.stmt.executeQuery(sql); // calls Statement#executeQuery
return this.stmt.executeQuery();       // calls your set-up PreparedStatement

这篇关于preparedStatement语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！