JDBC SQL Exception: query executes correctly on the MySQL prompt but gives error in java

3
votes

I am trying to run the following SQL on MySQL database using JDBC. 

String selectQuery = "SELECT IFNULL(MAX(list_id), 0)+1 AS 'max_list_id' FROM UserHospitalLists WHERE user_id=?";

pStatement = connection.prepareStatement(selectQuery);
pStatement.setInt(1, 59);
ResultSet rs = pStatement.executeQuery(selectQuery);
while (rs.next()) {
     maxListId = rs.getString("max_list_id");
}

and getting the following 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:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:525)

The query executes correctly on the MySQL prompt. What can be the problem ?

1
javajdbc

1 Answers

3
votes

This is the problem:

ResultSet rs = pStatement.executeQuery(selectQuery);

You're using the wrong overload of executeQuery - it's the one declared by Statement. You want the one introduced by PreparedStatement. You've already prepared it with the SQL, then set the parameters - you just want:

ResultSet rs = pStatement.executeQuery();