Why Does `s.executeQuery(query)` Cause a MySQLSyntaxErrorException 'near '?''?

MySQLSyntaxErrorException near "?": Resolving Syntax Errors with PreparedStatements

Executing PreparedStatements in Java can occasionally encounter syntax errors, one of which is the dreaded "near '?'" exception. To understand its cause and resolution, let's delve into the issue.

Problem Description

Attempts to execute a PreparedStatement result in a MySQLSyntaxErrorException with error code 1064, indicating a syntax error. Substitution of values in the query using the MySQL query browser succeeds, suggesting that the issue lies within the code.

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

Exception

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

Solution

The root cause of this error is that MySQL encounters an unprocessed "?" in the SQL query. To resolve it, follow these steps:

1. Overridden Prepared Query: Note that the code is overriding the intended behavior of PreparedStatements. The line ResultSet rs = s.executeQuery(query); is incorrect.
2. Correct Execution: Instead, use the argumentless PreparedStatement#executeQuery() method to execute the prepared query correctly.

ResultSet rs = s.executeQuery();

Additional Considerations

• Resource Leakage: The code is not closing the connection, statement, and result set appropriately. Implement a try-with-resources block to avoid resource leaks.

The above is the detailed content of Why Does `s.executeQuery(query)` Cause a MySQLSyntaxErrorException 'near '?''?. For more information, please follow other related articles on the PHP Chinese website!