Why Does `statement.executeQuery(searchPerson);` Cause a SQL Syntax Error in Java Prepared Statements?

Java Prepared Statements: Troubleshooting SQL Syntax Errors with '?' Placeholders

Using Java's prepared statements often leads to "SQL syntax error" messages, frequently stemming from improper placeholder (?) usage. Let's examine a common scenario:

The following Java code snippet demonstrates an incorrect approach:

String searchPerson = "select * from persons where surname like ? and name like ?";
statement.executeQuery(searchPerson); 

The searchPerson string correctly defines the SQL query with placeholders for surname and name. However, passing searchPerson directly to statement.executeQuery() is the source of the error. executeQuery() expects a prepared statement already set up with its parameters; it doesn't accept the SQL string as a parameter.

The Correct Implementation:

The solution lies in correctly binding the parameters before executing the query. The executeQuery() method should be called without any arguments after parameter binding. The corrected code would look like this (assuming surnameValue and nameValue hold the actual values):

String searchPerson = "select * from persons where surname like ? and name like ?";
PreparedStatement statement = connection.prepareStatement(searchPerson);
statement.setString(1, surnameValue);
statement.setString(2, nameValue);
ResultSet rs = statement.executeQuery(); // Execute without additional parameters
// Process the ResultSet (rs) here

This revised code first creates a PreparedStatement object, then sets the parameter values using setString(), and finally executes the query using executeQuery() without passing the SQL string again. This ensures the database correctly interprets the placeholders and avoids the syntax error.

The above is the detailed content of Why Does `statement.executeQuery(searchPerson);` Cause a SQL Syntax Error in Java Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!