Why Does My Java Code Throw a 'Parameter Index Out of Range' SQL Exception?

Parameter Index Out of Range in SQL Exception: Causes and Solution

Encountering the exception "java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)" during database insertion can be frustrating. This error occurs when you try to set a parameter value in a prepared statement while the SQL query lacks the corresponding placeholders (?) for those parameters.

Cause of the Error:

This error arises when you attempt to use the setXxx() method on PreparedStatement, but the SQL query string does not have the necessary placeholders for the specified parameter index.

Example of Incorrect Code:

String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (val1, val2, val3)";

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1); // This will fail.
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);

Solution:

To resolve this issue, you need to modify the SQL query string to include placeholders (?) for all the parameters you intend to set.

Example of Corrected Code:

String sql = "INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?)";

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, val1);
preparedStatement.setString(2, val2);
preparedStatement.setString(3, val3);

Note:

• Parameter indices start from 1.
• You should avoid using single or double quotes around the placeholders. Doing so will cause the SQL parser to interpret them as string values, leading to the same exception.

Additional Information:

• [JDBC Tutorial - Prepared Statements](https://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/index.html#prepared)

The above is the detailed content of Why Does My Java Code Throw a 'Parameter Index Out of Range' SQL Exception?. For more information, please follow other related articles on the PHP Chinese website!