springboot+mybatis实现数据分页(三种方式) |
您所在的位置:网站首页 › list做分页查询 › springboot+mybatis实现数据分页(三种方式) |
项目准备
1.创建用户表 1.UserMapper接口 @Mapper @Repository public interface UserMapper { int selectCount(); List selectUserFindAll(); }2.整合mybatis(application.yaml) spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/crud?serverTimezone=UTC username: root password: hao20001010 mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.hao.springboot.entity3.测试dao层(成功) @SpringBootTest class SpringBootCrudEndApplicationTests { @Autowired UserMapper userMapper; @Test void contextLoads() { List users = userMapper.selectUserFindAll(); for(User user:users){ System.out.println(user); } } @Test void contextLoads2(){ } }4.编写service层 public interface UserService { int selectCount(); List selectUserByArray(int currentPage,int pageSize); } /** * @author:抱着鱼睡觉的喵喵 * @date:2020/12/26 * @description: */ @Service public class UserServiceImpl implements UserService { @Autowired UserMapper userMapper; @Override public int selectCount() { int count = userMapper.selectCount(); return count; } /** * 原始分页逻辑实现 * @param currentPage 当前页 * @param pageSize 每页的数量 * @return */ @Override public List selectUserByArray(int currentPage, int pageSize) { List users = userMapper.selectUserFindAll(); int count=selectCount(); int startCurrentPage=(currentPage-1)*pageSize; //开启的数据 int endCurrentPage=currentPage*pageSize; //结束的数据 int totalPage=count/pageSize; //总页数 if (currentPage>totalPage || currentPage return users.subList(startCurrentPage,endCurrentPage); } } }5.controller层 @RestController public class UserController { @Autowired UserService userService; @GetMapping("/user/{currentPage}/{pageSize}") public List selectFindPart(@PathVariable("currentPage") int currentPage, @PathVariable("pageSize") int pageSize){ List list = userService.selectUserByArray(currentPage, pageSize); if (list==null){ throw new UserNotExistException("访问出错!QWQ"); }else{ return list; } } }6.异常处理 public class UserNotExistException extends RuntimeException{ private static final long serialVersionUID = 1L; private String msg; public UserNotExistException(String msg) { super("user not exist"); this.msg=msg; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } }7.controller异常处理类 /** * @author:抱着鱼睡觉的喵喵 * @date:2020/12/26 * @description: */ @ControllerAdvice //处理controller层出现的异常 public class ControllerExceptionHandler { @ExceptionHandler(UserNotExistException.class) @ResponseBody @ResponseStatus(value = HttpStatus.INTERNAL_SERVER_ERROR) //状态码 public Map handlerUserNotExistException(UserNotExistException ex){ Map result=new HashMap(); result.put("msg", ex.getMsg()); result.put("message", ex.getMessage()); return result; } }8.访问http://localhost:8080/user/5/10出现如下 {“msg”:“访问出错!QWQ”,“message”:“user not exist”} 9.访问http://localhost:8080/user/2/3 出现如下 [{“id”:4,“userName”:“sky”,“password”:“789”},{“id”:5,“userName”:“nulls”,“password”:“tom”},{“id”:6,“userName”:“zsh”,“password”:“zsh”}] 总结: 缺点:数据库查询并返回所有的数据,而我们需要的只是极少数符合要求的数据。当数据量少时,还可以接受。当数据库数据量过大时,每次查询对数据库和程序的性能都会产生极大的影响。 二、通过sql语句进行分页操作1.在UserMapper接口中新增一个方法 @Mapper @Repository public interface UserMapper { //原生分页 int selectCount(); List selectUserFindAll(); //通过sql语句进行分页 List selectBySql(Map map); }2.UserService接口中新增方法,以及UserServiceImpl类中进行重写 public interface UserService { int selectCount(); /** * 原生分页 * @param currentPage * @param pageSize * @return */ List selectUserByArray(int currentPage,int pageSize); /** * 通过sql分页 * @param currentPage * @param pageSize * @return */ List selectUserBySql(int currentPage,int pageSize); } @Service public class UserServiceImpl implements UserService { @Autowired UserMapper userMapper; @Override public int selectCount() { int count = userMapper.selectCount(); return count; } /** * 原始分页逻辑实现 * @param currentPage * @param pageSize * @return */ @Override public List selectUserByArray(int currentPage, int pageSize) { List users = userMapper.selectUserFindAll(); int count=selectCount(); int startCurrentPage=(currentPage-1)*pageSize; //从第几个数据开始 int endCurrentPage=currentPage*pageSize; //结束的数据 int totalPage=count/pageSize; //总页数 if (currentPage>totalPage || currentPage return users.subList(startCurrentPage,endCurrentPage); } } /** *通过sql语句进行分页 */ @Override public List selectUserBySql(int currentPage, int pageSize) { Map map=new HashMap(); int startCurrentPage=(currentPage-1)*pageSize; //从第几个数据开始 int count=selectCount(); int totalPage=count/pageSize; //总页数 if (currentPage>totalPage || currentPage map.put("currentPage",startCurrentPage); map.put("pageSize",pageSize); List list = userMapper.selectBySql(map); return list; } } }3.controller层编写 @RestController public class UserController { @Autowired UserService userService; //Java原生实现分页模块 @GetMapping("/user/{currentPage}/{pageSize}") public List selectFindByJava(@PathVariable("currentPage") int currentPage, @PathVariable("pageSize") int pageSize){ List list = userService.selectUserByArray(currentPage, pageSize); if (list==null){ throw new UserNotExistException("访问出错!QWQ"); }else{ return list; } } //sql分页方法模块 @GetMapping("/user2/{currentPage}/{pageSize}") public List selectFindBySql(@PathVariable("currentPage") int currentPage, @PathVariable("pageSize") int pageSize){ List list = userService.selectUserBySql(currentPage,pageSize); if (list==null){ throw new UserNotExistException("访问出错!QWQ"); }else{ return list; } } }4.UserMapper.xml添加查询条件,使用limit进行分页 select * from user select count(*) from user select * from user limit #{currentPage} , #{pageSize}5.启动访问http://localhost:8080/user2/5/10 出现如下 {“msg”:“访问出错!QWQ”,“message”:“user not exist”} 接着正确访问http://localhost:8080/user2/2/2 [{“id”:3,“userName”:“tom”,“password”:“456”},{“id”:4,“userName”:“sky”,“password”:“789”}] 总结: 缺点:虽然这里实现了按需查找,每次检索得到的是指定的数据。但是每次在分页的时候都需要去编写limit语句,很冗余。而且不方便统一管理,维护性较差。所以我们希望能够有一种更方便的分页实现。 三、拦截器实现分页 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |