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