Mybatis拦截器实现分页

最终dao层结果:

public interface ModelMapper {
 Page<Model> pageByConditions(RowBounds rowBounds, Model record); 
}

接下来一步一步来实现分页。

一.创建Page对象:

public class Page<T> extends PageList<T> {
 private int pageNo = 1;// 页码,默认是第一页
 private int pageSize = 15;// 每页显示的记录数,默认是15
 private int totalRecord;// 总记录数
 private int totalPage;// 总页数
 public Page() {
 }
 public Page(int pageNo, int pageSize, int totalRecord,
  List<T> results) {
 this.pageNo = pageNo;
 this.pageSize = pageSize;
 this.totalRecord = totalRecord;
 this.setResult(results);
 int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1;
 this.setTotalPage(totalPage);
 }
 public int getPageNo() {
 return pageNo;
 }
 public void setPageNo(int pageNo) {
 this.pageNo = pageNo;
 }
 public int getPageSize() {
 return pageSize;
 }
 public void setPageSize(int pageSize) {
 this.pageSize = pageSize;
 }
 public int getTotalRecord() {
 return totalRecord;
 }
 public void setTotalRecord(int totalRecord) {
 this.totalRecord = totalRecord;
 // 在设置总页数的时候计算出对应的总页数,在下面的三目运算中加法拥有更高的优先级,所以最后可以不加括号。
 int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1;
 this.setTotalPage(totalPage);
 }
 public int getTotalPage() {
 return totalPage;
 }
 public void setTotalPage(int totalPage) {
 this.totalPage = totalPage;
 }
 @Override
 public String toString() {
 StringBuilder builder = new StringBuilder();
 builder.append("Page [pageNo=").append(pageNo).append(", pageSize=").append(pageSize).append(", results=")
  .append(getResult()).append(", totalPage=").append(totalPage).append(", totalRecord=").append(totalRecord)
  .append("]");
 return builder.toString();
 }
}

可以发现,这里继承了一个PageList类;这个类也是自己创建的一个类,实现List接口。为什么要PageList这个类,是因为Page需要实现List接口,而接口中的抽象方法,需要逐一实现,所以提供PageList在统一的地方写实现List接口的方法。

为什么Page需要实现List接口,这个会在稍后的代码中做解释。

PageList类:

public class PageList<T> implements List<T> {
 private List<T> result;
 public List<T> getResult() {
 return result;
 }
 public void setResult(List<T> result) {
 this.result = result;
 }
 @Override
 public int size() {
 return result.size();
 }
 @Override
 public boolean isEmpty() {
 return result.isEmpty();
 }
 @Override
 public boolean contains(Object o) {
 return result.contains(o);
 }
 @Override
 public Iterator<T> iterator() {
 return result.iterator();
 }
 @Override
 public Object[] toArray() {
 return result.toArray();
 }
 @Override
 public <E> E[] toArray(E[] a) {
 return result.toArray(a);
 }
 @Override
 public boolean add(T e) {
 return result.add(e);
 }
 @Override
 public boolean remove(Object o) {
 return result.remove(o);
 }
 @Override
 public boolean containsAll(Collection<?> c) {
 return result.containsAll(c);
 }
 @Override
 public boolean addAll(Collection<? extends T> c) {
 return result.addAll(c);
 }
 @Override
 public boolean addAll(int index, Collection<? extends T> c) {
 return result.addAll(index, c);
 }
 @Override
 public boolean removeAll(Collection<?> c) {
 return result.removeAll(c);
 }
 @Override
 public boolean retainAll(Collection<?> c) {
 return result.retainAll(c);
 }
 @Override
 public void clear() {
 result.clear();
 }
 @Override
 public T get(int index) {
 return result.get(index);
 }
 @Override
 public T set(int index, T element) {
 return result.set(index, element);
 }
 @Override
 public void add(int index, T element) {
 result.add(index, element);
 }
 @Override
 public T remove(int index) {
 return result.remove(index);
 }
 @Override
 public int indexOf(Object o) {
 return result.indexOf(o);
 }
 @Override
 public int lastIndexOf(Object o) {
 return result.lastIndexOf(o);
 }
 @Override
 public ListIterator<T> listIterator() {
 return result.listIterator();
 }
 @Override
 public ListIterator<T> listIterator(int index) {
 return result.listIterator(index);
 }
 @Override
 public List<T> subList(int fromIndex, int toIndex) {
 return result.subList(fromIndex, toIndex);
 }
}

二.提供Dao以及mapper.xml

dao的写法:

Page<Model> pageByConditions(RowBounds rowBounds, Model record);

mapper.xml:

<!-- 表名 -->
 <sql id="tableName" >
 model
 </sql>
 <!-- 数据表所有列名 -->
 <sql id="Base_Column_List" >
 id, 
 name 
 </sql>
 <!-- 查询字段 -->
 <sql id="Base_Search_Param" >
 <if test="id != null" >
  and id = #{id,jdbcType=INTEGER}
 </if>
 <if test="name != null" >
  and name = #{name,jdbcType=VARCHAR}
 </if>
 </sql>
 <!-- 分页查询语句 -->
 <select id="pageByConditions" resultMap="BaseResultMap">
 SELECT 
  <include refid="Base_Column_List" />
 FROM 
  <include refid="tableName" />
 WHERE 1=1
  <include refid="Base_Search_Param" />
 </select>

