Mybatis：多对多查询

需求

        查询⽤户同时查询出该⽤户的所有⻆⾊

Bean 

public class User implements Serializable {

    private Integer id;
    private String username;

    //表示用户关联的角色
    private List<Role> roleList = new ArrayList<>();
}


public class Role {

    private Integer id;
    private String roleName;
    private String roleDesc;
}

第一种方式XML

Mapper

public interface IUserMapper {

    //查询所有用户、同时查询每个用户关联的角色信息
    public List<User> findAllUserAndRole();
}

XML

    <resultMap id="userRoleMap" type="com.lagou.pojo.User">
        <result property="id" column="userid"></result>
        <result property="username" column="username"></result>
        <collection property="roleList" ofType="com.lagou.pojo.Role">
            <result property="id" column="roleid"></result>
            <result property="roleName" column="roleName"></result>
            <result property="roleDesc" column="roleDesc"></result>
        </collection>
    </resultMap>

    <select id="findAllUserAndRole" resultMap="userRoleMap">
        select * from user u left join sys_user_role ur on u.id = ur.userid
										 left join sys_role r on r.id = ur.roleid
    </select>

第二种方式注解

Mapper

public interface IUserMapper {

    //查询所有用户、同时查询每个用户关联的角色信息
    @Select("select * from user")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "roleList",column = "id",javaType = List.class,
             many = @Many(select = "com.lagou.mapper.IRoleMapper.findRoleByUid"))
    })
    public List<User> findAllUserAndRole();
}



public interface IRoleMapper {

    @Select("select * from sys_role r,sys_user_role ur where r.id = ur.roleid and ur.userid = #{uid}")
    public List<Role> findRoleByUid(Integer uid);
}