João Victor Ferreira João Victor Ferreira - 1 month ago 5
SQL Question

java.sql.SQLException: invalid column index

I've been trying to select data using preparedStatement and passing these arguments but an Exception happens.

Could anyone help me? Here is my code.

Connection connection = DBConnection.getConnection();
String query = "select * from integrantes where nome like '?' or rm like '?'";
ArrayList<Integrante> list = new ArrayList<>();
try {
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, ('%' + term + '%'));
preparedStatement.setString(2, ('%' + term + '%'));
JOptionPane.showMessageDialog(null, ((OraclePreparedStatement) preparedStatement).getOriginalSql());
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
list.add(new Integrante(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getInt(4)));
}
} catch(SQLException e) {
JOptionPane.showMessageDialog(null, "Erro ao listar o(s) integrante(s)." + e);
} catch(NullPointerException e) {
JOptionPane.showMessageDialog(null, "Ocorreu um erro inesperado.");
} catch(Exception e) {
JOptionPane.showMessageDialog(null, "Ocorreu um erro no código.");
}

return list;

Answer

When using a PreparedStatement you must NOT put quotes around placeholders that are going to be substituted with strings. Your statement

"select * from integrantes where nome like '?' or rm like '?'"

causes the question marks to be treated as literals, so the statement has ZERO parameters, thus resulting in the exception. Change it to

"select * from integrantes where nome like ? or rm like ?"
Comments