Implementation of Paging Function with MyBatis Interceptor

Implementation Method of Paging Function of MyBatis Interceptor

Foreword:

First of all, let's talk about the implementation principle. Use the interceptor to intercept the original sql, then add the keywords and attributes of the paging query, and assemble the new sql statement to be executed by mybatis.

Apart from business code, there is not much to write, so mention a few key ones:

1. The paging object Page class. Give the object a current number of pages (front-end), the total number of records (interceptor assignment) 2 parameters, he can help you calculate the paging sql statement with 2 parameters.

/**
 *  Entity class corresponding to paging 
 */
public class Page {
  /**
   *  Total number of articles 
   */
  private int totalNumber;
  /**
   *  What page is the current 
   */
  private int currentPage;
  /**
   *  Total pages 
   */
  private int totalPage;
  /**
   *  Number of items displayed per page 
   */
  private int pageNumber = 5;
  /**
   *  In the database limit Parameters of, starting from which article 
   */
  private int dbIndex;
  /**
   *  In the database limit Parameters of, 1 How many articles are taken altogether 
   */
  private int dbNumber;

  /**
   *  Calculate and set related property values based on the property values in the current object 
   */
  public void count() {
    //  Calculate the total number of pages 
    int totalPageTemp = this.totalNumber / this.pageNumber;
    int plus = (this.totalNumber % this.pageNumber) == 0 ? 0 : 1;
    totalPageTemp = totalPageTemp + plus;
    if(totalPageTemp <= 0) {
      totalPageTemp = 1;
    }
    this.totalPage = totalPageTemp;

    //  Set the current number of pages 
    //  The total number of pages is less than the current number of pages, and the current number of pages should be set to the total number of pages 
    if(this.totalPage < this.currentPage) {
      this.currentPage = this.totalPage;
    }
    //  The current number of pages is less than 1 Set to 1
    if(this.currentPage < 1) {
      this.currentPage = 1;
    }

    //  Settings limit Parameters of 
    this.dbIndex = (this.currentPage - 1) * this.pageNumber;
    this.dbNumber = this.pageNumber;
  }

  public int getTotalNumber() {
    return totalNumber;
  }

  public void setTotalNumber(int totalNumber) {
    this.totalNumber = totalNumber;
    this.count();
  }

  public int getCurrentPage() {
    return currentPage;
  }

  public void setCurrentPage(int currentPage) {
    this.currentPage = currentPage;
  }

  public int getTotalPage() {
    return totalPage;
  }

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

  public int getPageNumber() {
    return pageNumber;
  }

  public void setPageNumber(int pageNumber) {
    this.pageNumber = pageNumber;
    this.count();
  }

  public int getDbIndex() {
    return dbIndex;
  }

  public void setDbIndex(int dbIndex) {
    this.dbIndex = dbIndex;
  }

  public int getDbNumber() {
    return dbNumber;
  }

  public void setDbNumber(int dbNumber) {
    this.dbNumber = dbNumber;
  }
}

2. Key interceptor implementation

package com.imooc.interceptor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import com.imooc.entity.Page;

/**
 *  Paging interceptor 
 * 
 * @author Skye
 *
 */
@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class }) })
public class PageInterceptor implements Interceptor {

