spring-data-mongodb optional query parameter

Question

I am using spring-data-mongodb.

I want to query database by passing some optional parameter in my query.

I have a domain class.

public class Doc {  
    @Id
    private String id;

    private String type;

    private String name;

    private int index;  

    private String data;

    private String description;

    private String key;

    private String username;
    // getter & setter
}

My controller:

@RequestMapping(value = "/getByCategory", method = RequestMethod.GET, consumes = MediaType.APPLICATION_JSON, produces = MediaType.APPLICATION_JSON)
    public Iterable<Doc> getByCategory(
            @RequestParam(value = "key", required = false) String key,
            @RequestParam(value = "username", required = false) String username,
            @RequestParam(value = "page", required = false, defaultValue = "0") int page,
            @RequestParam(value = "size", required = false, defaultValue = "0") int size,
            @RequestParam(value = "categories") List<String> categories)
            throws EntityNotFoundException {
        Iterable<Doc> nodes = docService.getByCategory(key, username , categories, page, size);
        return nodes;
    }

Here Key and username are optional query parameters.

If I pass any one of them it should return the matching document with given key or username.

My service method is:

public Iterable<Doc> getByCategory(String key, String username, List<String> categories, int page, int size) {

        return repository.findByCategories(key, username, categories, new PageRequest(page, size));
    }

Repository:

@Query("{ $or : [ {'key':?0},{'username':?1},{categories:{$in: ?2}}] }")    
List<Doc> findByCategories(String key, String username,List<String> categories, Pageable pageable);

But by using above query it does not returns a document with either given key or username. What is wrong in my query?

This is how I am making request http://localhost:8080/document/getByCategory?key=key_one&username=ppotdar&categories=category1&categories=category2

Answer 1

Personally, I'd ditch the interface-driven repository pattern at that point, create a DAO that @Autowires a MongoTemplate object, and then query the DB using a Criteria instead. that way, you have clear code that isn't stretching the capabilities of the @Query annotation.

So, something like this (untested, pseudo-code):

@Repository
public class DocDAOImpl implements DocDAO {
    @Autowired private MongoTemplate mongoTemplate;

    public Page<Doc> findByCategories(UserRequest request, Pageable pageable){
        //Go through user request and make a criteria here
        Criteria c = Criteria.where("foo").is(bar).and("x").is(y); 
        Query q = new Query(c);
        Long count = mongoTemplate.count(q);

        // Following can be refactored into another method, given the Query and the Pageable.
        q.with(sort); //Build the sort from the pageable.
        q.limit(limit); //Build this from the pageable too
        List<Doc> results = mongoTemplate.find(q, Doc.class);
        return makePage(results, pageable, count);
    }

    ...
}

I know this flies in the face of the trend towards runtime generation of DB code, but to my mind, it's still the best approach for more challenging DB operations, because it's loads easier to see what's actually going on.

Answer 2

Filtering out parts of the query depending on the input value is not directly supported. Nevertheless it can be done using @Query the $and operator and a bit of SpEL.

interface Repo extends CrudRepository<Doc,...> {

  @Query("""
         { $and : [ 
            ?#{T(com.example.Repo.QueryUtil).ifPresent([0], 'key')}, 
            ?#{T(com.example.Repo.QueryUtil).ifPresent([1], 'username')},
            ... 
         ]}
         """)
  List<Doc> findByKeyAndUsername(@Nullable String key, @Nullable String username, ...)

  class QueryUtil {
    public static Document ifPresent(Object value, String property) {
      if(value == null) {
        return new Document("$expr", true); // always true
      }
      return new Document(property, value); // eq match
    }
  }

  // ...
}

Instead of addressing the target function via the T(...) Type expression writing an EvaluationContextExtension (see: json spel for details) allows to get rid of repeating the type name over and over again.