mybatis实现分页
Mybatis使用RowBounds对象进行分页，它是针对ResultSet结果集执行的逻辑分页，而非物理分页。如果想要实现物理分页，则可以在sql内直接书写带有物理分页的参数来完成，也可以使用分页插件来完成物理分页。
分页插件的基本原理是使用Mybatis提供的插件接口，实现自定义插件，在插件的拦截方法内拦截待执行的sql，然后重写sql，根据dialect方言，添加对应的物理分页语句和物理分页参数。
分页插件
mybatis分页主要是使用了mybatis中提供的拦截器功能，实现Interceptor接口，重写intercept方法
// @Signature其实就是一个需要拦截的方法封装
// type表示拦截那个类，就是mybatis中的四个核心接口Executor、ParameterHandler、ResultSetHandler 以及 StatementHandler
// method表示拦截哪个方法，args为该方法所需参数
// StatementHandler#prepare是用来从Connection获取Statement对象的
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class,Integer.class})})
public class MyPageInterceptor implements Interceptor {
private int page;
private int size;
@SuppressWarnings("unused")
private String dbType;
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 获取需要加载的对象，即所拦截的类对象
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
// mybatis中的反射方法
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过循环来进行分离出最原始的目标类)
while(metaObject.hasGetter("h")){
Object object = metaObject.getValue("h");
metaObject = SystemMetaObject.forObject(object);
}
// 分离最后一个代理对象的目标类
while(metaObject.hasGetter("target")){
Object object = metaObject.getValue("target");
metaObject = SystemMetaObject.forObject(object);
}
// 需要属性mappedStatement来获取配置文件中的内容
MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
String mapId = mappedStatement.getId();
if(mapId.matches(".+ByPager$")){
ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
Map<String, Object> params = (Map<String, Object>)parameterHandler.getParameterObject();
page = (int)params.get("page");
size = (int)params.get("size");
// 取出即将执行的sql
String sql = (String) metaObject.getValue("delegate.boundSql.sql");
sql += " limit "+(page-1)*size +","+size;
metaObject.setValue("delegate.boundSql.sql", sql);
}
return invocation.proceed();
}
// target是拦截器要拦截的对象
@Override
public Object plugin(Object target) {
// 使用JDK的动态代理，给target对象创建一个delegate代理对象，以此来实现方法拦截和增强功能，它会回调intercept()方法
return Plugin.wrap(target, this);
}
// 用来传递插件的参数
@Override
public void setProperties(Properties properties) {
String limit = properties.getProperty("limit","10");
this.page = Integer.parseInt(limit);
this.dbType = properties.getProperty("dbType", "mysql");
}
}
测试
执行方法
@Select("select * from student where class_id = #{classId}")
@Results(
{
@Result(id = true,column = "id",property = "id"),
@Result(column = "name",property = "name")
}
)
List<Student> getByClassIdByPager(@Param(value = "classId") int classId,@Param(value = "page") int page,@Param(value = "size") int size);
打印sql为
select * from student where class_id = ? limit 0,1
可以发现成功的进行分页
这种方式是从一开始获取Statement的时候就开始改写原始sql
分页且获取总条数和总页数
@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class OffsetLimitInterceptor implements Interceptor {
public Object intercept(Invocation invocation) throws Throwable {
Object[] queryArgs = invocation.getArgs();
RowBounds rowBounds = (RowBounds) queryArgs[2];
PageParam pageParam = new PageParam(rowBounds);
// 没有使用分页，直接执行返回
if (pageParam.getOffset() == 0 && pageParam.getLimit() == Integer.MAX_VALUE) {
return invocation.proceed();
}
Executor executor = (Executor) invocation.getTarget();
MappedStatement ms = (MappedStatement) queryArgs[0];
// 获取参数
Object parameter = queryArgs[1];
BoundSql boundSql = ms.getBoundSql(parameter);
// 若是使用Criteria对象时的sql，那么在additionalParameters中便有值
Field additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
additionalParametersField.setAccessible(true);
Map<String, Object> additionalParameters = (Map<String, Object>) additionalParametersField.get(boundSql);
if (rowBounds instanceof PageParam) {
// 查询总条数
MappedStatement countMs = newMappedStatement(ms, Integer.class);
CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
String countSql = "select count(*) from (" + boundSql.getSql() + ") temp";
BoundSql countBoundSql = new BoundSql(ms.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
Set<String> keySet = additionalParameters.keySet();
for (String key : keySet) {
countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
}
List<Object> countQueryResult = executor.query(countMs, parameter, RowBounds.DEFAULT, (ResultHandler) queryArgs[3], countKey, countBoundSql);
int count = (int) countQueryResult.get(0);
pageParam.setTotalCount(count);
}
CacheKey pageKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
pageKey.update("RowBounds");
String pageSql = boundSql.getSql() + " limit " + rowBounds.getOffset() + "," + rowBounds.getLimit();
BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), parameter);
Set<String> keySet = additionalParameters.keySet();
for (String key : keySet) {
pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
}
List result = executor.query(ms, parameter, RowBounds.DEFAULT, (ResultHandler) queryArgs[3], pageKey, pageBoundSql);
return new Page(result, pageParam);
}
private MappedStatement newMappedStatement(MappedStatement ms, Class<Integer> intClass) {
MappedStatement.Builder builder = new MappedStatement.Builder(
ms.getConfiguration(), ms.getId() + "_count", ms.getSqlSource(), ms.getSqlCommandType()
);
ResultMap resultMap = new ResultMap.Builder(ms.getConfiguration(), ms.getId(), intClass, new ArrayList<>(0)).build();
builder.resource(ms.getResource())
.fetchSize(ms.getFetchSize())
.statementType(ms.getStatementType())
.timeout(ms.getTimeout())
.parameterMap(ms.getParameterMap())
.resultSetType(ms.getResultSetType())
.cache(ms.getCache())
.flushCacheRequired(ms.isFlushCacheRequired())
.useCache(ms.isUseCache())
.resultMaps(Arrays.asList(resultMap));
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
return builder.build();
}
public Object plugin(final Object target) {
return Plugin.wrap(target, (Interceptor) this);
}
public void setProperties(final Properties properties) {
}
}
// 分页参数
public class PageParam extends RowBounds implements Serializable {
private static final long serialVersionUID = 1L;
private int totalCount;
protected int page;
protected int limit;
public PageParam() {
this.page = 1;
this.limit = Integer.MAX_VALUE;
}
public PageParam(RowBounds rowBounds) {
this.page = 1;
this.limit = Integer.MAX_VALUE;
if (rowBounds instanceof PageParam) {
final PageParam pageBounds = (PageParam) rowBounds;
this.page = pageBounds.page;
this.limit = pageBounds.limit;
} else {
this.page = rowBounds.getOffset() / rowBounds.getLimit() + 1;
this.limit = rowBounds.getLimit();
}
}
public PageParam(final int limit) {
this.page = 1;
this.limit = limit;
}
public PageParam(int page, int limit) {
this.page = page;
this.limit = limit;
}
public int getTotalCount() {
return this.totalCount;
}
public void setTotalCount(final int totalCount) {
this.totalCount = totalCount;
}
public int getPage() {
return this.page;
}
public void setPage(final int page) {
this.page = page;
}
public int getLimit() {
return this.limit;
}
public void setLimit(final int limit) {
this.limit = limit;
}
public int getOffset() {
if (this.page >= 1) {
return (this.page - 1) * this.limit;
}
return 0;
}
public String toString() {
final StringBuilder sb = new StringBuilder("PageBounds{");
sb.append("page=").append(this.page);
sb.append(", limit=").append(this.limit);
sb.append(", totalCount=").append(this.totalCount);
sb.append('}');
return sb.toString();
}
}
// 分页结果
public class Page<E> extends ArrayList<E> {
private static final long serialVersionUID = 1L;
private PageParam paginator;
public Page() {
}
public Page(final Collection<? extends E> c) {
super(c);
}
public Page(final Collection<? extends E> c, final PageParam p) {
super(c);
this.paginator = p;
}
public Page(final PageParam p) {
this.paginator = p;
}
public PageParam getPaginator() {
return this.paginator;
}
public int getPageSize() {
if (this.paginator != null) {
return this.paginator.getLimit();
}
return 0;
}
public int getPageNo() {
if (this.paginator != null) {
return this.paginator.getPage();
}
return 0;
}
public int getTotalCount() {
if (this.paginator != null) {
return this.paginator.getTotalCount();
}
return 0;
}
@Override
public boolean equals(final Object obj) {
if (!super.equals(obj)) {
return false;
}
if (obj.getClass() != Page.class) {
return false;
}
final Page<E> fobj = (Page<E>) obj;
return this.paginator != null && this.paginator.equals(fobj.getPaginator());
}
@Override
public int hashCode() {
if (this.paginator != null) {
return this.getPaginator().hashCode();
}
return super.hashCode();
}
}
测试一下
@Select("select * from student where class_id = #{classId}")
@Results(
{
@Result(id = true,column = "id",property = "id"),
@Result(column = "name",property = "name")
}
)
Page<Student> getByClassIdPage(@Param(value = "classId") int classId, @Param(value = "pageParam")PageParam pageParam);
查看执行的sql为
select count(*) from (select * from student where class_id = ?) temp
select * from student where class_id = ? limit 0,1
这样返回的结果就包含了总条数了
本文由 mdnice 多平台发布
585
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
