MybatisPlus查询一对多list结果collection实例

前言

查询用户信息，结果放到一个实体类里
这个实体类里有两个List<对象>字段，分别是这个用户的角色列表、岗位列表
以下仅供参考

实体类

package cn.daenx.myadmin.system.vo;

import cn.daenx.myadmin.system.po.SysPosition;
import cn.daenx.myadmin.system.po.SysRole;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 用户信息
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SysUserVo {
    private String id;

    /**
     * 部门ID
     */
    private String deptId;

    /**
     * 部门名称
     */
    private String deptName;

    /**
     * 真实姓名
     */
    private String realName;

    /**
     * 年龄
     */
    private String age;

    /**
     * 性别，0=女，1=男，2=未知
     */
    private String sex;

    /**
     * 个人简介
     */
    private String profile;

    /**
     * 个性签名
     */
    private String userSign;

    /**
     * 头像
     */
    private String avatar;

    /**
     * 账户余额
     */
    private Integer money;

    /**
     * 用户昵称
     */
    private String nickName;

    /**
     * 用户账号
     */
    private String username;

    /**
     * 账号状态，0=正常，1=停用，2=注销
     */
    private Integer status;

    /**
     * 用户手机号
     */
    private String phone;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 微信open_id
     */
    private String openId;

    /**
     * 锁定结束时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime banToTime;

    /**
     * 到期时间，null则永不过期
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime expireToTime;

    /**
     * 备注
     */
    @TableField(value = "remark")
    private String remark;

    /**
     * 用户类型ID
     */
    private String userTypeId;

    /**
     * 用户类型名称
     */
    private String userTypeName;

    /**
     * 创建时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime createTime;

    /**
     * 修改时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime updateTime;

    /**
     * 创建人账号
     */
    private String createName;

    /**
     * 创建人ID
     */
    private String createId;

    /**
     * 修改人账号
     */
    private String updateName;

    /**
     * 修改人ID
     */
    private String updateId;

    /**
     * 用户角色列表
     */
    private List<SysRole> roles;

    /**
     * 用户岗位列表
     */
    private List<SysPosition> positions;

    /**
     * 是否是管理员
     */
    private Boolean admin;
}

xml

<select id="getUserInfoByUserId" resultMap="userInfoMap">
        SELECT su.id             AS id,
               su.dept_id        AS deptId,
               sd.`name`         AS deptName,
               sud.real_name     AS realName,
               sud.age           AS age,
               sud.sex           AS sex,
               sud.`profile`     AS `profile`,
               sud.user_sign     AS userSign,
               sud.avatar        AS avatar,
               sud.money         AS money,
               sud.nick_name     AS nickName,
               su.username       AS username,
               su.`status`       AS `status`,
               su.phone          AS phone,
               su.email          AS email,
               su.open_id        AS openId,
               su.ban_to_time    AS banToTime,
               su.expire_to_time AS expireToTime,
               su.remark         AS remark,
               su.user_type_id   AS userTypeId,
               sut.`name`        AS userTypeName,
               su.create_time    AS createTime,
               su.update_time    AS updateTime,
               su1.username      AS createName,
               su.create_id      AS createId,
               su2.username      AS updateName,
               su.update_id      AS updateId
        FROM sys_user su
                 LEFT JOIN sys_user_detail sud ON sud.user_id = su.id
                 LEFT JOIN sys_dept sd ON sd.id = su.dept_id
                 LEFT JOIN sys_user_type sut ON sut.id = su.user_type_id
                 LEFT JOIN sys_user su1 ON su1.id = su.create_id
                 LEFT JOIN sys_user su2 ON su2.id = su.update_id
        WHERE su.is_delete = 0
        <if test="userId != null and userId != ''">
            AND su.id = #{userId}
        </if>
    </select>

    <resultMap id="userInfoMap" type="cn.daenx.myadmin.system.vo.SysUserVo">
        <result column="id" jdbcType="VARCHAR" property="id"/>
        <collection property="roles" column="id" select="queryUserRoles"></collection>
        <collection property="positions" column="id" select="queryUserPositions"></collection>
    </resultMap>
    <select id="queryUserRoles" resultType="cn.daenx.myadmin.system.po.SysRole">
        SELECT sr.*
        FROM sys_role_user sru
                 LEFT JOIN sys_role sr ON sru.role_id = sr.id
        WHERE sru.user_id = #{id}
    </select>
    <select id="queryUserPositions" resultType="cn.daenx.myadmin.system.po.SysPosition">
        SELECT sp.*
        FROM sys_position_user spu
                 LEFT JOIN sys_position sp ON spu.position_id = sp.id
        WHERE spu.user_id = #{id}
    </select>

效果

其他方法

百度mprelation