The java paging interception class implements sql automatic paging
- 2020-05-17 05:36:52
- OfStack
The example of this article shares the complete java paging interception class for your reference, the details are as follows
package com.opms.interceptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
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.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.RowBounds;
import com.wifi.core.page.Page;
/**
* By intercepting <code>StatementHandler</code> the <code>prepare</code> Method, override sql The statement implements physical paging.
* As usual, the type to be intercepted in the signature must be the interface.
*
* @author The lake in the breeze
*
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PageInterceptor implements Interceptor {
/**
* The log
*/
private static final Log logger = LogFactory.getLog(PageInterceptor.class);
/**
* A statement object
*/
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
/**
* A statement object
*/
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
/**
* Database type ( The default is mysql)
*/
private static String defaultDialect = "mysql";
/**
* To intercept ID( Regular match )
*/
private static String defaultPageSqlId = ".*4Page$";
/**
* Database type ( The default is mysql)
*/
private static String dialect = "";
/**
* To intercept ID( Regular match )
*/
private static String pageSqlId = "";
/**
* @param invocation parameter
* @return Object
* @throws Throwable An exception is thrown
*/
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY);
// Detach the proxy object chain ( Since the target class may be intercepted by multiple interceptors, resulting in multiple proxies, the 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);
}
// Separation of the last 1 The target class of the proxy object
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
}
dialect=defaultDialect;pageSqlId=defaultPageSqlId;
/* Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");
dialect = configuration.getVariables().getProperty("dialect");
if (null == dialect || "".equals(dialect)) {
logger.warn("Property dialect is not setted,use default 'mysql' ");
dialect = defaultDialect;
}
pageSqlId = configuration.getVariables().getProperty("pageSqlId");
if (null == pageSqlId || "".equals(pageSqlId)) {
logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");
pageSqlId = defaultPageSqlId;
}*/
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
// Override only those that require paging sql Statements. through MappedStatement the ID Match, default override to Page At the end of the MappedStatement the sql
if (mappedStatement.getId().matches(pageSqlId)) {
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
Object parameterObject = boundSql.getParameterObject();
if (parameterObject == null) {
throw new NullPointerException("parameterObject is null!");
} else {
Object obj = metaStatementHandler
.getValue("delegate.boundSql.parameterObject.page");
// Introduced to the page Parameter and when paging needs to be turned on
if(obj!=null&&obj instanceof Page &&((Page)obj).isPagination()){
Page page = (Page) metaStatementHandler
.getValue("delegate.boundSql.parameterObject.page");
String sql = boundSql.getSql();
// rewrite sql
String pageSql = buildPageSql(sql, page);
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
// With physical paging, you don't need it mybatis Is paging out of memory, so reset the following two parameters
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
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();
}
/**
* Query the total number of records from the database and calculate the total number of pages, write back into the paging parameter <code>PageParameter</code>, This allows the caller to pass through Paging parameters
* <code>PageParameter</code> Get relevant information.
*
* @param sql parameter
* @param connection The connection
* @param mappedStatement parameter
* @param boundSql The binding sql
* @param page 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 + ") as total";
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.setTotalCount(totalCount);
page.init(page.getCurPage(), page.getPageSize(), totalCount);
} 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);
}
}
}
/**
* right SQL parameter (?) Set the value
*
* @param ps parameter
* @param mappedStatement parameter
* @param boundSql The binding sql
* @param parameterObject Parameter object
* @throws SQLException throw sql abnormal
*/
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
/**
* Generate specific pages based on the database type sql
*
* @param sql Meal lodge
* @param page page
* @return String
*/
private String buildPageSql(String sql, Page page) {
if (page != null) {
StringBuilder pageSql = new StringBuilder();
if ("mysql".equals(dialect)) {
pageSql = buildPageSqlForMysql(sql, page);
} else if ("oracle".equals(dialect)) {
pageSql = buildPageSqlForOracle(sql, page);
} else {
return sql;
}
return pageSql.toString();
} else {
return sql;
}
}
/**
* mysql Page break statement
*
* @param sql parameter
* @param page page
* @return String
*/
public StringBuilder buildPageSqlForMysql(String sql, Page page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurPage() - 1) * page.getPageSize());
pageSql.append(sql);
pageSql.append(" limit " + beginrow + "," + page.getPageSize());
return pageSql;
}
/**
* reference hibernate Implementation completion oracle paging
*
* @param sql parameter
* @param page parameter
* @return String
*/
public StringBuilder buildPageSqlForOracle(String sql, Page page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurPage() - 1) * page.getPageSize());
String endrow = String.valueOf(page.getCurPage() * page.getPageSize());
pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
pageSql.append(sql);
pageSql.append(" ) temp where rownum <= ").append(endrow);
pageSql.append(") where row_id > ").append(beginrow);
return pageSql;
}
/**
* @param target parameter
* @return Object
*/
public Object plugin(Object target) {
// When the target class is StatementHandler Type, the target class is wrapped, or the target itself is returned , Reduces the number of times the target is proxied
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
/**
* @param properties parameter
*/
public void setProperties(Properties properties) {
}
}