com.github.pagehelper.Page Java Examples

/**
 * 使用Mapper接口调用时，使用PageHelper.startPage效果更好，不需要添加Mapper接口参数
 */
@Test
public void testMapperWithStartPage_OrderBy() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        //获取第1页，10条内容，默认查询总数count
        PageHelper.orderBy("id desc");
        User user = new User();
        user.setName("刘");
        List<User> list = userMapper.selectLike(user);
        assertEquals(92, list.get(0).getId());
        assertEquals(15, list.size());
        assertEquals(15, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

@Override
public Pagination<UserEntity> pageAll(Integer page, Integer rows, String sorterField, String sorterOrder, UserEntity userEntity, String gmtCreatedStart, String gmtCreatedEnd, String gmtDeletedStart, String gmtDeletedEnd) {
    if (sorterField != null && !"".equals(sorterField) && sorterOrder != null && !"".equals(sorterOrder)) {
        sorterField = StringUtil.camelToUnderline(sorterField);
        String orderBy = sorterField + " " + sorterOrder;
        PageHelper.startPage(page, rows, orderBy);
    } else {
        PageHelper.startPage(page, rows);
    }
    Page<UserEntity> userEntityPage = userMapper.pageAll(userEntity, gmtCreatedStart, gmtCreatedEnd, gmtDeletedStart, gmtDeletedEnd);
    Pagination<UserEntity> pagination = new Pagination<>();
    List<UserEntity> userEntityList = new ArrayList<>();
    List<UserEntity> tempUserEntityList = userEntityPage.getResult();
    if (tempUserEntityList != null) {
        for (UserEntity userEntity2 : tempUserEntityList) {
            userEntity2.setPassword(null);
            userEntity2.setDepartment(departmentService.listAllByDepartmentId(userEntity2.getDepartmentId()));
            userEntityList.add(userEntity2);
        }
    }
    pagination.setRows(userEntityList);
    pagination.setTotal(userEntityPage.getTotal());
    return pagination;
}
 

@Override
public Pagination<UserEntity> pageAllByDepartmentId(Long departmentId, Integer page, Integer rows) {
    Pagination<UserEntity> pagination = new Pagination<>();
    PageHelper.startPage(page, rows);
    Page<UserEntity> tempUserEntityPage = userMapper.pageAllByDepartmentId(departmentId);
    List<UserEntity> userEntityList = new ArrayList<>();
    if (tempUserEntityPage != null) {
        for (UserEntity userEntity : tempUserEntityPage.getResult()) {
            userEntity.setPassword(null);
            userEntityList.add(userEntity);
        }
        pagination.setRows(userEntityList);
        pagination.setTotal(tempUserEntityPage.getTotal());
    }
    return pagination;
}
 

@Override
public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
    paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
    paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
    //处理pageKey
    pageKey.update(page.getStartRow());
    pageKey.update(page.getPageSize());
    //处理参数配置
    if (boundSql.getParameterMappings() != null) {
        List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
        if (page.getStartRow() == 0) {
            newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
        } else {
            newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, Integer.class).build());
            newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
        }
        MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
        metaObject.setValue("parameterMappings", newParameterMappings);
    }
    return paramMap;
}
 

@Test
public void brokerTokenTest() {
    BrokerTokenEntity brokerTokenEntity = new BrokerTokenEntity();
    brokerTokenEntity.setRole("test");
    brokerTokenEntity.setDescription("This role for test");
    brokerTokenEntity.setToken("xxxxxxxxxxxxx");
    brokerTokensRepository.save(brokerTokenEntity);
    Page<BrokerTokenEntity> brokerTokenEntityPage = brokerTokensRepository.getBrokerTokensList(1, 1);
    brokerTokenEntityPage.count(true);
    brokerTokenEntityPage.getResult().forEach((result) -> {
        Assert.assertEquals(brokerTokenEntity.getRole(), result.getRole());
        Assert.assertEquals(brokerTokenEntity.getDescription(), result.getDescription());
    });

    brokerTokenEntity.setDescription("This role for update test");
    brokerTokenEntity.setToken("tokentestupdate");
    brokerTokensRepository.update(brokerTokenEntity);
    Optional<BrokerTokenEntity> optionalBrokerTokenEntity = brokerTokensRepository.findTokenByRole(brokerTokenEntity.getRole());
    BrokerTokenEntity updatedBrokerTokenEntity = optionalBrokerTokenEntity.get();
    Assert.assertEquals(brokerTokenEntity.getRole(), updatedBrokerTokenEntity.getRole());
    Assert.assertEquals(brokerTokenEntity.getDescription(), updatedBrokerTokenEntity.getDescription());

    brokerTokensRepository.remove(brokerTokenEntity.getRole());
    Assert.assertFalse(brokerTokensRepository.findTokenByRole(brokerTokenEntity.getRole()).isPresent());
}
 

