bryansis2010 bryansis2010 - 1 year ago 82
SQL Question

preparedStatement syntax error

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

where the
method is

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) {
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(
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(
at java.lang.reflect.Constructor.newInstance(
at com.mysql.jdbc.Util.handleNewInstance(
at com.mysql.jdbc.Util.getInstance(
at com.mysql.jdbc.SQLError.createSQLException(
at com.mysql.jdbc.MysqlIO.checkErrorPacket(
at com.mysql.jdbc.MysqlIO.checkErrorPacket(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.ConnectionImpl.execSQL(
at com.mysql.jdbc.ConnectionImpl.execSQL(
at com.mysql.jdbc.StatementImpl.executeQuery(
at util.P_DBCon.select1(
at app.RandomNumberGenerator.main(

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

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

Any help is much appreciated.


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 < ?" ;

and still the error message comes out.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download