0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
LeoNeo
  • 739
  • 1
  • 9
  • 28
  • Declare your method with all the parameters using the @QueryParam annotation. The ones the user does not provide will be null or empty-string. Write your SQL to ignore null values. – Hitobat Nov 25 '18 at 10:45
  • How do I write an SQL query to ignore null values? – LeoNeo Nov 27 '18 at 15:19
  • I looked around and I see cases where if the argument is null or empty pass in a default value or the same value that already exists in the table but none that says that if there is a query like `Select * from users where name = :name` and if the passed in arg is null then just do `Select * from users. – LeoNeo Nov 27 '18 at 15:30
  • You can do something like `SELECT * FROM users WHERE (:name IS NULL OR name=:name)`. – Hitobat Nov 30 '18 at 13:19

1 Answers1

0

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;
    }
}
Rishab178
  • 163
  • 2
  • 9