MyBatis paging plugin (PageHelper) introduces and integrates SpringMVC

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

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

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

• 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!