mybatis 物理分页 mysql_MyBatis实现物理分页

importorg.apache.ibatis.executor.parameter.ParameterHandler;importorg.apache.ibatis.executor.statement.StatementHandler;importorg.apache.ibatis.logging.Log;importorg.apache.ibatis.logging.LogFactory;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.plugin.*;importorg.apache.ibatis.reflection.MetaObject;importorg.apache.ibatis.reflection.factory.DefaultObjectFactory;importorg.apache.ibatis.reflection.factory.ObjectFactory;importorg.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;importorg.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;importorg.apache.ibatis.scripting.defaults.DefaultParameterHandler;importorg.apache.ibatis.session.RowBounds;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.Properties;/*** 通过拦截StatementHandler的prepare方法，重写sql语句实现物理分页。*/@Intercepts({@Signature(type= StatementHandler.class, method = "prepare", args = {Connection.class})})public class PageInterceptor implementsInterceptor {private static final ObjectFactory DEFAULT_OBJECT_FACTORY = newDefaultObjectFactory();private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = newDefaultObjectWrapperFactory();private static String defaultDialect = "mysql"; //数据库类型(默认为mysql)

private static String defaultPageSqlId = ".*Page$"; //需要拦截的ID(正则匹配)

private static String dialect = ""; //数据库类型(默认为mysql)

private static String pageSqlId = ""; //需要拦截的ID(正则匹配)

@Overridepublic Object intercept(Invocation invocation) throwsThrowable {

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);

}if (null == dialect || "".equals(dialect)) {

logger.warn("Property dialect is not setted,use default 'mysql' ");

dialect=defaultDialect;

}if (null == pageSqlId || "".equals(pageSqlId)) {

logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");

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{

Page page=(Page) metaStatementHandler

.getValue("delegate.boundSql.parameterObject.page");

String sql=boundSql.getSql();//重写sql

String pageSql =buildPageSql(sql, page);

metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);//采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数

metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);

metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);

Connection connection= (Connection) invocation.getArgs()[0];//重设分页参数里的总页数等

setPageParameter(sql, connection, mappedStatement, boundSql, page);

}

}//将执行权交给下一个拦截器

returninvocation.proceed();

}/*** 从数据库里查询总的记录数并计算总页数，回写进分页参数PageParameter,这样调用者就可用通过 分页参数

* PageParameter获得相关信息。

*

*@paramsql

*@paramconnection

*@parammappedStatement

*@paramboundSql

*@parampage*/

private voidsetPageParameter(String sql, Connection connection, MappedStatement mappedStatement,

BoundSql boundSql, Page page) {//记录总记录数

String countSql = "select count(0) from (" + sql + ") as total";

PreparedStatement countStmt= null;

ResultSet rs= null;try{

countStmt=connection.prepareStatement(countSql);

BoundSql countBS= newBoundSql(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);int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);

page.setTotalPage(totalPage);

}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);

}

}

}/*** 对SQL参数(?)设值

*

*@paramps

*@parammappedStatement

*@paramboundSql

*@paramparameterObject

*@throwsSQLException*/

private voidsetParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,

Object parameterObject)throwsSQLException {

ParameterHandler parameterHandler= newDefaultParameterHandler(mappedStatement, parameterObject, boundSql);

parameterHandler.setParameters(ps);

}/*** 根据数据库类型，生成特定的分页sql

*

*@paramsql

*@parampage

*@return

*/

privateString buildPageSql(String sql, Page page) {if (page != null) {

StringBuilder pageSql= newStringBuilder();if ("mysql".equals(dialect)) {

pageSql=buildPageSqlForMysql(sql, page);

}else if ("oracle".equals(dialect)) {

pageSql=buildPageSqlForOracle(sql, page);

}else{returnsql;

}returnpageSql.toString();

}else{returnsql;

}

}/*** mysql的分页语句

*

*@paramsql

*@parampage

*@returnString*/

publicStringBuilder buildPageSqlForMysql(String sql, Page page) {

StringBuilder pageSql= new StringBuilder(100);

String beginrow= String.valueOf((page.getCurrentPage()-1) *page.getPageSize());

pageSql.append(sql);

pageSql.append(" limit " + beginrow + "," +page.getPageSize());returnpageSql;

}/*** 参考hibernate的实现完成oracle的分页

*

*@paramsql

*@parampage

*@returnString*/

publicStringBuilder buildPageSqlForOracle(String sql, Page 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);returnpageSql;

}

@OverridepublicObject plugin(Object target) {//当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数

if (target instanceofStatementHandler) {return Plugin.wrap(target, this);

}else{returntarget;

}

}

@Overridepublic voidsetProperties(Properties properties) {

}public static voidsetDialect(String dialect) {

PageInterceptor.dialect=dialect;

}public static voidsetPageSqlId(String pageSqlId) {

PageInterceptor.pageSqlId=pageSqlId;

}

}