@Test
public void testUserProvider() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    User user = new User();
    user.setId(100);
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        PageHelper.startPage(1, 10);
        List<User> list = userMapper.selectByUserProvider(user);
        assertEquals(100, list.get(0).getId());
        assertEquals(1, list.size());
        assertEquals(1, ((Page<?>) list).getTotal());

        user.setName("不存在");
        PageHelper.startPage(1, 10);
        list = userMapper.selectByUserProvider(user);
        assertEquals(0, list.size());
    } finally {
        sqlSession.close();
    }
}
 

@Test
public void testMapperWithStartPage() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        //获取第1页，10条内容，默认查询总数count
        Map<String, Object> params = new HashMap<String, Object>(2);
        params.put("pageNum", 1L);
        params.put("pageSize", "100");
        PageHelper.startPage(params);
        Map<String, Object> where = new HashMap<String, Object>();
        where.put("id", 100);
        List<User> list = userMapper.selectByWhereMap(new Where(where));
        assertEquals(100, list.get(0).getId());
        assertEquals(1, list.size());
        assertEquals(1, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

@Test
public void testInList() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        UserExample example = new UserExample();
        example.createCriteria().andIdIn(Arrays.asList(1, 2, 3, 4, 5));
        PageHelper.startPage(1, 20);
        List<User> list = userMapper.selectByExample(example);
        assertEquals(1, list.get(0).getId());
        assertEquals(5, list.size());
        assertEquals(5, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

/**
 * 使用Mapper接口调用时，使用PageHelper.startPage效果更好，不需要添加Mapper接口参数
 */
@Test
public void testMapperWithStartPage() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        //获取第1页，10条内容，默认查询总数count
        PageHelper.startPage(1, 10);
        List<User> list = userMapper.selectAllOrderByArray(new Integer[]{1, 2});
        assertEquals(3, list.get(0).getId());
        assertEquals(10, list.size());
        assertEquals(181, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

public static Object okList(List list, List pagedList) {
    Map<String, Object> data = new HashMap<String, Object>();
    data.put("list", list);

    if (pagedList instanceof Page) {
        Page page = (Page) pagedList;
        data.put("total", page.getTotal());
        data.put("page", page.getPageNum());
        data.put("limit", page.getPageSize());
        data.put("pages", page.getPages());
    } else {
        data.put("total", pagedList.size());
        data.put("page", 1);
        data.put("limit", pagedList.size());
        data.put("pages", 1);
    }

    return ok(data);
}
 

/**
 * union的count查询sql特殊
 */
@Test
public void testUnion() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        //获取第1页，10条内容，默认查询总数count
        PageHelper.startPage(1, 10);
        List<User> list = userMapper.selectUnion();
        assertEquals(1, list.get(0).getId());
        assertEquals(10, list.size());
        assertEquals(13, ((Page<?>) list).getTotal());

        //获取第1页，10条内容，默认查询总数count
        PageHelper.startPage(2, 10);
        list = userMapper.selectUnion();
        assertEquals(181, list.get(0).getId());
        assertEquals(3, list.size());
        assertEquals(13, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

@Override
public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
    paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
    paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
    //处理pageKey
    pageKey.update(page.getStartRow());
    pageKey.update(page.getPageSize());
    //处理参数配置
    if (boundSql.getParameterMappings() != null) {
        List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>();
        if (page.getStartRow() > 0) {
            newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, Integer.class).build());
        }
        if (page.getPageSize() > 0) {
            newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, Integer.class).build());
        }
        newParameterMappings.addAll(boundSql.getParameterMappings());
        MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
        metaObject.setValue("parameterMappings", newParameterMappings);
    }
    return paramMap;
}
 

/**
 * 使用Mapper接口调用时，使用PageHelper.startPage效果更好，不需要添加Mapper接口参数
 */
