本文共 6403 字,大约阅读时间需要 21 分钟。
普通查询
某个字段匹配数组内的元素数量的,假如region只有一个元素的db.getCollection('map').find({region:{$size:1}})假如region只有0个元素的db.getCollection('map').find({region:{$size:0}})db.getCollection('map').find({region:{$size:1}}).count()db.getCollection('map').find({"area":{$gt:[]}}).count()db.getCollection('map').find({"area":{$eq:[]}}).count()字段为[]db.getCollection('map').find({"address_city":{$eq:[]}})字段不存在db.getCollection('map').find({"local":{$exists:false}});这个不能按>和
db.getCollection('map').find({"type":"3"})db.map.find({"type":"3"})
找出非空
db.getCollection('表').find({"字段":{$exists:true}});
查_id需要注意
db.map_area.find({"_id":ObjectId("5811c104dd908725398b5XXX")})
mongodb原生正则查询
任意匹配db.getCollection('表').find({"description":/玉兰小区/});前缀db.getCollection('表').find({"description":/^玉兰/});加i兼容大小写db.getCollection('表').find({"station_area":/EC/i});
spring MongoDB
//等效 criteria.andOperator(criteria.where("name").regex(".*" + v + ".*")); criteria.andOperator(criteria.where("name").regex(".*\\" + v + "\\.*")); criteria.andOperator(criteria.where("name").regex(".*?" + v + ".*")); //前缀查询 criteria.andOperator(criteria.where("name").regex(".*^" + v + ".*"));
条件查询
//完全匹配Pattern pattern = Pattern.compile("^商品$", Pattern.CASE_INSENSITIVE);//右匹配Pattern pattern = Pattern.compile("^.*商品$", Pattern.CASE_INSENSITIVE);//左匹配Pattern pattern = Pattern.compile("^商品.*$", Pattern.CASE_INSENSITIVE);//模糊匹配Pattern pattern = Pattern.compile("^.*商品.*$", Pattern.CASE_INSENSITIVE);Query query = Query.query(Criteria.where(fieldName).regex(pattern)); Listusers = mongoTemplate.find(query, UserInfo.class, classname);
返回指定字段
只返回address,不要id。注意前面的{}不可少
db.getCollection('user').find({},{address:1,_id:0})
数字查询mongodb原生语句
db.getCollection('表').find({"number": {$regex: '76', $options:'i'}});
Java代码同时多条件or查询
Pattern pattern = Pattern.compile(search, Pattern.CASE_INSENSITIVE); criteria.orOperator(criteria.where("name").regex(".*?\\" + search + ".*"), criteria.where("number").regex(pattern));
查询大于某个字段长度的列表
db.getCollection('表').find({"字段": {"$exists": true, "$regex": /^.{5,}$/}}).limit(5)
更新数据
db.t1.updateOne({"_id":ObjectId("523eb382d7f8b9ac3b18b4571")},{"$set":{"name":"XXX1"}})
Query query = Query.query(Criteria.where("_id").is("53eb382d7f8b9ac3b18b4574"));Update update = Update.update("_id", "53eb382d7f8b9ac3b18b4574").set("name","xxx22").set("description","xxxx22");
in查询
原生语句
in查询db.getCollection('表').find({$or:[{number:"01"},{number:"02"}]})db.getCollection('表').find({$or:[{number:"01"},{number:"02"},{status:1}]})
spring中MongoTemplate
Query query = new Query();Criteria criteria = new Criteria();if (condition.getType().equals("in")) { Listv = Arrays.asList(condition.split(",")); criteria.andOperator(criteria.where("name").in(v));}query.addCriteria(criteria);
多条件查询or
Criteria c1= Criteria.where("name").is("XXX");Criteria c2= Criteria.where("number").is("01");Criteria cr = new Criteria();Query query = new Query();query.addCriteria(cr.orOperator(c1,c2));
and
query.addCriteria(cr.andOperator(c1,c2));
多条件查询也可以这样写:
Criteria criteria = new Criteria().andOperator( Criteria.where("id").is(28), Criteria.where("name").is("123") );
动态拼凑Criteria
Query query = new Query();Criteria cr = new Criteria(); ListcriteriaList=new ArrayList<>();//这里可以写for循环Criteria c1= Criteria.where("name").is("XXX");criteriaList.add(c1);Criteria c2= Criteria.where("number").is("01");criteriaList.add(c2);if(criteriaList.size()>0) { Criteria[] criteriaArray = criteriaList.toArray(new Criteria[criteriaList.size()]); cr.andOperator(criteriaArray);}query.addCriteria(cr);if (select != null && select.size() > 0) { for (String tmp : select) { query.fields().include(tmp); }}List list = mongoOperations.find(query, clazz);
这个写的还可以,比较全,可以延伸参考下:
先看这个:
//当前第0页,每页2条
query.skip(page*row);query.limit(row);
分页要注意 skip要*row
public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getSkip() { skip=(pageNo-1)*pageSize; return skip; } public void setSkip(int skip) { this.skip = skip; }
参考
分页参考:
Criteria c1=null; Criteria c2=null; Criteria c3=null; ArrayListlist=new ArrayList (); if (vo.getCatalog1()!=null){ c1 = Criteria.where(MyBean.FILD_CATALOG1).is(vo.getCatalog1()); list.add(c1); } if (vo.getCatalog()!=null){ c2=Criteria.where(MyBean.FILD_CATALOG).is(vo.getCatalog()); list.add(c2); } if (vo.getTitle()!=null){ //模糊查询 c3=Criteria.where(MyBean.FILD_TITLE).regex(".*?\\" +vo.getTitle()+ ".*"); list.add(c3); } Criteria[] arr=new Criteria[list.size()]; list.toArray(arr); Criteria criteria=new Criteria().andOperator(arr); Query query=new Query(criteria); query.skip((vo.getPage()-1)*vo.getPageSize()); query.limit(vo.getPageSize()); List list= mongoTemplate.find(query, MyBean.class); return list;
分页排序
//分页和排序if (simplePageRequest.getPage() != null && simplePageRequest.getRows() != null) { query.skip(simplePageRequest.getPage() - 1); query.limit(simplePageRequest.getRows());}if (simplePageRequest.getSort() != null && simplePageRequest.getOrder() != null) { Sort.Direction SortDirection; if (simplePageRequest.getSort().toLowerCase().equals("asc")) { SortDirection = Sort.Direction.ASC; } else { SortDirection = Sort.Direction.DESC; } Sort sort = new Sort(SortDirection, simplePageRequest.getOrder()); query.with(sort);}
说明:多个排序参数
Sort sort = new Sort(Sort.Direction.ASC, “ID”).and(new Sort(Sort.Direction.ASC, “TIME”));
查看索引:
db.getCollection('表').getIndexes()
查询数组个数限制
img是个数组,需要数组个数>2个
写法1:这是一种比较骚的写法,一般看不明白:
criteria.and("img.2").exists(true);
写法2:取反
criteria.norOperator( Criteria.where("img").size(0));
写法3:枚举 有局限多了不好搞 这实际上是查单个等于的
criteria.orOperator( Criteria.where("img").size(3), Criteria.where("img").size(4), Criteria.where("img").size(5), Criteria.where("img").size(6));
不为空不为null
criteria.and("img").ne("").ne(null);
扩展参考:
查询数组:
包含,完全匹配
转载地址:http://umrpf.baihongyu.com/