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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
如果图片无法查看,请看这里
# 多对多查询的语句
对应的sql语句:
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
1
2
2
查询结果如下:
id | username | id | rolename |
---|---|---|---|
1 | lucy | 1 | CTO |
2 | tom | 1 | CTO |
1 | lucy | 2 | CEO |
2 | tom | 2 | CEO |
# 创建Role实体,修改User实体
User
class User {
var id: Int? = null
var username: String? = null
// 代表当前用户具备那些订单
var orderList: List<Order>? = null
// 代表当前用户具备的那些角色
var roleList: List<Role>? = null
override fun toString(): String {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
", roleList=" + roleList +
'}'
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class User {
private Integer id;
private String username;
// 代表当前用户具备那些订单
private List<Order> orderList;
// 代表当前用户具备的那些角色
private List<Role> 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<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
// Make sure to add code blocks to your code group
Role
class Role {
private val id: Int? = null
private val rolename: String? = null
override fun toString(): String {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
'}'
}
}
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
public class Role {
private Integer id;
private String rolename;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
'}';
}
}
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
// Make sure to add code blocks to your code group
# UserMapper添加接口方法
/**
* 查询所有用户以及对应的角色
* @return
*/
fun findAllUserAndRole(): List<User?>?
1
2
3
4
5
2
3
4
5
/**
* 查询所有用户以及对应的角色
* @return
*/
public List<User> findAllUserAndRole();
1
2
3
4
5
2
3
4
5
// Make sure to add code blocks to your code group
# 配置UserMapper.xml
<resultMap id="userRoleMap" type="com.terwergreen.pojo.User">
<result property="id" column="id"></result>
<result property="username" column="username"></result>
<collection property="roleList" ofType="com.terwergreen.pojo.Role">
<result column="rid" property="id"></result>
<result column="rolename" property="rolename"></result>
</collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
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
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 测试结果
==> 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:
<== Columns: id, username, rid, rolename
<== Row: 1, lucy, 1, CTO
<== Row: 2, tom, 1, CTO
<== Row: 1, lucy, 2, CEO
<== Row: 2, tom, 2, CEO
<== Total: 4
User{id=1, username='lucy', orderList=null, roleList=[Role{id=1, rolename='CTO'}, Role{id=2, rolename='CEO'}]}
User{id=2, username='tom', orderList=null, roleList=[Role{id=1, rolename='CTO'}, Role{id=2, rolename='CEO'}]}
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 知识总结
MyBatis多表配置方式:
一对一配置:使用做配置
一对多配置:使用+做配置
多对多配置:使用+做配置
# 本文代码地址
mybatis-multitable (opens new window)
![NOTE] 文章更新历史
2022/05/08 feat:新增Kotlin支持。
2022/05/04 feat:初稿。
编辑 (opens new window)
上次更新: 2023/02/22, 13:47:25
- 01
- 解决css部分border被圆角切掉之后圆角的边框消失问题03-18
- 02
- 使用TypeScript开发一个自定义的Node-js前端开发脚手架03-08
- 03
- Github-Actions使用release-please实现自动发版03-06