Why Does My PreparedStatement in Java Throw a MySQL Syntax Error Near '?'?

MySQL Syntax Error with PreparedStatement Question

When attempting to execute a query with PreparedStatement in Java, an error occurs indicating a syntax error near "?". The exception is:

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

Code Snippet

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); // Fail!

Problem Resolution

The error occurs because MySQL does not recognize the "?" in the SQL query as a placeholder. This is due to the following call:

rs = s.executeQuery(query); // Fail!

Instead of using PreparedStatement#executeQuery with no arguments, the original query string is passed, which overrides the prepared statement.

To resolve the issue, the executeQuery method of PreparedStatement should be called without arguments:

rs = s.executeQuery(); // OK!

Resource Leakage

Although unrelated to the original problem, the code leaks resources. To prevent this, resources should be closed using the JDBC idiom:

try {
    // Acquire resources here
} catch (Exception e) {
    // Handle exception
} finally {
    // Close resources here
}

The above is the detailed content of Why Does My PreparedStatement in Java Throw a MySQL Syntax Error Near '?'?. For more information, please follow other related articles on the PHP Chinese website!