User表嵌套查Role表（一对多）

User表和Role表的结构

CREATE TABLE t_user
(
   id                   BIGINT NOT NULL COMMENT '用户ID',
   position_id          BIGINT COMMENT '职位ID',
   department_id        BIGINT COMMENT '部门ID',
   code                 VARCHAR(32) UNIQUE NOT NULL COMMENT '用户编码',
   password             VARCHAR(32) COMMENT '密码',
   name                 VARCHAR(32) UNIQUE NOT NULL COMMENT '用户名称',
   ... (其他字段)
   PRIMARY KEY (id)
);

CREATE TABLE t_role
(
   id                   BIGINT NOT NULL COMMENT '角色ID',
   company_id           BIGINT COMMENT '公司ID',
   org_id               BIGINT COMMENT '机构ID',
   code                 VARCHAR(32) UNIQUE COMMENT '角色代码',
   name                 VARCHAR(32)  NOT NULL COMMENT '角色名称',
   ...(其他字段)
   PRIMARY KEY (id)
);

User和Role的entity：为节省篇幅，删除了代码中的注释、构造函数、getter、setter、toStrings

@Repository
@Table(name = "t_user")
public class User extends BaseEntity implements Serializable {
    
    @Transient
    protected List<Role> roleList; //role表实体，一对多查询
    protected Long positionId;
    protected Long departmentId;
    protected String code;
    protected String password;
    protected String name;
    protected String profilePicture;
    protected Byte sex;
    protected Date birthday;
    protected String tel;
    protected String email;
    protected String other;
    protected String remark;
}

@Repository
@Table(name = "t_role")
public class Role extends BaseEntity implements Serializable {
    protected Long companyId;
    protected Long orgId;
    protected String code;
    protected String name;
    protected String remark;
}

基本过程：通过嵌套select的方式，把User表对应在Role表中的多条数据取出来，放在一个List中，再放入User表的对象里。

xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bosssoft.bes.userpermission.dao.UserDao">
    <!-- 用户表（结果集）-->
    <resultMap id="UserMap" type="com.bosssoft.bes.userpermission.pojo.entity.User">
        <id column="id" property="id"/>
        <result column="status" property="status" />
        <result column="created_by" property="createdBy" />
        <result column="created_time" property="createdTime" />
        <result column="updated_by" property="updatedBy" />
        <result column="updated_time" property="updatedTime" />
        <result column="version" property="version" />

        <result column="position_id" property="positionId"/>
        <result column="department_id" property="departmentId" />
        <result column="code" property="code" />
        <result column="password" property="password" />
        <result column="name" property="name" />
        <result column="profile_picture" property="profilePicture" />
        <result column="password" property="password" />
        <result column="sex" property="sex" />
        <result column="birthday" property="birthday" />
        <result column="tel" property="tel" />
        <result column="email" property="email" />
        <result column="other" property="other" />
        <result column="remark" property="remark" />
        <!-- 指定子集的collection，对应entity中的List -->
        <collection property="roleList" ofType="com.bosssoft.bes.userpermission.pojo.entity.Role"
                    javaType="java.util.ArrayList" select="getRoles"
                    column="id">
        </collection>
    </resultMap>

    <!-- 角色表（子集） -->
    <resultMap id="RoleMap" type="com.bosssoft.bes.userpermission.pojo.entity.Role">
        <id column="id" property="id"/>
        <result column="org_id" property="orgId"/>
        <result column="company_id" property="companyId"/>
        <result column="status" property="status" />
        <result column="created_by" property="createdBy" />
        <result column="created_time" property="createdTime" />
        <result column="updated_by" property="updatedBy" />
        <result column="updated_time" property="updatedTime" />
        <result column="version" property="version" />
		<result column="code" property="code" />
        <result column="name" property="name" />
		<result column="remark" property="remark" />
    </resultMap>

    <select id="queryByCondition" parameterType="java.lang.String" resultMap="UserMap">
        SELECT
            tu.*
        FROM t_user tu, t_department td, t_company tc
        <where>
            td.company_id = tc.id AND tu.department_id = td.id
            <if test="name != null and name != ''">
                AND tu.name LIKE CONCAT (#{name},'%')
            </if>
            <if test="code != null and code != ''">
                AND tu.code LIKE CONCAT (#{code},'%')
            </if>
            <if test="tel != null and tel != ''">
                AND tu.tel LIKE CONCAT (#{tel},'%')
            </if>
            <if test="companyId != null">
                AND tc.id = #{companyId}
            </if>
        </where>
        ORDER BY tu.updated_time DESC
    </select>

<!-- 获取角色的select -->
    <select id="getRoles" parameterType="java.lang.Long" resultMap="RoleMap">
        SELECT
            *
        FROM t_role tr
            LEFT JOIN
            t_user_role_relationship turr
                ON tr.id = turr.role_id
        WHERE turr.user_id = #{userId}
    </select>
	
</mapper>