Generating a dymamic sql SELECT statement

I'm having some small trouble trying to dynamically generate an SQL SELECT statement generated from an entry from a webpage. Basically i have a small search engine in the website, it accepts three parameters (price,city,brand), so i have a JavaBean built in the same way with only 3 attributes, the same ones. When performing a SELECT on the database i could make it this way using PreparedStatement

String sql=select * from product where price<=? and city=? and brand=?;
prep=conn.prepareStatement(sql);
prep.setDouble(1,price);
prep.setString(2,city);
prep.setString(3,brand);

where prep is of course a PreparedStatement obj My problem comes when the user does not insert a parameter in the field of the web page, because apparently he is not interested on a limitation. I need to find a way to "cut" the entry at runtime, like:

city field=""? then take out city from the search criteria:

String sql=select * from product where price<=? and brand=?;

One way i could do that is filling in the code in the servlet with else-if but thats not the only solution i'm sure. Another potential solution would be to use IFNULL(expr1,expr2) function of SQL but in case is null, then i should just remove the null-field from the query.

Do you have any advice?

i hope its clear enough the wa开发者_如何学Pythony i explained it.

---

You can use OR in your WHERE clause to do something like:

WHERE (Price<=? OR ? IS NULL)
AND   (City = ? OR ? IS NULL)
AND   (Brand = ? OR ? IS NULL)

---

Try to replace condition "=" with Like '%[input_value]%'. In this way if uset left blank field you don't have any restriction.

---

You can use like for strings.

String sql = "SELECT * FROM product WHERE price <= ? AND city like '?' AND brand like '?'";
prep = conn.prepareStatement(sql);
prep.setDouble(1, price);
prep.setString(2, city);
prep.setString(3, brand);

See Exact match with sql like and the bind

And in case city/brand is not selected, then set it

prep.setString(2, "%");
prep.setString(3, "%");

If price is not selected, then use some very big number.