分页sql
select * from 表名 limit 0,20;//第一页
select * from 表名 limit 20,20;//第二页
select * from 表名 limit 40,20;//第三页
需要PageNo(页码),PageSize(每页展示条数)作为方法入参
实现分页主要是对PageNo的处理,PageSize都是20
第一页的PageNo是(1-1)*20,sql是这样的select * from 表名 limit 0,20;
第二页的PageNo是(2-1)*20,sql是这样的select * from 表名 limit 20,20;
第三页的PageNo是(3-1)*20,sql是这样的select * from 表名 limit 40,20;
通过规律发现PageNo可以写为(PageNo-1)*PageSize作为我们分页方法的入参即可
Controller层
@RestController
@RequestMapping("/user")
@Validated
public class UserController {
@Autowired
private UserService userService;
/**
* 用户信息分页查询
* @param pageSize
* @param pageNo
* @return
*/
@RequestMapping("/queryrPage")
public Resp queryByUserPage(@RequestParam(value = "pageSize") Integer pageSize,
@RequestParam(value = "pageNo") Integer pageNo) {
List<SysUserDO> sysUser = userService.queryByPage((pageNo - 1) * pageSize, pageSize);
if (sysUser != null && sysUser.size() > 0) {
return Resp.success(sysUser);
} else {
return Resp.success("");
}
}
}
Service层
/**
* 用户服务
*/
public interface UserService {
/**
* 用户信息分页查询
* @return
*/
List<SysUserDO> queryByPage(Integer pageNo, Integer pageSize);
}
业务实现
@Service
public class UserServiceImpl implements UserService {
@Autowired
private SysUserDOMapper sysUserDOMapper;
/**
* 用户信息分页查询
*
* @param pageSize
* @param pageNo
* @return
*/
@Override
public List<SysUserDO> queryByPage(Integer pageNo, Integer pageSize) {
return sysUserDOMapper.queryByPage(pageNo, pageSize);
}
}
Mapper
public interface SysUserDOMapper extends BaseMapper<SysUserDO> {
List<SysUserDO> queryByPage(@Param("pageNo")Integer pageNo, @Param("pageSize")Integer pageSize);
}
Mapper.xml
<select id="queryByPage" resultMap="BaseResultMap">
select * from user_base limit #{pageNo},#{pageSize}
</select>