在实际项目中，大家经常为mybatis的分页使出各种方案，其中不乏采用mybatis-plus等第三方控件的方法，但整体来说并不灵活。其实分页思想就是对查询结果集增加了预处理，最近笔者在一个项目中看到了某大佬手撸的一个分页类源码，顿时拜倒折服，也同大家互勉。
代码采用AOP非侵入方式，拦截方法名直接全局 *** 作，不需要对源码提出任何需求就可以轻松实现分页。
Pager类
package top.powersys.core.pager;
import javax.servlet.http.HttpSession;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import top.powersys.core.util.GlobalUtil;
import top.powersys.core.util.I18nUtil;
import java.util.Map;
public class Pager {
private int showCount = 10;
private int showTag = 10;
private int totalResult;
private int totalPage;
private int currentPage;
private int currentResult;
private String pageStr;
private String url;
private String para;
private String orderBy;
private boolean showSizeOption = true;
public Pager() {
this.showCount = GlobalUtil.pagesize;
HttpSession session = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest().getSession();
if (session != null) {
Object pageSizeInSession = session.getAttribute(GlobalUtil.PAGESIZE_KEY);
if (pageSizeInSession != null)
this.showCount = Integer.parseInt(pageSizeInSession.toString());
}
if (this.showCount > 100)
this.showCount = 100;
}
public Pager(final int currentPage, final String url, final String orderby) {
this();
this.url = url;
this.orderBy = orderby;
this.currentPage = currentPage;
}
public Pager(final int currentPage, final String url, final String orderby, final String param) {
this(currentPage, url, orderby);
this.para = param;
}
public Pager(final int currentPage, final String url, final String orderby, final Map param) {
this(currentPage, url, orderby);
StringBuilder paramMap = new StringBuilder();
for (Map.Entry entry : param.entrySet()) {
if (StringUtils.isNotEmpty(entry.getKey()) && entry.getValue() != null && StringUtils.isNotEmpty(String.valueOf(entry.getValue())))
paramMap.append("&" + entry.getKey() + "=" + entry.getValue());
}
this.para = paramMap.toString();
}
public Pager(final int currentPage, final String url, final Map paramWithOrder) {
this(currentPage, url, paramWithOrder.get("ob").toString() + " " + (paramWithOrder.get("ot").toString().equals("0") ? "asc" : "desc"), paramWithOrder);
}
public String getOrderBy() {
if (StringUtils.isBlank(orderBy)) return "";
else return " order by " + orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getTotalResult() {
return totalResult;
}
public void setTotalResult(int totalResult) {
this.totalResult = totalResult;
}
public int getTotalPage() {
totalPage = totalResult % showCount == 0 ? (totalResult / showCount) : (totalResult / showCount + 1);
return totalPage;
}
public int getCurrentPage() {
if (currentPage < 1 || currentPage > getTotalPage()) currentPage = 1;
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getCurrentResult() {
currentResult = (getCurrentPage() - 1) * this.showCount + 1;
if (currentResult < 0)
currentResult = 0;
return currentResult;
}
public String getPara() {
if (StringUtils.isBlank(para))
return "";
else
return para;
}
public void setPara(String para) {
this.para = para;
}
public boolean isShowSizeOption() {
return showSizeOption;
}
public void setShowSizeOption(boolean showSizeOption) {
this.showSizeOption = showSizeOption;
}
public int getShowCount() {
return this.showCount;
}
public String getPageStr() {
if (StringUtils.isBlank(para))
para = "";
String pagename = I18nUtil.getMessage("i18n.common", "page"), records = I18nUtil
.getMessage("i18n.common", "records"), total = I18nUtil
.getMessage("i18n.common", "total"), pagesize = I18nUtil
.getMessage("i18n.common", "pagesize");
StringBuilder sb = new StringBuilder();
if (totalResult > 0) {
if (currentPage == 1) {
sb.append(" ");
} else {
sb.append("
+ url
+ "?"
+ para
+ "&p=1'>
+ url + "?" + para + "&p=" + (currentPage - 1)
+ "'>");
}
int startTag = 1;
if (currentPage > showTag) {
startTag = currentPage - 1;
}
int endTag = startTag + showTag - 1;
for (int i = startTag; i <= totalPage && i <= endTag; i++) {
if (currentPage == i)
sb.append(" " + i + "");
else
sb.append(" + url + "?" + para + "&p=" + i
+ "'>" + i + "");
}
if (currentPage == totalPage) {
sb.append(" ");
} else {
sb.append("
+ url
+ "?"
+ para
+ "&p="
+ (currentPage + 1)
+ "'>
+ url + "?" + para + "&p=" + totalPage
+ "'>");
}
sb.append(" " + total + " " + totalResult
+ " " + records + " "
+ totalPage + " " + pagename);
if (this.showSizeOption == true) {
sb.append(" "
+ pagesize
+ " ");
for (int i = 1; i <= 10; i++) {
sb.append("");
} else {
sb.append(">" + (i * 10) + "");
}
}
sb.append("");
}
}
pageStr = sb.toString();
return pageStr;
}
}
Pager的参数页，自己编写了Handler处理器对jdbc灵活配置
package top.powersys.core.pager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.metaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
public class PagerParameterHandler implements ParameterHandler {
private final TypeHandlerRegistry typeHandlerRegistry;
private final MappedStatement mappedStatement;
private final Object parameterObject;
private BoundSql boundSql;
private Configuration configuration;
public PagerParameterHandler(MappedStatement mappedStatement,
Object parameterObject, BoundSql boundSql) {
this.mappedStatement = mappedStatement;
this.configuration = mappedStatement.getConfiguration();
this.typeHandlerRegistry = mappedStatement.getConfiguration()
.getTypeHandlerRegistry();
this.parameterObject = parameterObject;
this.boundSql = boundSql;
}
public Object getParameterObject() {
return parameterObject;
}
@SuppressWarnings({"unchecked", "rawtypes"})
public void setParameters(PreparedStatement ps) throws SQLException {
ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
List parameterMappings = boundSql.getParameterMappings();
if (parameterMappings != null) {
metaObject metaObject = parameterObject == null ? null : configuration.newmetaObject(parameterObject);
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
PropertyTokenizer prop = new PropertyTokenizer(propertyName);
if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
} else if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null) {
value = configuration.newmetaObject(value).getValue(propertyName.substring(prop.getName().length()));
}
} else {
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
TypeHandler typeHandler = parameterMapping.getTypeHandler();
if (typeHandler == null) {
throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
}
typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
}
}
}
}
}
最后，以非侵入的形式，采用拦截器对查询语句拦截拼接
package top.powersys.core.pager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.Set;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.baseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.DatabaseIdProvider;
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.log4j.Logger;
import top.powersys.core.util.ReflectHelper;
import javax.sql.DataSource;
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class,Integer.class})})
public class PagerPlugin implements Interceptor {
private static Logger logger = Logger.getLogger(PagerPlugin.class);
private static String dialect = "";
private static String pageSqlId = ".*ListPage.*";
@SuppressWarnings({"unchecked", "rawtypes"})
public Object intercept(Invocation ivk) throws Throwable {
if (ivk.getTarget() instanceof RoutingStatementHandler) {
RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk
.getTarget();
baseStatementHandler delegate = (baseStatementHandler) ReflectHelper
.getValueByFieldName(statementHandler, "delegate");
MappedStatement mappedStatement = (MappedStatement) ReflectHelper
.getValueByFieldName(delegate, "mappedStatement");
if (mappedStatement.getId().matches(pageSqlId)) {
BoundSql boundSql = delegate.getBoundSql();
Object parameterObject = boundSql.getParameterObject();
if (parameterObject == null) return ivk.proceed();
Pager pageView = null;
if (parameterObject instanceof Map) {
for (Entry entry : (Set) ((Map) parameterObject).entrySet()) {
if (entry.getValue() instanceof Pager) {
pageView = (Pager) entry.getValue();
break;
}
}
} else if (parameterObject instanceof Pager) {
pageView = (Pager) parameterObject;
} else {
pageView = ReflectHelper.getValueByFieldType(parameterObject, Pager.class);
}
if (pageView == null) return ivk.proceed();
String sql = boundSql.getSql();
PreparedStatement countStmt = null;
ResultSet rs = null;
try {
Connection connection = (Connection) ivk.getArgs()[0];
String countSql = "select count(*) from (" + sql + ") tmp_count";
countStmt = connection.prepareStatement(countSql);
ReflectHelper.setValueByFieldName(boundSql, "sql", countSql);
PagerParameterHandler parameterHandler = new PagerParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(countStmt);
rs = countStmt.executeQuery();
int count = 0;
if (rs.next()) count = rs.getInt(1);
pageView.setTotalResult(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
rs.close();
countStmt.close();
}
String pageSql = generatePageSql(sql, pageView);
logger.debug(pageSql);
ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
}
}
return ivk.proceed();
}
private String generatePageSql(String sql, Pager page) {
if (StringUtils.isBlank(dialect)) getDialect();
StringBuilder pageSql = new StringBuilder();
if ("sqlserver".equals(dialect)) {
pageSql.append("SELECt * FROM ( SELECt ROW_NUMBER() OVER(");
pageSql.append(page.getOrderBy());
pageSql.append(") AS r, A.* FROM (");
pageSql.append(sql);
pageSql.append(") A ) B WHERe r between ");
pageSql.append(page.getCurrentResult());// row_number from 1
pageSql.append(" AND ");
pageSql.append(page.getCurrentResult() + page.getShowCount() - 1);
} else if ("mysql".equals(dialect)) {
pageSql.append(sql);
pageSql.append(page.getOrderBy());
pageSql.append(" limit " + (page.getCurrentResult() - 1) + "," + page.getShowCount());
} else if ("oracle".equals(dialect)) {
pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
pageSql.append(sql);
pageSql.append(page.getOrderBy());
pageSql.append(") as tmp_tb where ROWNUM<=");
pageSql.append(page.getCurrentResult() + page.getShowCount());
pageSql.append(") where row_id>");
pageSql.append(page.getCurrentResult());
}
return pageSql.toString();
}
public Object plugin(Object arg0) {
return Plugin.wrap(arg0, this);
}
public void setProperties(Properties p) {
}
private void getDialect() {
try {
dialect = databaseIdProvider.getDatabaseId(dataSource);
} catch (SQLException e) {
e.printStackTrace();
}
}
private DataSource dataSource;
private DatabaseIdProvider databaseIdProvider;
public void setDatabaseIdProvider(DatabaseIdProvider databaseIdProvider) {
this.databaseIdProvider = databaseIdProvider;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
这里，还用到了反射
package top.powersys.core.util;
import java.lang.reflect.Field;
public class ReflectHelper {
public static Field getFieldByFieldName(Object obj, String fieldName) {
for (Class superClass = obj.getClass(); superClass != Object.class; superClass = superClass
.getSuperclass()) {
try {
return superClass.getDeclaredField(fieldName);
} catch (NoSuchFieldException e) {
}
}
return null;
}
public static Object getValueByFieldName(Object obj, String fieldName)
throws SecurityException, NoSuchFieldException,
IllegalArgumentException, IllegalAccessException {
Field field = getFieldByFieldName(obj, fieldName);
Object value = null;
if(field!=null){
if (field.isAccessible()) {
value = field.get(obj);
} else {
field.setAccessible(true);
value = field.get(obj);
field.setAccessible(false);
}
}
return value;
}
public static void setValueByFieldName(Object obj, String fieldName,
Object value) throws SecurityException, NoSuchFieldException,
IllegalArgumentException, IllegalAccessException {
Field field = obj.getClass().getDeclaredField(fieldName);
if (field.isAccessible()) {
field.set(obj, value);
} else {
field.setAccessible(true);
field.set(obj, value);
field.setAccessible(false);
}
}
@SuppressWarnings("unchecked")
public static T getValueByFieldType(Object obj, Class fieldType) {
Object value = null;
for (Class superClass = obj.getClass(); superClass != Object.class; superClass = superClass
.getSuperclass()) {
try {
Field[] fields = superClass.getDeclaredFields();
for (Field f : fields) {
if (f.getType() == fieldType) {
if (f.isAccessible()) {
value = f.get(obj);
break;
} else {
f.setAccessible(true);
value = f.get(obj);
f.setAccessible(false);
break;
}
}
}
if (value != null) {
break;
}
} catch (Exception e) {
}
}
return (T) value;
}
}
由于是经典springmvc项目，配置项也有一些复杂
sqlserver mysql oracle
classpath*:top/powersys*Mapper.xml
虽然说长江后浪推前浪，但看到大佬的封装模式，最后我也是自惭形秽，一套侵入mybatis分页源码，已经说明了基础是多么重要。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）