4. Pagination

4.1 paging with Limit

List<User> selectByLimit(Map<String,Integer> map);

<select id="selectByLimit" parameterType="map" resultType="user">
    select * from user limit #{startIndex},#{pageSize}
</select>

SqlSession sqlSession = MyBatisUtil.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
HashMap<String,Integer> map = new HashMap<>();
map.put("startIndex",1);
map.put("pageSize",2);
final List<User> users = mapper.selectByLimit(map);
System.out.println(users);
sqlSession.close();

4.2 paging plug-in PageHelper

Official reference: https://pagehelper.github.io/

1. Import PageHelper dependency

<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

2. Configure paging plug-in

Configure the interceptor plug-in in MyBatis configuration xml

<!--
    plugins The position in the configuration file must meet the requirements, otherwise an error will be reported, in the following order:
    properties?, settings?,
    typeAliases?, typeHandlers?,
    objectFactory?,objectWrapperFactory?,
    plugins?,
    environments?, databaseIdProvider?, mappers?
-->
<plugins>
    <!-- com.github.pagehelper by PageHelper Package name of class -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <!-- Use the following method to configure parameters. All parameters will be described later -->
        <property name="param1" value="value1"/>
	</plugin>
</plugins>

Configure the interceptor plug-in in the Spring configuration file

Using the spring property configuration method, you can use the plugins property to configure as follows:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <!-- Note other configurations -->
  <property name="plugins">
    <array>
      <bean class="com.github.pagehelper.PageInterceptor">
        <property name="properties">
          <!--Configure parameters in the following way, one per line -->
          <value>
            params=value1
          </value>
        </property>
      </bean>
    </array>
  </property>
</bean>

Introduction to configuration file parameters

The paging plug-in provides several optional parameters. When these parameters are used, they can be configured according to the examples in the above two configuration methods.

The optional parameters of the paging plug-in are as follows:

• Dialect: PageHelper mode will be used for paging by default. If you want to implement your own paging logic, you can implement the Dialect(com.github.pagehelper.Dialect) interface, and then configure this attribute as the fully qualified name of the implementation class.

The following parameters are for the default dialog. When using a custom dialect implementation, the following parameters have no effect.

1. helperDialect: the paging plug-in will automatically detect the current database link and automatically select the appropriate paging method. You can configure the helperDialect property to specify which dialect the paging plug-in uses. When configuring, you can use the following abbreviations:
   oracle,mysql,mariadb,sqlite,hsqldb,postgresql,db2,sqlserver,informix,h2,sqlserver2012,derby
   Special note: when using sqlserver2012 database, you need to manually specify it as sqlserver2012, otherwise you will use SqlServer2005 for paging.
   You can also implement AbstractHelperDialect, and then configure the property to the fully qualified name of the implementation class to use the custom implementation method.
2. offsetAsPageNum: the default value is false. This parameter is valid when RowBounds is used as the paging parameter. When this parameter is set to true, the offset parameter in RowBounds will be used as pageNum, which can be paged with page number and page size.
3. rowBoundsWithCount: the default value is false. This parameter is valid when rowboundaries is used as the paging parameter. When this parameter is set to true, count query will be performed using RowBounds paging.
4. Pagesizezzero: the default value is false. When this parameter is set to true, if pageSize=0 or rowbounds If limit = 0, all the results will be queried (equivalent to that the paged query is not executed, but the returned result is still of Page type).
5. reasonable: paging rationalization parameter. The default value is false. When this parameter is set to true, the first page will be queried when pagenum < = 0, and the last page will be queried when pagenum > pages (exceeds the total). When the default is false, the query is performed directly according to the parameters.
6. Params: in order to support the startPage(Object params) method, this parameter is added to configure the parameter mapping. It is used to take values from the object according to the attribute name. pageNum,pageSize,count,pageSizeZero,reasonable can be configured. If the mapping is not configured, the default value is pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;pageSizeZero=pageSizeZero.
7. supportMethodsArguments: supports the transfer of paging parameters through Mapper interface parameters. The default value is false. The paging plug-in will automatically take values from the parameter values of the query method according to the fields configured in params above. When an appropriate value is found, it will automatically page. For the usage method, please refer to com.com in the test code github. pagehelper. test. ArgumentsMapTest and ArgumentsObjTest under the basic package.
8. autoRuntimeDialect: the default value is false. When set to true, it is allowed to automatically identify the paging of the corresponding dialect according to multiple data sources at runtime (automatic selection of sqlserver2012 is not supported, only sqlserver can be used). Refer to scenario 5 below for usage and precautions.
9. closeConn: the default value is true. When the runtime dynamic data source is used or the helperDialect property is not set to automatically obtain the database type, a database connection will be automatically obtained. This property is used to set whether to close the obtained connection. It is closed by default. When it is set to false, the obtained connection will not be closed. The setting of this parameter should be determined according to the data source you choose.

