最近有接触到Mybatis的分页处理，网上找了一些资料，实践了一些实现方法，也写一下博客，作为学习的记录。
网上给出的通用的方式基本上都是：通过Mybatis的拦截，重新拼接SQL（添上limit，offset）。很多地方理解不到位，还只是停留在借鉴了直接使用的情况。
1、首先定义一些基础数据类Page,PageContext,Dialect,MySqlDialect
package com.sg.base.page;
import java.util.List;
public class Page implements java.io.Serializable {
private static final long serialVersionUID = -8322296629302943918L;
protected int pageSize = 10; // 每页默认10条数据
protected int currentPage = 1; // 当前页
protected int totalPages = 0; // 总页数
protected int totalRows = 0; // 总数据数
protected int pageStartRow = 0; // 每页的起始行数
protected int pageEndRow = 0; // 每页显示数据的终止行数
//get、set方法
public Page() {}
public void init(int totalRows) {
this.init(totalRows, this.pageSize, this.currentPage);
}
/**
* 初始化分页参数:需要先设置totalRows
*/
public void init(int rows, int pageSize,int currentPage ) {
this.pageSize = pageSize;
this.totalRows = rows;
this.totalPages = (this.totalRows/this.pageSize)+(this.totalRows%this.pageSize>0?1:0);
if(currentPage>0)gotoPage(currentPage);
}
/**
* 计算当前页的取值范围：pageStartRow和pageEndRow
*/
private void calculatePage() {
hasPreviousPage = (currentPage - 1) > 0?true:false;
hasNextPage = currentPage >= totalPages?false:true;
int pageStartRecord = currentPage * pageSize;
boolean isLessThanTotalRows = pageStartRecord < totalRows;
pageEndRow = isLessThanTotalRows?pageStartRecord:totalRows;
pageStartRow = isLessThanTotalRows?pageEndRow - pageSize:pageSize * (totalPages - 1);
}
/**
* 直接跳转到指定页数的页面
*/
public void gotoPage(int page) {
currentPage = page;
calculatePage();
debug();
}
public void debug() {
System.out.println("要显示的页面数据已经封装,具体信息如下：");
String debug = "共有数据数:" + totalRows + "\n共有页数:" + totalPages + "\n当前页数为:"
+ currentPage + "\n是否有前一页:" + hasPreviousPage + "是否有下一页:"
+ hasNextPage + "\n开始行数:" + pageStartRow + "\n终止行数:" + pageEndRow;
System.out.println(debug);
}
boolean hasNextPage = false; // 是否有下一页
boolean hasPreviousPage = false; // 是否有前一页
public boolean isHasNextPage() {
return hasNextPage;
}
public boolean isHasPreviousPage() {
return hasPreviousPage;
}
//用的datatable分页插件，有用到一下参数（高版本datatable的参数变了）
protected int sEcho; //当前页
protected int iTotalRecords; //总记录数
protected int iTotalDisplayRecords; //显示总记录数
private List<?> aaData; // 返回的结果
//get、set方法
}/**
* 主要作用:在当前请求线程中保存分页对象,使得分页对象可以在线程内共享,从而达到更小的依赖性
*/
public class PageContext extends Page {
private static final long serialVersionUID = 2491790900505242096L;
private static ThreadLocal<PageContext> context = new ThreadLocal<PageContext>();
public static PageContext getContext(){
PageContext ci = context.get();
if(ci == null) {
ci = new PageContext();
context.set(ci);
}
return ci;
}
public static void removeContext() {
context.remove();
}
protected void initialize() {}
}/**
* 类似hibernate的Dialect,但只精简出分页部分
*/
public class Dialect {
public boolean supportsLimit(){
return false;
}
public boolean supportsLimitOffset() {
return supportsLimit();
}
/**
* 将sql变成分页sql语句,直接使用offset,limit的值作为占位符.</br>
* 源代码为: getLimitString(sql,offset,String.valueOf(offset),limit,String.valueOf(limit))
*/
public String getLimitString(String sql, int offset, int limit) {
return getLimitString(sql,offset,Integer.toString(offset),limit,Integer.toString(limit));
}
/**
* 将sql变成分页sql语句,提供将offset及limit使用占位符(placeholder)替换.
* <pre>
* 如mysql
* dialect.getLimitString("select * from user", 12, ":offset",0,":limit") 将返回
* select * from user limit :offset,:limit
* </pre>
* @return 包含占位符的分页sql
*/
public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit,String limitPlaceholder) {
throw new UnsupportedOperationException("paged queries not supported");
}
}public class MySqlDialect extends Dialect {
public String getLimitString(String sql, int offset, int limit) {
sql = sql.trim();
StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
pagingSelect.append(sql)；
pagingSelect.append(" limit "+offset+" , "+(offset + limit));
return pagingSelect.toString();
}
}2、在Mybatis配置文件（mybatis-config.xml）中添加拦截
<plugins>
<plugin interceptor="com.sg.base.page.PaginationInterceptor">
<property name="dialectClass" value="com.sg.base.page.MySqlDialect"/>
</plugin>
</plugins>3、编写拦截类PaginationInterceptor
package com.sg.base.page;
//只拦截select部分
@Intercepts({@Signature(type=Executor.class,method="query",args={ MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class })})
public class PaginationInterceptor implements Interceptor{
private final static Log log = LogFactory.getLog(PaginationInterceptor.class);
//数据库方言类
private static String dialectClass;
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement=(MappedStatement)invocation.getArgs()[0];
String id = mappedStatement.getId();
log.debug("MappedStatement id = "+id);
//这里用的方式是查询方法带PAGEQUERY关键字
if(id.toUpperCase().contains("PAGEQUERY")){
PageContext page=PageContext.getContext();
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
if(boundSql==null || StringUtils.isEmpty(boundSql.getSql()))
return null;
String originalSql = boundSql.getSql().trim();//originalSql==null?boundSql.getSql().trim():originalSql;
//得到总记录数 这里看是否需要传递总记录数以避免重复获得总记录数？
int totalRecord = countRecords(originalSql,mappedStatement,boundSql);
//分页计算
page.init(totalRecord);
final Dialect dialect;
try
{
//加载实例化配置文件中的具体的Dialect
Class<? extends Dialect> clazz = (Class<? extends Dialect>) Class.forName(dialectClass);
dialect = clazz.newInstance();
}
catch (Exception e)
{
throw new ClassNotFoundException("Cannot create dialect instance: " + dialectClass, e);
}
//添加上limit，offset
String pagesql=dialect.getLimitString(originalSql, page.getPageStartRow(),page.getPageSize());
invocation.getArgs()[2] = RowBounds.DEFAULT;
//复制得到新的MappedStatement
MappedStatement newMappedStatement = copyFromNewSql(mappedStatement, boundSql, pagesql, boundSql.getParameterMappings(), boundSql.getParameterObject());
invocation.getArgs()[0]= newMappedStatement;
}
return invocation.proceed();
}
/**
* 拦截器对应的封装原始对象的方法
*/
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* 设置注册拦截器时设定的属性
*/
public void setProperties(Properties properties) {
log.debug("================ setProperties ===========");
dialectClass = properties.getProperty("dialectClass");
if (dialectClass == null || "".equals(dialectClass))
{
throw new RuntimeException("Mybatis分页插件 ExecutorInterceptor 无法获取 dialectClass 参数!");
}
}
private int countRecords(String originalSql,MappedStatement mappedStatement,BoundSql boundSql) throws SQLException{
Connection connection = null;
PreparedStatement countStmt = null;
ResultSet rs = null;
try{
int totalRecorld = 0;
Object paramObject = boundSql.getParameterObject();
StringBuilder countSql = new StringBuilder(originalSql.length()+100 );
countSql.append("select count(1) from (").append(originalSql).append(") t");
connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
countStmt = connection.prepareStatement(countSql.toString());
BoundSql countBS = copyFromBoundSql(mappedStatement, boundSql, countSql.toString(), boundSql.getParameterMappings(), paramObject);
setParameters(countStmt,mappedStatement,countBS,paramObject);
rs = countStmt.executeQuery();
if (rs.next()) {
totalRecorld = rs.getInt(1);
}
return totalRecorld;
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){};
if(countStmt!=null)try{countStmt.close();}catch(Exception e){};
if(connection!=null)try{connection.close();}catch(Exception e){};
}
}
public static class BoundSqlSqlSource implements SqlSource {
BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
/**
* 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
* @param ps
* @param mappedStatement
* @param boundSql
* @param parameterObject
* @throws java.sql.SQLException
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException {
ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (parameterMappings != null) {
Configuration configuration = mappedStatement.getConfiguration();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
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 = null;
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());
}
}
}
}
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
Builder builder = new Builder(ms.getConfiguration(),ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
String[] keys = ms.getKeyProperties();
if(keys!=null){
String keysstr = Arrays.toString(keys);
keysstr = keysstr.replace("[","");
keysstr = keysstr.replace("]","");
builder.keyProperty(keysstr);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.cache(ms.getCache());
MappedStatement newMs = builder.build();
return newMs;
}
private MappedStatement copyFromNewSql(MappedStatement mappedStatement, BoundSql boundSql,
String sql, List<ParameterMapping> parameterMappings, Object parameter)
{
BoundSql newBoundSql = copyFromBoundSql(mappedStatement, boundSql, sql, parameterMappings, parameter);
return copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
}
private BoundSql copyFromBoundSql(MappedStatement mappedStatement, BoundSql boundSql,
String sql, List<ParameterMapping> parameterMappings, Object parameter)
{
BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), sql, parameterMappings, parameter);
for (ParameterMapping mapping : boundSql.getParameterMappings()){
String prop = mapping.getProperty();
if (boundSql.hasAdditionalParameter(prop)) {
newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}
return newBoundSql;
}
}
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