ok,以上都是mybatis的基本操作,就不做多余解释。

三.创建拦截器:

我们需要做的是创建一个拦截器(PageInterceptor)、一个执行者(PageExecutor)。

1.PageInteceptor:实现Inteceptor接口,将PageExecutor进行执行,拦截sql添加分页sql(limit xx,xx)

2.PageExecutor:实现Executor接口,在查询时,添加查询总数并修改返回值类型。因为要做的是分页,是查询操作,所以里边的非查询方法都使用基本的实现,只修改两个query方法。

PageInteceptor完整代码:

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.util.Properties;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
@Intercepts({
 @Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class,
  RowBounds.class, ResultHandler.class }),
 @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
 private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
 private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
 private String pattern = "^.*page.*$"; // 需要进行分页操作的字符串正则表达式
 public String getPattern() {
 return pattern;
 }
 public void setPattern(String pattern) {
 this.pattern = pattern;
 }
 @Override
 public Object intercept(Invocation invocation) throws Throwable {
 if (invocation.getTarget() instanceof StatementHandler) {
  return handleStatementHandler(invocation);
 }
 return invocation.proceed();
 }
 /**
 * @param invocation
 * @return
 * @throws IllegalAccessException 
 * @throws InvocationTargetException 
 */
 private Object handleStatementHandler(Invocation invocation)
  throws InvocationTargetException, IllegalAccessException {
 StatementHandler statementHandler = (StatementHandler) invocation
  .getTarget();
 MetaObject metaStatementHandler = MetaObject.forObject(
  statementHandler, DEFAULT_OBJECT_FACTORY,
  DEFAULT_OBJECT_WRAPPER_FACTORY);
 RowBounds rowBounds = (RowBounds) metaStatementHandler
  .getValue("delegate.rowBounds");
 if (rowBounds == null || (rowBounds.getOffset() == RowBounds.NO_ROW_OFFSET && rowBounds
  .getLimit() == RowBounds.NO_ROW_LIMIT)) {
  return invocation.proceed();
 }
 // 分离代理对象链(由于目标类可能被多个拦截器拦截,从而形成多次代理,通过下面的两次循环可以分离出最原始的的目标类)
 while (metaStatementHandler.hasGetter("h")) {
  Object object = metaStatementHandler.getValue("h");
  metaStatementHandler = MetaObject.forObject(object,
   DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
 }
 // 分离最后一个代理对象的目标类
 while (metaStatementHandler.hasGetter("target")) {
  Object object = metaStatementHandler.getValue("target");
  metaStatementHandler = MetaObject.forObject(object,
   DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
 }
 // 将mybatis的内存分页,调整为物理分页
 BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
 String sql = boundSql.getSql();
 // 重写sql
 String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();
 metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
 // 采用物理分页后,就不需要mybatis的内存分页了,所以重置下面的两个参数
 metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
 metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
 // 将执行权交给下一个拦截器
 return invocation.proceed();
 }
 @Override
 public Object plugin(Object o) {
 if (Executor.class.isAssignableFrom(o.getClass())) {
  PageExecutor executor = new PageExecutor((Executor)o, pattern);
  return Plugin.wrap(executor, this);
 } else if (o instanceof StatementHandler) {
  return Plugin.wrap(o, this);
 }
 return o;
 }
 @Override
 public void setProperties(Properties properties) {
 }
}

PageExecutor完整代码:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;
public class PageExecutor implements Executor {
 private Executor executor;
 private String pattern;
 public PageExecutor(Executor executor, String pattern) {
 this.executor = executor;
 this.pattern = pattern;
 }
 @Override
 public int update(MappedStatement ms, Object parameter) throws SQLException {
 return executor.update(ms, parameter);
 }
 @Override
 public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler,
  CacheKey cacheKey, BoundSql boundSql) throws SQLException {
 RowBounds rb = new RowBounds(rowBounds.getOffset(), rowBounds.getLimit());
 List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler,
  cacheKey, boundSql);
 return pageResolver(rows, ms, parameter, rb);
 }
 /**
 * 修改返回值类型
 * @param rows
 * @param ms
 * @param parameter
 * @param rowBounds
 * @return
 */
 private <E> List<E> pageResolver(List<E> rows, MappedStatement ms,
  Object parameter, RowBounds rowBounds) {
 String msid = ms.getId();
 // 如果需要分页查询,修改返回类型为Page对象
 if (msid.matches(pattern)) {
  int count = getCount(ms, parameter);
  int offset = rowBounds.getOffset();
  int pagesize = rowBounds.getLimit();
  return new Page<E>(offset/pagesize + 1, pagesize, count, rows);
 }
 return rows;
 }
 /**
 * 获取总数
 * @param ms
 * @param parameter
 * @return
 */
 private int getCount(MappedStatement ms, Object parameter) {
 BoundSql bsql = ms.getBoundSql(parameter);
 String sql = bsql.getSql();
 String countSql = getCountSql(sql);
 Connection connection = null;
 PreparedStatement stmt = null;
 ResultSet rs = null;
 try {
  connection = ms.getConfiguration().getEnvironment().getDataSource()
   .getConnection();
  stmt = connection.prepareStatement(countSql);
  rs = stmt.executeQuery();
  if (rs.next())
  return rs.getInt(1);
 } catch (SQLException e) {
  e.printStackTrace();
 } finally {
  try {
  if (connection != null && !connection.isClosed()) {
   connection.close();
  }
  } catch (SQLException e) {
  e.printStackTrace();
  }
 }
 return 0;
 }
 private String getCountSql(String sql) {
 String countHql = " SELECT count(*) "
  + removeSelect(removeOrders(sql));

 return countHql;
 }
 protected String removeOrders(String sql) {
 Pattern p = Pattern.compile("ORDER\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
 Matcher m = p.matcher(sql);
 StringBuffer sb = new StringBuffer();
 while (m.find()) {
  m.appendReplacement(sb, "");
 }
 m.appendTail(sb);
 return sb.toString();
 }
 // 去除sql语句中select子句
 private static String removeSelect(String hql) {
 int beginPos = hql.toLowerCase().indexOf("from");
 if (beginPos < 0) {
  throw new IllegalArgumentException(" hql : " + hql + " must has a keyword 'from'");
 }
 return hql.substring(beginPos);
 }
 @Override
 public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler)
  throws SQLException {
 BoundSql boundSql = ms.getBoundSql(parameter);
 return query(ms, parameter, rowBounds, resultHandler,
  executor.createCacheKey(ms, parameter, rowBounds, boundSql),
  boundSql);
 }
 @Override
 public List<BatchResult> flushStatements() throws SQLException {
 return executor.flushStatements();
 }
 @Override
 public void commit(boolean required) throws SQLException {
 executor.commit(required);
 }
 @Override
 public void rollback(boolean required) throws SQLException {
 executor.rollback(required);
 }
 @Override
 public CacheKey createCacheKey(MappedStatement ms, Object parameterObject,
  RowBounds rowBounds, BoundSql boundSql) {
 return executor
  .createCacheKey(ms, parameterObject, rowBounds, boundSql);
 }
 @Override
 public boolean isCached(MappedStatement ms, CacheKey key) {
 return executor.isCached(ms, key);
 }
 @Override
 public void clearLocalCache() {
 executor.clearLocalCache();
 }
 @Override
 public void deferLoad(MappedStatement ms, MetaObject resultObject,
  String property, CacheKey key, Class<?> targetType) {
 executor.deferLoad(ms, resultObject, property, key, targetType);
 }
 @Override
 public Transaction getTransaction() {
 return executor.getTransaction();
 }
 @Override
 public void close(boolean forceRollback) {
 executor.close(forceRollback);
 }
 @Override
 public boolean isClosed() {
 return executor.isClosed();
 }
}

