Problem using parameter in Preparedstatement

Asked

Viewed 522 times

0

I wonder if there is any problem in using the variable "parameter" after "AND", because when I tested putting the column name directly in SQL it worked and when I try to pass the column name with the parameter does not return anything. There’s nothing I can do to make this work? OBS: The column name will not be passed in a jTextField, it will be chosen through jRadioButtons. 

public List<Evento> buscar(String parametro,String pesq){
    PreparedStatement stmt  = null;
    ResultSet rs = null;
    List<Evento> busca = new ArrayList<>();

    try {
        stmt = con.prepareStatement("SELECT id_evento,nome_cliente,numero,cidade,dia,horario,equipamento,pagamento " +
                                    "FROM cliente c INNER JOIN evento e ON (e.fk_cliente = c.id_cliente) AND ? LIKE ?");
        stmt.setString(1, parametro);
        stmt.setString(2, "%"+pesq+"%");

        rs = stmt.executeQuery();

        while(rs.next()){

            Evento evento = new Evento();

            evento.setId_evento(rs.getInt("id_evento"));
            evento.setNome(rs.getNString("nome_cliente"));
            evento.setNumero(rs.getNString("numero"));
            evento.setCidade(rs.getNString("cidade"));
            evento.setData(rs.getNString("dia"));
            evento.setHorario(rs.getNString("horario"));
            evento.setEquipamento(rs.getNString("equipamento"));
            evento.setPagamento(rs.getNString("pagamento"));

            busca.add(evento);

        }

    } catch (SQLException ex) {
        Logger.getLogger(EventoDAO.class.getName()).log(Level.SEVERE, null, ex);
    }finally{
        ConexaoBD.closeConnection(con, (com.mysql.jdbc.PreparedStatement) stmt, rs);
    }
    return busca;
}

I tried as follows, as @Sorack and @Carlosheuberger recommended:

public List<Evento> buscar(int parametro,String nome,String numero){
    PreparedStatement stmt  = null;
    ResultSet rs = null;
    List<Evento> busca = new ArrayList<>();

    try {
        stmt = con.prepareStatement("SELECT id_evento,\n" +
                                    "      nome_cliente,\n" +
                                    "      numero,\n" +
                                    "      cidade,\n" +
                                    "      dia,\n" +
                                    "      horario,\n" +
                                    "      equipamento,\n" +
                                    "      pagamento\n" +
                                    "FROM cliente c INNER JOIN evento e ON e.fk_cliente = c.id_cliente\n" +
                                    "WHERE CASE ?\n" +
                                    "      1 THEN nome_cliente LIKE ?\n" +
                                    "      2 THEN cidade LIKE ?\n" +
                                    "      END");
        stmt.setInt(1, parametro);
        stmt.setString(2, "%"+nome+"%");
        stmt.setString(3, "%"+numero+"%");

        rs = stmt.executeQuery();

        while(rs.next()){

            Evento evento = new Evento();

            evento.setId_evento(rs.getInt("id_evento"));
            evento.setNome(rs.getNString("nome_cliente"));
            evento.setNumero(rs.getNString("numero"));
            evento.setCidade(rs.getNString("cidade"));
            evento.setData(rs.getNString("dia"));
            evento.setHorario(rs.getNString("horario"));
            evento.setEquipamento(rs.getNString("equipamento"));
            evento.setPagamento(rs.getNString("pagamento"));

            busca.add(evento);

        }

    } catch (SQLException ex) {
        Logger.getLogger(EventoDAO.class.getName()).log(Level.SEVERE, null, ex);
    }finally{
        ConexaoBD.closeConnection(con, (com.mysql.jdbc.PreparedStatement) stmt, rs);
    }
    return busca;
}

But you made that mistake:

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 '1 THEN nome_cliente LIKE '%João%' 2 THEN cidade LIKE '%João%'

What can it be this time?

NOTE: The values of LIKE’s are the same, because I’m using the same Textfield to get the values.

java mysql sql database prepared-statement

  • Sorry, @Carlosheuberger, I realized now that I put "table name", but actually it is the name of the column I want to pass as parameter. I’ll make the correction.

  • The column name also cannot be sent as parameter. To do what you want you need to mount the queryat runtime

  • Thank you, @Sorack, but can you explain to me better what you said? I’m sorry, I didn’t get it right.

  • Thanks for the tips, @Carlosheuberger!

  • @Jov1c the problem occurs because passing the variable by parameter it transforms into srtring woe to you query would look something like this: WHERE 'campo' = 'valor'.

  • However, those quotation marks around the field could not exist.

  • @Carlosheuberger I think the suggestion of CASE-WHEN is better than riding the query at runtime. Passes the column seems to me a half bad architectural decision

Show 2 more comments

1 answer

0


You cannot pass the column name to a PreparedStatement. One solution to work around your problem is to check which column was selected on WHERE of query:

SELECT id_evento,
      nome_cliente,
      numero,
      cidade,
      dia,
      horario,
      equipamento,
      pagamento
  FROM cliente c INNER JOIN evento e ON e.fk_cliente = c.id_cliente
 WHERE (? = 1 AND nome_cliente LIKE ?)
    OR (? = 2 AND cidade LIKE ?)

In the query above, if the first parameter has the value 1 the column used in the search will be the nome_cliente, if it is 2 will be the cidade. You can implement which columns you want just by adding one more OR in condition.

Or you can use a CASE as @Carlosheuberger quoted:

SELECT id_evento,
      nome_cliente,
      numero,
      cidade,
      dia,
      horario,
      equipamento,
      pagamento
  FROM cliente c INNER JOIN evento e ON e.fk_cliente = c.id_cliente
WHERE CASE ?
        1 THEN nome_cliente LIKE ?
        2 THEN cidade LIKE ?
      END

Browser other questions tagged java mysql sql database prepared-statement

You are not signed in. Login or sign up in order to post.