MyBatis_Plus federated table paging query

MyBatis_Plus federated table paging query

When we need associative table paging query, the single-table method encapsulated by MyBatis_plus can no longer meet our needs, so we need to carry out associative table paging query

Suppose we need the following SQL statement:

        SELECT
        su.id,
        su.username,
        su.sex,
        su.user_identity,
        su.user_company,
        su.status,
        su.third_type,
        su.telephone,
        su.avatar,
        su.email,
        su.realname,
        su.post,
        su.del_flag,
        su.create_time,
        sr.role_name
        FROM
        sys_user AS su
        LEFT JOIN sys_user_role AS sur ON su.id = sur.user_id
        LEFT JOIN sys_role AS sr ON sur.role_id = sr.id
        order by su.create_time desc
Copy the code

Then we need to do the following:

• 1. Create UserInfoVO. Java

UserInfoVo is actually a page data object. Because the page needs to display the user table data and query the role name in another table according to the user, UserInfoVo similarly constructs the result in MyBatis. In MP we can use IPage<xxxEntity/DTO/Vo> to return custom multi-table federated query list data and pagination presentation requirements.

import lombok.Data; @Data public class UserInfoVO extends UserInfo { /** * id */ @TableId(type = IdType.ASSIGN_ID) private String id; /** * @excel (name = "@excel ", width = 15) private String username; /** * realName */ @excel (name = "realname ", width = 15) private String RealName; / * * * * / password @ JsonProperty (access = JsonProperty. Access. WRITE_ONLY) private String password; / * * * * / @ JsonProperty md5 password salt (access = JsonProperty. Access. WRITE_ONLY) private String salt; /** * avatar */ @excel (name = "avatar ", width = 15, type = 2) private String avatar; /** * Gender (1: male 2: Female) */ @excel (name = "gender ", width = 15, dicCode = "sex") @dict (dicCode = "sex") / width = 15) private String email; @excel (name = "phone ", width = 15) private String phone; /** * @tablelogic @excel (name = "delete status ", width = 15, dicCode = "del_flag") private Integer delFlag; @tableField (exist = false) private String roleName; } 123456789Copy the code

• 2, UserInfoMapper. In Java

Constants.WRAPPER: Query records based on entity conditions, so

IPage<UserInfoVO> getUserList(@Param("username") String username, @Param("realname") String realname, @Param("status") Integer status, @Param("page") Page<UserInfoVO> page, @Param(Constants.WRAPPER) Wrapper<UserInfoVO> wrapper);
}
Copy the code

• 3, UserInfoMapper. XML

${ew.customSQLSegment} : wraps custom SQL code and uses QueryWrapper (LambdaQueryWrapper) for output.

    <select id="getUserList" resultType="org.jeecg.modules.system.entity.SysUser">
        SELECT
        su.id,
        su.username,
        su.sex,
        su.user_company,
        su.status,
        su.telephone,
        su.avatar,
        su.email,
        su.realname,s
        su.del_flag,
        su.create_time,
        sr.role_name
        FROM
        sys_user AS su
        LEFT JOIN sys_user_role AS sur ON su.id = sur.user_id
        LEFT JOIN sys_role AS sr ON sur.role_id = sr.id
        <where>
            su.open_id is null and del_flag = 0
            <if test="username ! =null and username! = "">
                and su.username like concat(The '%',#{username},The '%')
            </if>
            <if test="realname ! =null and realname! = "">
                and su.realname like concat(The '%',#{realname},The '%')
            </if>
            
            <if test="status ! = null and status! = "">
                and su.status like concat(The '%',#{status}The '%')
            </if>
        </where>
        order by su.create_time desc
        ${ew.customSqlSegment}
    </select>
Copy the code

• 4, UserInfoServiceImpl. In Java

    @Override
    public IPage<UserInfoVO> getUserList(String username, String realname, Integer status, Page<UserInfoVO> page, QueryWrapper<UserInfoVO> queryWrapper) {
        return userMapper.getUserList(username, realname, status, page, queryWrapper);
    }

Copy the code

ServiceImpl returns an IPage<XXXEntity/Dto/Vo> object. IPage is a paging object that can be passed in to the IPage in the Controller.

• 5, UserController. In Java

QueryWrapper<UserInfoVO> queryWrapper = new QueryWrapper<>();
        Page<UserInfoVO> page = new Page<UserInfoVO>(pageNo, pageSize);
        IPage<UserInfoVO> userPageList = sysUserService.getUserList(sysUser.getUsername(), sysUser.getRealname(), sysUser.getStatus(), page, queryWrapper);
        return Result.OK(userPageList);
Copy the code

Above is the paging query (joint table) operation, the use of spelling SQL way to achieve multiple table joint paging query, is the most efficient one.