乐码库:一个极速、放心、纯净的下载站! 更新: 资源发布
  • 您的位置:首页 > 技术文档 > Java > java分页拦截类实现sql自动分页
  • 收藏本页
      java分页拦截类实现sql自动分页
      发布时间:2016-12-21 08:07:59 关键词: java分页拦截类,sql自动分页
      内容简介:这篇文章主要为大家详细介绍了java分页拦截类可以实现sql自动分页,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

    本文实例为大家分享了完整的java分页拦截类,供大家参考,具体内容如下

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

    以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持乐码库。

      相关内容
      最新更新
      热门排行榜