MyBatis如何使用PageHelper实现分页查询

 -- 判断数据表是否存在，存在则删除 DROP TABLE IF EXISTS tb_user; -- 创建“用户信息”数据表 CREATE TABLE IF NOT EXISTS tb_user ( user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号', user_name VARCHAR(50) NOT NULL COMMENT '用户姓名', province VARCHAR(50) NOT NULL COMMENT '省份', create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间' ) COMMENT = '用户信息表'; -- 添加数据 INSERT INTO tb_user(user_name,province) VALUES ('pan_junbiao的博客_01','广东省'),('pan_junbiao的博客_02','黑龙江省'),('pan_junbiao的博客_03','山东省'),('pan_junbiao的博客_04','安徽省'),('pan_junbiao的博客_05','黑龙江省'), ('pan_junbiao的博客_06','江苏省'),('pan_junbiao的博客_07','黑龙江省'),('pan_junbiao的博客_08','广东省'),('pan_junbiao的博客_09','陕西省'),('pan_junbiao的博客_10','广东省'), ('pan_junbiao的博客_11','广东省'),('pan_junbiao的博客_12','江苏省'),('pan_junbiao的博客_13','陕西省'),('pan_junbiao的博客_14','安徽省'),('pan_junbiao的博客_15','山东省'), ('pan_junbiao的博客_16','陕西省'),('pan_junbiao的博客_17','安徽省'),('pan_junbiao的博客_18','江苏省'),('pan_junbiao的博客_19','黑龙江省'),('pan_junbiao的博客_20','安徽省'), ('pan_junbiao的博客_21','江苏省'),('pan_junbiao的博客_22','广东省'),('pan_junbiao的博客_23','安徽省'),('pan_junbiao的博客_24','陕西省'),('pan_junbiao的博客_25','广东省'), ('pan_junbiao的博客_26','广东省'),('pan_junbiao的博客_27','安徽省'),('pan_junbiao的博客_28','山东省'),('pan_junbiao的博客_29','山东省'),('pan_junbiao的博客_30','黑龙江省'), ('pan_junbiao的博客_31','广东省'),('pan_junbiao的博客_32','江苏省'),('pan_junbiao的博客_33','陕西省'),('pan_junbiao的博客_34','安徽省'),('pan_junbiao的博客_35','山东省');

  com.github.pagehelperpagehelper-spring-boot-starter1.2.13 mysqlmysql-connector-java8.0.20

 spring: #DataSource数据源 datasource: url: jdbc:mysql://localhost:3306/db_admin?useSSL=false& username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver #MyBatis配置 mybatis: type-aliases-package: com.pjb.entity #别名定义 configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #指定 MyBatis 所用日志的具体实现，未指定时将自动查找 map-underscore-to-camel-case: true #开启自动驼峰命名规则（camel case）映射 lazy-loading-enabled: true #开启延时加载开关 aggressive-lazy-loading: false #将积极加载改为消极加载（即按需加载）,默认值就是false lazy-load-trigger-methods: "" #阻挡不相干的操作触发，实现懒加载 cache-enabled: true #打开全局缓存开关（二级环境），默认值就是true #MyBatis使用pageHelper分页 pagehelper: helper-dialect: mysql reasonable: true support-methods-arguments: true

 package com.pjb.entity; import org.springframework.stereotype.Component; import java.util.Date; /** * 用户信息实体类 * @author pan_junbiao **/ @Component public class UserInfo { private int userId; //用户编号 private String userName; //用户姓名 private String province; //省份 private Date createTime; //注册时间 //省略getter与setter方法... @Override public String toString() { return "编号：" + this.getUserId() +" 姓名：" + this.getUserName() + " 省份：" + this.getProvince(); } }

 package com.pjb.entity; /** * 用户查询条件类 * @author pan_junbiao **/ public class UserSearchParam { private String userName; //用户姓名 private String province; //省份 //省略getter与setter方法... }

 package com.pjb.mapper; import com.pjb.entity.UserInfo; import com.pjb.entity.UserSearchParam; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.SelectProvider; import org.apache.ibatis.jdbc.SQL; import org.springframework.stereotype.Repository; import java.util.List; /** * 用户信息Mapper动态代理接口 * @author pan_junbiao **/ @Mapper @Repository public interface UserMapper { /** * 查询用户列表 */ @SelectProvider(type = UserSqlBuilder.class, method = "searchUserList") public List searchUserList(UserSearchParam param); //建议将SQL Builder以映射器接口内部类的形式进行定义 public class UserSqlBuilder { public String searchUserList(UserSearchParam param) { return new SQL() { { SELECT("*"); FROM("tb_user"); WHERE("1 = 1"); if(param!=null) { //用户姓名 if(param.getUserName()!=null && param.getUserName().length()>0) { WHERE("user_name like '%${userName}%'"); } //省份 if(param.getProvince()!=null && param.getProvince().length()>0) { WHERE("province = #{province}"); } } } }.toString(); } } }

 @Autowired private UserMapper userMapper; /** * 分页查询用户列表 * @author pan_junbiao */ @Test public void searchUserByParam() { int pageIndex = 2; ///获取第2页的数据 int pageSize = 10; //每页10条数据 String orderBy = "create_time ASC"; //排序 //分页信息 PageHelper.startPage(pageIndex, pageSize, orderBy); //查询条件类 UserSearchParam userSearchParam = new UserSearchParam(); //userSearchParam.setUserName("pan_junbiao的博客"); //查询条件1 //userSearchParam.setProvince("广东省"); //查询条件2 //执行分页查询 PageInfo userInfoPage = new PageInfo(userMapper.searchUserList(userSearchParam)); //打印用户列表 System.out.println("\n"); List userInfoList = userInfoPage.getList(); userInfoList.stream().forEach(System.out::println); //打印分页信息 System.out.println("当前页码：第" + userInfoPage.getPageNum() + "页"); System.out.println("分页大小：每页" + userInfoPage.getPageSize() + "条"); System.out.println("数据总数：共" + userInfoPage.getTotal() + "条"); System.out.println("总页数：共" + userInfoPage.getPages() + "页"); }

  com.github.pagehelperpagehelper4.1.4

 public class EasyUIDataGridResult implements Serializable { private long total; private List rows; public long getTotal() { return total; } public void setTotal(long total) { this.total = total; } public List getRows() { return rows; } public void setRows(List rows) { this.rows = rows; } }

 PageHelper.startPage(1, 10); List list; if(param1 != null){ list = countryMapper.selectIf(param1); } else { list = new ArrayList(); }

 List list; if(param1 != null){ PageHelper.startPage(1, 10); list = countryMapper.selectIf(param1); } else { list = new ArrayList(); }

 PageHelper.clearPage();