@Test
public void testMapperWithStartPage_OrderBy() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        //获取第1页，10条内容，默认查询总数count
        PageHelper.startPage(1, 10, "id desc");
        List<User> list = userMapper.selectChoose(183, 2);
        assertEquals(182, list.get(0).getId());
        assertEquals(10, list.size());
        assertEquals(182, ((Page<?>) list).getTotal());

        //获取第1页，10条内容，默认查询总数count
        PageHelper.startPage(1, 10, "id desc");
        list = userMapper.selectChoose(183, 2);
        assertEquals(182, list.get(0).getId());
        assertEquals(10, list.size());
        assertEquals(182, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

@RequestMapping(value = "/getlist", method = RequestMethod.POST)
@ResponseBody
public PageResultBean<List<ErpStudent>> getList(int page, int limit, String keyword) {
    List<ErpStudent> list;
    PageHelper.startPage(page, limit, "create_date desc");
    if (keyword != null && !keyword.trim().isEmpty()) {
        keyword = "%" + keyword.trim() + "%";
        Example example = new Example(ErpStudent.class);
        example.createCriteria().andLike("name", keyword);
        list = service.selectByExample(example);
    } else {
        list = service.selectAll();
    }
    return new PageResultBean(list, page, limit, ((Page) list).getTotal());

}
 

/**
 * 分页 - 书单更多接口
 * @param booklistId            书单ID
 * @param page                  页数
 * @param limit                 每页数量
 * @return
 */
@Override
public Result getBooklistPagingBooks(Integer booklistId, Integer page, Integer limit) {
    String key = RedisHomepageKey.getBooklistItemPagingKey(booklistId);
    // 使用页码+数量作为Hash的key，防止不同数量的分页页码冲突
    String field = page.toString() + limit;
    List<BooklistBookVO> list = this.redisService.getHashListVal(key, field, BooklistBookVO.class);
    if (CommonUtil.isEmpty(list)) {
        list = new ArrayList<>();
        PageHelper.startPage(page, limit);
        Page<IndexBooklistItem> pageList = (Page<IndexBooklistItem>) this.indexBooklistItemMapper.findPageWithResult(booklistId);
        for (int i = 0; i < pageList.size(); i++) {
            String bookId = pageList.get(i).getBookId();
            BooklistBookVO vo = this.getBookVO(bookId);
            if (vo != null) {
                list.add(vo);
            }
        }

        if (list.size() > 0) {
            this.redisService.setHashValExpire(key, field, list, RedisExpire.HOUR_TWO);
        }
    }
    return ResultUtil.success(list);
}
 

@Select({"<script>",
        "SELECT environment, cluster, tenant, namespace, persistent, topic,"
            + "sum(producer_count) as producerCount,"
            + "sum(subscription_count) as subscriptionCount,"
            + "sum(msg_rate_in) as msgRateIn,"
            + "sum(msg_throughput_in) as msgThroughputIn,"
            + "sum(msg_rate_out) as msgRateOut,"
            + "sum(msg_throughput_out) as msgThroughputOut,"
            + "avg(average_msg_size) as averageMsgSize,"
            + "sum(storage_size) as storageSize, time_stamp  FROM topics_stats",
        "WHERE environment=#{environment} and tenant=#{tenant} and namespace=#{namespace} and time_stamp=#{timestamp} and " +
                "topic IN <foreach collection='topicList' item='topic' open='(' separator=',' close=')'> #{topic} </foreach>" +
        "GROUP BY environment, cluster, tenant, namespace, persistent, topic, time_stamp" +
        "</script>"})
Page<TopicStatsEntity> findByMultiTopic(
        @Param("environment") String environment,
        @Param("tenant") String tenant,
        @Param("namespace") String namespace,
        @Param("persistent") String persistent,
        @Param("topicList") List<String> topicList,
        @Param("timestamp") long timestamp);
 

@Test
public void getNamespaceByTenantOrNamespaceTest() {
    NamespaceEntity namespacesEntity = new NamespaceEntity();
    initNamespaceEntity(namespacesEntity);
    namespacesRepository.save(namespacesEntity);
    String tenant = "test-namespace-public";
    Page<NamespaceEntity> namespacesEntityPageByTenant = namespacesRepository.
            findByTenantOrNamespace(1, 2, tenant);
    namespacesEntityPageByTenant.count(true);
    checkResult(namespacesEntityPageByTenant);
    String namespace = "test-namespace-default";
    Page<NamespaceEntity> namespacesEntityPageByNamespace = namespacesRepository.
            findByTenantOrNamespace(1, 2, namespace);
    namespacesEntityPageByNamespace.count(true);
    checkResult(namespacesEntityPageByNamespace);
    namespacesEntityPageByNamespace.getResult().forEach((result) -> {
        namespacesRepository.remove(result.getTenant(), result.getNamespace());
    });
    Page<NamespaceEntity> deleteNamespace = namespacesRepository.getNamespacesList(1, 2);
    deleteNamespace.count(true);
    checkDeleteResult(deleteNamespace);
}
 

@Test
public void testPageNum() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    try {
        PageHelper.offsetPage(5, 5);
        List<User> list = userMapper.selectAll();
        assertEquals(2, ((Page<?>) list).getPageNum());
        assertEquals(5, list.size());
        assertEquals(183, ((Page<?>) list).getTotal());

        PageHelper.offsetPage(15, 5);
        list = userMapper.selectAll();
        assertEquals(4, ((Page<?>) list).getPageNum());
        assertEquals(5, list.size());
        assertEquals(183, ((Page<?>) list).getTotal());
    } finally {
        sqlSession.close();
    }
}
 

