Failed query to database

Question

Failed query to database

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm trying to make a query to a car database and it does not work for me, the function where I do the query is this:

public static ArrayList<Coche> filtradoAnuncios(String valor_marca, String valor_modelo, String valor_anyoDesde, String valor_anyoHasta, String valor_puertas, String valor_cambio, String valor_precioDesde, String valor_precioHasta, String valor_kilometros, String valor_potencia, String valor_combustible) {

    //Marca
    if(valor_marca.equals("Cualquiera")) {
        valor_marca = "";
    }

    //Modelo
    if(valor_modelo.equals("Cualquiera")) {
        valor_modelo = "";
    }

    //Puertas
    if(valor_puertas.equals("Cualquiera")) {
        valor_puertas = "";
    }

    //Cambio
    if(valor_cambio.equals("Cualquiera")) {
        valor_cambio = "";
    }

    //Combustible
    if(valor_combustible.equals("Cualquiera")) {
        valor_combustible = "";
    }

    String sql = "SELECT * FROM anuncio_coches WHERE marca LIKE ? AND modelo LIKE ? AND anyo between ? and ? AND precio between ? and ? AND kilometros between ? AND potencia between ? AND puertas LIKE ? AND combustible LIKE ? AND cambio LIKE ?";
    ArrayList<Coche> listaBusqueda = new ArrayList<>();

    String sql1 = "SELECT * FROM anuncio_coches WHERE marca LIKE '%" + valor_marca + "%' AND modelo LIKE '%" + valor_modelo + "%' AND anyo between " + valor_anyoDesde + " and " + valor_anyoHasta + " AND precio between  " + valor_precioDesde + " and " + valor_precioHasta + " AND kilometros between " + valor_kilometros + " AND potencia between " + valor_potencia + " AND puertas LIKE '%"+ valor_puertas+"%' AND combustible LIKE '%" + valor_combustible + "%' AND cambio LIKE '%" + valor_cambio + "%'";

    try(PreparedStatement ps = con.prepareStatement(sql);){ 
        ps.setString(1, "'%" + valor_marca + "%'");
        ps.setString(2, "'%" + valor_modelo + "%'");
        ps.setString(3, valor_anyoDesde);
        ps.setString(4, valor_anyoHasta);
        ps.setString(5, valor_precioDesde);
        ps.setString(6, valor_precioHasta);
        ps.setString(7, valor_kilometros);
        ps.setString(8, valor_potencia);
        ps.setString(9, "'%" + valor_puertas + "%'");
        ps.setString(10, "'%" + valor_combustible + "%'");
        ps.setString(11, "'%" + valor_cambio + "%'");
        ps.execute();
        ResultSet rs = ps.getResultSet();
        while(rs.next()) {
            String marca = rs.getString("marca");
            String modelo = rs.getString("modelo");
            int anyo = rs.getInt("anyo");
            int precio = rs.getInt("precio");
            int kilometros = rs.getInt("kilometros");
            int potencia = rs.getInt("potencia");
            int puertas = rs.getInt("puertas");
            String combustible = rs.getString("combustible");
            Double consumo = rs.getDouble("consumo");
            String cambio = rs.getString("cambio");
            String color = rs.getString("color");
            listaBusqueda.add(new Coche(marca, modelo, precio, anyo, kilometros, potencia, puertas, combustible, consumo, cambio, color));
        }
    }
    catch(SQLException sqle) {
        sqle.printStackTrace();
        System.out.println("no funciona");
    }
    return listaBusqueda;
}

I do not know why it does not work, I've made an easy query, and the String sql at the end, when I collect the parameters it looks like this:

SELECT * FROM car_cars WHERE brand LIKE '%%' AND model LIKE '%%' AND anyo between 1980 and 2018 AND price between 0 and 1000000 AND km between 0 and 1000000 AND power between 0 and 10000 AND doors LIKE '% % 'AND fuel LIKE' %% 'AND change LIKE' %% '

I put this query in MySQLWorkbench and it works for me. I should leave the records of all the cars that I have in the database but it does not, I hope you help me, thank you very much!

sql java jdbc

asked by Csc99 20.06.2018 в 15:34

source

2 answers

Update statment mysql php row in database Play complete audio file with python-vlc

score 0 · Answer 1

The problem is the single quotes, which you do not have to add before the % (when you use ps.setString(...) it is already assumed that it is a text and that when the query is generated you will have to use them)

try(PreparedStatement ps = con.prepareStatement(sql);){ 
        ps.setString(1, "%" + valor_marca + "%");
        ps.setString(2, "%" + valor_modelo + "%");
        ps.setString(3, valor_anyoDesde);
        ps.setString(4, valor_anyoHasta);
        ps.setString(5, valor_precioDesde);
        ps.setString(6, valor_precioHasta);
        ps.setString(7, valor_kilometros);
        ps.setString(8, valor_potencia);
        ps.setString(9, "%" + valor_puertas + "%");
        ps.setString(10, "%" + valor_combustible + "%");
        ps.setString(11, "%" + valor_cambio + "%");

...
}

score 0 · Answer 2

In the String sql you indicate that you expect to receive 11 parameters and the ps object of the type PreparedStatement actually send you 11 parameters, but let's analyze the object sql:

SELECT * 
FROM anuncio_coches 
WHERE marca LIKE ? 
    AND modelo LIKE ? 
    AND anyo between ? and ? 
    AND precio between ? and ? 
    AND kilometros between ? 
    AND potencia between ? 
    AND puertas LIKE ? 
    AND combustible LIKE ? 
    AND cambio LIKE ?;

As a conclusion you have, that the objects sql and sql1 have the query wrongly created, curiously then you indicate how the query is and that is fine, here my recommendation is to have the code and request help if required.