很多博客或者自学网站都有讲解 mybatis 如何使用,但是往往忽略了如何使用 mybatis 做分页查询。分页查询是开发中一项重要的技能,处理好的话能让开发简化很多,处理不好就要重复造轮子了。
我们先下载之前课程学习的代码:https://pan.baidu.com/s/1Ly7cH3Vq0tCb1cPS32-A_A 提取码:4ntj
OK,既然要分页,我们先搞好分页需要的类,为了方便,统一都放在 config 包下创建的 mybatis 包下。
如图,我们依次按顺序创建类:PageQuery(用于数据库分页查询的实体)、SearchInfoUtil(用于构造数据库分页的实体、封装前端返回参数的工具类)、SearchInterceptor(分页查询的拦截器,拦截每一次查询)、SQLHelper(用于查询总数和构建正确的分页查询语句工具类)。
PageQuery 类完整代码:
package com.study.config;
import java.util.HashMap;
import java.util.Map;
/**
* @author biandan
* @description 对应操作数据库分页的实体
* @signature 让天下没有难写的代码
* @create 2021-05-13 下午 11:22
*/
public class PageQuery {
private static final long serialVersionUID = 6735895291649236251L;
private int pageNumber;//当前页码
private int pageSize;//每页显示的最大记录数
private int total;//记录总数
private int totalPages;//总页数
private int startIndex;//开始查询的位置
private int endIndex;//结束查询的位置
private Map<String, Object> queryParams = new HashMap<>();//搜索条件
public void setTotal(int total) {
this.total = total;
//如果当前页码为负数,则置为 1
if(this.pageNumber <= 0){
this.pageNumber = 1;
}
//计算总页数,先判断 总记录数 ÷ 每页显示记录数 是否可用整除
if(this.total % this.pageSize == 0){
this.totalPages = this.total % this.pageSize;
}else {
this.totalPages = (this.total % this.pageSize)+1;
}
//判断当前页是否大于总页数
if(this.pageNumber > this.totalPages){
this.pageNumber = 1;
}
//起始位置的计算
this.startIndex = (this.pageNumber - 1) * pageSize;
//结束位置的计算
if(this.totalPages == 0){
this.endIndex = 0;
}else{
this.endIndex = this.startIndex + this.pageSize -1;
}
}
//重写 toString 方法
@Override
public String toString() {
return "PageQuery{" +
"pageNumber=" + pageNumber +
", pageSize=" + pageSize +
", total=" + total +
", totalPages=" + totalPages +
", startIndex=" + startIndex +
", endIndex=" + endIndex +
", queryParams=" + queryParams +
'}';
}
//********************* 以下是 get、set 方法 ************************//
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotal() {
return total;
}
public int getTotalPages() {
return totalPages;
}
public void setTotalPages(int totalPages) {
this.totalPages = totalPages;
}
public int getStartIndex() {
return startIndex;
}
public void setStartIndex(int startIndex) {
this.startIndex = startIndex;
}
public int getEndIndex() {
return endIndex;
}
public void setEndIndex(int endIndex) {
this.endIndex = endIndex;
}
public Map<String, Object> getQueryParams() {
return queryParams;
}
public void setQueryParams(Map<String, Object> queryParams) {
this.queryParams = queryParams;
}
}
说明:
我们重写了 toString 方法,以及对 setTotal 函数进行了逻辑的处理,其它属性的 get、set 方法不变。
SearchInfoUtil 类完整代码:
package com.study.config;
import org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author biandan
* @description 分页工具类
* @signature 让天下没有难写的代码
* @create 2021-05-13 下午 11:22
*/
public class SearchInfoUtil {
public static final int DEFAULT_PAGE_NUMBER = 1;//默认查询页码
public static final int DEFAULT_PAGE_SIZE = 10;//默认查询的记录数
//获取并构造分页查询的入参实体
public static PageQuery getPageQuery(Map<String,Object> queryParams,int page,int rows){
PageQuery pageQuery = new PageQuery();
//先设置默认值,后续会覆盖
pageQuery.setPageNumber(DEFAULT_PAGE_NUMBER);
pageQuery.setPageSize(DEFAULT_PAGE_SIZE);
try{
if(page > 0) pageQuery.setPageNumber(page);
if(rows > 0) pageQuery.setPageSize(rows);
//查询参数
if(!CollectionUtils.isEmpty(queryParams)){
pageQuery.setQueryParams(queryParams);
}
}catch (Exception e){
e.printStackTrace();
}
return pageQuery;
}
//构造成调用者需要的格式,返回给前端
public static Map<String,Object> getResult(PageQuery pageQuery, List<?> list){
Map<String,Object> map = new HashMap<>();
map.put("rows",list);
map.put("total",pageQuery.getTotal());
return map;
}
}
说明:
返回给前端的 getResult 方法里,我们注意到 map 的两个 key 分别是:rows 和 total,我们这里是对应 EasyUI 的分页返回标准数据对象的:http://www.jeasyui.net/plugins/183.html
如果是别的前端框架,需要按照需要来修改即可。
SQLHelper 类完整代码:
package com.study.config;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* @author biandan
* @description
* @signature 让天下没有难写的代码
* @create 2021-05-13 下午 11:22
*/
public class SQLHelper {
/**
* 查询总纪录数
* @param sql SQL语句
* @param mappedStatement mapped
* @param parameterObject 参数
* @param boundSql boundSql
* @return 总记录数
* @throws SQLException sql查询错误
*/
public static int getCount(String sql, MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql) throws SQLException {
String countSql = "select count(*) from (" + sql + ") as tmp_count";
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;
try {
//获取数据库连接
connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
//预处理
ps = connection.prepareStatement(countSql);
//将值赋值给预处理的对应参数位置
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
SQLHelper.setParameters(ps, mappedStatement, countBS, parameterObject);
rs = ps.executeQuery();//执行查询
if (rs.next()) {
count = rs.getInt(1);//返回的第一条记录就是总数量
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (connection != null) connection.close();
}
return count;
}
/**
* 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
*
* @param ps 表示预编译的 SQL 语句的对象。
* @param mappedStatement MappedStatement
* @param boundSql SQL
* @param parameterObject 参数对象
* @throws java.sql.SQLException 数据库异常
*/
@SuppressWarnings("unchecked")
public static void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (parameterMappings != null) {
Configuration configuration = mappedStatement.getConfiguration();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
MetaObject metaObject = parameterObject == null ? null :
configuration.newMetaObject(parameterObject);
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
PropertyTokenizer prop = new PropertyTokenizer(propertyName);
if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
} else if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null) {
value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
}
} else {
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
@SuppressWarnings("rawtypes")
TypeHandler typeHandler = parameterMapping.getTypeHandler();
if (typeHandler == null) {
throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
}
typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
}
}
}
}
}
SearchInterceptor 拦截器完整代码:
package com.study.config;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import java.util.Properties;
/**
* @author biandan
* @description 分页查询拦截器
* @signature 让天下没有难写的代码
* @create 2021-05-13 下午 11:22
*/
@Component
@Intercepts({@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class SearchInterceptor implements Interceptor {
//拦截器执行的内容
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object param = invocation.getArgs()[1];
//判断是否要进行分页
if (param != null && param instanceof PageQuery) {
PageQuery pageQuery = (PageQuery) param;
//获取绑定的sql,并将参数对象与sql语句的#{}一一对应
BoundSql boundSql = mappedStatement.getBoundSql(pageQuery.getQueryParams());
Object paramObject = boundSql.getParameterObject();
//获取原始的数据库语句
String originSql = boundSql.getSql().trim();
System.out.println("originSql=" + originSql);
//获取总数
int total = SQLHelper.getCount(originSql, mappedStatement, paramObject, boundSql);
pageQuery.setTotal(total);
//分页查询
String pageSql = originSql + " limit " + pageQuery.getStartIndex() + "," + pageQuery.getPageSize();
invocation.getArgs()[2] = new RowBounds(RowBounds.NO_ROW_OFFSET, RowBounds.NO_ROW_LIMIT);
BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), pageSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
invocation.getArgs()[0] = newMs;
invocation.getArgs()[1] = pageQuery.getQueryParams();
}
return invocation.proceed();
}
//用当前这个拦截器生成对目标target的代理,把目标target和拦截器this传给了包装函数
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
//用于设置额外的参数,参数配置在拦截器的Properties节点里
@Override
public void setProperties(Properties properties) {}
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(),
ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null) {
for (String keyProperty : ms.getKeyProperties()) {
builder.keyProperty(keyProperty);
}
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.cache(ms.getCache());
return builder.build();
}
public static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
说明:需要增加 Spring 注解,以便让 Spring 容器进行管理,否则拦截器无效。我们这里增加了通用的注解:@Component
OK,到这里我们就完成了分页需要的配置信息。接下来,以分页查询学生信息为学习切入点,讲解如何使用我们自己开发的分页查询。
我们在 StudentEntityMapper.xml 文件里增加一个节点:
<!-- 分页查询 -->
<select id="findByPage" resultMap="BaseResultMap">
select id, student_no, student_name, introduce
from t_student
<where>
<if test="studentNo != null and studentNo != ''">
and student_no = #{studentNo, jdbcType=VARCHAR}
</if>
<if test="studentName != null and studentName != ''">
and student_name like CONCAT('%',#{studentName,jdbcType=VARCHAR}, '%')
</if>
</where>
</select>
很多人有疑问,分页查询没有 limit 关键字的吗?那它是怎么实现分页的呢?
回答一下这个问题,因为我们的类 SearchInterceptor 实现了 mybatis 的拦截器,拦截所有执行 mybatis 的语句,如果参数中包含了我们自定义的类:PageQuery 的话,就会在 SQL 语句后拼接 limit 关键字。这样就实现了我们想要的分页效果。
然后我们在 dao 层的 StudentEntityMapper 增加 findByPage 接口:
List<StudentEntity> findByPage(PageQuery pageQuery);
然后在业务层 StudentService 增加接口:
Map<String, Object> findByPage(Map<String,Object> queryParams, int page, int rows);
业务层的实现类 StudentServiceImpl 增加分页的实现方法:
@Override
public Map<String, Object> findByPage(Map<String,Object> queryParams, int page, int rows) {
Map<String,Object> map = new HashMap<>();
try{
PageQuery pageQuery = SearchInfoUtil.getPageQuery(queryParams,page,rows);
List<StudentEntity> list = studentEntityMapper.findByPage(pageQuery);
map = SearchInfoUtil.getResult(pageQuery,list);//封装结果
}catch (Exception e){
e.printStackTrace();
}
return map;
}
然后编写 Controller 层的 StudentController,增加调用分页的方法:
@RequestMapping(value = "/findByPage",method = RequestMethod.POST)
public Map<String, Object> findByPage(@RequestBody Map<String,Object> reqMap) {
Integer page = (Integer)reqMap.get("page");
Integer rows = (Integer)reqMap.get("rows");
Map<String,Object> queryParams = (Map)reqMap.get("queryParams");
Map<String, Object> map = studentService.findByPage(queryParams, page, rows);
return map;
}
OK,搞定,启动微服务,接下来测试:
去控制台查看打印的 SQL 语句,拦截器帮我们添加了分页的 SQL 语句:
如果需要返回当前页、每页最大查询数等其它信息,就在 SearchInfoUtil 类的 getResult 方法里增加返回即可。
OK,关于 Mybatis 的分页查询讲解到这。