dynamic parametrized queries using Spring data mongodb

Question

I am looking for a way to dynamically create parametrized queries. In essence, I want to decouple the queries from the application and make them configurable.

For instance, I want to create a query with parameters, such as

{ firstName: ?0 }

As you probably know, this is perfectly feasable in Spring data mongodb using an interface:

interface MyQuery {

  @Query("{firstName: ?0}")
  public Person getByFirstName(final String name);
}

But this couples my query at compile time, while I want to change them at runtime.

---

I can't however find a way to use this mechanism dynamically. The particular class that is used under the hood is StringBasedMongoQuery, and uses heavy reflection utilities in order to determine the query, the parameters, the returntype, etc. You can manage most of it using Groovy I think, however Annotations seem to be a pickle.

This makes me think I chose the wrong way to approach this problem.

---

I myself only see a few options left:

• Reimplement Spring's fancy query parsing for mongo myself (seems a lot of work)
• Implement poor man's query parameter replacement myself. (this will not be flexible, will not yield correct result on null parameters...)

I'm at loss here, it's too bad that I can't seem to use StringBasedMongoQuery standalone, it seems to be coupled to reflection.

---

Update:

My suggestions seem pointless here, as I noticed also Spring data mongodb's StringBasedMongoQuery does not support 'dynamic' queries with nullable parameters; in fact this is quite logical, it is quite hard to cut parts out of your query when a parameter is null, and quite impossible to do it right.

The following sheds some light on the issue: https://jira.spring.io/browse/DATAJPA-209

So it seems my possible options have shifted to:

• We make different queries and the query used depends on the parameters that are available. This creates a possible nightmare for maintanance.
• We expose a groovy or other JVM script that allows us to use one of the available Criteria-esque API's of Spring or the mongodb Java driver, that will then build the query for us. The disadvantage of this is that someone that wants to configure the queries now has to know Java in the very least.
• We make our queries null-safe where needed at the expense of readability.

Answer 1

This method takes a map of parameters with their values and forms a query based on it. Obvious minus for this solution is that formed query is based on ANDs. So, if you need a dynamic query which consists only of logical ANDs this is the way to go. I'm still working on a fully dynamic query with all logical operations. Written in Groovy:

Query buildSearchQuery(Map searchParams, List resultFields, int limit) {
Query query = new Query()
searchParams.each {
    if (it.getValue() != null && it.getValue() != '') {
        query.addCriteria(Criteria.where(it.getKey()).regex('^'+ it.getValue(), 'i'))
        }
    }

    resultFields.each {
        query.fields().include(it)
    }
    query.limit(limit)

    query
}

Answer 2

Another way that you could solve this is to use the Example object... I'm leveraging spring-data-mongodb btw so not sure if that applies for you or not...

public interface PetRepository extends MongoRepository<Pet, String>{
}

...

@Service
public class PetService {

private Logger logger = LogManager.getLogger(PetService.class);

@Autowired
PetRepository petRepository;

public List<Pet> findPetsByExample(String name, String sex, String color, Integer age){
    //Guard clauses omitted...
    Example<Pet> example = Example.of(new Pet(name, sex, color, age));
    return Lists.newArrayList(petRepository.findAll(example));
}

Now what this is doing is leveraging the QueryByExampleExecutorclass that's extended within the MongoRepository to under the hood do a comparison of the supplied example pet with the contents of your documents. This will only look at fields on our example pet that have supplied values and will ignore Nulls in it's comparison. You can also include custom matchers as well to perform further filtering. Here's a link to some example documentation: https://github.com/spring-projects/spring-data-examples/tree/master/mongodb/query-by-example

Answer 3

I found a better and easier solution to this problem than the suggestions I already made myself here in this post. I'll tell you how I will solve this problem for future reference for other.

While mongodb has nothing like query parameters or anything like that, we can make stuff a lot easier by using some other rather unique constructs mongodb offers, together with a strict structure. We can adhere to this structure because it concerns dynamic configurable queries for users.

Our structure is simple, and as follows. We have a basic query structure that looks as follows:

{ $and: []}

We define one basic query that will always be filled in. This can be as simple as the empty JS object. Like this, our query becomes

{ $and: [{}] }

So, this just queries everything. Now we can define a subquery for each condition that can be dynamic. We can choose to add this query to the $and if it meets the conditions. Since we eadhere to put everything in an and construct, we know how the query will look, and we don't need very fancy parameter substitution to get something very flexible to work easily.

A query can become for instance:

{ $and: [{}, {active: true}, {started: false}]}

And we can add those little subqueries for added and started whenever we want.