Mybatis 分页查询
该篇博客记录采用pagehelper分页插件实现Mybatis分页功能
一、依赖
- pom.xml
<!-- pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
二、配置
- application.yml
# pagehelper
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
- CURD通用接口封装
public interface CurdService<T> {
/**
* 分页查询
* 这里统一封装了分页请求和结果，避免直接引入具体框架的分页对象, 如MyBatis或JPA的分页对象
* 从而避免因为替换ORM框架而导致服务层、控制层的分页接口也需要变动的情况，替换ORM框架也不会
* 影响服务层以上的分页接口，起到了解耦的作用
* @param pageRequest 自定义，统一分页查询请求
* @return PageResult 自定义，统一分页查询结果
*/
PageResult findPage(PageRequest pageRequest);
}
- 分页请求封装
public class PageRequest {
/**
* 当前页码
*/
private int pageNum = 1;
/**
* 每页数量
*/
private int pageSize = 10;
/**
* 查询参数
*/
private Map<String, Object> params = new HashMap<>();
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public Map<String, Object> getParams() {
return params;
}
public void setParams(Map<String, Object> params) {
this.params = params;
}
public Object getParam(String key) {
return getParams().get(key);
}
}
- 分页结果封装
public class PageResult {
/**
* 当前页码
*/
private int pageNum;
/**
* 每页数量
*/
private int pageSize;
/**
* 记录总数
*/
private long totalSize;
/**
* 页码总数
*/
private int totalPages;
/**
* 分页数据
*/
private List<?> content;
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public long getTotalSize() {
return totalSize;
}
public void setTotalSize(long totalSize) {
this.totalSize = totalSize;
}
public int getTotalPages() {
return totalPages;
}
public void setTotalPages(int totalPages) {
this.totalPages = totalPages;
}
public List<?> getContent() {
return content;
}
public void setContent(List<?> content) {
this.content = content;
}
}
- 分页助手封装
public class MybatisPageHelper {
public static final String findPage = "findPage";
/**
* 分页查询, 约定查询方法名为 “findPage”
* @param pageRequest 分页请求
* @param mapper Dao对象，MyBatis的 Mapper
* @param args 方法参数
* @return
*/
public static PageResult findPage(PageRequest pageRequest, Object mapper) {
return findPage(pageRequest, mapper, findPage);
}
/**
* 调用分页插件进行分页查询
* @param pageRequest 分页请求
* @param mapper Dao对象，MyBatis的 Mapper
* @param queryMethodName 要分页的查询方法名
* @param args 方法参数
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static PageResult findPage(PageRequest pageRequest, Object mapper, String queryMethodName, Object... args) {
// 设置分页参数
int pageNum = pageRequest.getPageNum();
int pageSize = pageRequest.getPageSize();
PageHelper.startPage(pageNum, pageSize);
// 利用反射调用查询方法
Object result = ReflectionUtils.invoke(mapper, queryMethodName, args);
return getPageResult(pageRequest, new PageInfo((List) result));
}
/**
* 将分页信息封装到统一的接口
* @param pageRequest
* @param page
* @return
*/
private static PageResult getPageResult(PageRequest pageRequest, PageInfo<?> pageInfo) {
PageResult pageResult = new PageResult();
pageResult.setPageNum(pageInfo.getPageNum());
pageResult.setPageSize(pageInfo.getPageSize());
pageResult.setTotalSize(pageInfo.getTotal());
pageResult.setTotalPages(pageInfo.getPages());
pageResult.setContent(pageInfo.getList());
return pageResult;
}
}
- 反射相关辅助方法
public class ReflectionUtils {
/**
* 根据方法名调用指定对象的方法
* @param object 要调用方法的对象
* @param method 要调用的方法名
* @param args 参数对象数组
* @return
*/
public static Object invoke(Object object, String method, Object... args) {
Object result = null;
Class<? extends Object> clazz = object.getClass();
Method queryMethod = getMethod(clazz, method, args);
if(queryMethod != null) {
try {
result = queryMethod.invoke(object, args);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
} else {
try {
throw new NoSuchMethodException(clazz.getName() + " 类中没有找到 " + method + " 方法。");
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
return result;
}
/**
* 根据方法名和参数对象查找方法
* @param clazz
* @param name
* @param args 参数实例数据
* @return
*/
public static Method getMethod(Class<? extends Object> clazz, String name, Object[] args) {
Method queryMethod = null;
Method[] methods = clazz.getMethods();
for(Method method:methods) {
if(method.getName().equals(name)) {
Class<?>[] parameterTypes = method.getParameterTypes();
if(parameterTypes.length == args.length) {
boolean isSameMethod = true;
for(int i=0; i<parameterTypes.length; i++) {
Object arg = args[i];
if(arg == null) {
arg = "";
}
if(!parameterTypes[i].equals(args[i].getClass())) {
isSameMethod = false;
}
}
if(isSameMethod) {
queryMethod = method;
break ;
}
}
}
}
return queryMethod;
}
}
- HTTP返回结果封装
public class HttpResult {
private int code = 200;
private String msg;
private Object data;
public static HttpResult error() {
return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, "未知异常，请联系管理员");
}
public static HttpResult error(String msg) {
return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, msg);
}
public static HttpResult error(int code, String msg) {
HttpResult r = new HttpResult();
r.setCode(code);
r.setMsg(msg);
return r;
}
public static HttpResult ok(String msg) {
HttpResult r = new HttpResult();
r.setMsg(msg);
return r;
}
public static HttpResult ok(Object data) {
HttpResult r = new HttpResult();
r.setData(data);
return r;
}
public static HttpResult ok() {
return new HttpResult();
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
}
三、实现
- SysUserController.java
@RestController
@RequestMapping("/user")
public class SysUserController {
@Autowired
private SysUserService sysUserService;
@PostMapping(value="/findPage")
public HttpResult findPage(@RequestBody PageRequest pageRequest) {
return HttpResult.ok(sysUserService.findPage(pageRequest));
}
}
- SysUserService.java
public interface SysUserService extends CurdService<SysUser> {
}
- SysUserMapper.java
public interface SysUserMapper {
List<SysUser> findPage();
}
- SysUserServiceImpl.java
//经过对分页查询业务的封装，只需调用MybatisPageHelper.findPage(pageRequest, sysUserMapper)即可实现分页查询功能
@Service
public class SysUserServiceImpl implements SysUserService {
@Autowired
private SysUserMapper sysUserMapper;
@Override
public PageResult findPage(PageRequest pageRequest) {
return MybatisPageHelper.findPage(pageRequest, sysUserMapper);
}
}
- SysUserMapper.xml
<?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.coisini.admin.mapper.SysUserMapper">
<resultMap id="BaseResultMap" type="com.coisini.admin.entity.SysUser">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="nick_name" jdbcType="VARCHAR" property="nickName" />
<result column="avatar" jdbcType="VARCHAR" property="avatar" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="salt" jdbcType="VARCHAR" property="salt" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="mobile" jdbcType="VARCHAR" property="mobile" />
<result column="status" jdbcType="TINYINT" property="status" />
<result column="dept_id" jdbcType="BIGINT" property="deptId" />
<result column="create_by" jdbcType="VARCHAR" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="last_update_by" jdbcType="VARCHAR" property="lastUpdateBy" />
<result column="last_update_time" jdbcType="TIMESTAMP" property="lastUpdateTime" />
<result column="del_flag" jdbcType="TINYINT" property="delFlag" />
</resultMap>
<sql id="Base_Column_List">
id, name, nick_name, avatar, password, salt, email, mobile, status, dept_id, create_by,
create_time, last_update_by, last_update_time, del_flag
</sql>
<select id="findPage" resultMap="BaseResultMap">
select u.*, (select d.name from sys_dept d where d.id = u.dept_id) deptName from sys_user u
</select>
</mapper>
四、测试
- 关于如何集成Mybatis，参考：mybatis 生成代码配置 mybatis-generator:generate 的使用详解，前半部分有详细的POM代码
