下面列出了怎么用com.github.pagehelper.PageHelper的API类实例代码及写法，或者点击链接到github查看源代码。
public List<DtsAddress> querySelective(Integer userId, String name, Integer page, Integer limit, String sort,
String order) {
DtsAddressExample example = new DtsAddressExample();
DtsAddressExample.Criteria criteria = example.createCriteria();
if (userId != null) {
criteria.andUserIdEqualTo(userId);
}
if (!StringUtils.isEmpty(name)) {
criteria.andNameLike("%" + name + "%");
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return addressMapper.selectByExample(example);
}
public List<LitemallIssue> querySelective(String question, Integer page, Integer limit, String sort, String order) {
LitemallIssueExample example = new LitemallIssueExample();
LitemallIssueExample.Criteria criteria = example.createCriteria();
if (!StringUtils.isEmpty(question)) {
criteria.andQuestionLike("%" + question + "%");
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return issueMapper.selectByExample(example);
}
/**
* 测试通用Mapper - 条件查询
*/
@Test
public void testQueryByCondition() {
initData();
Example example = new Example(User.class);
// 过滤
example.createCriteria().andLike("name", "%Save1%").orEqualTo("phoneNumber", "17300000001");
// 排序
example.setOrderByClause("id desc");
int count = userMapper.selectCountByExample(example);
// 分页
PageHelper.startPage(1, 3);
// 查询
List<User> userList = userMapper.selectByExample(example);
PageInfo<User> userPageInfo = new PageInfo<>(userList);
Assert.assertEquals(3, userPageInfo.getSize());
Assert.assertEquals(count, userPageInfo.getTotal());
log.debug("【userPageInfo】= {}", userPageInfo);
}
@Override
public List<SmsRole> select(SmsRoleParam smsRoleParam, Integer pageSize, Integer pageNum){
PageHelper.startPage(pageNum, pageSize);
SmsRoleExample example = new SmsRoleExample();
SmsRoleExample.Criteria criteria = example.createCriteria();
if(!StringUtils.isEmpty(smsRoleParam.getName())){
criteria.andNameLike("%"+smsRoleParam.getName()+"%");
}
if(!StringUtils.isEmpty(smsRoleParam.getCreateTime())){
criteria.andCreateTimeEqualTo(smsRoleParam.getCreateTime());
}
if(!StringUtils.isEmpty(smsRoleParam.getStatus())){
criteria.andStatusEqualTo(smsRoleParam.getStatus());
}
if(!StringUtils.isEmpty(smsRoleParam.getDescription())){
criteria.andNameLike("%"+smsRoleParam.getDescription()+"%");
}
example.setOrderByClause("id desc");
return roleMapper.selectByExample(example);
}
@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());
}
/**
* 更新FqTopic页面
*/
@RequestMapping("/detail/{fqTopicId}")
public String fqTopicEdit(@PathVariable Long fqTopicId, Model model) {
try {
FqTopic fqTopic = fqTopicService.selectByPrimaryKey(fqTopicId);
if(fqTopic == null){
return GENERAL_NOT_FOUNF_404_URL;
}
model.addAttribute("fqTopic", fqTopic);
List<FqTopicReply> replies = fqTopicService.listReplies(fqTopicId);
if(replies == null){
replies = Lists.newArrayList();
}
model.addAttribute("replies",replies);
PageHelper.startPage(1,10,false);
FqTopicExample fqTopicExample = new FqTopicExample();
fqTopicExample.createCriteria().andTypeEqualTo(fqTopic.getType());
fqTopicExample.setOrderByClause("GMT_CREATE desc");
List<FqTopic> fqTopics = fqTopicService.selectByExample(fqTopicExample);
model.addAttribute("sameSource",fqTopics);
} catch (Exception e) {
logger.error("话题详情页",e);
return GENERAL_ERROR_URL;
}
return "/fqTopic/detail.html";
}
public Response list(TestPlan testPlan, PageRequest pageRequest) {
boolean needPaging = pageRequest.needPaging();
if (needPaging) {
PageHelper.startPage(pageRequest.getPageNum(), pageRequest.getPageSize());
}
List<TestPlan> testPlans = selectByTestPlan(testPlan);
List<TestPlanVo> testPlanVos = convertTestPlansToTestPlanVos(testPlans);
if (needPaging) {
long total = Page.getTotal(testPlans);
return Response.success(Page.build(testPlanVos, total));
} else {
return Response.success(testPlanVos);
}
}
/**
* 使用#{}在查询列中进行计算，count查询时需要特殊对待
*/
@Test
public void testSelectColumn2() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
try {
//获取第1页，10条内容，默认查询总数count
PageHelper.startPage(1, 10);
List<User> list = userMapper.selectMULId(1);
//1,'Angola','AO'
assertEquals(1, list.get(0).getId());
assertEquals(10, list.size());
assertEquals(183, ((Page<?>) list).getTotal());
//获取第1页，10条内容，默认查询总数count
PageHelper.startPage(1, 10);
list = userMapper.selectMULId(5);
//1,'Angola','AO'
assertEquals(5, list.get(0).getId());
assertEquals(10, list.size());
assertEquals(183, ((Page<?>) list).getTotal());
} finally {
sqlSession.close();
}
}
public List<LitemallStorage> querySelective(String key, String name, Integer page, Integer limit, String sort, String order) {
LitemallStorageExample example = new LitemallStorageExample();
LitemallStorageExample.Criteria criteria = example.createCriteria();
if (!StringUtils.isEmpty(key)) {
criteria.andKeyEqualTo(key);
}
if (!StringUtils.isEmpty(name)) {
criteria.andNameLike("%" + name + "%");
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return storageMapper.selectByExample(example);
}
public List<LitemallCouponUser> queryList(Integer userId, Integer couponId, Short status, Integer page, Integer size, String sort, String order) {
LitemallCouponUserExample example = new LitemallCouponUserExample();
LitemallCouponUserExample.Criteria criteria = example.createCriteria();
if (userId != null) {
criteria.andUserIdEqualTo(userId);
}
if(couponId != null){
criteria.andCouponIdEqualTo(couponId);
}
if (status != null) {
criteria.andStatusEqualTo(status);
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
if (!StringUtils.isEmpty(page) && !StringUtils.isEmpty(size)) {
PageHelper.startPage(page, size);
}
return couponUserMapper.selectByExample(example);
}
@Override
public List<DmsDiseCatalogResult> select(DmsDiseCatalogParam dmsDiseCatalogParam, Integer pageSize, Integer pageNum) {
PageHelper.startPage(pageNum,pageSize);
DmsDiseCatalogExample example = new DmsDiseCatalogExample();
DmsDiseCatalogExample.Criteria criteria = example.createCriteria();
if(!StringUtils.isEmpty(dmsDiseCatalogParam.getName())){
criteria.andNameEqualTo(dmsDiseCatalogParam.getName());
}
if(!StringUtils.isEmpty(dmsDiseCatalogParam.getStatus())){
criteria.andStatusEqualTo(dmsDiseCatalogParam.getStatus());
}
criteria.andStatusNotEqualTo(0);
example.setOrderByClause("id desc");//按id升序
List<DmsDiseCatalogResult> list = new ArrayList<>();
for (DmsDiseCatalog dmsDiseCatalog:dmsDiseCatalogMapper.selectByExample(example)) {
DmsDiseCatalogResult dmsDiseCatalogResult = new DmsDiseCatalogResult();
BeanUtils.copyProperties(dmsDiseCatalog,dmsDiseCatalogResult);
list.add(dmsDiseCatalogResult);
}
return list;
}
/**
* @Description:条件查询用户
* @param userVo 用户参数实体
* @param pageNum 当前页
* @param pageSize 每页显示的数据
* @author: Ryan
* @date 2018年11月10日
*/
@Override
public List<GemUser> findUserList(GemUserVo userVo, Integer pageNum, Integer pageSize) {
PageHelper.startPage(pageNum, pageSize);
Example example = new Example(GemUser.class);
Criteria createCriteria = example.createCriteria();
createCriteria.andIsNotNull("id");
String userName = userVo.getUserName();
if(GemFrameStringUtlis.isNotBlank(userName) && !userName.equalsIgnoreCase("null") && userName.length()>0) {
createCriteria.andLike("userName","%"+userName+"%");
}
String memberName = userVo.getMemberName();
if(GemFrameStringUtlis.isNotBlank(memberName) && !memberName.equalsIgnoreCase("null") && memberName.length()>0) {
createCriteria.andLike("memberName","%"+memberName+"%");
}
String phone = userVo.getPhone();
if(GemFrameStringUtlis.isNotBlank(phone) && !phone.equalsIgnoreCase("null") && phone.length()>0) {
createCriteria.andEqualTo("phone", phone);
}
String email = userVo.getEmail();
if(GemFrameStringUtlis.isNotBlank(email) && !email.equalsIgnoreCase("null") && email.length()>0) {
createCriteria.andEqualTo("email", email);
}
return userMapper.selectByExample(example);
}
public List<LitemallCouponUser> queryList(Integer userId, Integer couponId, Short status, Integer page, Integer size, String sort, String order) {
LitemallCouponUserExample example = new LitemallCouponUserExample();
LitemallCouponUserExample.Criteria criteria = example.createCriteria();
if (userId != null) {
criteria.andUserIdEqualTo(userId);
}
if(couponId != null){
criteria.andCouponIdEqualTo(couponId);
}
if (status != null) {
criteria.andStatusEqualTo(status);
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
if (!StringUtils.isEmpty(page) && !StringUtils.isEmpty(size)) {
PageHelper.startPage(page, size);
}
return couponUserMapper.selectByExample(example);
}
public List<LitemallFootprint> querySelective(String userId, String goodsId, Integer page, Integer size, String sort, String order) {
LitemallFootprintExample example = new LitemallFootprintExample();
LitemallFootprintExample.Criteria criteria = example.createCriteria();
if (!StringUtils.isEmpty(userId)) {
criteria.andUserIdEqualTo(Integer.valueOf(userId));
}
if (!StringUtils.isEmpty(goodsId)) {
criteria.andGoodsIdEqualTo(Integer.valueOf(goodsId));
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, size);
return footprintMapper.selectByExample(example);
}
public List<LitemallComment> querySelective(String userId, String valueId, Integer page, Integer size, String sort, String order) {
LitemallCommentExample example = new LitemallCommentExample();
LitemallCommentExample.Criteria criteria = example.createCriteria();
// type=2 是订单商品回复，这里过滤
criteria.andTypeNotEqualTo((byte) 2);
if (!StringUtils.isEmpty(userId)) {
criteria.andUserIdEqualTo(Integer.valueOf(userId));
}
if (!StringUtils.isEmpty(valueId)) {
criteria.andValueIdEqualTo(Integer.valueOf(valueId)).andTypeEqualTo((byte) 0);
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, size);
return commentMapper.selectByExample(example);
}
@SuppressWarnings("unchecked")
@Override
public PageResultAll<ConsoleMenu> getSystemRoles(int pageNum, int pageSize,
String order, String menuName, int _searchWord) {
ConsoleMenuExample example = new ConsoleMenuExample();
if(!StringUtils.isNull(menuName)){
example.createCriteria().andMenuNameLike("%"+menuName+"%").andParentIdEqualTo(_searchWord).andIsDeleteEqualTo("0");
}else{
example.createCriteria().andParentIdEqualTo(_searchWord).andIsDeleteEqualTo("0");
}
int count = ConsoleMenudao.countByExample(example);
PageResultAll<ConsoleMenu> consoleMenuPage = new PageResultAll<ConsoleMenu>(pageNum,pageSize,order);
PageHelper.startPage(pageNum,pageSize);
consoleMenuPage.setResult(ConsoleMenudao.selectByExample(example));
consoleMenuPage.setTotal(count);
return consoleMenuPage;
}
public Response list(Project project, PageRequest pageRequest) {
boolean needPaging = pageRequest.needPaging();
if (needPaging) {
PageHelper.startPage(pageRequest.getPageNum(), pageRequest.getPageSize());
}
List<Project> projects = selectByProject(project);
List<ProjectVo> projectVos = convertProjectsToProjectVos(projects);
if (needPaging) {
long total = Page.getTotal(projects);
return Response.success(Page.build(projectVos, total));
} else {
return Response.success(projectVos);
}
}
public List<LitemallFeedback> querySelective(Integer userId, String username, Integer page, Integer limit, String sort, String order) {
LitemallFeedbackExample example = new LitemallFeedbackExample();
LitemallFeedbackExample.Criteria criteria = example.createCriteria();
if (userId != null) {
criteria.andUserIdEqualTo(userId);
}
if (!StringUtils.isEmpty(username)) {
criteria.andUsernameLike("%" + username + "%");
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return feedbackMapper.selectByExample(example);
}
@Override
public Result queryPage(Map<String, Object> queryFilter, PageInfo<?> pageInfo) {
try {
PageHelper.startPage(pageInfo.getPageNum(), pageInfo.getPageSize());
List<E> list = query(queryFilter);
// 用PageInfo对结果进行包装
PageInfo page = new PageInfo(list);
// 分页信息封装
SimplePage simplePage = new SimplePage();
simplePage.setCurrentPageNum(page.getPageNum());
simplePage.setPageSize(page.getPageSize());
simplePage.setTotal(page.getTotal());
simplePage.setTotalPages(page.getPages());
// 返回结果封装
Map<String, Object> map = new HashMap<>();
map.put(Constants.LIST, page.getList());
map.put(Constants.PAGE, simplePage);
return Result.success(map);
} catch (Exception e) {
logger.error(MsgConstants.QUERY_FAIL, e);
throw new ServiceException(MsgConstants.QUERY_FAIL);
}
}
@Bean(name = "paraSqlSessionFactory")
public SqlSessionFactory paraSqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
logger.info("para sqlsession--" + this.paraDataSource().hashCode());
sqlSessionFactoryBean.setDataSource(this.paraDataSource());
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("dialect", "oracle");
properties.setProperty("pageSizeZero", "true");
properties.setProperty("reasonable", "false");
properties.setProperty("params", "pageNum=pageHelperStart;pageSize=pageHelperRows;");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "none");
pageHelper.setProperties(properties);
Interceptor[] interceptors = new Interceptor[] { pageHelper };
sqlSessionFactoryBean.setPlugins(interceptors);
return sqlSessionFactoryBean.getObject();
}
public List<LitemallOrder> queryByOrderStatus(Integer userId, List<Short> orderStatus, Integer page, Integer limit, String sort, String order) {
LitemallOrderExample example = new LitemallOrderExample();
example.setOrderByClause(LitemallOrder.Column.addTime.desc());
LitemallOrderExample.Criteria criteria = example.or();
criteria.andUserIdEqualTo(userId);
if (orderStatus != null) {
criteria.andOrderStatusIn(orderStatus);
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return litemallOrderMapper.selectByExample(example);
}
public List<DtsFootprint> querySelective(String userId, String goodsId, Integer page, Integer size, String sort,
String order) {
DtsFootprintExample example = new DtsFootprintExample();
DtsFootprintExample.Criteria criteria = example.createCriteria();
if (!StringUtils.isEmpty(userId)) {
criteria.andUserIdEqualTo(Integer.valueOf(userId));
}
if (!StringUtils.isEmpty(goodsId)) {
criteria.andGoodsIdEqualTo(Integer.valueOf(goodsId));
}
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, size);
return footprintMapper.selectByExample(example);
}
@RequestMapping(value = "/initInterceptor")
@ResponseBody
public Page<InterceptorView> initInterceptor(@RequestBody Map<String, String> map) {
Page<InterceptorView> page = new Page<>(map);
PageHelper.startPage(page.getPageNum(), page.getLength());
List<InterceptorInfo> interceptorLists = interceptorService.getList();
List<InterceptorView> interceptorViews = interceptorLists.stream().map(i -> {
InterceptorView view = new InterceptorView();
view.setId(i.getId());
view.setName(i.getName());
view.setDesc(i.getDesc());
view.setType(i.getType());
view.setContent(i.getContent());
view.setCreateTime(i.getCreateTime());
return view;
}).collect(Collectors.toList());
PageInfo<InterceptorInfo> pageInfo = new PageInfo<>(interceptorLists);
page.setDraw(page.getDraw());
page.setAaData(interceptorViews);
page.setRecordsTotal((int) pageInfo.getTotal());
page.setRecordsFiltered(page.getRecordsTotal());
return page;
}
/**
* 分页查询
*
* @param vo
* @return
*/
@Override
public PageInfo<User> findPageBreakByCondition(UserConditionVO vo) {
PageHelper.startPage(vo.getPageNumber(), vo.getPageSize());
List<SysUser> sysUsers = sysUserMapper.findPageBreakByCondition(vo);
if (CollectionUtils.isEmpty(sysUsers)) {
return null;
}
List<User> users = new ArrayList<>();
for (SysUser su : sysUsers) {
users.add(new User(su));
}
PageInfo bean = new PageInfo<SysUser>(sysUsers);
bean.setList(users);
return bean;
}
@Test
public void testSelectByViewWithoutSorts() {
DynamicQuery<ProductView> dynamicQuery = DynamicQuery.createQuery(ProductView.class)
.ignore(ProductView::getCategoryID)
.and(ProductView::getPrice, in(BigDecimal.valueOf(16), BigDecimal.valueOf(18)));
Map<String, Object> queryParamMap = dynamicQuery.toQueryParamMap();
List<ProductView> productViews = PageHelper.startPage(0, 2, false)
.doSelectPage(() -> northwindDao.getProductViewsByDynamic2(queryParamMap));
for (ProductView p : productViews) {
// categoryID ignore to select
assertEquals(null, p.getCategoryID());
assertEquals(true, StringUtils.isNotBlank(p.getProductName()));
assertEquals(true, StringUtils.isNotBlank(p.getCategoryName()));
}
}
/**
* 分页查询
*
* @param vo
* @return
*/
@Override
public PageInfo<Template> findPageBreakByCondition(TemplateConditionVO vo) {
PageHelper.startPage(vo.getPageNumber(), vo.getPageSize());
List<SysTemplate> list = sysTemplateMapper.findPageBreakByCondition(vo);
if (CollectionUtils.isEmpty(list)) {
return null;
}
List<Template> boList = new ArrayList<>();
for (SysTemplate sysTemplate : list) {
boList.add(new Template(sysTemplate));
}
PageInfo bean = new PageInfo<SysTemplate>(list);
bean.setList(boList);
return bean;
}
@Bean(name = "gjpaymentSqlSessionFactory")
public SqlSessionFactory gjpaymentSqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
logger.info("gjpayment sqlsession--" + this.gjpaymentDataSource().hashCode());
sqlSessionFactoryBean.setDataSource(this.gjpaymentDataSource());
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("dialect", "oracle");
properties.setProperty("pageSizeZero", "true");
properties.setProperty("reasonable", "false");
properties.setProperty("params", "pageNum=pageHelperStart;pageSize=pageHelperRows;");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "none");
pageHelper.setProperties(properties);
Interceptor[] interceptors = new Interceptor[] { pageHelper };
sqlSessionFactoryBean.setPlugins(interceptors);
return sqlSessionFactoryBean.getObject();
}
public List<LitemallCollect> queryByType(Integer userId, Byte type, Integer page, Integer limit, String sort, String order) {
LitemallCollectExample example = new LitemallCollectExample();
LitemallCollectExample.Criteria criteria = example.createCriteria();
if (type != null) {
criteria.andTypeEqualTo(type);
}
criteria.andUserIdEqualTo(userId);
criteria.andDeletedEqualTo(false);
if (!StringUtils.isEmpty(sort) && !StringUtils.isEmpty(order)) {
example.setOrderByClause(sort + " " + order);
}
PageHelper.startPage(page, limit);
return collectMapper.selectByExample(example);
}
@Override
public PageInfo<IssueNumVO> queryIssueByOptionForAgile(Long projectId, Long issueId, String issueNum, Boolean self, String content, PageRequest pageRequest) {
pageRequest.setSort(PageUtil.sortResetOrder(pageRequest.getSort(), SEARCH, new HashMap<>()));
//pageRequest.resetOrder("search", new HashMap<>());
IssueNumDTO issueNumDTO = null;
if (self) {
issueNumDTO = issueMapper.queryIssueByIssueNumOrIssueId(projectId, issueId, issueNum);
if (issueNumDTO != null) {
pageRequest.setSize(pageRequest.getSize() - 1);
}
}
PageInfo<IssueNumDTO> issueDOPage = PageHelper.startPage(pageRequest.getPage(), pageRequest.getSize(),
PageUtil.sortToSql(pageRequest.getSort())).doSelectPageInfo(() ->
issueMapper.queryIssueByOptionForAgile(projectId, issueId, issueNum, self, content));
if (self && issueNumDTO != null) {
issueDOPage.getList().add(0, issueNumDTO);
issueDOPage.setSize(issueDOPage.getSize() + 1);
}
return PageUtil.buildPageInfoWithPageInfoList(issueDOPage, issueAssembler.issueNumDoToDto(issueDOPage.getList(), projectId));
}
public Page<TopicStatsEntity> findByMultiTopic(Integer pageNum,
Integer pageSize,
String environment,
String tenant,
String namespace,
String persistent,
List<String> topicList, long timestamp) {
PageHelper.startPage(pageNum, pageSize);
return topicsStatsMapper.findByMultiTopic(environment, tenant, namespace, persistent, topicList, timestamp);
}