The paging query of MongoDB dynamic condition

First, the use of Querybyexampleexecutor

1. Inheriting mongorepository

Public interface Studentrepository extends Mongorepository<student, string> {
    
}

2. Code implementation

• Using the Examplematcher match-----fuzzy queries that only support strings, other types are exact matches
• Example encapsulating entity classes and matching filters
• Using the FindAll method in the Querybyexampleexecutor interface

 public page<student> getlistwithexample (Studentreqvo studentreqvo) {
 Sort sort = sort.by (sort.direction . DESC, "Createtime"); 
 pageable pageable = Pagerequest.of (Studentreqvo.getpagenum (), studentreqvo.getpagesize (), sort);    
 
 Student Student = new Student (); 
 Beanutils.copyproperties (STUDENTREQVO, student); 
 
//Create a match, that is, how to use the query condition 
 examplematcher matcher = examplematcher.matching ()//Build Object 
. withstringm Atcher (ExampleMatcher.StringMatcher.CONTAINING)//change the default string matching method: Fuzzy query 
. Withignorecase (TRUE)//change default case Ignore mode: Ignore size Write 
. Withmatcher ("name", ExampleMatcher.GenericPropertyMatchers.contains ())//query with "include matching" 
. W  Ithignorepaths ("Pagenum", "pageSize");    Ignore properties, do not participate in query 
 
//Create instance 
 example<student> Example = Example.of (Student, matcher); 
 page<student> students = Studentrepository.findall (example, pageable); 
 
 return students; 
} 

Disadvantages:

• Filtering condition grouping is not supported. That is, filter conditions are not supported with or (or) to connect, all filter conditions are simple one layer with and (and) connection
• A range query that does not support two values, such as a time-range query

Second, mongotemplate combined with query

Implementation one: Use criteria to encapsulate query criteria

Public page<student> Getlistwithcriteria (Studentreqvo studentreqvo) {

Sort sort = sort.by (Sort.Direction.DESC, "createtime");
pageable pageable = Pagerequest.of (Studentreqvo.getpagenum (), studentreqvo.getpagesize (), sort);

Query query = new query ();

Dynamic stitching Query conditions
if (! Stringutils.isempty (Studentreqvo.getname ())) {
Pattern pattern = pattern.compile ("^.*" + studentreqvo.getname () + ". *$", pattern.case_insensitive);
Query.addcriteria (Criteria.where ("name"). Regex (pattern));
}

if (studentreqvo.getsex () = null) {
Query.addcriteria (Criteria.where ("sex"). Is (Studentreqvo.getsex ()));
}
if (studentreqvo.getcreatetime () = null) {
Query.addcriteria (Criteria.where ("Createtime"). LTE (Studentreqvo.getcreatetime ()));
}

Total calculation
Long total = mongotemplate.count (query, Student.class);

Query result set
list<student> studentlist = Mongotemplate.find (Query.with (pageable), student.class);
page<student> studentpage = new Pageimpl (studentlist, pageable, total);
return studentpage;
}

Implementation II: Using example and criteria to encapsulate query criteria

Public page<student> Getlistwithexampleandcriteria (Studentreqvo studentreqvo) {

Sort sort = sort.by (Sort.Direction.DESC, "createtime");
pageable pageable = Pagerequest.of (Studentreqvo.getpagenum (), studentreqvo.getpagesize (), sort);

Student Student = new Student ();
Beanutils.copyproperties (STUDENTREQVO, student);

Create a match, that is, how to use the query criteria
Examplematcher Matcher = examplematcher.matching ()//Build Object
. Withstringmatcher (ExampleMatcher.StringMatcher.CONTAINING)//change the default string matching method: Fuzzy query
. Withignorecase (TRUE)//change default case Ignore mode: Ignore case
. Withmatcher ("name", ExampleMatcher.GenericPropertyMatchers.contains ())//title query with "include match"
. Withignorepaths ("Pagenum", "pageSize"); Ignore attributes, do not participate in queries

Create an instance
example<student> Example = Example.of (Student, Matcher);
Query query = new query (Criteria.byexample (example));
if (studentreqvo.getcreatetime () = null) {
Query.addcriteria (Criteria.where ("Createtime"). LTE (Studentreqvo.getcreatetime ()));
}

Total calculation
Long total = mongotemplate.count (query, Student.class);

Query result set
list<student> studentlist = Mongotemplate.find (Query.with (pageable), student.class);
page<student> studentpage = new Pageimpl (studentlist, pageable, total);
return studentpage;
}

