Shulou(Shulou.com)06/03 Report--

This article mainly introduces Mybatis-plus how to achieve multi-conditional filtering paging, the article is very detailed, has a certain reference value, interested friends must read it!

1. Database maps objects to view objects

In the development process, the secondary encapsulated view object (VO) is used for content presentation.

Package com.fod.fodapi.vo;import lombok.Data;/** * user View object * @ author 86137 * @ date 2021-09-26 15:03 * / @ Datapublic class UrmUserInfoVO {/ * * user primary key * / private Integer id; / * * user account * / private String userNumber / * user name * / private String userName; / * * user profile * / private String userImage; / * user gender * / private Integer userSex; / * * contact information * / private String userPhone / * * status * / private Integer status; / * * user role * * / private String userRole;} 2. Test SQL

Testing sql is written when the database client is testing data to ensure that sql query errors do not occur when writing code

SELECT users.id, users.user_number, users.user_name, users.user_image, users.user_sex,users.user_phone, users.native_place, users.status Roles.role_nameFROM urm_user_info AS users JOIN urm_user_online AS onlines ON users.id = onlines.user_info JOIN urm_user_role_relevance AS userAndRole ON users.id = userAndRole.user_id LEFT JOIN urm_role_info AS roles ON userAndRole.role_id = roles.id WHERE users.STATUS = 1 AND users.deleted = 0 AND onlines.online_status = 1ORDER BY users.add_time

Test results:

3. MVC layer decomposition 3.1 query parameter encapsulation

Some parameters in the query process need to be reused, such as: current page, number of current pages, filter criteria, and so on. At the same time, it can also solve the problem that too many query parameters lead to too much code for the interface layer to receive data.

Field encapsulation for public use: (class name: PublicSelect)

Private Integer page = 1; / page private Integer limit = 10; / / how many pieces of data per page private String sort = "add_time"; / / sort field private Boolean order = true; / / sort mode (true:asc,false:desc)

Specific function query field encapsulation: (class name: UrmUserInfoSelect)

You can inherit when using public fields.

/ * user query criteria * / @ Datapublic class UrmUserInfoSelect extends PublicSelect {/ * * user account * / private String userNumber; / * * user name * / private String userName; / * * user gender * / private Integer userSex; / * * contact information * / private String userPhone; / * * status * / private Integer status / * * New time * / private Date addTime;} 3.2 Controller layer code

In the controller layer, the author only uses data reception, basic data filtering and verification, and return operations, and the business is all in the interface implementation layer (serverImpl).

@ ApiOperation (value = "personnel online list") @ GetMapping ("/ list") @ ResponseBodypublic Object listSelective (@ RequestBody UrmUserInfoSelect select) {return rdmPersonOnlineService.listSelective (select);} 3.3 Service interface layer public interface RdmPersonOnlineService {Object listSelective (UrmUserInfoSelect select);} 3.4 ServiceImpl interface implementation layer

All business-related content will be written in this class

@ Overridepublic Object listSelective (UrmUserInfoSelect select) {/ / QueryWrapper is used to build the filter data condition content of sql. Read the official Mybatis-Plus document QueryWrapper voQueryWrapper = new QueryWrapper (); voQueryWrapper.eq ("users.status", STATUS_START); voQueryWrapper.eq ("users.deleted", DELETED_NO); voQueryWrapper.eq ("onlines.online_status", USER_ONLINE) VoQueryWrapper.orderBy (true, select.getOrder (), select.getSort ()); / / build paging conditions and automatically perform paging operations through Page Page voPage = new Page (select.getPage (), select.getLimit ()); / / setRecords is a parameter voPage.setRecords (urmUserInfoMapper.selectiveUserInfoByOnlineStatus (voPage,voQueryWrapper)) in the Page class that places query results; return ResponseUtil.ok (voPage) } 3.5 Mapper data persistence layer

When writing sql in the mypper layer: * * @ Param (Constants.WRAPPER) must be added to the condition parameters in the method, and ${ew.customSqlSegment} * * must be added at the end of the Sql. Since the filter criteria (query criteria) have been configured in the condition parameters, there is no need to write again in the sql of mapper.

@ Mapper@Repositorypublic interface UrmUserInfoMapper extends BaseMapper {/ / the + that appears is enter line feed @ Select ("SELECT" + "users.id, users.user_number, users.user_name, users.user_image, users.user_sex, users.user_phone, users.native_place, users.status." Roles.role_name "+" FROM "+" urm_user_info AS users "+" JOIN urm_user_online AS onlines ON users.id = onlines.user_info "+" JOIN urm_user_role_relevance AS userAndRole ON users.id = userAndRole.user_id "+ "LEFT JOIN urm_role_info AS roles ON userAndRole.role_id = roles.id ${ew.customSqlSegment}") List selectiveUserInfoByOnlineStatus (Page voPage @ Param (Constants.WRAPPER) QueryWrapper voQueryWrapper) } 4. Result

Screening condition

{"page": 1, "limit": 5, "sort": null, "order": true, "userNumber": null, "userName": null, "userSex": null, "userPhone": null, "status": null, "addTime": null}

Filter results:

{"errno": 0, "data": {"records": [{"id": 1, "userNumber": "admin", "userName": "admin", "userImage": null, "userSex": 1, "userPhone": "1234567890123" "status": 1, "userRole": null, "userOrganize": null}, {"id": 2, "userNumber": "123456", "userName": "Zhang San", "userImage": null "userSex": 0, "userPhone": "1234567890123", "status": 1, "userRole": null, "userOrganize": null}, {"id": 3, "userNumber": "123456789" "userName": "Li Si", "userImage": null, "userSex": 1, "userPhone": "1234567890123", "status": 1, "userRole": null, "userOrganize": null} {"id": 4, "userNumber": "123123", "userName": "Wang Wu", "userImage": null, "userSex": 1, "userPhone": "1234567890123", "status": 1, "userRole": null "userOrganize": null}, {"id": 5, "userNumber": "12121212", "userName": "Ma Liu", "userImage": null, "userSex": 1, "userPhone": "1234567890123" "status": 1, "userRole": null, "userOrganize": null}], "total": 6, "size": 5, "current": 1, "orders": [], "optimizeCountSql": true, "hitCount": false, "countId": null "maxLimit": null, "searchCount": true, "pages": 2}, "errmsg": "success"}

The figure shows:

5 supplement 5.1 paging failure problem

When paging appears, the query comes out of all the data, and will not be paged. The reason is a problem with the mybatis-plus configuration.

There may be configuration differences between different versions (the author uses 3.4.1)

@ Configuration (proxyBeanMethods = false) public class MybatisPlusConfig {@ Bean public MybatisPlusInterceptor mybatisPlusInterceptor () {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor (); / / paging plug-in interceptor.addInnerInterceptor (paginationInterceptor ()); return interceptor } / * paging plug-in * / public PaginationInnerInterceptor paginationInterceptor () {PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor (); / / set the database type to mysql paginationInnerInterceptor.setDbType (DbType.MYSQL) / / set the operation after the requested page is larger than the maximum page, true is transferred back to the home page, and false continues to request the default false paginationInnerInterceptor.setOverflow (false); / / set the maximum number of page limits, default 500s,-1 unlimited paginationInnerInterceptor.setMaxLimit (- 1L); return paginationInnerInterceptor }} the above is all the content of the article "how to achieve multi-conditional filtering paging in Mybatis-plus". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.