java.sql.SQLException: invalid column index

Question

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;

select * is a poor way to structure a SQL statement rather than listing the columns. I would guess that your query does not return 4 columns but it's impossible to know. — Justin Cave
– Justin Cave, Commented Oct 16, 2016 at 6:07
select id_integrante, nome, rm id_grupo from integrantes; it works as well when I execute in Oracle SQL Developer — João Victor Ferreira
– João Victor Ferreira, Commented Oct 16, 2016 at 6:12
That is 3 columns not 4 (id_grupon is an alias for the rm column). — Justin Cave
– Justin Cave, Commented Oct 16, 2016 at 6:14

Jim Garrison · Accepted Answer · 2016-10-16 06:12:28Z

4

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 ?"

answered Oct 16, 2016 at 6:12

Jim Garrison

87k20 gold badges161 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

java.sql.SQLException: invalid column index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related