Question: I need to get users from my database, by this query :

```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND city = 'Osgiliot'
AND country = 'Gondor'

```

The problem is that in my app, i will often have city or country missing, or both of them missing.

I can make 3 or four HQL queries for that, but that seems like a not very efficient solution. 

Is there a way to have just one query which will accommodate the cases when one or both of these attributes (city and country) is null, and how do i convert that query to HQL ?

My DB is on MySql Server.

PS :

I have tried something like this, but it didn't work :

```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND city like IFNULL('%', <my-city-param>)
AND country like IFNULL('%', <my-country-param>);

```

I'm looking for something like that in HQL format.

Thanks in advance. :)

Answer: 
If you have an entity called `Users` and you not always pass the city and country parameters filled and would like to ignore them, this is the JPQL/HQL and Java codes:

```
StringBuilder jpql = new StringBuilder();

jpql.append("SELECT user FROM Users user ");
jpql.append("WHERE user.first_name = :firstname ");
jpql.append("AND user.last_name = :lastname ");

Map<String, Object> paramsQuery = new HashMap<String, Object>();

if (cityParam == null) {
    paramsQuery.put("city", "%" + cityParam+ "%");
    jpql.append("AND (city like :city) ");
}

if (countryParam == null){
    paramsQuery.put("country", "%" + countryParam+ "%");
    jpql.append("AND (country like :country) ");
}

Query q = em.createQuery(jpql.toString())
q.setParameter("firstname", firstnameParam);
q.setParameter("lastname",lastnameParam);
setQueryParameters(params, q);

```

The helper method to set the optional parameters in the query:

```
public Query setQueryParameters(Map<String, Object> params, Query query) {
    Set<Entry<String, Object>> entrySet = params.entrySet();
    for (Entry<String, Object> entry : entrySet) {
        query.setParameter(entry.getKey(), entry.getValue());
    }

    return query;
}

```
```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND (city is null or @city is null or city like '%' + @city)
AND (country is null or @country is null or country like '%' + @country)

```