关于Page需要实现List接口的原因:可以看到,query方法返回值是List<E>,而我们现在要在dao中使用Page<E>对象来接收mybatis返回的结果,所以需要让Page实现List接口。

分页查询执行顺序:进入PageInterceptor的plugin方法,拦截到执行者,进入PageExecutor的query方法,执行executor.query()时,又再次回到PageInterceptor的plugin方法,这次会执行

进入intercept方法,将执行的sql拼接上分页限制语句,然后查询出数据结果集合。executor.query()执行完成后,继续执行pageResolver,如果方法名称和配置的需要执行分页操作的字符串匹配时,查询数据总量,并返回Page对象;如果不匹配,直接返回List对象。

四.xml配置:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 <property name="dataSource" ref="dataSource" />
 <property name="configLocation" value="classpath:/conf/mybatis/mybaties-config.xml"></property>
 <property name="mapperLocations">
  <list>
  <value>classpath:/conf/mybatis/**/*-mapper.xml</value>
  </list>
 </property>
 <property name="plugins">
  <list>
  <ref bean="pageInterceptor"/>
  </list> 
 </property>
 </bean>
 <bean id="pageInterceptor" class="cn.com.common.PageInterceptor">
 <property name="pattern" value="^.*page.*$"></property>
 </bean>

五.测试代码:

@Test
 public void testPage() {
 int pageNo = 1;
 int pageSize = 10;
 RowBounds bounds = new RowBounds((pageNo - 1) * pageSize, pageSize);
 Model record = new Model();
 Page<Model> list = modelMapper.pageByConditions(bounds, record);
 }

本文主要介绍了Mybatis拦截器实现分页的步骤与方法。具有很好的参考价值,下面跟着小编一起来看下吧

代码技巧

转载请关注公众号:代码技巧 回复:授权

本文链接地址:https://www.oudahe.com/p/12443/