Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

1. Use SQL statement paging

grammar

select * from user limit startIndex,pageSize;
select * from user limit n; --[0,n]
Copy the code

Implement paging

Mybatis interface

    // Paging query user
    List<User> getUserLimit(Map<String,Integer> map);
Copy the code

UserMapper.xml

    <select id="getUserLimit" resultType="user" parameterType="map" resultMap="Limit">
        select * from mybatis.user limit #{startIndex},#{pageSize}
    </select>
Copy the code

test

   @Test
   public void getUserLimit(a){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       HashMap<String, Integer> map = new HashMap<String, Integer>();
       map.put("startIndex".1);
       map.put("pageSize".2);
       List<User> userLimit = mapper.getUserLimit(map);
       for (User user : userLimit) {
           System.out.println(user);
       }
Copy the code

The results of

2. The RowBounds class is paginated (extended)

Paging is implemented at the Java code level

Mybatis interface

    //RowBounds implements paging query of users
    List<User> getUserRowBounds(a);
Copy the code

UserMapper.xml

<! -- RowBounds implements paging query to users -->
    <select id="getUserRowBounds"   resultType="user">
        select * from mybatis.user
    </select>
Copy the code

test

    @Test
    public void getUserRowBounds(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        / / implementation RowBounds
        RowBounds rowBounds = new RowBounds(1.2);
        List<User> userLimit = sqlSession.selectList("com.cheng.dao.UserMapper.getUserRowBounds".null,rowBounds);
        for (User user : userLimit) {
            System.out.println(user);
        }
        sqlSession.close();
    }
Copy the code

3, the paging plugin PageHelper

Mybatis pagination plugin :PageHelper

Method of use

1. Introduce a paging plug-in

There are two methods for importing paging plug-ins. Maven is recommended.

2. Import the Jar package

You can download the latest version of the JAR package from the address below

  • Oss.sonatype.org/content/rep…
  • Repo1.maven.org/maven2/com/…

Since you are using SQL parsing tools, you will also need to download JSQLParser.jar:

  • Repo1.maven.org/maven2/com/…

3. Use Maven

Add the following dependencies to pom.xml:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>The latest version</version>
</dependency>
Copy the code

4. Configure the interceptor plug-in

Pay special attention to, the new interceptor is com. The dead simple. Pagehelper. PageInterceptor. Com. Making. Pagehelper pagehelper now is a special the dialect implementation class, is the default implementation class paging plug-in, provides the use of the same as before.

Configure the interceptor plug-in in the MyBatis configuration XML

<! -- The location of the plugins in the configuration file must be correct, otherwise an error will be reported, in the following order: properties? , settings? , typeAliases? , typeHandlers? , objectFactory? ,objectWrapperFactory? , plugins? , environments? , databaseIdProvider? , mappers? -->
<plugins>
    <! -- com.github. Pagehelper specifies the package name of pageHelper -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <! Use the following method to configure the parameters:
        <property name="param1" value="value1"/>
	</plugin>
</plugins>
Copy the code

5. Page plug-in parameters

The paging plug-in provides a number of optional parameters, which can be used by following the examples in the previous two configurations.

Paging plug-in Optional parameters are as follows:

  • dialect: By default, PageHelper is used for paging. If you want to implement your own paging logic, you canDialect(com.github.pagehelper.Dialect) interface, and then configure this property to be the fully qualified name of the implementation class.

The following parameters are for the default dialect. The following parameters have no effect when using a custom Dialect implementation.

  1. helperDialect: The paging plug-in automatically detects the current database link and automatically selects the appropriate paging method. You can configurehelperDialectProperty to specify which dialect the paging plug-in uses. When configuring, you can use the following abbreviated values:oracle.mysql.mariadb.sqlite.hsqldb.postgresql.db2.sqlserver.informix.h2.sqlserver2012.derby** If SqlServer2012 is used, you need to manually specify assqlserver2012Otherwise, SqlServer2005 will be used for paging. You can tooAbstractHelperDialectThen configure the property to the fully qualified name of the implementation class to use the custom implementation method.
  2. offsetAsPageNum: The default value isfalse, this parameter is used in pairsRowBoundsValid as a paging parameter. When this parameter is set totrueWhen will beRowBoundsIn theoffsetParameters aspageNumUse, you can use page number and page size parameters for paging.
  3. rowBoundsWithCount: The default value isfalse, this parameter is used in pairsRowBoundsValid as a paging parameter. When this parameter is set totrueWhen usingRowBoundsPaging does count queries.
  4. pageSizeZero: The default value isfalseWhen this parameter is set totrueIf thepageSize=0orRowBounds.limit = 0All of the results will be queried (equivalent to no paging query, but the result is still returnedPageType).
  5. reasonable: Paging rationalization parameter. The default value isfalse. When this parameter is set totrueWhen,pageNum<=0Will query the first page,pageNum>pages(when the total number exceeds), the last page is queried. The defaultfalseIs used to query information based on parameters.
  6. params: In support ofstartPage(Object params)Method is added to configure parameter mapping, which is used to take values from objects based on property names and can be configuredpageNum,pageSize,count,pageSizeZero,reasonableIf no mapping is configured, the default value ispageNum=pageNum; pageSize=pageSize; count=countSql; reasonable=reasonable; pageSizeZero=pageSizeZero.
  7. supportMethodsArguments: Supports passing paging parameters through Mapper interface parameters, defaultfalseThe paging plug-in automatically takes the parameter values from the query method based on the aboveparamsThe value in the configured field is automatically paged when an appropriate value is found. You can refer to the test code for instructionscom.github.pagehelper.test.basicUnder the bagArgumentsMapTest 和 ArgumentsObjTest.
  8. autoRuntimeDialect: The default value isfalse. Set totrueAllows automatic identification of pages in dialects based on multiple data sources at run time. (Automatic selection is not supportedsqlserver2012, can only be usedsqlserver), usage and precautions refer to the followingScene five.
  9. closeConn: The default value istrue. When using runtime dynamic data sources or not sethelperDialectProperty to automatically obtain a database connection. Use this property to set whether to close the obtained connection. DefaulttrueDisable, set tofalseAfter, the obtained connection is not closed. The setting of this parameter depends on the data source selected.

Important note:

When offsetAsPageNum=false, reasonable is forced to false by RowBounds query due to PageNum. Using the pageHelper. startPage method is not affected.

PageHelper User manual