java分页拦截类实现sql自动分页

package com.opms.interceptor;


import java.sql.connection;
import java.sql.preparedstatement;
import java.sql.resultset;
import java.sql.sqlexception;
import java.util.properties;

import org.apache.ibatis.executor.parameter.parameterhandler;
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.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.scripting.defaults.defaultparameterhandler;
import org.apache.ibatis.session.rowbounds;

import com.wifi.core.page.page;

/**
 * 通过拦截<code>statementhandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。
 * 老规矩，签名里要拦截的类型只能是接口。
 * 
 * @author 湖畔微风
 * 
 */
@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();
 /**
  * 数据库类型(默认为mysql)
  */
 private static string defaultdialect = "mysql"; 
 /**
  * 需要拦截的id(正则匹配)
  */
 private static string defaultpagesqlid = ".*4page$"; 
 /**
  * 数据库类型(默认为mysql) 
  */
 private static string dialect = ""; 
 /**
  * 需要拦截的id(正则匹配)
  */
 private static string pagesqlid = ""; 
 /**
  * @param invocation 参数
  * @return object
  * @throws throwable 抛出异常
  */
 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);
  }
  dialect=defaultdialect;pagesqlid=defaultpagesqlid;
  /* configuration configuration = (configuration) metastatementhandler.getvalue("delegate.configuration");
  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;
  }*/
  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 {
    object obj = metastatementhandler
      .getvalue("delegate.boundsql.parameterobject.page");
    // 传入了page参数且需要开启分页时
    if(obj!=null&&obj instanceof page &&((page)obj).ispagination()){
     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);
    }
   }
  }
  // 将执行权交给下一个拦截器
  return invocation.proceed();
 }

 /**
  * 从数据库里查询总的记录数并计算总页数，回写进分页参数<code>pageparameter</code>,这样调用者就可用通过 分页参数
  * <code>pageparameter</code>获得相关信息。
  * 
  * @param sql 参数
  * @param connection 连接
  * @param mappedstatement 参数
  * @param boundsql 绑定sql
  * @param page 页
  */
 private void setpageparameter(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 = new boundsql(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);
   page.init(page.getcurpage(), page.getpagesize(), totalcount);

  } 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参数(?)设值
  * 
  * @param ps 参数
  * @param mappedstatement 参数
  * @param boundsql 绑定sql
  * @param parameterobject 参数对象
  * @throws sqlexception 抛出sql异常
  */
 private void setparameters(preparedstatement ps, mappedstatement mappedstatement, boundsql boundsql,
   object parameterobject) throws sqlexception {
  parameterhandler parameterhandler = new defaultparameterhandler(mappedstatement, parameterobject, boundsql);
  parameterhandler.setparameters(ps);
 }

 /**
  * 根据数据库类型，生成特定的分页sql
  * 
  * @param sql 餐宿
  * @param page 页
  * @return string
  */
 private string buildpagesql(string sql, page 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, page page) {
  stringbuilder pagesql = new stringbuilder(100);
  string beginrow = string.valueof((page.getcurpage() - 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, page page) {
  stringbuilder pagesql = new stringbuilder(100);
  string beginrow = string.valueof((page.getcurpage() - 1) * page.getpagesize());
  string endrow = string.valueof(page.getcurpage() * 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;
 }
 /**
  * @param target 参数
  * @return object
  */
 public object plugin(object target) {
  // 当目标类是statementhandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
  if (target instanceof statementhandler) {
   return plugin.wrap(target, this);
  } else {
   return target;
  }
 }
 /**
  * @param properties 参数
  */
 public void setproperties(properties properties) {
 }

}