JarCastingpager
MyBatis分页插件，支持单表分页，多表关联查询的分页
目前只在MySQL和H2数据库上测试通过，其他数据库理论上也支持
* 注意：此插件与其他分页插件有冲突，比如和PageHelper，只能二选一
* 如果你的查询结果和预期不一样，那么很可能是有其他分页插件冲突了
使用方法
- SpringBoot引入依赖
<dependency>
<groupId>com.github.dreamroute</groupId>
<artifactId>pager-spring-boot-starter</artifactId>
<version>latest version</version>
</dependency>
当前最新版本，点击查看
- 在Mapper接口方法上添加@Pager注解，并将接口的参数改为类型为
PageRequest<T>
- @Pager的属性，distinctBy（默认是"id"），用在多表查询的主表去重，一般来说是主表别名+主键字段，如：
select * from user u left join addr a on u.id = a.uid where xxx order by u.id，那么@Pager(distinctBy = u.id)- 单表不需要配置distinctBy属性
- 定义请求参数
PageRequest<T> request = ...
- 调用方法，例如:
PageResponse<User> result = Pager.page(request, userMapper::selectXxx); - 完成接入，你无需编写统计SQL语句，也无需关心多表联查数据分页不准确的问题，统统插件帮你完成
分页原理
单表
- 原始SQL：
SELECT
*
FROM
smart_user
WHERE
NAME = #{param.name}
- 被插件拦截，自动插入分页信息之后的SQL：
SELECT
*
FROM
smart_user
WHERE
NAME = ?
LIMIT ?, ?
- 被插件拦截，自动生成统计SQL：
SELECT
COUNT( * ) _$count$_
FROM
( SELECT * FROM smart_user WHERE NAME = ? ) _$_t
多表
- 原始SQL：
SELECT
u.*,
a.id aid,
a.NAME aname,
a.user_id
FROM
smart_user u
LEFT JOIN smart_addr a ON u.id = a.user_id
WHERE
u.NAME = #{param.name} and a.user_id = #{param.userId}
ORDER BY
u.id DESC,
u.NAME ASC
- 被插件拦截，自动插入分页信息之后的SQL：
SELECT
u.*,
a.id aid,
a.NAME aname,
a.user_id
FROM
smart_user u
LEFT JOIN smart_addr a ON u.id = a.user_id
WHERE
u.id IN (
SELECT
u.id
FROM
(
SELECT DISTINCT
u.id,
u.NAME
FROM
smart_user u
LEFT JOIN smart_addr a ON u.id = a.user_id
WHERE
u.NAME = ?
AND a.user_id = ?
ORDER BY
u.id DESC,
u.NAME ASC
LIMIT ?, ?
) u
)
AND u.NAME = ?
AND a.user_id = ?
ORDER BY
u.id DESC,
u.NAME ASC
- 被插件拦截，自动生成统计SQL：
SELECT
count( DISTINCT u.id ) __count__
FROM
smart_user u
LEFT JOIN smart_addr a ON u.id = a.user_id
WHERE
u.NAME = ?
AND a.user_id = ?
举例
下列举的例都在本工程下的pager-sample中，可以clone下来运行单元测试
建表：
CREATE TABLE `smart_user`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`password` varchar(32) DEFAULT '123456',
`version` bigint(20) DEFAULT NULL,
`phone_no` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `smart_addr`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`user_id` bigint(20),
PRIMARY KEY (`id`)
);
CREATE TABLE `smart_city`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`addr_id` bigint(20),
PRIMARY KEY (`id`)
);
单表分页
请求参数对象：
@Data
public class User {
private String name;
}
UserMapper接口
public interface UserMapper {
@Pager
List<User> selectOneTable(PageRequest<User> request);
}
SQL语句
<select id="selectOneTable" resultType="com.github.dreamroute.pager.starter.sample.entity.User">
select * from smart_user where name = #{param.name}
</select>
请求
@Test
void selectOneTableTest() {
PageRequest<User> request = new PageRequest<>();
request.setPageNum(1);
request.setPageSize(2);
User user = new User();
user.setName("w.dehai");
request.setParam(user);
PageResponse<User> result = Pager.page(request, userMapper::selectOneTable);
System.err.println(result);
}
多表分页
请求参数对象：
@Data
public class City {
private Long id;
private String name;
private Long addrId;
}
@Data
public class Addr {
private Long id;
private String name;
private Long userId;
}
请求参数对象：
@Data
public class User {
private Long id;
private String name;
private String password;
private String phoneNo;
private Long version;
}
编写Mapper接口，并且给接口添加Pager注解：
public interface UserMapper {
@Pager(distinctBy = "u.id")
List<SelectFromTwoTablesResp> selectFromTwoTables(PageRequest<SelectFromTwoTables> request);
@Pager(distinctBy = "u.id")
List<SelectFromThreeTablesResp> selectFromThreeTables(PageRequest<SelectFromThreeTables> request);
}
SQL语句：
<select id="selectFromTwoTables" resultMap="twoTablesResultMap">
select u.*, a.id aid, a.name aname, a.user_id from smart_user u left join smart_addr a on u.id = a.user_id where u.name = #{param.name} and a.user_id = #{param.userId} order by u.id desc, u.name asc
</select>
<resultMap id="twoTablesResultMap" type="com.github.dreamroute.pager.starter.sample.dto.SelectFromTwoTablesResp">
<id column="id" property="id" />
<result column="name" property="name"/>
<collection property="addrs" ofType="addr">
<id column="aid" property="id"/>
<result column="aname" property="name"/>
<result column="user_id" property="userId"/>
</collection>
</resultMap>
<select id="selectFromThreeTables" resultMap="threeTablesResultMap">
select u.*, a.id aid, a.name aname, a.user_id, c.id cid, c.name cname, c.addr_id from smart_user u left join smart_addr a on u.id = a.user_id left join smart_city c on a.id = c.addr_id
where u.name = #{param.name} and a.user_id = #{param.userId} and c.name = #{param.cityName} order by u.id desc, u.name asc
</select>
<resultMap id="threeTablesResultMap" type="com.github.dreamroute.pager.starter.sample.dto.SelectFromThreeTablesResp">
<id column="id" property="id" />
<result column="name" property="name"/>
<collection property="addrs" ofType="addr">
<id column="aid" property="id"/>
<result column="aname" property="name"/>
<result column="user_id" property="userId"/>
<collection property="cities" ofType="city">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
<result column="addr_id" property="addrId"/>
</collection>
</collection>
</resultMap>
请求
@Test
void selectFromTwoTablesTest() {
PageRequest<SelectFromTwoTables> request = new PageRequest<>();
request.setPageNum(1);
request.setPageSize(2);
SelectFromTwoTables param = new SelectFromTwoTables();
param.setName("w.dehai");
param.setUserId(1L);
request.setParam(param);
PageResponse<SelectFromTwoTablesResp> result = Pager.page(request, userMapper::selectFromTwoTables);
System.err.println(result);
}
@Test
void selectFromThreeTablesTest() {
PageRequest<SelectFromThreeTables> request = new PageRequest<>();
request.setPageNum(1);
request.setPageSize(2);
SelectFromThreeTables param = new SelectFromThreeTables();
param.setName("w.dehai");
param.setUserId(1L);
param.setCityName("成都");
request.setParam(param);
PageResponse<SelectFromThreeTablesResp> result = page(request, userMapper::selectFromThreeTables);
System.err.println(result);
}
得到的结果：
@Data
public class PageResponse<T> {
private int pageNum;
private int pageSize;
private long totalNum;
private List<T> data;
}