Mybatis paging method: 1. Use arrays for paging. First query all the data, and then intercept the required parts from the list. 2. Use the Sql statement to perform paging, and add the limit paging statement after the sql statement. 3. Use the interceptor for paging, and add the limit statement to the end of the sql statement through the interceptor to perform paging queries. 4. To use RowBounds to implement paging, you need to obtain all qualified data at once, and then operate the big data in memory to achieve the paging effect.

#The operating environment of this tutorial: windows7 system, java8, Dell G3 computer.

Mybatis’ paging methods can be divided into two categories:

1. Logical paging

2. Physical paging

The specific way of paging in mybatis

• Paging with the help of arrays (logical paging)

• With the help of Sql statements Paging (physical paging)

• Interceptor paging (physical paging) Use the interceptor to add a limit statement to the end of the sql statement to query, once and for all the best

• RowBounds implements paging (logical paging)

#1. Paging with the help of arrays

Principle: Database During the query operation, all records that meet the conditions in the database are obtained and stored in the temporary array of the application. Then, all records that meet the conditions are obtained through the subList method of List.

Implementation:

First, create the StudentMapper interface at the dao layer for database operations. Define the query method for paging through the array in the interface, as shown below:

 List<Student> queryStudentsByArray();

The method is very simple, that is, to get all the data, receive it through the list, and then perform the paging operation.

Create the StudentMapper.xml file and write the query sql statement:

 <select id="queryStudentsByArray"  resultMap="studentmapper">
        select * from student
 </select>

It can be seen that when writing the sql statement, we did not perform any paging related operations. All student information can be found here.

Next, obtain the data in the service layer and implement paging:

Define the IStuService interface and define the paging method:

List<Student> queryStudentsByArray(int currPage, int pageSize);

Indicate which page to display by receiving the currPage parameter Data, pageSize represents the number of data items displayed on each page.

Create the IStuService interface implementation class StuServiceIml to implement the method, and paginate the obtained array through currPage and pageSize:

 @Override
    public List<Student> queryStudentsByArray(int currPage, int pageSize) {
        List<Student> students = studentMapper.queryStudentsByArray();
//        從第幾條數(shù)據(jù)開始
        int firstIndex = (currPage - 1) * pageSize;
//        到第幾條數(shù)據(jù)結(jié)束
        int lastIndex = currPage * pageSize;
        return students.subList(firstIndex, lastIndex);
    }

Through the subList method, obtain all the data between the two indexes.

Finally create a test method in the controller:

  @ResponseBody
    @RequestMapping("/student/array/{currPage}/{pageSize}")
    public List<Student> getStudentByArray(@PathVariable("currPage") int currPage, @PathVariable("pageSize") int pageSize) {
        List<Student> student = StuServiceIml.queryStudentsByArray(currPage, pageSize);
        return student;
    }

Get the specified data through the currPage and pageSize passed in by the user.

Test:

First we get all the data obtained before the paging effect is implemented, as shown below:

Continue Come down and enter http://localhost:8080/student/student/array/1/2 in the browser to test the paging data. Get the data on the first page and display two pieces of data on each page.

The results are as follows:

The output is the specified data from 0-2, which shows that our paging function through the array is successful. (Because related queries are used here, it seems that there may be a lot of data)

Disadvantages: The database queries and returns all the data, and what we need is only a very small amount of data that meets the requirements. When the amount of data is small, it is acceptable. When the amount of database data is too large, each query will have a great impact on the performance of the database and program.

2. Use Sql statements for paging

After learning about the shortcomings of paging through arrays, we found that we cannot perform paging in the database every time. All data are retrieved. Then perform secondary operations on the large amount of data obtained in the program, which consumes a lot of space and performance. Therefore, we hope to directly retrieve only records that meet the conditions in the database language without processing them through the program. At this time, Sql statement paging technology came into being.

Implementation: It is also very simple to implement paging through sql statements. We just need to change the statement of our query to achieve it, that is, add a limit paging statement after the sql statement.

First of all, add the sql statement query method in the StudentMapper interface, as follows:

List<Student> queryStudentsBySql(Map<String,Object> data);

Then write the sql statement in the StudentMapper.xml file to paginate through the limiy keyword:

 <select id="queryStudentsBySql" parameterType="map" resultMap="studentmapper">
        select * from student limit #{currIndex} , #{pageSize}
</select>

Next, define the method in the IStuService interface, and implement sql paging in StuServiceIml.

