This paper introduces the use of Mybatis interceptor to realize paging, and in the dao layer, directly return the customized paging object.
The final dao layer results:
public interface ModelMapper {
Page<Model> pageByConditions(RowBounds rowBounds, Model record);
}
The next step is paging.
1. Create Page objects:
public class Page<T> extends PageList<T> {
private int pageNo = 1;// Page number, default page 1
private int pageSize = 15;// The number of records displayed per page by default is 15
private int totalRecord;// Total number of records
private int totalPage;// PageCount
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;
// When setting the total number of pages, the corresponding total number of pages is calculated. In the following three-mesh operation, addition has a higher priority, so it can end up without brackets.
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();
}
}
It can be found that a PageList class is inherited here; this class is also a class created by itself to implement the List interface. Why PageList class is needed because Page needs to implement List interface, and the abstract methods in the interface need to be implemented one by one, so PageList is provided to write a method to implement List interface in a unified place.
Why does Page need to implement the List interface? This will be explained later in the code.
PageList class:
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);
}
}
II. Provide Dao and mapper.xml
dao:
Page<Model> pageByConditions(RowBounds rowBounds, Model record);
mapper.xml:
<!-- Table name --> <sql id="tableName" > model </sql> <!-- All column names in the data table --> <sql id="Base_Column_List" > id, name </sql> <!-- Query field --> <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> <!-- Paging Query Statement --> <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, these are the basic operations of mybatis, so no redundant explanations are given.
3. Create interceptors:
The interceptor principle and execution sequence can be referred to at http://www.cnblogs.com/fangjian0423/p/mybatis-interceptor.html, http://blog.csdn.net/abcd89898989/article/details/51261163.
What we need to do is create a Page Interceptor and an Executor.
1. Page Inteceptor: Implement Inteceptor interface, execute Page Executor, intercept sql to add paging sql (limit xx,xx)
2.PageExecutor: Implement the Executor interface. When querying, add the total number of queries and modify the return value type. Because what we need to do is pagination and query operation, so the non-query methods inside all use the basic implementation, only modify two query methods.
Page Inteceptor complete code:
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.*$"; // String regular expressions that require paging 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(); } // Separating Proxy Object Chain(Since the target class may be intercepted by multiple interceptors, thus forming multiple proxies, the most original target class can be separated by the following two loops) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // Separating the target class of the last proxy object while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // take mybatis Memory Paging, Adjusted to Physical Paging BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); String sql = boundSql.getSql(); // Rewrite sql String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit(); metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); // With physical paging, you don't need to mybatis Memory pagination, so reset the following two parameters metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET); metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT); // Deliver execution to the next interceptor 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 complete code:
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); } /** * Modify the return value type * @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(); // If paging queries are required, modify the return type to be Page object 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; } /** * Total number obtained * @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(); } // Remove sql Statement select clause 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(); } }
The reason why Page needs to implement the List interface: You can see that the return value of the query method is List < E>, and now we want to use the Page < E > object in dao to receive the results returned by mybatis, so we need to let Page implement the List interface.
Paging query execution order: Enter the plugin method of PageInterceptor, intercept the executor, enter the query method of PageExecutor, execute the executor.query(), and then return to the plugin method of PageInterceptor again, which will be executed this time.
Enter the intercept method, splice the executed sql into paging restriction statements, and then query the data result set. After executor.query() is executed, page Resolver continues to be executed. If the method name matches the string configuration that requires paging, query the total amount of data and return the Page object; if not, return the List object directly.
IV. xml configuration:
<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>
5. Test code:
@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); }