在实际项目中,大家经常为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<String, Object> param) {
this(currentPage, url, orderby);
StringBuilder paramMap = new StringBuilder();
for (Map.Entry<String, Object> 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();
}
/***
*
* @param currentPage
* @param url
* @param paramWithOrder *
* ob==orderby
* ot==ordertype 0=asc .1(or other)=desc
*/
public Pager(final int currentPage, final String url, final Map<String, Object> 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(" <span data-icon=\"󰛱\"></span> <span data-icon=\"󰛳\"></span>");
} else {
sb.append(" <a href='"
+ url
+ "?"
+ para
+ "&p=1'><span data-icon=\"󰛱\"></span></a> <a href='"
+ url + "?" + para + "&p=" + (currentPage - 1)
+ "'><span data-icon=\"󰛳\"></span></a>");
}
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(" <span class='text-danger'>" + i + "</span>");
else
sb.append(" <a href='" + url + "?" + para + "&p=" + i
+ "'>" + i + "</a>");
}
if (currentPage == totalPage) {
sb.append(" <span data-icon=\"󰛴\"></span> <span data-icon=\"󰛲\"></span>");
} else {
sb.append(" <a href='"
+ url
+ "?"
+ para
+ "&p="
+ (currentPage + 1)
+ "'><span data-icon=\"󰛴\"></span></a> <a href='"
+ url + "?" + para + "&p=" + totalPage
+ "'><span data-icon=\"󰛲\"></span></a>");
}
sb.append(" " + total + " <span class='text-info'>" + totalResult
+ "</span> " + records + " <span class='text-info'>"
+ totalPage + "</span> " + pagename);
if (this.showSizeOption == true) {
sb.append(" "
+ pagesize
+ " <select id=\"perPage\" οnchange=\"if(this.options[this.selectedIndex].value!=''){location='"
+ url + "?");
if (StringUtils.isNotEmpty(para))
sb.append(para + "&");
sb.append("p=1&pagesize='+this.options[this.selectedIndex].value;}\">");
for (int i = 1; i <= 10; i++) {
sb.append("<option value=\"" + (i * 10) + "\"");
if ((i * 10) == showCount) {
sb.append(" selected='selected'>" + (i * 10) + "</option>");
} else {
sb.append(">" + (i * 10) + "</option>");
}
}
sb.append("</select>");
}
}
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<ParameterMapping> 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<Entry>) ((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> T getValueByFieldType(Object obj, Class<T> 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项目,配置项也有一些复杂
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties">
<props>
<prop key="SQL Server">sqlserver</prop>
<prop key="MySQL">mysql</prop>
<prop key="Oracle">oracle</prop>
</props>
</property>
</bean>
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverclass}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="idleConnectionTestPeriodInSeconds" value="14400"/>
<property name="idleMaxAgeInSeconds" value="3600"/>
<property name="partitionCount" value="3"/>
<property name="minConnectionsPerPartition" value="1"/>
<property name="maxConnectionsPerPartition" value="20"/>
<property name="acquireIncrement" value="2"/>
</bean>
<bean id="customSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="databaseIdProvider" ref="databaseIdProvider"/>
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage"
value="top.powersys.system.vo,
top.powersys.log.vo,
top.powersys.parameter.vo,
top.powersys.archive.vo,
top.powersys.remoting.vo,
top.powersys.prepay.vo,
top.powersys.prepayservice.vo,
top.powersys.vendingservice.vo,
top.powersys.analysis.vo,
top.powersys.cmsinterface.vo"/>
<property name="plugins">
<list>
<bean class="top.powersys.core.pager.PagerPlugin">
<property name="databaseIdProvider" ref="databaseIdProvider"/>
<property name="dataSource" ref="dataSource"/>
</bean>
</list>
</property>
<property name="mapperLocations">
<list>
<value>classpath*:top/powersys/**/*Mapper.xml</value>
</list>
</property>
<!--<!–放开可协助查看sql语句–>-->
<!-- <property name="configLocation" value="/WEB-INF/conf/mybatis-config.xml"></property>-->
</bean>
虽然说长江后浪推前浪,但看到大佬的封装模式,最后我也是自惭形秽,一套侵入mybatis分页源码,已经说明了基础是多么重要。