分页查询

PageInfo(github)

导入依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.2</version>
</dependency>

controller

@ApiOperation("分页查询后台菜单")
    @RequestMapping(value = "/list/{parentId}", method = RequestMethod.GET)
    @ResponseBody
    public CommonResult<CommonPage<UmsMenu>> list(@PathVariable Long parentId,
                                                  @RequestParam(value = "pageSize", defaultValue = "5") Integer pageSize,
                                                  @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) {
        List<UmsMenu> menuList = menuService.list(parentId, pageSize, pageNum);
        PageInfo<UmsMenu> pageInfo = new PageInfo<>(menuList);
        return CommonResult.success(pageInfo);
    }

service

@Override
    public List<UmsMenu> list(Long parentId, Integer pageSize, Integer pageNum) {
        PageHelper.startPage(pageNum, pageSize);
        UmsMenuExample example = new UmsMenuExample();
        example.setOrderByClause("sort desc");
        example.createCriteria().andParentIdEqualTo(parentId);
        // 此处查询的结果集为第pageNum的数据
        return menuMapper.selectByExample(example);
    }

 

 

Page(MybatisPlus)

配置类

/**
 * 配置MP的分页插件
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

单表查询

    @GetMapping("/list")
    public Result<Page> pageDemo(int pageNum, int pageSize) {
        // 构造分页构造器
        Page pageInfo = new Page(pageNum, pageSize);
        // 构造条件构造器
        LambdaQueryWrapper<Employee> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(Employee::getUpdateTime);
        // 执行查询（page是内部函数）
        employeeService.page(pageInfo, queryWrapper);
        return Result.success(pageInfo);
    }

连表查询

	@Override
	public IPage<SbOnlineUser> queryOnLineUsers(int pageNo, int pageSize, SbWearableDevices sbWearableDevices, List<String> userIds) {
		IPage<SbOnlineUser> page = new Page<>(pageNo, pageSize);
		return userMapper.queryByOnLineUser(page, sbWearableDevices, userIds);
	}
    
    // 自定义SQL
    <select id="queryByOnLineUser" resultType="org.jeecg.modules.system.entity.SbOnlineUser">
		SELECT
			b.id,
			b.realname,
			b.email,
			b.sex,
			b.phone,
			b.work_no,
		    a.last_online_time,
		    a.last_offline_time,
		    b.depart_ids,
			TIMESTAMPDIFF(MINUTE, a.last_online_time, NOW()) as online_duration
		    ,IF(a.last_offline_time is null,-1,TIMESTAMPDIFF(MINUTE, a.last_offline_time , NOW())) as offline_duration
		FROM
			sb_wearable_devices a
				LEFT JOIN sys_user b ON a.`user`=b.id
		WHERE
		a.`status`=1
		<if test="sbWearableDevices.onlineStatus!=null">
			and a.online_status=#{sbWearableDevices.onlineStatus}
		</if>
		<if test="userIds!=null">
			and b.id in
			<foreach collection="userIds" item="id" open="(" separator="," close=")">
				#{id}
			</foreach>
		</if>
	</select>