mybatis 物理分页 mysql_mybatis实战系列(三)mybatis中的分页使用 动态分页 物理分页...

package cn.xhgg.utils;

import java.lang.reflect.Field;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import java.util.Properties;

import org.apache.ibatis.executor.ErrorContext;

import org.apache.ibatis.executor.ExecutorException;

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.MappedStatement;

import org.apache.ibatis.mapping.ParameterMapping;

import org.apache.ibatis.mapping.ParameterMode;

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

@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})

public class PagePlugin implements Interceptor {

private static String dialect = ""; //

private static String pageSqlId = ""; //mapper.xml

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

throw new NullPointerException("parameterObject��δʵ��");

}else{

Connection connection = (Connection)ivk.getArgs()[0];

String sql = boundSql.getSql();

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

PreparedStatement countStmt = connection.prepareStatement(countSql);

BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(),countSql,boundSql.getParameterMappings(),parameterObject);

setParameters(countStmt,mappedStatement,countBS,parameterObject);

ResultSet rs = countStmt.executeQuery();

int count = 0;

if (rs.next()) {

count = rs.getInt(1);

}

rs.close();

countStmt.close();

Page page = null;

if(parameterObject instanceof Page){ //

page = (Page) parameterObject;

page.setTotalCount(count);

}else{ //

Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page");

if(pageField!=null){

page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page");

if(page==null)

page = new Page();

page.setTotalCount(count);

ReflectHelper.setValueByFieldName(parameterObject,"page", page); //ͨ

}else{

throw new NoSuchFieldException(parameterObject.getClass().getName()+"������ page ���ԣ�");

}

}

String pageSql = generatePageSql(sql,page);

ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); //

}

}

}

return ivk.proceed();

}

/** * org.apache.ibatis.executor.parameter.DefaultParameterHandler * @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */

private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException {

ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());

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

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

}

}

}

}

/** * * @param sql * @param page * @return */

private String generatePageSql(String sql,Page page){

if(page!=null && dialect!=null && dialect.trim().length()>0){

StringBuffer pageSql = new StringBuffer();

if("mysql".equals(dialect)){

pageSql.append(sql);

pageSql.append(" limit "+page.getStart()+","+page.getPageShow());

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

pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");

pageSql.append(sql);

pageSql.append(") as tmp_tb where ROWNUM<=");

pageSql.append(page.getStart()+page.getPageShow());

pageSql.append(") where row_id>");

pageSql.append(page.getStart());

}

return pageSql.toString();

}else{

return sql;

}

}

public Object plugin(Object arg0) {

// TODO Auto-generated method stub

return Plugin.wrap(arg0, this);

}

public void setProperties(Properties p) {

dialect = p.getProperty("dialect");

// if (dialect!=null && dialect.trim().length()>0) {

// System.out.println("dialect property is not found!");

// }

pageSqlId = p.getProperty("pageSqlId");

// if (pageSqlId!=null && pageSqlId.trim().length()>0) {

// System.out.println("pageSqlId property is not found!");

// }

}

}