Disadvantages:

• Returning fixed fields is not supported

Three, Mongotemplate combined basicquery

• Basicquery is a subclass of query
• Support for returning fixed fields

Public page<student> getlistwithbasicquery (Studentreqvo studentreqvo) {
Sort sort = sort.by (Sort.Direction.DESC, "createtime");
pageable pageable = Pagerequest.of (Studentreqvo.getpagenum (), studentreqvo.getpagesize (), sort);

QueryBuilder QueryBuilder = new QueryBuilder ();

Dynamic stitching Query conditions
if (! Stringutils.isempty (Studentreqvo.getname ())) {
Pattern pattern = pattern.compile ("^.*" + studentreqvo.getname () + ". *$", pattern.case_insensitive);
Querybuilder.and ("name"). Regex (pattern);
}

if (studentreqvo.getsex () = null) {
Querybuilder.and ("Sex"). Is (Studentreqvo.getsex ());
}
if (studentreqvo.getcreatetime () = null) {
Querybuilder.and ("Createtime"). Lessthanequals (Studentreqvo.getcreatetime ());
}

Query query = new Basicquery (Querybuilder.get (). toString ());
Total calculation
Long total = mongotemplate.count (query, Student.class);

Query result set criteria
Basicdbobject fieldsobject = new Basicdbobject ();
ID has value by default, not specified
Fieldsobject.append ("id", 1)//1 query, return data with value; 0 no query, no value
. Append ("name", 1);
query = new Basicquery (Querybuilder.get (). toString (), Fieldsobject.tojson ());

Query result set
list<student> studentlist = Mongotemplate.find (Query.with (pageable), student.class);
page<student> studentpage = new Pageimpl (studentlist, pageable, total);
return studentpage;
}

Iv. combination of mongotemplate and aggregation

• Using Aggregation aggregation queries
• Support for returning fixed fields
• Supports grouping calculation totals, sums, averages, maximums, minimums, and more

Public page<student> getlistwithaggregation (Studentreqvo studentreqvo) {
Sort sort = sort.by (Sort.Direction.DESC, "createtime");
pageable pageable = Pagerequest.of (Studentreqvo.getpagenum (), studentreqvo.getpagesize (), sort);

Integer pagenum = Studentreqvo.getpagenum ();
Integer pageSize = Studentreqvo.getpagesize ();

list<aggregationoperation> operations = new arraylist<> ();
if (! Stringutils.isempty (Studentreqvo.getname ())) {
Pattern pattern = pattern.compile ("^.*" + studentreqvo.getname () + ". *$", pattern.case_insensitive);
Criteria = Criteria.where ("name"). Regex (pattern);
Operations.add (Aggregation.match (criteria));
}
if (null! = Studentreqvo.getsex ()) {
Operations.add (Aggregation.match (Criteria.where ("sex"). Is (Studentreqvo.getsex ())));
}
Long totalcount = 0;
Gets the total number of pages that are added
if (null! = Operations && operations.size () > 0) {
Aggregation Aggregationcount = aggregation.newaggregation (Operations); Operations is empty, will error
aggregationresults<student> Resultscount = mongotemplate.aggregate (Aggregationcount, "Student", Student.class );
TotalCount = Resultscount.getmappedresults (). Size ();
} else {
list<student> list = Mongotemplate.findall (Student.class);
TotalCount = List.size ();
}

Operations.add (Aggregation.skip ((Long) pagenum * pageSize));
Operations.add (Aggregation.limit (pageSize));
Operations.add (Aggregation.sort (Sort.Direction.DESC, "createtime"));
Aggregation Aggregation = aggregation.newaggregation (Operations);
aggregationresults<student> results = mongotemplate.aggregate (aggregation, "Student", Student.class);

Query result set
page<student> studentpage = new Pageimpl (Results.getmappedresults (), pageable, totalcount);
return studentpage;
}

The paging query of MongoDB dynamic condition