Mongodb between query in java

Question

i want to read all records that are between col1(value) and col2(value) in mongodb. so i write this code but it not work correctly. How implement this query?

my entity is : in my entity i suppose date as long because day or month no important for me.

private long id;
private String name;
private String description;
private long born_date ; // col1
private long death_date; // col2

all records between 200 and 500

      col1(val = 200)       col2(val = 500)
------|---------------------|------------

my code:

 Query betweenQuery = new Query();
    betweenQuery.addCriteria(Criteria.where("col1").gte(vla1)).
    addCriteria(Criteria.where("col2").lte(val2));
    return MongoOperations.find(betweenQuery , Entity.class);

What does it do? What do you expect it to do? What do you expect if col1 meets the criteria but col2 doesn't? — John3136, Mar 11 '15 at 05:23
That doesn't make a lot of sense. Since there are two columns you probably want both range conditions on both columns. Otherwise col will return values more than 500 and col2 will return values less than 200, but otherwise expect the conditions you wanted. — Neil Lunn, Mar 11 '15 at 05:24
@ John3136, i expect read all records in range of (200,500). — Emil, Mar 11 '15 at 05:28
I think if you show all of the fields in one of the records, someone might be able to help write the correct query. — faljbour, Mar 11 '15 at 07:09
you should be using "born_date" and "death_date" instead of "col1" and "col2". let me know if I am wrong. I use Mongodb with Spring and never seen this "col1" "col2" notation used — faljbour, Mar 11 '15 at 07:44

score 1 · Accepted Answer · answered Mar 11 '15 at 07:27

As stated earlier, it does not make sense to split such a condition over two fields. Likely what you really want is that same condition on "both" fields:

    Query betweenQuery = new Query();
    betweenQuery.addCriteria(Criteria.where("col1").gte(200).lte(500)).
            addCriteria(Criteria.where("col2").gte(200).lte(500));

Serializes like this:

{ "col1" : { "$gte" : 200 , "$lte" : 500 } , "col2" : { "$gte" : 200 , "$lte" : 500 } }

So basically each column must contain values that are between those selected.

Also note that "BETWEEN" here is considered "inclusive", otherwise alter to $gt and $lt respectively

Mongodb between query in java

1 Answers1