A paging plugin for Mybatis and of of and of
- 2020-05-26 08:31:03
- OfStack
Note: this blog post is completely different from the current paging plug-in, so it is recommended that you check the latest source code and documentation from the project address above.
I used to worry about paging Mybatis, and I have searched a lot of relevant articles on the Internet, but I didn't use any of them at last. In the paging area it's completely handwritten paging SQL and count sql, so that's a little bit of a hassle.
There was a time when I wanted to write a pagination implementation from inside Mybatis. I wrote a pagination implementation for LanguageDriver. Automatic pagination was no problem, but the total number of queries (count) still could not be solved once.
Most recently, paging was used again, so it was necessary to write a generic paging class for convenience, so again refer to most of the Mybatis paging code on the web.
In fact, a long time ago, someone had an open source implementation on github, which supported MySQL,Oracle,sqlserver, similar to the reference above, but more comprehensive. However, I think too many classes are too troublesome, so I realized a class with only one interceptor, which can actually be divided into two classes. Among them, I wrote one class as a static class and put it in the interceptor. You can also extract the Page class for the convenience of using Page.
Starting with the implementation method, the plug-in 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 critical to the overall implementation and thought. First I intercept the prepare method to change the paging SQL to do the count query. Then I intercept the handleResultSets method to get the final processing result and put the result into the Page object.
Here is the code to modify the paging, which is for Oracle data. If you want to use another database, you can modify the code here yourself.
/**
* Modify the original SQL As the paging 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 there is an selelct count statement in the setPageParameter method below, which also needs to be modified according to the database type:
// Record the total number of records
String countSql = "select count(0) from (" + sql + ")"; Why don't I offer support for a variety of databases, which I don't think is necessary. There are other databases that don't support paging, and the simpler the plug-in, the easier it will be for developers to understand and modify. Modifying it to your own paging query is certainly not a problem.
Finally, the complete code (read on, here's how to use it) :(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 - General purpose paging 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 page
* @param pageNum
* @param pageSize
*/
public static void startPage(int pageNum, int pageSize) {
localPage.set(new Page(pageNum, pageSize));
}
/**
* To end the pagination and return the result, the method must be called, otherwise localPage will 1 Keep it straight until you get down 1 time 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);
// Detach the proxy object chain ( Because the target class may be intercepted by multiple interceptors, multiple proxies are formed, through the following two loops
// You can isolate the most primitive target class )
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = SystemMetaObject.forObject(object);
}
// Separation of the last 1 The target class of the proxy object
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = SystemMetaObject.forObject(object);
}
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
// The paging information if (localPage.get() != null) {
Page page = localPage.get();
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
// The paging parameter is used as the parameter object parameterObject the 1 A property
String sql = boundSql.getSql();
// rewrite sql
String pageSql = buildPageSql(sql, page);
// Rewrite the paging sql
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
Connection connection = (Connection) invocation.getArgs()[0];
// Reset the total number of pages in the paging parameter, etc
setPageParameter(sql, connection, mappedStatement, boundSql, page);
// Hand over the power of execution 1 An 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;
}
/**
* Intercept only these two types
* 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 As the paging 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();
}
/**
* Gets 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) {
// Record the 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);
}
}
}
/**
* Plug in the parameter values
* @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: paging
* 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 the interceptor, you first need to configure the interceptor in the Mybatis configuration:
<plugins>
<plugin interceptor="com.mybatis.util.PageHelper"></plugin>
</plugins>
When configuring the interceptor, we need to pay attention to the position of plugins. The order of plugins position is as follows:
properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, plugins?, environments?, databaseIdProvider?, mappers?
Finally, the example code that calls the method (Service layer) :
@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();
}
Can see from above using this plugin to use, it is very simple, only need to use PageHelper startPage and endPage method before, during, and after the query, the code in the middle of the call result already exists in the Page result, if you place the results of 1 return 1 call PageHelper, the returned result is still a List, take the first value (I don't think anyone can use it in a place like this, so don't go wrong, of course).
In addition, all mybatis codes between startPage and endPage will be paginated, and PageHelper will only retain the results of the last one time. Therefore, it is necessary to ensure that only one mybatis query is executed in startPage at a time. If there are multiple pagination, please use startPage and endPage multiple times.
Since only an implementation of Oracle is available here, I hope that readers of other databases that refer to the paging plug-in's implementation will be able to open source the code as well.
Project address: http: / / xiazai ofstack. com / 201612 / yuanma Mybatis_PageHelper_jb51 zip