Note: This blog is completely different from the current paging plug-in, so it is recommended that you check the latest source code and documentation through the above project address to understand.
I have been worried about Mybatis pagination query before, and I have searched for many related articles online, but I didn't use the last one. The paged place is completely handwritten with SQL and count SQL, which is very troublesome.
Later, for a while, I wanted to write a pagination implementation from within Mybatis. I wrote an implementation for LanguageDriver. There is no problem with automatic pagination, but the total number of queries (count) still cannot be solved at one time, and it was left unresolved.
Recently, I have to use pagination again. For the sake of convenience, I have to write a general pagination class, so I refer to most Mybatis pagination codes on the Internet again.
In fact, a long time ago, someone opened the source implementation on github, supporting MySQL, Oracle, and SQLserver, which is similar to the reference above and has a more comprehensive consideration. But I think too many classes are too troublesome, so I implemented a class with only one interceptor, which can actually be divided into two classes. One of the classes was written as a static class by me and placed in the interceptor. You can also extract the Page class to facilitate the use of Page.
Let’s talk about the implementation method first. This plugin has only one class: PageHelper.Java
The interceptor signature is:
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}), @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})})The signature here is crucial to the entire implementation and idea. First, I intercept the prepare method to change the pagination SQL and do count query. Then I intercept the handleResultSets method to get the last processing result and put the result into the Page object.
The following is the code to modify the page, which is a modification for Oracle data. If you use other databases, you can modify the code here yourself.
/** * Modify the original SQL to pagination SQL * @param sql * @param page * @return */ private String buildPageSql(String sql, Page page) { StringBuilder pageSql = new StringBuilder(200); pageSql.append("select * from ( select temp.*, rownum row_id from ( "); pageSql.append(sql); pageSql.append(" ) temp where rownum <= ").append(page.getEndRow()); pageSql.append(") where row_id > ").append(page.getStartRow()); return pageSql.toString(); } Then, in the following setPageParameter method, a select count statement needs to be modified according to the database type:
// Total number of records String countSql = "select count(0) from (" + sql + ")";Why don’t I provide support for various databases? I don’t think it is necessary. Some databases do not support paging, and the simpler this plugin is, the easier it is for developers to understand and modify. Modifying it into the pagination query you need is definitely not a problem.
Finally, the complete code is added (continue reading, there is also the usage method below): (Click to download)
package com.mybatis.util; import org.apache.ibatis.executor.parameter.ParameterHandler; import org.apache.ibatis.executor.resultset.ResultSetHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; import org.apache.log4j.Logger; import java.sql.*; import java.util.List; import java.util.Properties; /** * Mybatis - Universal Pagination Interceptor* @author liuzh/abel533/isea * Created by liuzh on 14-4-15. */ @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}), @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})}) public class PageHelper implements Interceptor { private static final Logger logger = Logger.getLogger(PageHelper.class); public static final ThreadLocal<Page> localPage = new ThreadLocal<Page>(); /** * Start paging* @param pageNum * @param pageSize */ public static void startPage(int pageNum, int pageSize) { localPage.set(new Page(pageNum, pageSize)); } /** * End the paging and return the result. The method must be called, otherwise localPage will be saved until the next startPage * @return */ public static Page endPage() { Page page = localPage.get(); localPage.remove(); return page; } @Override public Object intercept(Invocation invocation) throws Throwable { if (localPage.get() == null) { return invocation.proceed(); } if (invocation.getTarget() instanceof StatementHandler) { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler); // Separate the proxy object chain (because the target class may be intercepted by multiple interceptors, multiple proxying is formed, and the following two loops // The most primitive target class can be separated) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = SystemMetaObject.forObject(object); } // The target class that separates the last proxy object while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = SystemMetaObject.forObject(object); } MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); //Page information if (localPage.get() != null) { Page page = localPage.get(); BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); //Page parameter as a property of parameterObject parameterString sql = boundSql.getSql(); // Rewrite sql String pageSql = buildPageSql(sql, page); // Rewrite paging sql metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); Connection connection = (Connection) invocation.getArgs()[0]; // Reset the total number of pages in the paging parameters, etc. setPageParameter(sql, connection, mappedStatement, boundSql, page); // Hand over the execution rights to the next interceptor return invocation.proceed(); } else if (invocation.getTarget() instanceof ResultSetHandler) { Object result = invocation.proceed(); Page page = localPage.get(); page.setResult((List) result); return result; } return null; } /** * Only intercept these two types of * StatementHandler * ResultSetHandler * @param target * @return */ @Override public Object plugin(Object target) { if (target instanceof StatementHandler || target instanceof ResultSetHandler) { return Plugin.wrap(target, this); } else { return target; } } @Override public void setProperties(Properties properties) { } /** * Modify the original SQL to pagination SQL * @param sql * @param page * @return */ private String buildPageSql(String sql, Page page) { StringBuilder pageSql = new StringBuilder(200); pageSql.append("select * from ( select temp.*, rownum row_id from ( "); pageSql.append(sql); pageSql.append(" ) temp where rownum <= ").append(page.getEndRow()); pageSql.append(") where row_id > ").append(page.getStartRow()); return pageSql.toString(); } /** * Get the total number of records* @param sql * @param connection * @param mappedStatement * @param boundSql * @param page */ private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Page page) { // Total number of records String countSql = "select count(0) from (" + sql + ")"; PreparedStatement countStmt = null; ResultSet rs = null; try { countStmt = connection.prepareStatement(countSql); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject()); rs = countStmt.executeQuery(); int totalCount = 0; if (rs.next()) { totalCount = rs.getInt(1); } page.setTotal(totalCount); int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1); page.setPages(totalPage); } catch (SQLException e) { logger.error("Ignore this exception", e); } finally { try { rs.close(); } catch (SQLException e) { logger.error("Ignore this exception", e); } try { countStmt.close(); } catch (SQLException e) { logger.error("Ignore this exception", e); } } } /** * Substitute parameter value* @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */ private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql); parameterHandler.setParameters(ps); } /** * Description: Pagination* Author: liuzh * Update: liuzh(2014-04-16 10:56) */ public static class Page<E> { private int pageNum; private int pageSize; private int startRow; private int endRow; private long total; private int pages; private List<E> result; public Page(int pageNum, int pageSize) { this.pageNum = pageNum; this.pageSize = pageSize; this.startRow = pageNum > 0 ? (pageNum - 1) * pageSize : 0; this.endRow = pageNum * pageSize; } public List<E> getResult() { return result; } public void setResult(List<E> result) { this.result = result; } public int getPages() { return pages; } public void setPages(int pages) { this.pages = pages; } public int getEndRow() { return endRow; } public void setEndRow(int endRow) { this.endRow = endRow; } 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 int getStartRow() { return startRow; } public void setStartRow(int startRow) { this.startRow = startRow; } public long getTotal() { return total; } public void setTotal(long total) { this.total = total; } @Override public String toString() { return "Page{" + "pageNum=" + pageNum + ", pageSize=" + pageSize + ", startRow=" + startRow + ", endRow=" + endRow + ", total=" + total + ", pages=" + pages + '}'; } } } To use this interceptor, you first need to configure the interceptor in the Mybatis configuration:
<plugins> <plugin interceptor="com.mybatis.util.PageHelper"></plugin> </plugins>
When configuring an interceptor, you need to pay attention to the location of plugins. The order of plugins is as follows:
properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, plugins?, environments?, databaseIdProvider?, mappers?
Finally, there is the example code (Service layer) that calls this method:
@Override public PageHelper.Page<SysLoginLog> findSysLoginLog(String loginIp, String username, String loginDate, String exitDate, String logerr, int pageNumber, int pageSize) throws BusinessException { PageHelper.startPage(pageNumber, pageSize); sysLoginLogMapper.findSysLoginLog(loginIp, username, loginDate, exitDate, logerr); return PageHelper.endPage(); }From the above, we can see that using this plug-in is very simple. You only need to use the startPage and endPage methods of PageHelper before and after the query. The call result of the intermediate code already exists in the result of PageHelper. If you call PageHelper in a place that returns a result, the returned result is still a List, and you can just take the first value (I think no one will use it like this in this place, of course there is no error in this way).
In addition, all mybatis codes between startPage and endPage will be paginated, and PageHelper will only retain the last result. Therefore, when using it, you need to ensure that only one mybatis query is executed in it at a time. If there are multiple paging, please use startPage and endPage multiple times.
Since only Oracle implementations are provided here, I hope that readers who refer to other databases implemented by this paging plugin can also open source the corresponding code.
Project address: http://xiazai.VeVB.COM/201612/yuanma/Mybatis_PageHelper_jb51.zip
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.