List<Student> queryStudentsBySql(int currPage, int pageSize);

 @Override
    public List<Student> queryStudentsBySql(int currPage, int pageSize) {
        Map<String, Object> data = new HashedMap();
        data.put("currIndex", (currPage-1)*pageSize);
        data.put("pageSize", pageSize);
        return studentMapper.queryStudentsBySql(data);
    }

The sql paging statement is as follows: select * from table limit index, pageSize;

So the currIndex is calculated in the service: the first record to be queried index.

Test:

Enter in the browser http://localhost:8080/student/student/sql/1/2 to get the data of the first page, each page Display two pieces of data.

result:

從輸出結(jié)果可以看出和數(shù)組分頁的結(jié)果是一致的，因此sql語句的分頁也是沒問題的。

缺點(diǎn)：雖然這里實(shí)現(xiàn)了按需查找，每次檢索得到的是指定的數(shù)據(jù)。但是每次在分頁的時(shí)候都需要去編寫limit語句，很冗余。而且不方便統(tǒng)一管理，維護(hù)性較差。所以我們希望能夠有一種更方便的分頁實(shí)現(xiàn)。

3、攔截器分頁

上面提到的數(shù)組分頁和sql語句分頁都不是我們今天講解的重點(diǎn)，今天需要實(shí)現(xiàn)的是利用攔截器達(dá)到分頁的效果。自定義攔截器實(shí)現(xiàn)了攔截所有以ByPage結(jié)尾的查詢語句，并且利用獲取到的分頁相關(guān)參數(shù)統(tǒng)一在sql語句后面加上limit分頁的相關(guān)語句，一勞永逸。不再需要在每個(gè)語句中單獨(dú)去配置分頁相關(guān)的參數(shù)了。。

首先我們看一下攔截器的具體實(shí)現(xiàn)，在這里我們需要攔截所有以ByPage結(jié)尾的所有查詢語句，因此要使用該攔截器實(shí)現(xiàn)分頁功能，那么再定義名稱的時(shí)候需要滿足它攔截的規(guī)則（以ByPage結(jié)尾），如下所示：

package com.cbg.interceptor;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.sql.Connection;
import java.util.Map;
import java.util.Properties;

/**
 * Created by chenboge on 2017/5/7.
 * <p>
 * Email:baigegechen@gmail.com
 * <p>
 * description:
 */

