Problem using parameter in a PreparedStatement

Navigation
0

I would like to know if there is any problem in using the "parameter" variable after the "AND", because when I tested putting the column name directly in SQL worked and when I try to pass the column name with the parameter does not return anything. Is there anything I can do to make this work? OBS: The column name will not be passed in a jTextField, it will be chosen using 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 the following way, as @Sorack and @CarlosHeuberger recommended me: 

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 it gave this error: 

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 be this time?

The values of LIKE's are the same, because I'm using the same TextField to get the values.

    
asked by anonymous 02.08.2018 / 06:03

1 answer

0

You can not pass the column name to a PreparedStatement . One solution to work around your problem is to check which column was selected in 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% with% above, if the first parameter has the value query the column used in the search will be the 1 , if it is nome_cliente will be 2 . You can implement which columns you want by just adding another cidade in the condition.

Or you can use OR as colleague @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
    
02.08.2018 / 13:39
How to install pywin32 as the miniconda3 program? Uploading files in PHP