Mybatis Physical Pagination_ZenDei技術網路在線

1. Requirements: when we use the sql like "select * from targetTable", we get all records of the table, but we usually just need one page of records(a ...

1. Requirements:

    when we use the sql like "select * from targetTable", we get all records of the table,

    but we usually just need one page of records(about 10 records).

    so we need to change the sql sentences.

2. Solution(Mybatis Physical Pagination):

   2.1 Mybatis help us to reduce the difficult of operating database, its interceptor is a good tool to change the original SQL sentence.

    Let's begin!

   2.2 Page 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.Configuration;
import org.apache.ibatis.session.RowBounds;

/**
 * 通過攔截<code>StatementHandler</code>的<code>prepare</code>方法，重寫sql語句實現物理分頁。
 * 
 */
@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();
    private static String defaultDialect = "oracle"; // 資料庫類型(預設為mysql)
    private static String defaultPageSqlId = ".*Page$"; // 需要攔截的ID(正則匹配)
    private static String dialect = "oracle"; // 資料庫類型(預設為mysql)
    private static String pageSqlId = ""; // 需要攔截的ID(正則匹配)

    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);
        }
        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 'oracle' ");
            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 {
                PageParameter page = (PageParameter) 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);

            }
        }
        // 將執行權交給下一個攔截器
        return invocation.proceed();
    }
/**
     * 根據資料庫類型，生成特定的分頁sql
     * 
     * @param sql
     * @param page
     * @return
     */
    private String buildPageSql(String sql, PageParameter 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, PageParameter page) {
        StringBuilder pageSql = new StringBuilder(100);
        String beginrow = String.valueOf((page.getPageNo() - 1) * page.getPageSize());
        pageSql.append(sql);
        pageSql.append(" limit " + beginrow + "," + page.getPageSize());
        return pageSql;
    }

    /**
     * oracle的分頁
     * 
     * @param sql
     * @param page
     * @return String
     */
    public StringBuilder buildPageSqlForOracle(String sql, PageParameter page) {
        StringBuilder pageSql = new StringBuilder(100);
        String beginrow = String.valueOf((page.getPageNo() - 1) * page.getPageSize());
        String endrow = String.valueOf(page.getPageNo() * 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;
    }

    public Object plugin(Object target) {
        // 當目標類是StatementHandler類型時，才包裝目標類，否者直接返回目標本身,減少目標被代理的次數
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    public void setProperties(Properties properties) {
    }

}

2.3 Page Object

/**
 * 分頁參數類
 * 
 */
public class PageParameter {

    public static final int DEFAULT_PAGE_SIZE = 10;

    private int pageSize;
    private int pageNo;
    private int prePage;
    private int nextPage;
    private int totalPage;
    private int totalCount;

    public PageParameter() {
        this.pageNo = 1;
        this.pageSize = DEFAULT_PAGE_SIZE;
    }
    /**
     * get method for reflect
     * @return
     */
    public PageParameter getPage(){
        return new PageParameter();
    }

    /**
     * 
     * @param currentPage
     * @param pageSize
     */
    public PageParameter(int pageNo, int pageSize) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
    }

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPrePage() {
        return prePage;
    }

    public void setPrePage(int prePage) {
        this.prePage = prePage;
    }

    public int getNextPage() {
        return nextPage;
    }

    public void setNextPage(int nextPage) {
        this.nextPage = nextPage;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

}

2.4 MVC Controller

import org.apache.commons.io.filefilter.FalseFileFilter;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

import javax.annotation.Resource;
import com.cdv.ppms.core.feature.orm.mybatis.PageParameter;
import com.cdv.ppms.web.model.ObjProgramWithBLOBs;
import com.cdv.ppms.web.service.ProgramService;
import com.cdv.ppms.web.service.impl.ProgramServiceImpl;
/**
 * 節目管理
 * @author rocky
 *
 */
@Controller
@RequestMapping("/program/")
public class ProgramController {
    @Resource
    private ProgramService programService;

    @RequestMapping("uneditedProgram")
    public String uneditedProgram(@RequestParam(required=false) Integer pageNo , Model model){
        PageParameter page = new PageParameter();
        if(pageNo!=null && pageNo>1){
            page.setPageNo(pageNo);
        }
        List<ObjProgramWithBLOBs> programList = programService.selectProgramListPage(page);
        int totalCount = programService.selectTotalCount();
        page.setTotalCount(totalCount);
        page.setTotalPage(totalCount%page.getPageSize()==0 ? totalCount/page.getPageSize() : totalCount/page.getPageSize()+1);
        model.addAttribute("programList", programList);
        model.addAttribute("page", page);
        return "/program/program_unedited";
    }
}

2.5 JSP page

    <%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
            + path + "/";
    
%>
 <div class="row">
                                <div class="gigantic pagination">
                      <a href="#" class="first" data-action="first">&laquo;</a>
                      <a href="#" class="previous" data-action="previous">&lsaquo;</a>
                      <input type="text" readonly="readonly" data-max-page="40" />
                      <a href="#" class="next" data-action="next">&rsaquo;</a>
                      <a href="#" class="last" data-action="last">&raquo;</a>
                    </div>
                </div>

<link rel="stylesheet" href="<%=path %>/static/css/jqpagination.css"/>
    <link rel="stylesheet" href="<%=path %>/static/css/demo.css"/>
    <script src="<%=path %>/static/script/jquery.jqpagination.min.js"></script>
    <script>
    var path = '<%=path %>';
    var pageNo = ${page.pageNo};
    var totalPage = ${page.totalPage};
    $('.pagination').jqPagination({
          link_string : path+'/rest/program/uneditedProgram?pageNo={page_number}',
          current_page: pageNo, //設置當前頁 預設為1
          max_page : totalPage, //設置最大頁 預設為1
          page_string : '當前第{current_page}頁,共{max_page}頁',
          paged : function(page) {
              console.log(page);
              $("#pageNo").val(page);
              $("#pageForm").submit();
          }
        });
    </script>

2.6 mybatis config file

    <plugins>
        <!--Paging Interceptor   -->
        <plugin interceptor="com.cdv.ppms.core.feature.orm.mybatis.PageInterceptor"/>
    </plugins>

2.7 jqPagination Plugin

https://github.com/beneverard/jqPagination