Java JDBC error statement [closed]

Question

Java JDBC error statement [closed]

Navigation

#1 by (3 votes)
#2 by (3 votes)

3

I paste the code and consult from it, since I pass the field of the database from a combo and the value, the field does not take it, the value does. What happens?

public ObservableList<Categoria> buscarCategoriaPorSeleccion(
    String campo, String valor
) throws SQLException {

    // Declaro los elemntos
    PreparedStatement st = null;
    ResultSet rs = null;
    ObservableList<Categoria> listaCategoria = FXCollections.observableArrayList();
    Categoria categoria = null;

    // Conecto con la base de datos
    conectar();
    try {
        st = this.conexion.prepareStatement("SELECT * FROM categoria WHERE ? LIKE ?");

        // Esto no funciona, si meto la variable en la query si lo hace, 
        st.setString(1, campo);

        // Esto sin problema
        st.setString(2, "%" + valor + "%");

        rs = st.executeQuery();

        while (rs.next()) {
            categoria = new Categoria();
            categoria.setIdCategoria(rs.getInt("categoria_id"));
            categoria.setNombreCategoria(rs.getString("categoria_nombre"));
            categoria.setDescripcionCategoria(rs.getString("categoria_descripcion"));
            listaCategoria.add(categoria);
        }

    } catch (SQLException e) {
        mensaje.error("Error SQL " + e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                mensaje.error("Error SQL " + e);
            }
        }

        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                mensaje.error("Error SQL " + e);
            }
        }
    }
    // Desconecto de la base de datos
    desconectar();

    // Devuelvo la lista
    return listaCategoria;
}

That is, it would only work like this: SELECT * FROM categoria WHERE "+ campo +" LIKE ? .

java jdbc

asked by Maurikius 22.03.2017 в 19:59

source

2 answers

3

In the logic of prepared statement the ? are replaced by the varores safely (escaped and all), and therefore the replacement does not work for field names.

I recommend you put together your query like this:

if (validar(campo)){
    String query = String.format("SELECT * FROM categoria WHERE %s LIKE ?",campo);
        conectar();
    try {
        st = this.conexion.prepareStatement(query);
        st.setString(1, "%" + valor + "%"); // Esto sin problema 
        rs = st.executeQuery();
        // ...
}

// deberías validar los nombres del campo permitidos, por ejemplo:
private boolean validar(String campo){
    return campo.matches("^categoria_(nombre|descripcion)$");
}

answered by 22.03.2017 в 20:48

javascript string.indexOf (array) does not work with long strings Doubts about the C ++ standards [closed]

score 3 · Accepted Answer

3

You can use PreparedStatement to assign values, but not column names. Therefore you will first have to build the SQL query yourself (as you do in the code you have shown) and then use PreparedStatement to assign the values .

answered by 22.03.2017 / 20:14

source