MyBatis中多表联合查询

当使用MyBatis进行多表联合查询时，根据需要一般可以有两种处理方式。例如在一个项目的权限管理模块中，一般会有用户-角色-权限的多表联合查询。

多表中的实体类

1.用户实体类

@Getter
@Setter
@ToString
public class User {

    private Integer userId;
    private String userName;
    private String userPwd;
    private Date createDate;
    private UserStatus status;
    private List<Role> roles;
    
}

2.角色实体类

@Getter
@Setter
@ToString
public class Role {

    private Integer roleId;
    private String roleName;
    private RoleStatus roleValid;
    private List<Right> rights;

}

3.权限实体类

@Getter
@Setter
@ToString
public class Right {

    private Integer rightId;
    private String rightName;
    private Integer rightValid;

}

若要查询一个用户-角色-权限的所有相关信息，便要将这三个表联合查询。

多表联合查询处理

1.新建多表联合类

新建多表联合的实体类供mapper文件映射

@Getter
@Setter
@ToString
public class UserCascade {

    private Integer userId;
    private String userName;
    private String userPwd;
    private Date createDate;
    private UserStatus status;
    private Integer roleId;
    private String roleName;
    private RoleStatus roleValid;
    private Integer rightId;
    private String rightName;
    private Integer rightValid;

}

mapper映射文件

  <resultMap id="UserCascadeInfo" type="org.kb4md.security.data.entity.UserCascade">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_password"/>
        <result property="createDate" column="create_date"/>
        <result property="status" column="status"/>
        <result property="roleId" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleValid" column="role_valid"/>
        <result property="rightId" column="right_id"/>
        <result property="rightName" column="right_name"/>
        <result property="rightValid" column="right_valid"/>
  </resultMap>
  <select id="getAllUsersCascadeAll" resultMap="UserCascadeInfo">
        SELECT sys_user.user_id,sys_user.user_name,sys_user.user_password,sys_user.create_date,sys_user.status,
        sys_role.role_id,sys_role.role_name,sys_role.role_valid,
        sys_right.right_id,sys_right.right_name,sys_right.right_valid
        FROM sys_user
        LEFT JOIN sys_role_of_user ON sys_user.user_id = sys_role_of_user.user_id
        LEFT JOIN sys_role ON sys_role_of_user.role_id = sys_role.role_id
        LEFT JOIN sys_right_of_role ON sys_role.role_id = sys_right_of_role.role_id
        LEFT JOIN sys_right ON sys_right_of_role.right_id = sys_right.right_id
  </select>

注意：当多表中有相同的属性时，在配置文件中，sql语句联合查询时使用字段别名，resultMap中对应的column属性使用相应的别名

2.利用collection映射属性的嵌套

mapper映射文件

    <resultMap id="roleInfo" type="org.kb4md.security.data.entity.Role">
        <result property="roleId" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleValid" column="role_valid"/>
        <collection property="rights" ofType="org.kb4md.security.data.entity.Right"
                    javaType="java.util.ArrayList">
            <result property="rightId" column="right_id"/>
            <result property="rightName" column="right_name"/>
            <result property="rightValid" column="right_valid"/>
        </collection>
    </resultMap>
    
    <select id="getAllRolesCascadeAll" resultMap="roleInfo">
        SELECT sys_role.role_id,sys_role.role_name,sys_role.role_valid,
        sys_right.right_id,sys_right.right_name,sys_right.right_valid
        FROM sys_role
        LEFT JOIN sys_right_of_role ON sys_role.role_id = sys_right_of_role.role_id
        LEFT JOIN sys_right ON sys_right.right_id = sys_right_of_role.right_id
    </select>

collection : 复杂的类型关联
property : 这是关联的实体类中的属性名，如role实体类中的private List rights;
javaType : property 属性对应的集合类型
ofType : property 集合中的泛型
column : 数据库表中的属性名