If you are using MyBatis, it is recommended to try this paging plugin. It is definitely the most convenient paging plugin to use.

The need for paging plug-ins

Paging is ubiquitous in Internet applications, and can be found in every place where data needs to be displayed. In everyday development, physical paging of a database is often used for efficiency. At this point, for a business logic SQL, most of the time you need to output two sections of SQL to achieve paging effect: count query total and limit paging, which adds a lot of work. For this kind of large, similar, non-business logic code, it is imperative to abstract out common plug-ins.

Principles of paging plug-ins

Mybatis provides developers with an interceptor interface, as long as the implementation of the interface, Mybatis before the execution of SQL, some custom operations. The paging plug-in is developed on this basis. For a SQL that needs paging, the plug-in intercepts and generates two sections of SQL. Here’s a simple example:

The original SQL:

select * from table where a = '1'

Copy the code

SQL:

select count(*) from table where a = '1'

Copy the code

Paged SQL:

Select * from table where a = '1' limit 5,10

Copy the code

This way we only need to develop the original SQL according to the business logic, and we don’t need to worry about the effect of paging syntax on the original SQL. The interceptor already takes care of that for us. More information about interceptors can be found at:

  • QueryInterceptor source
  • Executor Interceptor Advanced Tutorial – QueryInterceptor specification

Supported databases

The plug-in currently supports physical paging for the following databases:

  1. Oracle
  2. Mysql
  3. MariaDB
  4. SQLite
  5. Hsqldb
  6. PostgreSQL
  7. DB2
  8. Essentially a (2005200)
  9. Informix
  10. H2
  11. SqlServer2012
  12. Derby
  13. Phoenix

And for SpringMVC integration

Maven rely on

<! -- Paging plugin -->

<dependency>

    <groupId>com.github.pagehelper</groupId>

    <artifactId>pagehelper</artifactId>

The < version > 5.0.0 < / version >

</dependency>

Copy the code

Spring configuration file

Mybatis = SqlSessionFactoryBean = SqlSessionFactoryBean = SqlSessionFactoryBean

Original configuration mode:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

    <property name="dataSource" ref="dynamicDataSource"/>

    <property name="mapperLocations">

        <list>

            <value>classpath:mapper/*.xml</value>

            <value>classpath:mapper/*/*.xml</value>

        </list>

    </property>

</bean>

Copy the code

Plus the configuration of the paging plug-in:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

    <property name="dataSource" ref="dynamicDataSource"/>

    <property name="mapperLocations">

        <list>

            <value>classpath:mapper/*.xml</value>

            <value>classpath:mapper/*/*.xml</value>

        </list>

    </property>

<! -- Configure paging plug-in -->

    <property name="plugins">

        <array>

            <bean class="com.github.pagehelper.PageInterceptor">

                <property name="properties">

                    <value>

                        helperDialect=postgresql

                        reasonable=true

                    </value>

                </property>

            </bean>

        </array>

    </property>

</bean>

Copy the code

You can see that only the configuration is added. you can configure paging parameters. In general, you can configure the database type helperDialect. The complete parameters are as follows: 

<! -- This parameter defaults to false -->

<! -- When set to true, the first RowBounds argument offset is used as pageNum page number -->

<! PageNum in startPage -->

<property name="offsetAsPageNum" value="true"/>

<! -- This parameter defaults to false -->

<! -- When set to true, count queries are performed using RowBounds paging -->

<property name="rowBoundsWithCount" value="true"/>

<! When set to true, pageSize=0 or rowbounds. limit =0 will query all results -->

<! -->

<property name="pageSizeZero" value="true"/>

<! -- Version 3.3.0 available -- paging parameters rationalized, default false disabled -->

<! If pageNum<1, select * from pageNum; if pageNum>pages, select * from pageNum;

<! -- If pageNum<1 or pageNum>pages returns null data -->

<property name="reasonable" value="true"/>

<! -- Version 3.5.0 available -- to support the startPage(Object Params) method

<! -- Added a 'params' parameter to configure parameter mapping for values from Map or ServletRequest -->

<! - you can configure the pageNum, pageSize, count, pageSizeZero, reasonable, not configure mapping with default values -- -- >

<property name="params" value="pageNum=start; pageSize=limit; pageSizeZero=zero; reasonable=heli; count=contsql"/>

