Develop WebApi based on SpringBoot2 (5) MySQL paging query

1. The meaning of paging query

As the business of the system progresses, the data storage generated by the system will increase. If all data is directly returned to the front-end display according to the user's query conditions, not only will the query efficiency be low, the process of returning the result set will also be occupied. A large bandwidth affects the user's operating experience.

At this time, we can use the data in the user's query conditions to be paginated. For example, we will return 200 pieces of data according to 20 pieces per page, divided into 10 pages; because we only return 20 pieces of data for each query, in the data The bandwidth requirement is greatly reduced during the transmission process.

Even if there are 20,000 or more data in the result set in the query condition, only 20 data of the current page are returned each time the query is performed. For users, it avoids system stalls or query waiting caused by large amounts of data, which greatly improves user experience.

The system paging query can be regarded as a mode similar to book binding. A book has a lot of content. Print these contents on different papers, number them in order, and bind them. Users can find what they want to see according to the page number. content.

2. Dependency introduction

Introduce the dependency of paging check in pom.xml

        <dependency>            <groupId>com.github.pagehelper</groupId>            <artifactId>pagehelper-spring-boot-starter</artifactId>            <version>1.2.5</version>        </dependency>

3. Configuration file

Add pagehelper configuration in .yml

pagehelper:  helperDialect: mysql  reasonable: true  supportMethodsArguments: true  params: count=countSql

Parameter Description:

(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 plugin uses. When configuring, you can use the following abbreviated values: oracle,mysql,mariadb,sqlite,hsqldb,postgresql,db2,sqlserver,informix,h2,sqlserver2012,derby

Special note: When using the SqlServer2012 database, you need to manually specify it as sqlserver2012, otherwise it will use SqlServer2005 for paging. You can also implement AbstractHelperDialect, and then configure this property to the fully qualified name of the implementation class to use a 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, and page number and page size can be used for paging.

(3) rowBoundsWithCount : The default value is false. This parameter is valid when RowBounds is used as a paging parameter. When this parameter is set to true, the count query will be performed using RowBounds pagination.

(4) pageSizeZero : The default value is false. When this parameter is set to true, all results will be queried if pageSize=0 or RowBounds.limit = 0 (equivalent to no paged query, but the returned result is still 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 (when the total number is exceeded). When the default is false, query directly based on the parameters.

(6) params : In order to support the startPage (Object params) method, this parameter is added to configure the parameter mapping, which is used to obtain the value from the object according to the attribute name. You can configure pageNum, pageSize, count, pageSizeZero, reasonable, and do not configure the mapping. Use the default value, the default value is: pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;pageSizeZero=pageSizeZero.

(7) supportMethodsArguments : Support for passing paging parameters through the Mapper interface parameters, the default value is false, the paging plug-in will automatically take the value from the parameter value of the query method according to the field configured by the params above, and it will automatically when it finds a suitable value Paging. The usage method can refer to ArgumentsMapTest and ArgumentsObjTest in the com.github.pagehelper.test.basic package in the test code.

(8) autoRuntimeDialect : The default value is false. When set to true, it allows automatic identification of the corresponding dialect paging according to multiple data sources at runtime (automatic selection of sqlserver2012 is not supported, only sqlserver can be used).

(9) closeConn : The default value is true. When using the runtime dynamic data source or when the helperDialect property is not set to automatically obtain the database type, a database connection is automatically obtained, and the obtained connection is set through this property. The default true is closed. After setting it to false, the acquisition will not be closed. The connection of this parameter should be determined according to the data source of your choice.

4. Encapsulation of paging query usage method

public class PageUtils {     public static PageResult getPageResult(PageInfo<?> pageInfo) {        PageResult pageResult = new PageResult();        pageResult.setPageNum(pageInfo.getPageNum());        pageResult.setPageSize(pageInfo.getPageSize());        pageResult.setTotalSize(pageInfo.getTotal());        pageResult.setTotalPages(pageInfo.getPages());        pageResult.setContent(pageInfo.getList());        return pageResult;    }}

PageResult result set definition

public class PageResult {    /**     * 当前页码     */    private int pageNum;    /**     * 每页数量     */    private int pageSize;    /**     * 页码总数     */    private int totalPages;     /**     * 记录总数     */    private long totalSize;     /**     * 数据模型     */    private List<?> content;    public int getPageNum() {        return pageNum;    }    public void setPageNum(int pageNum) {        this.pageNum = pageNum;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    public long getTotalSize() {        return totalSize;    }    public void setTotalSize(long totalSize) {        this.totalSize = totalSize;    }    public int getTotalPages() {        return totalPages;    }    public void setTotalPages(int totalPages) {        this.totalPages = totalPages;    }    public List<?> getContent() {        return content;    }    public void setContent(List<?> content) {        this.content = content;    }}

5. Add paging implementation in Service

In the method of Service corresponding to the interface, add the implementation of paging query

    /**     * 查询用户列表     * @param key     * @param pageNum 当前页码     * @param pageSize 每页显示的条数     * @return     */    public PageResult queryAdminUserList(String key,int pageNum, int pageSize,BigInteger roleId)    {        //设置当前查询的页        PageHelper.startPage(pageNum,pageSize);        List<AdminUser> adminUserList=adminUserDao.queryAdminUserList(key,roleId);        //将结果集存储到分页查询统一结果集里面        PageInfo<AdminUser> pageInfo=new PageInfo<>(adminUserList);        PageResult pageResult= PageUtils.getPageResult(pageInfo);        return pageResult;    }

6. Controller increases the input of paging query parameters

    @CheckToken    @ApiOperation(value = "用户列表查询", notes = "用户列表查询")    @ApiImplicitParams({            @ApiImplicitParam(name = "key",value = "查询条件"),            @ApiImplicitParam(name = "pageNum",value = "当前页",required = true),            @ApiImplicitParam(name = "pageSize",value = "每页显示条数",required = true),            @ApiImplicitParam(name = "roleId",value = "角色id",required = true)    })    @ApiResponses({            @ApiResponse(code = 200,message = "成功",response = AdminUser.class),    })    @PostMapping(value = "queryAdminUserList")    public Result<Object> queryAdminUserList(String key, int pageNum, int pageSize,BigInteger roleId){        try {            PageResult pageUserList = adminUserService.queryAdminUserList(key,pageNum,pageSize,roleId);            return ResultGenerator.success("成功",pageUserList);        } catch (Exception e) {            e.printStackTrace();            return ResultGenerator.error("失败:" + e.getMessage());        }    }