MyBatis实现中间表关联查询

MyBatis实现中间表关联查询

通常构建数据多对多模型的关系时，我们需要再建立一张中间表来关联另外两张表。

下面介绍一下，怎么用MyBatis来实现中间表关联查询并封装到实体类对象中。

现在有三张表：sys_user、sys_role、sys_users_roles

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(255) NULL DEFAULT NULL COMMENT '用户名',
  `nick_name` varchar(255) NULL DEFAULT NULL COMMENT '昵称',
  `gender` varchar(2) CHARACTER SET utf8 NULL DEFAULT NULL COMMENT '性别',
  `phone` varchar(255) NULL DEFAULT NULL COMMENT '手机号码',
  `email` varchar(255) NULL DEFAULT NULL COMMENT '邮箱',
  `password` varchar(255) NULL DEFAULT NULL COMMENT '密码',
  `is_admin` bit(1) NULL DEFAULT b'0' COMMENT '是否为admin账号',
  `enabled` bigint(20) NULL DEFAULT NULL COMMENT '状态：1启用、0禁用',
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `UK_kpubos9gc2cvtkb0thktkbkes` USING BTREE(`email`),
  UNIQUE INDEX `username` USING BTREE(`username`),
  UNIQUE INDEX `uniq_username` USING B-Tree(`username`),
  UNIQUE INDEX `uniq_email` USING B-Tree(`email`),
  INDEX `inx_enabled`(`enabled`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COMMENT = '系统用户' ROW_FORMAT = Compact;

BEGIN;
INSERT INTO `sys_user` VALUES (1, 2, 'admin', '管理员', '男', '18888888888', '201507802@qq.com', '$2a$10$Egp1/gvFlt7zhlXVfEFw4OfWQCGPw0ClmMcc6FjTnvXNRVf9zdMRa', b'1', 1);
COMMIT;


-- -----------------------------------
-- Table structure for sys_users_roles
-- -----------------------------------
DROP TABLE IF EXISTS `sys_users_roles`;
CREATE TABLE `sys_users_roles`  (
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `role_id` bigint(20) NOT NULL COMMENT '角色id',
  PRIMARY KEY (`user_id`, `role_id`),
  INDEX `FKq4eq273l04bpu4efj0jd0jb98` USING BTREE(`role_id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '用户角色关联' ROW_FORMAT = COMPACT;

BEGIN;
INSERT INTO `sys_users_roles` VALUES (1, 1);
COMMIT;


-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS`sys_role`;
CREATE TABLE `sys_role`  (
  `role_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) NULL DEFAULT NULL COMMENT '名称',
  `level` int(255) NULL DEFAULT NULL COMMENT '角色级别',
  `description` varchar(255) NULL DEFAULT NULL COMMENT '描述',
  `data_scope` varchar(255) NULL DEFAULT NULL COMMENT '数据权限',
  PRIMARY KEY (`role_id`),
  UNIQUE INDEX `uniq_name`(`name`),
  INDEX `role_name_index`(`name`),
  INDEX USING BTREE(`role_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COMMENT = '角色表' ROW_FORMAT = COMPACT;

BEGIN;
INSERT INTO `sys_role` VALUES (1, '超级管理员', 1, '-', '全部', NULL, 'admin', '2018-11-23 11:04:37', '2020-08-06 16:10:24');
COMMIT;

对应的实体类：

@Setter
@Getter
public class User  implements Serializable {


    @TableId
    private Long id;

    private Set<Role> roles;

    private String username;

    private String nickName;

    @Email
    @NotBlank
    private String email;

    @NotBlank
    private String phone;

    private String gender;


    private String password;

    @NotNull
    private Boolean enabled;

    private Boolean isAdmin = false;


    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        User user = (User) o;
        return Objects.equals(id, user.id) &&
                Objects.equals(username, user.username);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, username);
    }
}

@Setter
@Getter
public class Role implements Serializable {

    private Long id;

    @NotBlank
    private String name;

    private String dataScope = DataScopeEnum.THIS_LEVEL.getValue();


    private Integer level = 3;

    private String description;

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        Role role = (Role) o;
        return Objects.equals(id, role.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }
}

其中 Set roles的查询和封装是一个问题，需要通过中间表关联查询。

而用mybatis实现中间表关联查询可以如下这实现：

<resultMap id="userResultMapper" type="user">
    <id column="user_id" property="id" />
    <result column="username" property="username" />
    <result column="nick_name" property="nickName" />
    <result column="gender" property="gender"/>
    <result column="phone" property="phone"/>
    <result column="email" property="email"/>
    <result column="password" property="password"/>
    <result column="enabled" property="enabled"/>
    <result column="is_admin" property="isAdmin"/>
    <collection property="roles" ofType="role">
        <id column="role_id" property="id"/>
        <result column="name" property="name"/>
        <result column="level" property="level"/>
        <result column="description" property="description"/>
        <result column="data_scope" property="dataScope"/>
    </collection>
</resultMap>
<select id="selectByName" resultMap="userResultMapper" parameterType="string">
    select a.*,c.* from sys_user a
    left join sys_users_roles b on a.user_id  = b.user_id
    left join sys_role c on b.role_id = c.role_id
    where a.username = #{username}
</select>

直接使用left join语句将三个表关联查询：把左边的（sys_user）全部查出来，右边有的则匹配，没有则为null。

这样就可以成功把查询到的roles集合封装到User对象里。