Important:

When offsetAsPageNum=false, due to the PageNum problem, reasonable will be forced to be false during RowBounds query. Use PageHelper The startpage method is not affected.

3. Use

The paging plug-in supports the following call modes:

//The first is the call of RowBounds mode
List<Country> list = sqlSession.selectList("x.y.selectIf", null, new RowBounds(0, 10));

//Second, the Mapper interface mode is recommended.
PageHelper.startPage(1, 10);
List<Country> list = countryMapper.selectIf(1);

//The third way is to call Mapper interface mode, which is recommended.
PageHelper.offsetPage(1, 10);
List<Country> list = countryMapper.selectIf(1);

//Fourth, parameter method call
//There are the following Mapper interface methods. You don't need two parameters after xml processing
public interface CountryMapper {
    List<Country> selectByPageNumSize(
            @Param("user") User user,
            @Param("pageNum") int pageNum,
            @Param("pageSize") int pageSize);
}
//Configure supportMethodsArguments=true
//Call directly in Code:
List<Country> list = countryMapper.selectByPageNumSize(user, 1, 10);

//Fifth, parameter object
//If pageNum and pageSize exist in the User object, they will also be paged as long as the parameter has a value
//There are the following User objects
public class User {
    //Other fields
    //The following two parameter names are consistent with the names of params configuration
    private Integer pageNum;
    private Integer pageSize;
}
//There are the following Mapper interface methods. You don't need two parameters after xml processing
public interface CountryMapper {
    List<Country> selectByPageNumSize(User user);
}
//When pagenum in user= null && pageSize!=  When null, automatic paging occurs
List<Country> list = countryMapper.selectByPageNumSize(user);

//Sixth, ISelect interface mode
//jdk6,7 usage, creating interface
Page<Country> page = PageHelper.startPage(1, 10).doSelectPage(new ISelect() {
    @Override
    public void doSelect() {
        countryMapper.selectGroupBy();
    }
});
//jdk8 lambda usage
Page<Country> page = PageHelper.startPage(1, 10).doSelectPage(()-> countryMapper.selectGroupBy());

//You can also return PageInfo directly. Pay attention to the doSelectPageInfo method and doSelectPage
pageInfo = PageHelper.startPage(1, 10).doSelectPageInfo(new ISelect() {
    @Override
    public void doSelect() {
        countryMapper.selectGroupBy();
    }
});
//Corresponding lambda usage
pageInfo = PageHelper.startPage(1, 10).doSelectPageInfo(() -> countryMapper.selectGroupBy());

//Count query returns the count of a query statement
long total = PageHelper.count(new ISelect() {
    @Override
    public void doSelect() {
        countryMapper.selectLike(country);
    }
});
//lambda
total = PageHelper.count(()->countryMapper.selectLike(country));

SqlSession sqlSession = MyBatisUtil.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
PageHelper.startPage(1, 2);
List<User> all = userDao.getAll();
System.out.println(((Page)all).getTotal());
System.out.println(((Page)all).getPages());
System.out.println(((Page)all).getStartRow());
System.out.println(((Page)all).getEndRow());
for (User user : all) {
    System.out.println(user);
}
sqlSession.close();

List<User> selectByPageNumSize(@Param("pageNumKey") int pageNum,@Param("pageSizeKey") int pageSize);

SqlSession sqlSession = MyBatisUtil.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> users = userDao.selectByPageNumSize(2,3);
PageInfo info = new PageInfo(users);
System.out.println(info.getPages());
System.out.println(info.getPageSize());
System.out.println(info.getStartRow());
System.out.println(info.getEndRow());
System.out.println(info.getPageNum());
System.out.println(info.getNavigateFirstPage());
System.out.println(info.getNavigateLastPage());
System.out.println(info.isHasPreviousPage());
System.out.println(info.isHasNextPage());

for (User user : users) {
    System.out.println(user);
}
sqlSession.close();

5. Notes

CURD operation with annotation

@Select("select * from user where id = #{id}")
User selectById(@Param("id") int id);

@Insert("insert into user(id,name,pwd) values(#{id},#{name},#{password})")
int Insert(User user);
@Update("update user set name = #{name} ,pwd = #{password} where id = #{id}")
int Update(User user);
@Delete("delete from user where id = #{id}")
int Delete(@Param("id") int id);

Register the interface in the core configuration file

<mapper class="com.wyz.dao.UserDao"/>

When there are multiple basic types or strings, you can use @ Param to mark the parameters. Reference types are unmarked.