<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.UserMapper">
<!-- 实体类:
public class User {
private Integer id;
private String name;
private Integer age;
private String email;
private Integer sex;
private Cat cat;
private List<Course> courseList;
}
public class Cat {
private String catname;
}
public class Course {
private String coursename;
private String teacher;
private Integer userid;
}
-->
<!-- 第一种分页方式:limit,借助Sql语句进行分页。
postman:
localhost:8080/limitPage
XxxController.java:
@RequestMapping("limitPage")
public void limitPage1(){
Integer pageNum = 2; // ⻚码
Integer pageSize = 3; // 每⻚显示记录条数
Integer startIndex = (pageNum - 1) * pageSize; // 起始下标
List<User> userList = userMapper.selectAllUserByLimitPage(startIndex, pageSize);
userList.forEach(user -> System.out.println(user));
}
XxxMapper.java:
List<User> selectAllUserByLimitPage(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);
效果:
User{id=4, name='Sandy', age=21, email='test4@baomidou.com', sex=null, cat=null, courseList=null}
User{id=5, name='Billie', age=24, email='test5@baomidou.com', sex=null, cat=null, courseList=null}
-->
<select id="selectAllUserByLimitPage" resultType="User">
select * from user limit #{startIndex},#{pageSize}
</select>
<!-- 第二种分页方式:自定义拦截器,拦截器分页。
拦截器功能的实现,在intercept方法中获取到select标签和sql语句的相关信息,拦截所有以ByPage结尾的select查询,并且统一在查询语句后面添加limit分页的相关语句,统一实现分页功能。
postman:
localhost:8080/selectAllUserByInterceptorByPage
XxxController.java:
@RequestMapping("selectAllUserByInterceptorByPage")
public void selectAllUserByInterceptorByPage() {
Map<String, Object> data = new HashMap();
data.put("currPage", 1);
data.put("pageSize", 4);
List<User> userList = userMapper.selectAllUserByInterceptorByPage(data);
userList.forEach(user -> System.out.println(user));
}
XxxMapper.java:
List<User> selectAllUserByInterceptor(Map<String,Object> data);
-->
<!--xml文件的select语句-->
<select id="selectAllUserByInterceptorByPage" parameterType="map" resultType="User">
select * from user
</select>
<!-- 第三种分页方式:PageHelper插件,拦截器分页。
limit方法获取数据不难,难的是获取分⻚相关的数据⽐较难。可以借助mybatis的PageHelper插件。
使⽤PageHelper插件进⾏分⻚,更加的便捷。
准备工作:
第⼀步:pom.xml,引⼊依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.1</version>
</dependency>
第⼆步:mybatis-config.xml⽂件中配置插件,typeAliases标签下⾯进⾏配置:
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
第二步:application.yml中配置插件
# PageHelper分页插件
pagehelper:
helperDialect: mysql
supportMethodsArguments: true
params: count=countSql
postman:
localhost:8080/pageHelperPage
XxxController.java:
@RequestMapping("pageHelperPage")
public void pageHelperPage(){
// 开启分⻚:在查询语句之前开启分⻚功能。
PageHelper.startPage(2, 2);
// 执⾏查询语句
List<User> userList = userMapper.selectAllUserByPageHelperPage();
// 获取分⻚信息对象:在查询语句之后封装PageInfo对象。(PageInfo对象将来会存储到request域当中。在⻚⾯上展示。)
PageInfo<User> pageInfo = new PageInfo<>(userList, 5);
System.out.println(pageInfo);
}
XxxMapper.java:
List<User> selectAllUserByPageHelperPage();
-->
<select id="selectAllUserByPageHelperPage" resultType="User">
select * from user
</select>
<!-- 第四种分页方式:RowBounds
RowBounds本质就是封装了limit,我们只需要在业务层关注分页即可,,无须再传入指定数据。
Mybatis内置了一个专门处理分页的类: RowBounds, 我们使用它可以轻松完成分页。
分页查询所有用户,通过自带的RowBounds
postman:
localhost:8080/pageRowBoundsPage
XxxController.java:
@RequestMapping("pageRowBoundsPage")
public void pageRowBoundsPage(){
int currPage = 3;
int pageSize = 2;
//注意:currPage和start别搞错了,一个表示当前页码,一个是从第几行读取记录
int start = (currPage-1) *pageSize;//计算从第几行读取记录
RowBounds rowBounds = new RowBounds(start,pageSize);
List<User> userList = userMapper.selectAllUserByRowBounds(rowBounds);
userList.forEach(user -> System.out.println(user));
}
XxxMapper.java:
List<User> selectAllUserByRowBounds(RowBounds rowBounds);
-->
<select id="selectAllUserByRowBounds" resultType="User">
select * from user
</select>
</mapper>
<!--
(1)报错:pagehelper分页不生效
不能引入pagehelper,因为这样会导致分页插件不起作用,除非在mybatisConfig.xml文件中再加入配置
(2)报错:版本冲突
版本冲突:http://www.yiduhao.com/index.php/index/news/news_detail.html?id=127
(3)报错:Invalid bound statement (not found): ] with root cause
mapper-locations: classpath:mapper/*.xml配置:
(4)报错:不能用类别名
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #日志
type-aliases-package: com.pojo #类别名
mapper-locations: classpath:mapper/*.xml #配置XxxMapper.xml所在路径
(5)报错:spring boot中interceptor拦截器未生效的解决
本次解决的方案:@Component
以后遇到大麻烦可以参考:http://events.jianshu.io/p/3309899ec399
(6)参考:
dljd - 老杜 - MyBatis讲义
https://mikechen.cc/23836.html#%E4%BD%BF%E7%94%A8PageHelper%E6%8F%92%E4%BB%B6%E5%88%86%E9%A1%B5
(7)更多地了解
(PageInfo属性表、PageInfo类的使用——Java Page分页显示、MyBatis分页插件-PageHelper的配置与应用)
-->
附:项目的目标结构和关键代码
application.yml文件:
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.203.5.185:3306/fLearn?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
username: root
password: MySQL#567890
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
type-aliases-package: com.pojo
mapper-locations: classpath:mapper/*.xml
# PageHelper分页插件
pagehelper:
helperDialect: mysql
reasonable: false
supportMethodsArguments: true
params: count=countSql
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>boot_recieve_data</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>boot_recieve_data</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>8</java.version>
<mybatis-plus-boot-starter.version>3.3.2</mybatis-plus-boot-starter.version>
<druid.version>1.2.8</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus-boot-starter.version}</version>
</dependency>
<!-- Mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.46</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory><!--所在的目录-->
<includes>
<include>**/*.properties</include><!--包括.properties、.xml文件都会扫描到-->
<include>**/*.xml</include>
</includes>
<filtering> false </filtering>
</resource>
</resources>
</build>
</project>