@Select({"<script>",
        "SELECT tenant, tenant_id as tenantId, admin_roles as adminRoles,allowed_clusters as allowedClusters," +
                "environment_name as environmentName " +
                " FROM tenants ",
        "WHERE tenant_id IN <foreach collection='tenantIdList' item='tenantId' open='(' separator=',' close=')'> #{tenantId} </foreach>" +
                "</script>"})
Page<TenantEntity> findByMultiId(@Param("tenantIdList") List<Long> tenantIdList);
 

/**
 * 分页查询，pageHelper转换SQL时报错with(nolock)不识别的问题，
 * 重写父类AbstractHelperDialect.getPageSql转换出错的方法。
 * 1. this.replaceSql.replace(sql);先转换成假的表名
 * 2. 然后进行SQL转换
 * 3. this.replaceSql.restore(sql);最后再恢复成真的with(nolock)
 */
@Override
public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
    String sql = boundSql.getSql();
    Page page = this.getLocalPage();
    String orderBy = page.getOrderBy();
    if (StringUtil.isNotEmpty(orderBy)) {
        pageKey.update(orderBy);
        sql = this.replaceSql.replace(sql);
        sql = OrderByParser.converToOrderBySql(sql, orderBy);
        sql = this.replaceSql.restore(sql);
    }

    return page.isOrderByOnly() ? sql : this.getPageSql(sql, page, pageKey);
}
 

@ApiOperation(value = "查询所有[树节点]", notes = "以树节点的形式展示")
@GetMapping("/tree")
public JsonResult<Page<ChinaArea>> tree() {
    List<TreeNode> collect = baseService.findAll(null)
            .stream()
            .distinct()
            .map(res -> new TreeNode(res.getAreaCode(), res.getName(), res.getParentCode(), res, null))
            .collect(Collectors.toList());
    return JsonResult.success(TreeNodeUtils.findRoots(collect));
}
 

@Select("SELECT topic_stats_id as topicStatsId,environment as environment,cluster as cluster,broker as broker," +
        "tenant as tenant,namespace as namespace,bundle as bundle,persistent as persistent," +
        "topic as topic,producer_count as producerCount,subscription_count as subscriptionCount," +
        "msg_rate_in as msgRateIn,msg_throughput_in as msgThroughputIn,msg_rate_out as msgRateOut," +
        "msg_throughput_out as msgThroughputOut,average_msg_size as averageMsgSize,storage_size as storageSize," +
        "time_stamp  FROM topics_stats " +
        "WHERE environment=#{environment} and tenant=#{tenant} and namespace=#{namespace} " +
        "and time_stamp=#{timestamp}")
Page<TopicStatsEntity> findByNamespace(
        @Param("environment") String environment,
        @Param("tenant") String tenant,
        @Param("namespace") String namespace,
        @Param("timestamp") long timestamp);
 

/**
 * 查询历史版本集合
 */
