preface

In this article, MyBatis-Plus is used to simplify the operation of the database greatly and to achieve a simple CURD.

This article through MyBatis-Plus to achieve a simple paging query.

There are two ways to do this:

  • Native paging query
  • Custom paging queries

Note: this article is a follow-up, if you have not read the previous, suggested to check first.

The specific implementation

Paging plug-in

First you need to create a configuration class to configure the paging plug-in as follows:

@Configuration
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(a) {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // Set the request page to be larger than the maximum page operation, true to return to the home page, false to continue the request default false
        // paginationInterceptor.setOverflow(false);
        // Set the maximum number of pages per page, default 500, -1 is not limited
        // paginationInterceptor.setLimit(500);
        // Enable count join optimization for only part of the left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        returnpaginationInterceptor; }}Copy the code

Native paging query

If you use MyBatis-Plus’s native paging query method, no other implementation is required (based on the previous article), you can use it directly. As follows:

@RestController
@RequestMapping("/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;

    @GetMapping("/page")
    public ApiResult selectUserPage(@RequestParam String nickname,
                                    @RequestParam long pageNum,
                                    @RequestParam long pageSize) {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("nickname", nickname);

        Page<User> page = new Page<>(pageNum, pageSize);

        IPage<User> userPage = userService.page(page, wrapper);
        return ApiResult.success(newQueryResult<>(userPage)); }}Copy the code

  • Page is used to specify paging conditions

  • The Wrapper condition constructor, used to specify query conditions

    Function has a lot of conditions, specific can view website: mp.baomidou.com/guide/wrapp…

The above code is useful to the QueryResult class as follows:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class QueryResult<T> {

    /** * total number of entries */
    private Long total;

    /** * returns the result set */
    private List<T> items;

    public QueryResult(IPage<T> page) {
        this.total = page.getTotal();
        this.items = page.getRecords(); }}Copy the code

Simple paging queries use the above approach without writing SQL.

However, paging queries may inevitably lead to multiple table join queries, or some complex SQL statements. At this time, native paging queries are not supported and need to be customized.

Custom paging queries

Customization means implementing SQL statements by yourself. Thanks to the paging plug-in, custom SQL statements also do not require the writing of page-related SQL. As follows:

<?xml version="1.0" encoding="UTF-8"? >

      
<mapper namespace="com.zhuqc.framework.dao.UserDao">

    <select id="selectUserPage" resultType="com.zhuqc.framework.entity.User">
        SELECT * FROM sys_user ${ew.customSqlSegment}
    </select>

</mapper>
Copy the code

If you want to customize SQL and want to use the Wrapper conditional constructor, add ${ew.customSQLSegment} to the SQL. And add parameters to the Mapper interface. As follows:

@Mapper
public interface UserDao extends BaseMapper<User> {

    IPage<User> selectUserPage(IPage<User> page, @Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
}
Copy the code
@Service
@Transactional
public class UserServiceImpl extends ServiceImpl<UserDao.User> implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public IPage<User> selectUserPage(IPage<User> page, Wrapper<User> queryWrapper) {
        returnuserDao.selectUserPage(page, queryWrapper); }}Copy the code

In the configuration file, specify the location of the Mapper file:

mybatis-plus:
  mapper-locations: classpath*:/sql-mappers/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
Copy the code

Once written, you can use it in the control layer:

@RestController
@RequestMapping("/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;

    @GetMapping("/page2")
    public ApiResult selectUserPage2(@RequestParam String nickname,
                                     @RequestParam long pageNum,
                                     @RequestParam long pageSize) {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("nickname", nickname);

        Page<User> page = new Page<>(pageNum, pageSize);

        IPage<User> userPage = userService.selectUserPage(page, wrapper);
        return ApiResult.success(newQueryResult<>(userPage)); }}Copy the code

conclusion

If you want to use paging queries without the paging plug-in, you need to query the total number first and then the data for the current page, which requires you to write two SQL statements.

Now, with the paging plug-in, simple paging queries are used without writing SQL. For complex queries, you only need to write SQL to query the current page data, and the SQL paging plug-in to query the total number is automatically generated.

By using MyBatis-Plus to implement paging queries, you can feel the development is greatly simplified.

Above, thanks for reading, if you feel helpful, might as well click a like!

The source code

Github.com/zhuqianchan…

Review past

  • Build backend frameworks from scratch – keep updating