MyBatis复杂映射开发之多对多查询

多对多查询的模型

���户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用。

多对多查询的需求：查询所有用户的同时查询出该用户对应的所有角色。

@startuml !theme plain top to bottom direction skinparam linetype ortho class sys_role { rolename: varchar(255) roleDesc: varchar(255) id: int(11) } class sys_user_role { userid: int(11) roleid: int(11) } class node2 as "user /* 用户表 */" { username: varchar(50) password: varchar(50) birthday: varchar(50) id: int(11) } sys_user_role ^-[#595959,plain]- sys_role : "roleid:id" sys_user_role ^-[#595959,plain]- node2 : "userid:id" @enduml

如果图片无法查看，请看这里

​

多对多查询的语句

对应的 sql 语句：

select u.id,u.username,r.id,r.rolename from user u left join sys_user_role ur on u.id=ur.useridinner join sys_role r on r.id= ur.roleid

查询结果如下：

id	username	id	rolename
1	lucy	1	CTO
2	tom	1	CTO
1	lucy	2	CEO
2	tom	2	CEO

创建 Role 实体，修改 User 实体

User

public class User {    private Integer id;    private String username;    // 代表当前用户具备那些订单    private List orderList;    // 代表当前用户具备的那些角色    private List roleList;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public List getOrderList() {        return orderList;    }    public void setOrderList(List orderList) {        this.orderList = orderList;    }    public List getRoleList() {        return roleList;    }    public void setRoleList(List roleList) {        this.roleList = roleList;    }    @Override    public String toString() {        return "User{" +                ", username='" + username + '\'' +                ", orderList=" + orderList +                ", roleList=" + roleList +                '}';    }}

Role

public class Role {    private Integer id;    private String rolename;    @Override  public String toString() {      return "Role{" +              ", rolename='" + rolename + '\'' +              '}';  }}

UserMapper 添加接口方法

/** * 查询所有用户以及对应的角色 * @return */public List findAllUserAndRole();

配置 UserMapper.xml

                                    select u.id,u.username,r.id,r.rolename from user u left join sys_user_role ur on u.id=ur.userid    inner join sys_role r on r.id=ur.roleid

测试结果

==>  Preparing: select u.id,u.username,r.id as rid,r.rolename from user u left join sys_user_role ur on u.id=ur.userid inner join sys_role r on r.id=ur.roleid==> Parameters: