mybatis返回集合 collections 一对多

sql脚本

create table permission
(
    id       bigint auto_increment
        primary key,
    name     varchar(255) null,
    resource varchar(255) null
)
    charset = utf8;

INSERT INTO koala.permission (name, resource)
VALUES ('贬下凡间', null);
INSERT INTO koala.permission (name, resource)
VALUES ('飞升渡劫', null);
INSERT INTO koala.permission (name, resource)
VALUES ('搞破坏', null);
INSERT INTO koala.permission (name, resource)
VALUES ('度化有缘人', null);
INSERT INTO koala.permission (name, resource)
VALUES ('化为舍利', null);
INSERT INTO koala.permission (name, resource)
VALUES ('炼丹', null);
INSERT INTO koala.permission (name, resource)
VALUES ('永生', null);

create table role
(
    id   bigint auto_increment
        primary key,
    name varchar(255) null,
    sn   varchar(255) null
)
    charset = utf8;

INSERT INTO koala.role (name, sn)
VALUES ('妖怪', '0');
INSERT INTO koala.role (name, sn)
VALUES ('神仙', '0');
INSERT INTO koala.role (name, sn)
VALUES ('佛系', '0');

create table role_permission
(
    role_id       bigint not null,
    permission_id bigint not null
)
    charset = utf8;

INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (3, 18);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (4, 16);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (4, 17);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (4, 19);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (4, 21);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (4, 22);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (5, 20);
INSERT INTO koala.role_permission (role_id, permission_id)
VALUES (5, 22);

create table user
(
    id       bigint auto_increment
        primary key,
    username varchar(255) null,
    password varchar(255) null
)
    charset = utf8;

INSERT INTO koala.user (username, password)
VALUES ('李靖', '666');
INSERT INTO koala.user (username, password)
VALUES ('猪八戒', '123');
INSERT INTO koala.user (username, password)
VALUES ('孙悟空', '123');
INSERT INTO koala.user (username, password)
VALUES ('沙悟净', '123');
INSERT INTO koala.user (username, password)
VALUES ('哪吒', '23');

create table user_role
(
    user_id bigint not null,
    role_id bigint not null
)
    charset = utf8;

INSERT INTO koala.user_role (user_id, role_id)
VALUES (1, 4);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (2, 5);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (2, 4);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (3, 3);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (3, 5);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (4, 4);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (4, 3);
INSERT INTO koala.user_role (user_id, role_id)
VALUES (5, 4);      

问题

获得用户角色

获得指定用户的角色sql

select u.username,r.name from user u left join user_role ur on u.id = ur.user_id left join role r on r.id = ur.role_id where u.id in (1,2,3);      

@Data
public class UserRoleVO {
    private String username;
    private String name;
}      

获得用户角色 一对多

@Data
public class UserRoleVO {
    private String username;
    private String nameList;
}      

select u.username,group_concat(r.name separator ',') from user u left join user_role ur on u.id = ur.user_id left join role r on r.id = ur.role_id where u.id in (1,2) group by u.username      

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1if1xGRK-1647627007990)(C:\Users\simpl\AppData\Local\Temp\1647618585401.png)]

复杂类型association

<resultMap id="roleMap" type="com.koala.domain.vo.UserRoleVO">
        <result column="username" property="username"></result>
        <association property="userRole" resultMap="RoleResultMap"></association>
    </resultMap>

    <resultMap id="RoleResultMap" type="com.koala.domain.entity.UserRole">
        <result column="user_id" jdbcType="BIGINT" property="userId"/>
        <result column="role_id" jdbcType="BIGINT" property="roleId"/>
    </resultMap>

    <select id="listUserRoleVO" resultMap="roleMap">
        select u.username,ur.role_id,ur.user_id from user u left join user_role ur on u.id = ur.user_id  where u.id in (1,2)
    </select>      

获得角色集合（未实现）

private String username;
    private Long roleId;
    private List<Role> roles;      

参考内容