Odtnhj

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.

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 >= ?";

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);

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();
 // ...
 

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.