Mybatis封裝分頁查詢的java公用類

分頁----對於資料量很大的查詢中，是必不可少的。mybatis底層的分頁sql語句由於需要我們自己去手動寫。而實現分頁顯示的時候我們需要根據分頁查詢條件查詢符合條件的總記錄數和記錄的詳細情況。因此，若是不去實現封裝一下的話，我們需要寫兩條SQL語句去實現它。一次用於查詢記錄數目。一次用於查詢分頁顯示的詳細記錄。當專案中碰到很多需要分頁的時候，我們便對於每一個Mapper.xml檔案都需要去寫兩條SQL語句。極其麻煩，程式碼重用----必須重用。所以，一個公共方法的分頁需求應運而生。

直接上分頁公共程式碼，其實現的原理是使用了攔截器的攔截作用。攔截一類分頁查詢的請求。我們根據傳進來的引數是否是需要interceptor()方法中攔截的引數，是的話則攔截，並執行相應的SQL追加，否則，不進行追加。直接放行。視作普通查詢。

   需要在Mybatis的配置檔案中配置載入伺服器的時候載入該公共類:mybatis-config.xml
    <!--?xml version="1.0" encoding="UTF-8"?--&gt
              PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      " />






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.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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
* 分頁攔截器
* @since 10.20.2014
*/
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PaginationInterceptor implements Interceptor {

   private final Logger logger = LoggerFactory.getLogger(PaginationInterceptor.class);

   private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
   private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();

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

         MappedStatement mappedStatement = (MappedStatement)
         metaStatementHandler.getValue("delegate.mappedStatement");
         // 只重寫需要分頁的sql語句。透過MappedStatement的ID匹配，預設重寫以Page結尾的
         // MappedStatement的sql
         BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
         Object parameterObject = boundSql.getParameterObject();

         if(parameterObject instanceof PageParam){
             if (parameterObject == null) {
                 throw new NullPointerException("parameterObject is null!");
             } else {
                 PageParam page = (PageParam)parameterObject;
                 String sql = boundSql.getSql();
                 // 重寫sql
                 String pageSql = buildPageSql(sql, page);
                 metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
                 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();
   }

   private String buildPageSql(String sql, PageParam page) {
        if (page != null) {
            StringBuilder pageSql = new StringBuilder();
                pageSql = buildPageSqlForOracle(sql, page);
            return pageSql.toString();
        } else {
            return sql;
        }
   }

   public StringBuilder buildPageSqlForOracle(String sql, PageParam 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;
   }

   /**
   * 從資料庫裡查詢總的記錄數並計算總頁數，回寫進分頁引數PageParam,這樣呼叫
   * 者就可用透過分頁引數PageParam獲得相關資訊。
   *
   * @param sql
   * @param connection
   * @param mappedStatement
   * @param boundSql
   * @param page
   * @throws SQLException
   */
   private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement,
            BoundSql boundSql, PageParam page) throws SQLException {
        // 記錄總記錄數
        String countSql = "select count(0) from (" + sql + ")";
        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);
            int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);
            page.setTotalPage(totalPage);
        } catch (SQLException e) {
            logger.error("exception", e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("exception", e);
            }
            try {
                countStmt.close();
            } catch (SQLException e) {
                logger.error("exception", e);
            }
        }
   }

   private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
            Object parameterObject) throws SQLException {
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(ps);
   }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties arg0) {
    }
}
將日誌的過濾模式調到DEBUG模式，控制檯可以列印出SQL
使用上述方法處理的分頁查詢，其只需要一條SQL語句就可以(Mapper.xml檔案的SQL)
< select id="selectChannelSettleByParam" parameterType="PageParam" resultMap="RS_CHANNELSETTLE">
       <!--[CDATA[
       ]]>
           from channlsettle where 1=1
           AND CHANNL_ID=#{params.channelSettleModel.channelId}

               <!--[CDATA[
                ]]>


               <!--[CDATA[
                ]]>

           order by INSTDATE desc


控制檯列印的SQL:
第一條:select count(0) from (select * from channlsettle where 1=1 AND CHANNL_ID=? AND to_number(CLEAR_DATE) >= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) <= to_number(substr(?,0,8)) order by INSTDATE desc)

第二條:select * from ( select temp.*, rownum row_id from ( select * from channlsettle where 1=1 AND CHANNL_ID=? AND to_number(CLEAR_DATE) >= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) <= to_number(substr(?,0,8)) order by INSTDATE desc ) temp where rownum <= 20) where row_id > 0

從而讓公共類實現了我們需要在Mapper.xml配置檔案中重複寫入兩條SQL的工作，以後沒一個分頁查詢都可以使用。

java企業級通用許可權安全框架原始碼 SpringMVC mybatis or hibernate+ehcache shiro druid bootstrap HTML5 <wbr>

Mybatis封裝分頁查詢的java公用類

相關文章