  public Object intercept(Invocation invocation) throws Throwable {
    StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
    MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
        SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
    MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
    // Pass MetaObject Metadata fetch method name id : com.XXX.queryMessageListByPage
    String id = mappedStatement.getId();
    // Matches in mybatis Paging-related queries defined in id
    if (id.matches(".+ByPage$")) {
      //BoundSql There are primitive ones in sql Statement and the corresponding query parameters 
      BoundSql boundSql = statementHandler.getBoundSql();
      Map<String, Object> params = (Map<String, Object>) boundSql.getParameterObject();
      Page page = (Page) params.get("page");
      String sql = boundSql.getSql();
      String countSql = "select count(*)from (" + sql + ")a";
      Connection connection = (Connection) invocation.getArgs()[0];
      PreparedStatement countStatement = connection.prepareStatement(countSql);
      ParameterHandler parameterHandler = (ParameterHandler) metaObject.getValue("delegate.parameterHandler");
      parameterHandler.setParameters(countStatement);
      ResultSet rs = countStatement.executeQuery();
      if (rs.next()) {
        // Why is it getInt ( 1 ) ?  Because the columns of the data table are derived from 1 Start counting 
        page.setTotalNumber(rs.getInt(1));
        System.out.println(" The interceptor learned that page The total number of records of is: " + page.getTotalNumber());
      }
      String pageSql = sql + " limit " + page.getDbIndex() + "," + page.getDbNumber();
      metaObject.setValue("delegate.boundSql.sql", pageSql);
    }
    return invocation.proceed();
  }

  /**
   * @param target
   *  Intercepted object 
   */
  public Object plugin(Object target) {
    //  If the interceptor class is compared to a company that buys tickets, then this It is a purchasing salesman (before entering the method, it is a salesman without agent ticket purchase ability, and after entering, it becomes a salesman with agent ability) 
    //  Obtain the information of interception target by annotation, return to the original target if it does not meet the interception requirement, and use dynamic proxy to generate proxy object if it meets the interception requirement 
    return Plugin.wrap(target, this);
  }

  public void setProperties(Properties properties) {
    // TODO Auto-generated method stub

  }

}

3. Register the interceptor written by yourself in mybatis-config. xml

 <!--  Custom paging interceptor  -->
  <plugins>
    <plugin interceptor=" The full class name of the interceptor you wrote ">
    </plugin>
  </plugins>

The sql statement in mapper. xml related to the Dao layer does not need to be changed.

4. The front end needs to give the back end a parameter to display which page. After assembling the query parameters through service layer, it is sent to MyBatis to check the paging data. The data returned by the paging DAO interface I defined is an list, which contains the paging query results. The front end can use jquery_pagination plug-in to realize paging display, and go to the official github to see how to set it.

<!--pagination Required scripts -->
<%
  //  Gets the context of the request 
  String context = request.getContextPath();
%>
<link href="../css/pagination.css" rel="external nofollow" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="../js/jquery-1.11.3.js"></script>
<script type="text/javascript" src="../js/jquery.pagination.js"></script>
<script type="text/javascript">

//  Actions triggered after clicking the page button 
function handlePaginationClick(new_page_index, pagination_container) {
<!-- From stuForm The form submits the parameters of the current page . You can use the restful The way, let springmvc Use @PathVariable Keyword to join the formal parameters defined by the. This 2 Parameters are provided by the paging control itself, so we don't need to find them ourselves, but the count is from 0 Start, and our background paging count starts from 1 Start, so add it manually 1 .  -->
  $("#stuForm").attr("action", " The pagination query you defined url/"+(new_page_index+1));
  $("#stuForm").submit();
  return false;
}

$(function(){
  $("#News-Pagination").pagination(${result.totalRecord}, {
    items_per_page:${result.pageSize}, //  How many records are displayed per page 
    current_page:${result.currentPage} - 1, //  What page of data is currently displayed 
    num_display_entries:8, //  Number of entries paged 
    next_text:" Under 1 Page ",
    prev_text:" Upper 1 Page ",
    num_edge_entries:2, //  Connect the paging body, the number of entries displayed 
    callback:handlePaginationClick( Current page, paging div Adj. id), // Callback function executed 
    load_first_page:false // Prevent page 1 Direct refresh (  This one is very important! )
  });
});
</script>
<!--  This part uses c:forEach Label prints a table of query results -->
<!-- Paging control name -->
<div id="News-Pagination"></div>

The purpose of writing this summary is to form an overall solution of paging function (both front-end and back-end are covered). On April 17, 18, I will write a small system to use what I learned some time ago. After that, I will come back to update the incorrect places in this article.

If you have any questions, please leave a message or go to this site community to exchange and discuss, thank you for reading, hope to help everyone, thank you for your support to this site!