How to remove a condition from where clause, if specific value is null (using prepared statements)

Question

Getting parameters from request:

String city = request.getParameter("city");
int price = Integer.parseInt(request.getParameter("price").toString());
int guests ... etc
date init_date... etc
date end_date...etc
initDate = parse init_date with simpledateformater...etc
endDate = parse init_date with simpledateformater...etc

Lets have a prepared statemente like this:

String getResult = "SELECT id_housing, name, description_short, price, 
photo FROM housing WHERE city = ? AND init_date <= ? AND end_date >= ? AND 
price = ? AND guests >= ?";

PreparedStatement stmt = con.prepareStatement(getResult);

stmt.setString(1, city);
stmt.setDate(2, new java.sql.Date(initDate.getTime()));
stmt.setDate(3, new java.sql.Date(endDate.getTime()));
stmt.setInt(4, price);
stmt.setInt(5, guests);
ResultSet rs = stmt.executeQuery();

The question is how can I remove city or price or any value after the WHERE clause, if the value is null. For example, if my user dooesnt write any text on the city input text, the request.getParameter is gonna be null (or an empty string "", i dont know). In that case, I want to remove the city = ? condition from the prepared statement, so the query will return rows with any city value).

I tried with

WHERE city = IFNULL(? , *) AND ...

But doesnt work.

One option is just to construct your `getResult` based on some conditions, so, for example, if `city == null` just don't add it to the query at all. Another, use one of the sql query builder java libs. Take a look at this for example https://stackoverflow.com/questions/15405288/prepared-statement-with-dynamic-where-clause — Sergei Sirik, Nov 14 '18 at 23:26
Possible duplicate of [Prepared statement with dynamic where clause](https://stackoverflow.com/questions/15405288/prepared-statement-with-dynamic-where-clause) — Sergei Sirik, Nov 14 '18 at 23:27

score 0 · Answer 1 · answered Nov 14 '18 at 23:25

Just change the getResult String when the city value is null. Something like this:

if(city == null) {
   getResult = "SELECT id_housing, name, description_short, price, photo FROM housing AND init_date <= ? AND end_date >= ? AND price = ? AND guests >= ?";

Justin · Answer 2 · 2018-11-14T23:38:10.307

0

String getResult = "SELECT id_housing, name, description_short, price, 
    photo FROM housing WHERE init_date <= ? AND end_date >= ? AND 
    price = ? AND guests >= ?";
if(city != null && !city.isempty()){
    getResult += " AND city = ? "
}

PreparedStatement stmt = con.prepareStatement(getResult);
if(city != null && !city.isempty()){
    stmt.setString(1, city);
}

edited Nov 14 '18 at 23:38

answered Nov 14 '18 at 23:28

Justin

1,356
2
9
16

Mick Mnemonic · Answer 3 · 2018-11-14T23:47:29.190

One way to achieve this is to have a query stub/template into which you dynamically append the WHERE condition. You keep the bind variables in a map which maps the variable index into the corresponding value:

String queryStub = "SELECT id_housing, name, description_short, price, " 
    + "photo FROM housing WHERE 1=1";

Map<Integer, Object> bindVariables = new HashMap<>();
StringBuilder sb = new StringBuilder(queryStub);
int paramIndex = 1;

if (city != null && !city.isEmpty()) {
   sb.append(" AND city = ?")
   bindVariables.put(paramIndex++, city);
}

// ... handle the other, possibly empty inputs

try (PreparedStatement stmt = con.prepareStatement(sb.toString());) {

    for (Map.Entry<Integer, Object> e : bindVariables.entrySet()) {
        stmt.setObject(e.getKey(), e.getValue());
    }

    ResultSet rs = stmt.executeQuery();
    // ...
}

Malt · Answer 4 · 2018-11-14T23:39:02.040

-1

Unfortunately. you need a separate query for this case.

This is one reasons it's often not a good idea to use raw JDBC to access a database. With complex queries, you end up with a bunch of error-prone logic that builds pieces and concatenates them. If you don't have many of these cases, then just use two different queries.

But if you keep running into more of these queries, then there are plenty of libraries that can help with everything from opening connections to safely executing queries without having to build different queries using raw strings. Jooq and mybatis come to mind.

edited Nov 14 '18 at 23:39

answered Nov 14 '18 at 23:26

Malt

28,965
9
65
105

he doesn't want to return only rows where city is null if city is not defined. he wants to return rows with any city, so you just have to drop the city from the where clause. – Justin Nov 14 '18 at 23:36

How to remove a condition from where clause, if specific value is null (using prepared statements)

4 Answers4