Data filtering in Dropwizard using DAOs based on optional parameters

Question

I want to implement filtering of data in my Dropwizard server. Let us say we want to search for Monuments. The user could use filters like name, city, category for filtering from the list of monuments.

The uri will look something like /monuments?name="Eiffel"&city"Paris"&category="Engineering Marvel"

I am not clear on how to implement the API for this because some users may stop after just specifying the name filter, while others may use all 3.

In such a case how do I implement the Java code and Queries in my DAO to modify the query based on what filters were passed on?

What I thought of and tried :

1 (worst case) - Have a list of all possible combinations of queries for these filters and use one depending on which combination of arguments was passed on.

2 - I thought the JDBI would automatically eliminate the params that are null when calling the DAO method, but that isn't the case, at least with the ilike clause (since all the above params are strings).

3 - Use Case in Postgres. But this is not scale-able since I might have other params to search with over time as the use cases increase.

4 - Use Java data structures along with DAOs to filter down the results internally like get all the monuments based on (let's say) name first, then pass the Ids to an SQL Query with city clause and then same with category clause. But this will take a long time to process as data increases.

This seems like a fairly common scenario but I can't find any good answers around this, Most talk about the URI and RESTful design instead of the implementation details.

Answer 1

While the answer suggested by @Hitobat would work fine when your optional arguments are few, but if you are dealing with a lot of optional arguments a more elegant solution would be to consider creating a request pojo and use Hibernate Criterias where you add a restriction only if the argument is not null.

Request class:

public class FilteredRequest{
    String name;
    String city;
    String category;
}

Resource Class:

@GET
@Path("/monuments")
public Response searchMonuments(@ApiParam("filters") FilteredRequest filteredRequest){
    return Response.ok().entity(monumentsDAO.findAll(filteredRequest)).build()
}

DAO:

public List<Monuments> class MonumentsDAO() {
    public findAll(FilteredRequest filteredRequest){
        Criteria criteria = getSearchCriteria(filteredRequest);
        return list(criteria);
    }

    private Criteria getSearchCriteria(FilteredRequest filteredRequest){
        Criteria criteria = criteria();
        if (StringUtils.isNotEmpty(filteredRequest.getName())) {
            criteria.add(Restrictions.eq("name", filteredRequest.getName()));
        }
        if (StringUtils.isNotEmpty(filteredRequest.getCity())) {
            criteria.add(Restrictions.eq("city", filteredRequest.getCity()));
        }
        if (StringUtils.isNotEmpty(filteredRequest.getCategory())) {
            criteria.add(Restrictions.eq("category", filteredRequest.getCategory()));
        }
        return criteria;
    }
}