current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 3 down vote favorite

I'm currently messing around with a Spring Boot REST API project for instructional purposes. I have a rather large table with 22 columns loaded into a MySQL database and am trying to give the user the ability to filter the results by multiple columns (let's say 6 for the purposes of this example).

I am currently extending a Repository and have initialized methods such as findByParam1 and findByParam2 and findByParam1OrderByParam2Desc and etc. and have verified that they are working as intended. My question to you guys is the best way to approach allowing the user the ability to leverage all 6 optional RequestParams without writing a ridiculous amount of conditionals/repository method variants. For example, I want to give the user the ability to hit url home/get-data/ to get all results, home/get-data?param1=xx to filter based on param1, and potentially, home/get-data?param1=xx&param2=yy...&param6=zz to filter on all the optional parameters.

For reference, here is what the relevant chunk of my controller looks like (roughly). 

@RequestMapping(value = "/get-data", method = RequestMethod.GET)
public List<SomeEntity> getData(@RequestParam Map<String, String> params) {
    String p1 = params.get("param1");
    if(p1 != null) {
        return this.someRepository.findByParam1(p1);
    }
    return this.someRepository.findAll();
}

My issue so far is that the way I am proceeding about this means that I will basically need n! amount of methods in my repository to support this functionality with n equalling the amount of fields/columns I want to filter on. Is there a better way to approach handling this, perhaps where I am filtering the repository 'in-place' so I can simply filter 'in-place' as I check the Map to see what filters the user did indeed populate?

EDIT: So I'm currently implementing a 'hacky' solution that might be related to J. West's comment below. I assume that the user will be specifying all n parameters in the request URL and if they do not (for example, they specify p1-p4 but not p5 and p6) I generate SQL that just matches the statement to LIKE '%' for the non-included params. It would look something like...

@Query("select u from User u where u.p1 = :p1 and u.p2 = :p2 ... and u.p6 = :p6") 
List<User> findWithComplicatedQueryAndSuch;

and in the Controller, I would detect if p5 and p6 were null in the Map and if so, simply change them to the String '%'. I'm sure there is a more precise and intuitive way to do this, although I haven't been able to find anything of the sort yet.

share|improve this question
    
Hmm, this is a tough one. I'm sure Spring supports something like this, I just don't know exactly what it is; I'm looking at the documentation because I'm curious now. As a potentially 'hacky" solution, you could just return the whole object, and then loop through and null the fields you didn't want included. Since you're mapping an object, even if you do this the right way, the fields you don't return from the query are going to be null. – J. West 19 hours ago
    
I'm not 100% sure what you mean by null the fields I don't want included, aka, how would that help me with not having to write out n factorial amount of repository functions like findByP1 and findByP1andP2 and findByP1andP3 and so on? This might be similar to the hacky method I am implementing right now (updating the answer) where I just assume the user is always filtering on 6 parameters and if they aren't, generate the SQL equivalent of LIKE '%' to basically not filter at all...is that what you mean by null the fields you don't want included? – fapple 19 hours ago
    
I think I misunderstood what you're asking. Take a look at this link on creating custom JpaSpecificationExecutor, it looks like it may be on the right track. cubrid.org/wiki_ngrinder/entry/… – J. West 19 hours ago
up vote 2 down vote

You can do this easily with a JpaSpecificationExecutor and a custom Specification: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

I would replace the HashMap with a DTO containing all optional get params, then build the specifications based on that DTO, obviously you can also keep the HashMap and build the specification based on it.

Basically:

public class VehicleFilter implements Specification<Vehicle>
{
    private String art;
    private String userId;
    private String vehicle;
    private String identifier;

    @Override
    public Predicate toPredicate(Root<Vehicle> root, CriteriaQuery<?> query, CriteriaBuilder cb)
    {
        ArrayList<Predicate> predicates = new ArrayList<>();

        if (StringUtils.isNotBlank(art))
        {
            predicates.add(cb.equal(root.get("art"), art));
        }
        if (StringUtils.isNotBlank(userId))
        {
            predicates.add(cb.equal(root.get("userId"), userId));
        }
        if (StringUtils.isNotBlank(vehicle))
        {
            predicates.add(cb.equal(root.get("vehicle"), vehicle));
        }
        if (StringUtils.isNotBlank(identifier))
        {
            predicates.add(cb.equal(root.get("identifier"), fab));
        }

        return predicates.size() <= 0 ? null : cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

// getter & setter
}

And the controller:

@RequestMapping(value = "/{ticket}/count", method = RequestMethod.GET)
public long getItemsCount(
    @PathVariable String ticket,
    VehicleFilter filter,
    HttpServletRequest request
) throws Exception
{
    return vehicleService.getCount(filter);
}

Service:

@Override
public long getCount(VehicleFilter filter)
{
    return vehicleRepository.count(filter);
}

Repository:

@Repository
public interface VehicleRepository extends JpaRepository<Vehicle, Integer>, JpaSpecificationExecutor<Vehicle>
{
}

Just a quick example adapted from company code, you get the idea!

share|improve this answer
    
Ah it looks like I was on the right track. Very interesting, I've never had a use case for something like this but I'm glad that I now know how to do it if I have to. – J. West 19 hours ago
    
It's a very easy and clean solution for supporting large query modifications, with basically zero overhead since you can use the "enhanced" DTO (not really a DTO anymore) right from the controller and pass it down to the repository. – dav1d 19 hours ago
up vote 0 down vote

Another solution with less coding would be to use QueryDsl integration with Spring MVC.

By using this approach all your request parameters will be automatically resolved to one of your domain properties and appended to your query.

For reference check the documentation https://spring.io/blog/2015/09/04/what-s-new-in-spring-data-release-gosling#querydsl-web-support and the example project https://github.com/spring-projects/spring-data-examples/tree/master/web/querydsl

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.