Copy the code

Use in code

The static pageHelper. startPage method is called before the Mybatis method that needs to be paginated. The first Mybatis query method immediately following this method will be paginated, and the paging plug-in will encapsulate the paging information into PageInfo.

// startPage(number of pages, number of data)

PageHelper.startPage(pageIndex, pageSize);

// Mybatis

List<InstanceVO> list = instanceDao.select(instance);

// Wrap the result with PageInfo

PageInfo pageInfo = new PageInfo(list);

Copy the code

In this non-invasive approach to the original SQL, you can get the paging effect and detailed paging information. PageInfo contains very comprehensive pagination properties:

public class PageInfo<T> implements Serializable {

    private static final long serialVersionUID = 1L;

/ / the current page

    private int pageNum;

// Number of pages per page

    private int pageSize;

// The number of current pages

    private int size;

// Since startRow and endRow are not commonly used, here is a specific usage

// Display startRow to endRow data

// The database line number of the first element of the current page

    private int startRow;

// The database line number of the last element of the current page

    private int endRow;

// Total number of records

    private long total;

/ / the total number of pages

    private int pages;

/ / the result set

    private List<T> list;

/ / the first page

    private int firstPage;

/ / the previous page

    private int prePage;

/ / the next page

    private int nextPage;

// Last page

    private int lastPage;

// If it is the first page

    private boolean isFirstPage = false;

// If it is the last page

    private boolean isLastPage = false;

// Is there a previous page

    private boolean hasPreviousPage = false;

// Is there a next page

    private boolean hasNextPage = false;

// Navigation page number

    private int navigatePages;

// All navigation page numbers

    private int[] navigatepageNums; 

.

}

Copy the code

Concrete examples

The original SQL:

select 

    id,name,create_time,create_user_id,update_time,update_user_id,is_delete 

from xxx.aaa 

where 

    ( is_delete = ? )

Copy the code

SQL after interception (from Mybatis log) :

# count

select count(0) from xxx.aaa WHERE (is_delete = ?)

Copy the code
# paging

select 

    id,name,create_time,create_user_id,update_time,update_user_id,is_delete 

from xxx.aaa

where 

    ( is_delete = ? ) LIMIT 3

Copy the code

Json data returned: The returned data is encapsulated with a custom class Result, models is business data, and Paging is paging information.

{

	"models":[

		{

			"createTime":1508890619000,

			"createUserId":"888888",

			"id":3,

			"isDelete":0,

"Name ":" [TEST] ",

			"updateTime":1512373972000,

			"updateUserId":"888888"

		},

		{

			"createTime":1508890619000,

			"createUserId":"888888",

			"id":4,

			"isDelete":0,

			"name":"bbb",

			"updateTime":1508891132000,

			"updateUserId":"888888"

		},

		{

			"createTime":1508890619000,

			"createUserId":"888888",

			"id":5,

			"isDelete":0,

			"name":"ccc",

			"updateTime":1508891132000,

			"updateUserId":"888888"

		}

].

	"paging":{

		"endRow":3,

		"firstPage":1,

		"hasNextPage":true,

		"hasPreviousPage":false,

		"isFirstPage":true,

		"isLastPage":false,

		"lastPage":5,

		"navigateFirstPage":1,

		"navigateLastPage":5,

		"navigatePages":8,

"NavigatepageNums" : [1, 2, 3, 4, 5],

		"nextPage":2,

		"pageNum":1,

		"pageSize":3,

		"pages":5,

		"prePage":0,

		"size":3,

		"startRow":1,

		"total":15

	},

	"resultCode":"100",

	"success":true,

	"valid":true

}

Copy the code

conclusion

When using paging plug-in, there is no need to handwritten paging SQL and count SQL in the paging place, there is no need to change the existing business code, just need to call a code before executing SQL can achieve paging, and get rich paging information. With this paging information, the front end can choose from a variety of paging methods, very convenient!

reference

  • Github.com/pagehelper/…
  • Gitee.com/free/Mybati…
  • www.ciphermagic.cn/mybatis-pag…

  • Author: Cipher
  • Links to this article: www.ciphermagic.cn/mybatis-pag…
  • Copyright Notice: All articles on this blog are licensed under CC BY-NC-SA 4.0CN unless otherwise stated. Reprint please indicate the source!