/**
 * @Intercepts 說明是一個(gè)攔截器
 * @Signature 攔截器的簽名
 * type 攔截的類型 四大對(duì)象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
 * method 攔截的方法
 * args 參數(shù)
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MyPageInterceptor implements Interceptor {

//每頁顯示的條目數(shù)
    private int pageSize;
//當(dāng)前現(xiàn)實(shí)的頁數(shù)
    private int currPage;

    private String dbType;


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //獲取StatementHandler，默認(rèn)是RoutingStatementHandler
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //獲取statementHandler包裝類
        MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);

        //分離代理對(duì)象鏈
        while (MetaObjectHandler.hasGetter("h")) {
            Object obj = MetaObjectHandler.getValue("h");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }

        while (MetaObjectHandler.hasGetter("target")) {
            Object obj = MetaObjectHandler.getValue("target");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }

        //獲取連接對(duì)象
        //Connection connection = (Connection) invocation.getArgs()[0];


        //object.getValue("delegate");  獲取StatementHandler的實(shí)現(xiàn)類

        //獲取查詢接口映射的相關(guān)信息
        MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
        String mapId = mappedStatement.getId();

        //statementHandler.getBoundSql().getParameterObject();

        //攔截以.ByPage結(jié)尾的請(qǐng)求，分頁功能的統(tǒng)一實(shí)現(xiàn)
        if (mapId.matches(".+ByPage$")) {
            //獲取進(jìn)行數(shù)據(jù)庫操作時(shí)管理參數(shù)的handler
            ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
            //獲取請(qǐng)求時(shí)的參數(shù)
            Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
            //也可以這樣獲取
            //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();

            //參數(shù)名稱和在service中設(shè)置到map中的名稱一致
            currPage = (int) paraObject.get("currPage");
            pageSize = (int) paraObject.get("pageSize");

            String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
            //也可以通過statementHandler直接獲取
            //sql = statementHandler.getBoundSql().getSql();

            //構(gòu)建分頁功能的sql語句
            String limitSql;
            sql = sql.trim();
            limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize;

            //將構(gòu)建完成的分頁sql語句賦值個(gè)體&#39;delegate.boundSql.sql&#39;，偷天換日
            MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
        }
//調(diào)用原對(duì)象的方法，進(jìn)入責(zé)任鏈的下一級(jí)
        return invocation.proceed();
    }


    //獲取代理對(duì)象
    @Override
    public Object plugin(Object o) {
    //生成object對(duì)象的動(dòng)態(tài)代理對(duì)象
        return Plugin.wrap(o, this);
    }

    //設(shè)置代理對(duì)象的參數(shù)
    @Override
    public void setProperties(Properties properties) {
//如果項(xiàng)目中分頁的pageSize是統(tǒng)一的，也可以在這里統(tǒng)一配置和獲取，這樣就不用每次請(qǐng)求都傳遞pageSize參數(shù)了。參數(shù)是在配置攔截器時(shí)配置的。
        String limit1 = properties.getProperty("limit", "10");
        this.pageSize = Integer.valueOf(limit1);
        this.dbType = properties.getProperty("dbType", "mysql");
    }
}

上面即是攔截器功能的實(shí)現(xiàn)，在intercept方法中獲取到select標(biāo)簽和sql語句的相關(guān)信息，攔截所有以ByPage結(jié)尾的select查詢，并且統(tǒng)一在查詢語句后面添加limit分頁的相關(guān)語句，統(tǒng)一實(shí)現(xiàn)分頁功能。

重點(diǎn)詳解：

StatementHandler是一個(gè)接口，而我們?cè)诖a中通過StatementHandler statementHandler = (StatementHandler) invocation.getTarget();獲取到的是StatementHandler默認(rèn)的實(shí)現(xiàn)類RoutingStatementHandler。而RoutingStatementHandler只是一個(gè)中間代理，他不會(huì)提供具體的方法。那你可能會(huì)納悶了，攔截器中基本上是依賴statementHandler獲取各種對(duì)象和屬性的，沒有具體屬性和方法怎么行？？接著看下面代碼：

private final StatementHandler delegate;

    public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        switch(RoutingStatementHandler.SyntheticClass_1.$SwitchMap$org$apache$ibatis$mapping$StatementType[ms.getStatementType().ordinal()]) {
        case 1:
            this.delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
            break;
        case 2:
            this.delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
            break;
        case 3:
            this.delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
            break;
        default:
            throw new ExecutorException("Unknown statement type: " + ms.getStatementType());
        }

    }

原來它是通過不同的MappedStatement創(chuàng)建不同的StatementHandler實(shí)現(xiàn)類對(duì)象處理不同的情況。這里的到的StatementHandler實(shí)現(xiàn)類才是真正服務(wù)的?？吹竭@里，你可能就會(huì)明白MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");中delegate的來源了吧。至于為什么要這么去獲取，后面我們會(huì)說道。

拿到statementHandler后，我們會(huì)通過MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);去獲取它的包裝對(duì)象，通過包裝對(duì)象去獲取各種服務(wù)。

MetaObject：mybatis的一個(gè)工具類，方便我們有效的讀取或修改一些重要對(duì)象的屬性。四大對(duì)象（ResultSetHandler，ParameterHandler，Executor和statementHandler）提供的公共方法很少，要想直接獲取里面屬性的值很困難，但是可以通過MetaObject利用一些技術(shù)（內(nèi)部反射實(shí)現(xiàn)）很輕松的讀取或修改里面的數(shù)據(jù)。

接下來說說：MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");

上面提到為什么要這么去獲取MappedStatement對(duì)象？？在RoutingStatementHandler中delegate是私有的（private final StatementHandler delegate;），有沒有共有的方法去獲取。所以這里只有通過反射來獲取啦。

MappedStatement是保存了xxMapper.xml中一個(gè)sql語句節(jié)點(diǎn)的所有信息的包裝類，可以通過它獲取到節(jié)點(diǎn)中的所有信息。在示例中我們拿到了id值，也就是方法的名稱，通過名稱區(qū)攔截所有需要分頁的請(qǐng)求。

通過StatementHandler的包裝類，不光能拿到MappedStatement，還可以拿到下面的數(shù)據(jù)：

    public abstract class BaseStatementHandler implements StatementHandler {
    protected final Configuration configuration;
    protected final ObjectFactory objectFactory;
    protected final TypeHandlerRegistry typeHandlerRegistry;
    protected final ResultSetHandler resultSetHandler;
    protected final ParameterHandler parameterHandler;
    protected final Executor executor;
    protected final MappedStatement mappedStatement;
    protected final RowBounds rowBounds;
    protected BoundSql boundSql;

    protected BaseStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        this.configuration = mappedStatement.getConfiguration();
        this.executor = executor;
        this.mappedStatement = mappedStatement;
        this.rowBounds = rowBounds;
        this.typeHandlerRegistry = this.configuration.getTypeHandlerRegistry();
        this.objectFactory = this.configuration.getObjectFactory();
        if(boundSql == null) {
            this.generateKeys(parameterObject);
            boundSql = mappedStatement.getBoundSql(parameterObject);
        }

        this.boundSql = boundSql;
        this.parameterHandler = this.configuration.newParameterHandler(mappedStatement, parameterObject, boundSql);
        this.resultSetHandler = this.configuration.newResultSetHandler(executor, mappedStatement, rowBounds, this.parameterHandler, resultHandler, boundSql);
    }

上面的所有數(shù)據(jù)都可以通過反射拿到。

幾個(gè)重要的參數(shù)：

• Configuration：所有配置的相關(guān)信息。

• ResultSetHandler：用于攔截執(zhí)行結(jié)果的組裝。

• ParameterHandler：攔截執(zhí)行Sql的參數(shù)的組裝。

• Executor：執(zhí)行Sql的全過程，包括組裝參數(shù)、組裝結(jié)果和執(zhí)行Sql的過程。

• BoundSql：執(zhí)行的Sql的相關(guān)信息。

接下來我們通過如下代碼拿到請(qǐng)求時(shí)的map對(duì)象（反射）。

 //獲取進(jìn)行數(shù)據(jù)庫操作時(shí)管理參數(shù)的handler
            ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
            //獲取請(qǐng)求時(shí)的參數(shù)
            Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
            //也可以這樣獲取
            //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();

拿到我們需要的currPage和pageSize參數(shù)后，就是組裝分頁查詢的sql語句’limitSql‘了。

最后通過MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);將原始的sql語句替換成我們新的分頁語句，完成偷天換日的功能，接下來讓代碼繼續(xù)執(zhí)行。

編寫好攔截器后，需要注冊(cè)到項(xiàng)目中，才能發(fā)揮它的作用。在mybatis的配置文件中，添加如下代碼：

    <plugins>
        <plugin interceptor="com.cbg.interceptor.MyPageInterceptor">
            <property name="limit" value="10"/>
            <property name="dbType" value="mysql"/>
        </plugin>
    </plugins>

如上所示，還能在里面配置一些屬性，在攔截器的setProperties方法中可以獲取配置好的屬性值。如項(xiàng)目分頁的pageSize參數(shù)的值固定，我們就可以配置在這里了，以后就不需要每次傳入pageSize了，讀取方式如下：

 //讀取配置的代理對(duì)象的參數(shù)
    @Override
    public void setProperties(Properties properties) {
        String limit1 = properties.getProperty("limit", "10");
        this.pageSize = Integer.valueOf(limit1);
        this.dbType = properties.getProperty("dbType", "mysql");
    }

到這里，有關(guān)攔截器的相關(guān)知識(shí)就講解的差不多了，接下來就需要測(cè)試，是否我們這樣寫真的有效？？

首先還是添加dao層的方法和xml文件的sql語句配置，注意項(xiàng)目中攔截的是以ByPage結(jié)尾的請(qǐng)求，所以在這里，我們的方法名稱也以此結(jié)尾：

方法
List<Student> queryStudentsByPage(Map<String,Object> data);

xml文件的select語句
    <select id="queryStudentsByPage" parameterType="map" resultMap="studentmapper">
        select * from student
    </select>

可以看出，這里我們就不需要再去手動(dòng)配置分頁語句了。

接下來是service層的接口編寫和實(shí)現(xiàn)方法：

方法：
List<Student> queryStudentsByPage(int currPage,int pageSize);

實(shí)現(xiàn)：
 @Override
    public List<Student> queryStudentsByPage(int currPage, int pageSize) {
        Map<String, Object> data = new HashedMap();
        data.put("currPage", currPage);
        data.put("pageSize", pageSize);
        return studentMapper.queryStudentsByPage(data);
    }

這里我們雖然傳入了currPage和pageSize兩個(gè)參數(shù)，但是在sql的xml文件中并沒有使用，直接在攔截器中獲取到統(tǒng)一使用。

最后編寫controller的測(cè)試代碼：

 @ResponseBody
    @RequestMapping("/student/page/{currPage}/{pageSize}")
    public List<Student> getStudentByPage(@PathVariable("currPage") int currPage, @PathVariable("pageSize") int pageSize) {
        List<Student> student = StuServiceIml.queryStudentsByPage(currPage, pageSize);
        return student;
    }

測(cè)試：

在瀏覽器輸入：http://localhost:8080/student/student/page/1/2

結(jié)果：

可見和上面兩種分頁的效果是一樣的。

4、RowBounds實(shí)現(xiàn)分頁

原理：通過RowBounds實(shí)現(xiàn)分頁和通過數(shù)組方式分頁原理差不多，都是一次獲取所有符合條件的數(shù)據(jù)，然后在內(nèi)存中對(duì)大數(shù)據(jù)進(jìn)行操作，實(shí)現(xiàn)分頁效果。只是數(shù)組分頁需要我們自己去實(shí)現(xiàn)分頁邏輯，這里更加簡化而已。

存在問題：一次性從數(shù)據(jù)庫獲取的數(shù)據(jù)可能會(huì)很多，對(duì)內(nèi)存的消耗很大，可能導(dǎo)師性能變差，甚至引發(fā)內(nèi)存溢出。

適用場景：在數(shù)據(jù)量很大的情況下，建議還是適用攔截器實(shí)現(xiàn)分頁效果。RowBounds建議在數(shù)據(jù)量相對(duì)較小的情況下使用。

簡單介紹：這是代碼實(shí)現(xiàn)上最簡單的一種分頁方式，只需要在dao層接口中要實(shí)現(xiàn)分頁的方法中加入RowBounds參數(shù)，然后在service層通過offset（從第幾行開始讀取數(shù)據(jù)，默認(rèn)值為0）和limit（要顯示的記錄條數(shù)，默認(rèn)為java允許的最大整數(shù)：2147483647）兩個(gè)參數(shù)構(gòu)建出RowBounds對(duì)象，在調(diào)用dao層方法的時(shí)，將構(gòu)造好的RowBounds傳進(jìn)去就能輕松實(shí)現(xiàn)分頁效果了。

具體操作如下：

dao層接口方法：

//加入RowBounds參數(shù)
public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds);

然后在service層構(gòu)建RowBounds，調(diào)用dao層方法：

  @Override
    @Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.SUPPORTS)
    public List<RoleBean> queryRolesByPage(String roleName, int start, int limit) {
        return roleDao.queryRolesByPage(roleName, new RowBounds(start, limit));
    }

RowBounds就是一個(gè)封裝了offset和limit簡單類，如下所示：

public class RowBounds {
    public static final int NO_ROW_OFFSET = 0;
    public static final int NO_ROW_LIMIT = 2147483647;
    public static final RowBounds DEFAULT = new RowBounds();
    private int offset;
    private int limit;

    public RowBounds() {
        this.offset = 0;
        this.limit = 2147483647;
    }

    public RowBounds(int offset, int limit) {
        this.offset = offset;
        this.limit = limit;
    }

    public int getOffset() {
        return this.offset;
    }

    public int getLimit() {
        return this.limit;
    }
}

只需要這兩步操作，就能輕松實(shí)現(xiàn)分頁效果了，是不是很神奇。但卻不簡單，內(nèi)部是怎么實(shí)現(xiàn)的？？給大家提供一個(gè)簡單的思路：RowBounds分頁簡單原理

結(jié)論：從上面四種sql分頁的實(shí)現(xiàn)方式可以看出，通過RowBounds實(shí)現(xiàn)是最簡便的，但是通過攔截器的實(shí)現(xiàn)方式是最優(yōu)的方案。只需一次編寫，所有的分頁方法共同使用，還可以避免多次配置時(shí)的出錯(cuò)機(jī)率，需要修改時(shí)也只需要修改這一個(gè)文件，一勞永逸。而且是我們自己實(shí)現(xiàn)的，便于我們?nèi)タ刂坪驮黾右恍┻壿嬏幚?，使我們?cè)谕鈱痈唵蔚氖褂谩Ｍ瑫r(shí)也不會(huì)出現(xiàn)數(shù)組分頁和RowBounds分頁導(dǎo)致的性能問題。當(dāng)然，具體情況可以采取不同的解決方案。數(shù)據(jù)量小時(shí)，RowBounds不失為一種好辦法。但是數(shù)據(jù)量大時(shí)，實(shí)現(xiàn)攔截器就很有必要了。

【相關(guān)推薦：編程教學(xué)】

The above is the detailed content of Several ways of paging in mybatis. For more information, please follow other related articles on the PHP Chinese website!