java - MySQLSyntaxErrorException near "?" when trying to execute PreparedStatement

Question

I'm trying to execute a query using a PreparedStatement in Java.

I am getting error number 1064 when I try to execute my query (syntax error).

I have tested this in MySQL query browser with substituted values which works fine.

What's wrong with my code?

Here's the relevant code:

String query = "select MemberID, MemberName from members where MemberID = ? or MemberName = ?";
Connection conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, 2);
s.setString(2, "zen");
ResultSet rs = s.executeQuery(query);

Here's the exception I'm getting:

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 '? or MemberName = ?' at line 1

Answer 1

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 '? or MemberName = ?' at line 1

MySQL doesn't understand the meaning of ? in the SQL query. It's indeed invalid SQL syntax. So somehow it's not been replaced by PreparedStatement. And guess what?

PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);        
rs = s.executeQuery(query); // Fail!

You're overridding the prepared query with the original query! You need to call the argumentless PreparedStatement#executeQuery() method instead of Statement#executeQuery(String).

PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);        
rs = s.executeQuery(); // OK!

---

Unrelated to the problem, your code is leaking resources. The DB will run out of them after several hours and your application will crash. To fix this, you need to follow the JDBC idiom of closing Connection, Statement and ResultSet in the finally block of the try block where they're been acquired. Check the JDBC basic tutorial for more detail.