Mybatis包分頁查詢java公用類

標籤：

Mybatis包分頁查詢java公用類
  分頁----對於資料量非常大的查詢中。是不可缺少的。

mybatis底層的分頁sql語句因為須要我們自己去手動寫。而實現分頁顯示的時候我們須要依據分頁查詢條件查詢合格總記錄數和記錄的具體情況。因此，若是不去實現封裝一下的話，我們須要寫兩條SQL語句去實現它。一次用於查詢記錄數目。一次用於查詢分頁顯示的具體記錄。

當項目中碰到非常多須要分頁的時候，我們便對於每個Mapper.xml檔案都須要去寫兩條SQL語句。

極其麻煩。代碼重用----必須重用。所以，一個公用方法的分頁需求應運而生。

  直接上分頁公用代碼，事實上現的原理是使用了攔截器的攔截作用。攔截一類分頁查詢的請求。我們依據傳進來的參數是否是須要interceptor()方法中攔截的參數，是的話則攔截，並運行對應的SQL追加，否則，不進行追加。直接允許存取。視作普通查詢。
 
   須要在Mybatis的設定檔裡配置載入server的時候載入該公用類:mybatis-config.xml
    <?xml version="1.0" encoding="UTF-8"?>
       <!DOCTYPE configuration
       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
       <configuration>
                <properties></properties>
               <plugins>
                       <plugin interceptor="com.iboxpay.clear.filter.PaginationInterceptor"></plugin>
              </plugins>
      </configuration>


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;  
    }  
    
    /**
     * 從資料庫裡查詢總的記錄數並計算總頁數，回寫進分頁參數<code>PageParam</code>,這樣調用  
     * 者就可用通過 分頁參數<code>PageParam</code>獲得相關資訊。

     *  
     * @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[
            select  *
        ]]>
            from channlsettle where 1=1
            <if test="params.channelSettleModel.channelId != null and 1000 != params.channelSettleModel.channelId">AND CHANNL_ID=#{params.channelSettleModel.channelId}</if>
            <if test="params.channelSettleModel.clearStartTime != null and ‘‘ != params.channelSettleModel.clearStartTime">
                <![CDATA[  
                       AND to_number(CLEAR_DATE) >= to_number(substr(#{params.channelSettleModel.clearStartTime},0,8))   
                 ]]>
            </if>
            <if test="params.channelSettleModel.clearEndTime != null and ‘‘ != params.channelSettleModel.clearEndTime">
                <![CDATA[  
                       AND to_number(CLEAR_DATE) <= to_number(substr(#{params.channelSettleModel.clearEndTime},0,8))   
                 ]]>
            </if>
            order by INSTDATE desc
    </select>

控制台列印的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的工作，以後沒一個分頁查詢都能夠使用。

著作權聲明：本文部落格原創文章，部落格，未經同意，不得轉載。

Mybatis包分頁查詢java公用類