设计思想：
1、分页sql与业务sql解耦合设计。
2、多线并发执行count(*)查询sql及分页sql。
3、站在巨人的肩膀上进行优化。
上代码：
PageInterceptor.java
package com.jy.platform.restservice.mybatis;
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import javax.sql.DataSource;
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.mapping.ParameterMapping;
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.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import com.jy.platform.core.message.PageParameter;
/**
* 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。
* 老规矩，签名里要拦截的类型只能是接口。
*
*
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PageInterceptor implements Interceptor {
private static final Log logger = LogFactory.getLog(PageInterceptor.class);
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
private static String defaultDialect = "oracle"; // 数据库类型(默认为mysql)
private static String defaultPageSqlId = ".*ByPaging$"; // 需要拦截的ID(正则匹配)
private static String dialect = "oracle"; // 数据库类型(默认为mysql)
private static String pageSqlId = ""; // 需要拦截的ID(正则匹配)
@Autowired
private DataSource dataSource;
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY);
// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
}
// 分离最后一个代理对象的目标类
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
}
Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");
if(configuration.getVariables()!=null){
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;
}
}else{
dialect = defaultDialect;
pageSqlId = defaultPageSqlId;
}
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
// 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的MappedStatement的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 {
PageParameter page = (PageParameter) metaStatementHandler
.getValue("delegate.boundSql.parameterObject.page");
String sql = boundSql.getSql();
// 重写sql
String pageSql = buildPageSql(sql, page);
String endrow = String.valueOf(page.getCurrentPage() * page.getPageSize());
String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if(parameterMappings==null||parameterMappings.size()==0){
parameterMappings=new ArrayList<ParameterMapping>();
}
ParameterMapping endParameterMapping = buildParameterMapping(BigDecimal.class,"endRown",configuration);
ParameterMapping beginParameterMapping = buildParameterMapping(BigDecimal.class,"beginRown",configuration);
parameterMappings.add(endParameterMapping);//end
parameterMappings.add(beginParameterMapping);//begin
boundSql.setAdditionalParameter("endRown", new BigDecimal(endrow));
boundSql.setAdditionalParameter("beginRown", new BigDecimal(beginrow));
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
metaStatementHandler.setValue("delegate.boundSql.parameterMappings", parameterMappings);
// 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
//新启线程计算totalCount，新线程执行完成后，page对象的totalCount已经设置好，新线程未执行完之前，page.getTotalCount()将阻塞
ExecutorService executor = Executors.newFixedThreadPool(1);
PageCountTask task = new PageCountTask(sql, page, mappedStatement, boundSql, dataSource);
Future futureResult = executor.submit(task);
page.setFutureResult(futureResult);
executor.shutdown();
}
}
// 将执行权交给下一个拦截器
return invocation.proceed();
}
/**
* 根据数据库类型，生成特定的分页sql
*
* @param sql
* @param page
* @return
*/
private String buildPageSql(String sql, PageParameter 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的分页语句
*
* @param sql
* @param page
* @return String
*/
public StringBuilder buildPageSqlForMysql(String sql, PageParameter page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
pageSql.append(sql);
pageSql.append(" limit " + beginrow + "," + page.getPageSize());
return pageSql;
}
/**
* 参考hibernate的实现完成oracle的分页
*
* @param sql
* @param page
* @return String
*/
public StringBuilder buildPageSqlForOracle(String sql, PageParameter page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
String endrow = String.valueOf(page.getCurrentPage() * 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;
}
@Override
public Object plugin(Object target) {
// 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
private ParameterMapping buildParameterMapping(Class<?> propertyType,String property,Configuration configuration) {
ParameterMapping.Builder builder = new ParameterMapping.Builder(configuration, property, propertyType);
return builder.build();
}
}
计算count()
PageCountTask.java
package com.jy.platform.restservice.mybatis;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import javax.sql.DataSource;
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.scripting.defaults.DefaultParameterHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.jy.platform.core.message.PageParameter;
/**
* Mybatis拦截器PageInterceptor中启动的任务，用于新启线程，计算totalCount总数量
*/
@SuppressWarnings("all")
public class PageCountTask implements Callable{
private final Logger logger = LoggerFactory.getLogger(PageCountTask.class);
private String originalSql;//原始的查询结果集的SQL
private PageParameter page;
private MappedStatement mappedStatement;
private BoundSql boundSql;
private DataSource dataSource;
public PageCountTask(String originalSql, PageParameter page, MappedStatement mappedStatement, BoundSql boundSql, DataSource dataSource){
this.originalSql = originalSql;
this.page = page;
this.mappedStatement = mappedStatement;
this.boundSql = boundSql;
this.dataSource = dataSource;
}
@Override
public Object call() throws Exception{
Connection connection = dataSource.getConnection();
//记录总记录数
String countSql = "select count(0) from (" + originalSql + ") total";
PreparedStatement countStmt = null;
ResultSet rs = null;
try {
countStmt = connection.prepareStatement(countSql);
//去除追加的endRown beginRown
List<ParameterMapping> countToalMapping = new ArrayList<ParameterMapping>();
countToalMapping.addAll(boundSql.getParameterMappings());
if(countToalMapping.size() > 0){
for(int i = countToalMapping.size()-1 ;i >= 0;i--){
ParameterMapping para = countToalMapping.get(i);
String pro = para.getProperty();
if("endRown".equals(pro)||"beginRown".equals(pro))
countToalMapping.remove(para);
}
}
//生成count相关的BoundSql
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,countToalMapping, boundSql.getParameterObject());
//往countStmt中设置参数
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBS);
parameterHandler.setParameters(countStmt);
//执行count
rs = countStmt.executeQuery();
int totalCount = 0;
if (rs.next()) totalCount = rs.getInt(1);
page.setTotalCount(totalCount);
int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);
page.setTotalPage(totalPage);
logger.debug(countSql.replaceAll("[\\t\\n\\r]", "").replaceAll("[\\\\]", ".").replaceAll("\"", ""));
//logger.debug(String.valueOf(boundSql.getParameterObject()));
logger.debug("Total:"+String.valueOf(totalCount));
} catch(Exception e){
logger.error("Ignore this exception", e);
} finally{
try{
if(rs != null) rs.close();
} catch(Exception e){
logger.error("Ignore this exception", e);
}
try{
if(countStmt != null) countStmt.close();
}catch(Exception e){
logger.error("Ignore this exception", e);
}
try{
if(connection != null) connection.close();
} catch(Exception e){
logger.error("Ignore this exception", e);
}
//总数统计完后 清空 futureResult
page.setFutureResult(null);
}
return "get";
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
设置page参数信息
PageParameter.java
package com.jy.platform.core.message;
import java.util.concurrent.Future;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 分页参数类
*
*/
public class PageParameter{
private static final Logger logger = LoggerFactory.getLogger(PageParameter.class);
public static final int DEFAULT_PAGE_SIZE = 20;
private int pageSize;
private int currentPage;
private int totalPage;
private int totalCount;
private Future futureResult;
public Future getFutureResult() {
return futureResult;
}
public void setFutureResult(Future futureResult) {
this.futureResult = futureResult;
}
public PageParameter() {
this.currentPage = 1;
this.pageSize = DEFAULT_PAGE_SIZE;
}
/**
*
* @param currentPage
* @param pageSize
*/
public PageParameter(int currentPage, int pageSize) {
this.currentPage = currentPage;
this.pageSize = pageSize;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalPage() {
try{
if(futureResult!=null && "get".equals((String)futureResult.get())){
futureResult = null;
return totalPage;
}
} catch(Exception e){
logger.error("page.totalPage",e);
}
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getTotalCount() {
try {
if(futureResult!=null && "get".equals((String)futureResult.get())){
futureResult = null;
return totalCount;
}
} catch(Exception e){
logger.error("page.totalCount",e);
}
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("PageParameter [pageSize=");
builder.append(pageSize);
builder.append(", currentPage=");
builder.append(currentPage);
builder.append(", totalPage=");
builder.append(totalPage);
builder.append(", totalCount=");
builder.append(totalCount);
builder.append("]");
return builder.toString();
}
}
4171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