@RequestMapping(value = "history_list", method = { RequestMethod.POST, RequestMethod.GET })
@RequiresPermissions(value = {"scm"})
public RespBase<?> historylist(ReleaseHistoryList agl, PageModel pm) {
	if (log.isInfoEnabled()) {
		log.info("HistoryVersionList request ... {}, {}", agl, pm);
	}
	RespBase<Object> resp = new RespBase<>();
	try {

		Page<ReleaseHistoryList> page = PageHelper.startPage(pm.getPageNum(), pm.getPageSize(), true);
		List<ReleaseHistoryList> list = historyService.historylist(agl);

		pm.setTotal(page.getTotal());

		resp.forMap().put("page", pm);
		resp.forMap().put("list", list);

	} catch (Exception e) {
		resp.setCode(RetCode.SYS_ERR);
		log.error("获取历史版本列表失败", e);
	}
	if (log.isInfoEnabled()) {
		log.info("HistoryVersionList response. {}", resp);
	}
	return resp;
}
 

/**
 * 描述:查找排班记录、分页
 * <p>author: ma
 */
@ApiOperation("查找排班记录、分页")
@RequestMapping(value = "/listSkd", method = RequestMethod.POST)
@ResponseBody
public  CommonResult<CommonPage<SmsSkdResult>> listSkd(SmsSkdParam queryParam,
                                                       @RequestParam(value = "pageSize", defaultValue = "5") Integer pageSize,
                                                       @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum){
    Page page =PageHelper.startPage(pageNum, pageSize);

    List<SmsSkdResult> smsSkdResultList = smsSkdService.listSkd(queryParam);
    Long pageTotal=page.getTotal();
    return CommonResult.success(CommonPage.restPage(smsSkdResultList,pageTotal));
}
 

public static PageInfo buildPageInfoWithPageInfoList(PageInfo pageInfo, List list) {
    Page page = new Page<>(pageInfo.getPageNum(), pageInfo.getPageSize());
    page.setTotal(pageInfo.getTotal());
    page.addAll(list);

    return page.toPageInfo();
}
 

@Override
public Pagination<MailEntity> pageAll(Integer page, Integer rows) {
    Pagination<MailEntity> pagination = new Pagination<>();
    PageHelper.startPage(page, rows);
    Page<MailEntity> mailEntityPage = mailMapper.pageAll();
    pagination.setRows(mailEntityPage.getResult());
    pagination.setTotal(mailEntityPage.getTotal());
    return pagination;
}
 

@Test
public void testFindByPage() {
    Page<Student> students = studentService.findByPage(1, 2);//Query pageNo=1, pageSize=2
    assertEquals(students.getTotal(),5);
    assertEquals(students.getPages(),3);
    log.debug(students.toString());
}
 

public Page<TopicStatsEntity> findByMultiNamespace(Integer pageNum,
                                            Integer pageSize,
                                            String environment,
                                            String tenant,
                                            List<String> namespaceList,
                                            long timestamp) {
    PageHelper.startPage(pageNum, pageSize);
    return topicsStatsMapper.findByMultiNamespace(environment, tenant, namespaceList, timestamp);
}
 

@Select("SELECT subscription_stats_id as subscriptionStatsId,topic_stats_id as topicStatsId," +
        "subscription as subscription,msg_backlog as msgBacklog,msg_rate_expired as msgRateExpired," +
        "msg_rate_out as msgRateOut,msg_throughput_out as msgThroughputOut,msg_rate_redeliver as msgRateRedeliver," +
        "number_of_entries_since_first_not_acked_message as numberOfEntriesSinceFirstNotAckedMessage," +
        "total_non_contiguous_deleted_messages_range as totalNonContiguousDeletedMessagesRange," +
        "subscription_type as subscriptionType,time_stamp  FROM subscriptions_stats " +
        "where topic_stats_id=#{topicStatsId} and time_stamp=#{timestamp}")
Page<SubscriptionStatsEntity> findByTopicStatsId(@Param("topicStatsId") long topicStatsId,
                                                 @Param("timestamp") long timestamp);
 

@Test
public void getEnvironmentsList() {
    EnvironmentEntity environmentEntity = new EnvironmentEntity();
    environmentEntity.setName("test-environment");
    environmentEntity.setBroker("http://localhost:8080");
    environmentsRepository.save(environmentEntity);
    Page<EnvironmentEntity> environmentEntityPage = environmentsRepository.getEnvironmentsList(1, 1);
    environmentEntityPage.count(true);
    environmentEntityPage.getResult().forEach((result) -> {
        Assert.assertEquals("test-environment", result.getName());
        Assert.assertEquals("http://localhost:8080", result.getBroker());
        environmentsRepository.remove(result.getName());
    });
}