mongodb 多条件分页查询的三种方法

今天药忘吃喽~ 2022-12-03 08:43 418阅读 0赞

转载自：[https://javacfox.github.io/2019/06/09/mongodb%E5%A4%9A%E6%9D%A1%E4%BB%B6%E5%88%86%E9%A1%B5%E6%9F%A5%E8%AF%A2%E7%9A%84%E4%B8%89%E7%A7%8D%E6%96%B9%E6%B3%95/][https_javacfox.github.io_2019_06_09_mongodb_E5_A4_9A_E6_9D_A1_E4_BB_B6_E5_88_86_E9_A1_B5_E6_9F_A5_E8_AF_A2_E7_9A_84_E4_B8_89_E7_A7_8D_E6_96_B9_E6_B3_95]

## 叙述 ##

介绍 MongoDB 带参数分页查询的具体实现方法

**一、使用limit和skip进行分页查询**

public List<User> pageList(int pageNum ,int pageSize){
        List<User> userList = new ArrayList<>();
        Mongo mg = new Mongo();
        DB db = mg.getDB("data");
        DBCollection coll = db.getCollection("t_user");
        DBCursor limit = coll.find().skip((pageNum-1)*pageSize).sort(new BasicDBObject()).limit(pageSize);
        while (limit.hasNext()){
            userList.add(parse(new User(),limit.next()));
        }
        return userList;
    }
    
    private User parse(User user,DBObject obj){
        user.setAge((int) obj.get("age"));
        user.setName((String)obj.get("name"));
        user.setPwd((String)obj.get("pwd"));
        return user;
    }
    //查询结果 1,2
    [
      {
        "id": null,
        "name": "ljl",
        "pwd": "123456",
        "age": 24
      },
      {
        "id": null,
        "name": "lsr",
        "pwd": "123456",
        "age": 18
      }
    ]

通过skip和limit方法可以简单的实现分页操作，但是如果数据量特别巨大的时候，会出现性能的问题，建议不使用！

**二、通过原生的方法实现条件查询、分页和排序**

public Page<User> getPageByOriginalFunction(int age,int pageNUmber,int pageSize){
        //查询条件,可以传递多个查询条件
        User user = new User();
        user.setAge(age);
        Example<User> example = Example.of(user);
    
        //分页条件
        //Pageable pageable = new PageRequest(pageNUmber,pageSize);
        Pageable pageable = PageRequest.of(pageNUmber,pageSize);
    
        return secondRepository.findAll(example,pageable);
    
    }
    
    //查询结果
    {
      "content": [
        {
          "id": "5cfb69ee4332ce07b864d12e",
          "name": "lsr",
          "pwd": "123456",
          "age": 18
        }
      ],
      "pageable": {
        "sort": {
          "sorted": false,
          "unsorted": true
        },
        "offset": 0,
        "pageSize": 2,
        "pageNumber": 0,
        "unpaged": false,
        "paged": true
      },
      "last": true,
      "totalPages": 1,
      "totalElements": 1,
      "number": 0,
      "size": 2,
      "sort": {
        "sorted": false,
        "unsorted": true
      },
      "first": true,
      "numberOfElements": 1
    }

**三、通过实现Pageable接口，自定义**

1.创建自定义分页类，实现Pageable接口

import lombok.AllArgsConstructor;
    import lombok.NoArgsConstructor;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    import javax.validation.constraints.Min;
    
    @NoArgsConstructor
    @AllArgsConstructor
    public class SpringDataPageAble implements Pageable {
    
        @Min(1)
        private Integer pageNumber = 1;
        @Min(1)
        private Integer pageSize = 10;
        private Sort sort;
    
        public void setSort(Sort sort) {
            this.sort = sort;
        }
    
        // 当前页面
        @Override
        public int getPageNumber() {
            return this.pageNumber;
        }
    
        // 每一页显示的条数
    
        @Override
        public int getPageSize() {
            return getPagesize();
        }
    
        // 第二页所需要增加的数量
    
        @Override
        public long getOffset() {
            return (getPageNumber() - 1) * getPagesize();
        }
    
        @Override
        public Sort getSort() {
            return sort;
        }
    
        public void setPagenumber(Integer pagenumber) {
            this.pageNumber = pageNumber;
        }
    
        public Integer getPagesize() {
            return this.pageSize;
        }
    
        public void setPagesize(Integer pagesize) {
            this.pageSize = pagesize;
        }
    
        @Override
        public Pageable next() {
            return null;
        }
    
        @Override
        public Pageable previousOrFirst() {
            return null;
        }
    
        @Override
        public Pageable first() {
            return null;
        }
    
        @Override
        public boolean hasPrevious() {
            return false;
        }
    }

2.在repository层定义分页方法

import com.tedu.huawei.entity.User;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.mongodb.repository.MongoRepository;
    
    public interface UserFirstRepository extends MongoRepository<User,String> {
        Page<User> getUserByAgeGreaterThan(int age, Pageable pageAble);
    }

3.service层调用方法

public Page<User> getUserByAgeGraterThan(int age, int pageNumber,int pageSize){
        SpringDataPageAble springDataPageAble = new SpringDataPageAble(pageNumber,pageSize,new Sort(Sort.Direction.ASC,"age"));
        return firstRepository.getUserByAgeGreaterThan(age,springDataPageAble);
    }

查询结果显示

{
      "content": [
        {
          "id": "5cfb66114332ce07b864d12d",
          "name": "lsr",
          "pwd": "123456",
          "age": 18
        },
        {
          "id": "5cfb85084332ce4ffca97907",
          "name": "panzi",
          "pwd": "654321",
          "age": 24
        }
      ],
      "pageable": {
        "pageNumber": 1,
        "pageSize": 2,
        "sort": {
          "sorted": true,
          "unsorted": false
        },
        "offset": 0,
        "pagesize": 2,
        "unpaged": false,
        "paged": true
      },
      "last": true,
      "totalPages": 1,
      "totalElements": 2,
      "number": 1,
      "size": 2,
      "sort": {
        "sorted": true,
        "unsorted": false
      },
      "first": false,
      "numberOfElements": 2
    }

**四、总结**

第一种方式实现简单方便，但是不适用于大数据量。第二种分页是原生的方法，不需要做额外的处理，但是查询条件单一，不能设置大于等于或者在某某之间，以及模糊查询有很大的限制。第三种方式实现也很简单，但是更加开放和使用性能好。

[https_javacfox.github.io_2019_06_09_mongodb_E5_A4_9A_E6_9D_A1_E4_BB_B6_E5_88_86_E9_A1_B5_E6_9F_A5_E8_AF_A2_E7_9A_84_E4_B8_89_E7_A7_8D_E6_96_B9_E6_B3_95]: https://javacfox.github.io/2019/06/09/mongodb%E5%A4%9A%E6%9D%A1%E4%BB%B6%E5%88%86%E9%A1%B5%E6%9F%A5%E8%AF%A2%E7%9A%84%E4%B8%89%E7%A7%8D%E6%96%B9%E6%B3%95/