MyBatis关系映射--一对多关系映射
MyBatis关系映射--一对多映射1.关系映射2.关系映射2.1查询映射2.2结果映射2.3不同的方式的分析3.例子3.1创建一个MyBatis工程3.2 导入jar包3.3实现Java类3.4properties3.5resource3.6mapper4.运行结果5.总结1.关系映射实际开发中，常常一对一的关系并不能满足开发需要，更多的是一对多的关系，比如1个人怎么也不止一个朋友，1个公司..._一对多映射
MyBatis关系映射--一对多关系映射
文章信息
- 原文链接：https://jiayq.blog.csdn.net/article/details/88254511
- 发布时间：2019-03-06 20:34:46
- 阅读量：1
- 分类：java同时被 3 个专栏收录, 订阅专栏, mybatis, MyBatis
- 标签：#MyBatis关系映射–一对多映射, #查询映射和结果映射, #MyBatis映射方式对比, #MyBatis多表一对多映射, #MyBatis映射实例
摘要
文章浏览阅读1.4k次。MyBatis关系映射–一对多映射1.关系映射2.关系映射2.1查询映射2.2结果映射2.3不同的方式的分析3.例子3.1创建一个MyBatis工程3.2 导入jar包3.3实现Java类3.4properties3.5resource3.6mapper4.运行结果5.总结1.关系映射实际开发中，常常一对一的关系并不能满足开发需要，更多的是一对多的关系，比如1个人怎么也不止一个朋友，1个公司…_一对多映射
MyBatis关系映射–一对多映射
- 1.关系映射
- 2.关系映射
- 2.1查询映射
- 2.2结果映射
- 2.3不同的方式的分析
- 2.1查询映射
- 3.例子
- 3.1创建一个MyBatis工程
- 3.2 导入jar包
- 3.3实现Java类
- 3.4properties
- 3.5resource
- 3.6mapper
- 3.1创建一个MyBatis工程
- 4.运行结果
- 5.总结
1.关系映射
实际开发中，常常一对一的关系并不能满足开发需要，更多的是一对多的关系，比如1个人怎么也不止一个朋友，1个公司至少1个员工，1本书多余1页等等，所以，一对多的关系也是开发的基本关系。
2.关系映射
1.通过查询映射
2.通过结果映射
2.1查询映射
查询映射就是查询一的表时，调用多的表的查询方法，传入一的id（或者其他字段）进行查询，然后组装的对象返回返回。查询映射就相当于查询了1+多次，使用了2条SQL，但是多的那个表的查询SQL执行了多次。
查询映射的写法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!-- 嵌套查询 -->
<resultMap type="people" id="peopleWithFriend">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<collection property="friends" ofType="friend" column="id"
select="friendMapper.selectByPeopleId">
</collection>
</resultMap>
<select id="selectOneToMore" parameterType="Long" resultMap="peopleWithFriend">
select <include refid="baseMapper.str_select_people"></include>
from <include refid="baseMapper.str_table_people"></include>
<where>
<if test="_parameter != null and _parameter != ''">
and id=#{_parameter}
</if>
</where>
</select>
其中friendMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="friendMapper">
<select id="selectByPeopleId" parameterType="Long" resultType="friend">
select <include refid="baseMapper.str_select_friend"></include>
from <include refid="baseMapper.str_table_friend"></include>
<where>
<if test="_parameter != null and _parameter != ''">
and people_id=#{_parameter}
</if>
</where>
</select>
<insert id="insertFriend" parameterType="friend">
insert into <include refid="baseMapper.str_table_friend"></include>
(<include refid="baseMapper.str_select_friend"></include>)
values(
#{id},#{name},#{people_id}
)
</insert>
</mapper>
2.2结果映射
结果映射就是通过一条select语句查询出所有的结果，然后把返回的数据封装为实体。
结果映射的写法：
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
<!-- 嵌套结果 -->
<sql id="str_select_people_friend">
p.id as pid,p.name as pname,p.age,p.sex,f.id as fid,f.name as fname
</sql>
<sql id="str_table_people_friend">
people p,friend f
</sql>
<resultMap type="people" id="resultPeopleWithFriend">
<id property="id" column="pid"/>
<result property="name" column="pname"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<collection property="friends" ofType="friend">
<id property="id" column="fid"/>
<result property="name" column="fname"/>
<result property="people_id" column="id"/>
</collection>
</resultMap>
<select id="selectResult" parameterType="Long" resultMap="resultPeopleWithFriend">
select <include refid="str_select_people_friend"></include>
from <include refid="str_table_people_friend"></include>
<where>
p.id = f.people_id
<if test="_parameter != null and _parameter != ''">
and p.id=#{_parameter}
</if>
</where>
</select>
2.3不同的方式的分析
特别是多个表进行关系的映射时，如果使用查询映射，其查询逻辑较为简单，易于修改，易于开发。
但是因为查询映射需要执行多条语句，肯定没有一次性返回结果的效率高，而且因为查询映射需要依赖多个外部资源，集成难度大，结果映射只需要专注于写SQL即可。
3.例子
3.1创建一个MyBatis工程
3.2 导入jar包
3.3实现Java类
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
package com.client;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.domain.Friend;
import com.util.MyBatisSessionUtils;
public class Main {
@Test
public void addfriend(){
SqlSession session = MyBatisSessionUtils.getSession();
Friend friend = new Friend();
friend.setId(0L);
friend.setName("0000");
friend.setPeople_id(101L);
System.out.println(session.insert("friendMapper.insertFriend", friend));
session.commit();
session.close();
}
@Test
public void batchAddFriend(){
SqlSession session = MyBatisSessionUtils.getSession();
Friend friend = new Friend();
for(int i = 1 ;i <= 36*3;i++){
friend.setId(Long.valueOf(i));
friend.setName("batchAdd"+i);
friend.setPeople_id(Long.valueOf(i%36+100));
System.out.println(friend);
session.insert("friendMapper.insertFriend", friend);
}
session.commit();
session.close();
}
@Test
public void queryPeople(){
SqlSession session = MyBatisSessionUtils.getSession();
System.out.println(session.selectOne("peopleMapper.selectById", 103L));
session.close();
}
@Test
public void queryFriend(){
SqlSession session = MyBatisSessionUtils.getSession();
System.out.println(session.selectOne("friendMapper.selectByPeopleId", 26L));
session.close();
}
@Test
public void queryFriends(){
SqlSession session = MyBatisSessionUtils.getSession();
session.selectList("friendMapper.selectByPeopleId", 126L).forEach(
p -> System.out.println(p));
session.close();
}
@Test
public void queryPeopleWithFriend(){
SqlSession session = MyBatisSessionUtils.getSession();
System.out.println(session.selectOne("oneToMoreMapper.selectOneToMore", 102L));
session.close();
}
@Test
public void queryPeopleWithResult(){
SqlSession session = MyBatisSessionUtils.getSession();
System.out.println(session.selectOne("oneToMoreMapper.selectResult", 103L));
session.close();
}
}
package com.domain;
import java.io.Serializable;
public class Friend implements Serializable{
/**
*
*/
private static final long serialVersionUID = 41844053171279021L;
private Long id;
private String name;
private Long people_id;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getPeople_id() {
return people_id;
}
public void setPeople_id(Long people_id) {
this.people_id = people_id;
}
@Override
public String toString() {
return "[id="+this.id+",name="+this.name+",people_id="+this.people_id+"]";
}
}
package com.domain;
import java.io.Serializable;
import java.util.List;
public class People implements Serializable{
/**
*
*/
private static final long serialVersionUID = -3270893239281340723L;
private Long id;
private String name;
private Integer age;
private Integer sex;
private List<Friend> friends;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public List<Friend> getFriends() {
return friends;
}
public void setFriends(List<Friend> friends) {
this.friends = friends;
}
@Override
public String toString() {
return "people [id=" + this.id + ",name=" + this.name + ",age="
+ this.age + ",sex=" + this.sex + ",friend=" + this.friends + "]";
}
}
package com.util;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisSessionUtils {
private static SqlSessionFactory sessionFactory = null;
private static final String PATH="com/resource/mybatis.xml";
static{
try{
Reader reader = Resources.getResourceAsReader(PATH);
sessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e){
e.printStackTrace();
}
}
public static SqlSession getSession(){
return sessionFactory.openSession();
}
}
3.4properties
log4j.properties
1
2
3
4
5
6
7
8
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
ojdbc.properties
1
2
3
4
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:oracle
username=study
password=study
3.5resource
mybatis.xml
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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置外在化 -->
<properties resource="com/property/ojdbc.properties"></properties>
<settings>
<!-- 延迟加载全局 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 关联对象属性的延迟加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- 改变运行时行为 -->
<typeAliases>
<!-- 配置别名 -->
<typeAlias type="com.domain.People" alias="people"/>
<typeAlias type="com.domain.Friend" alias="friend"/>
</typeAliases>
<!-- 环境配置 -->
<environments default="oracle">
<environment id="oracle">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mapper/baseMapper.xml"/>
<mapper resource="com/mapper/peopleMapper.xml"/>
<mapper resource="com/mapper/friendMapper.xml"/>
<mapper resource="com/mapper/oneToMoreMapper.xml"/>
</mappers>
</configuration>
3.6mapper
baseMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="baseMapper">
<sql id="str_select_people">
p.id,p.name,p.age,p.sex
</sql>
<sql id="str_table_people">
people p
</sql>
<sql id="str_select_friend">
f.id,f.name,f.people_id
</sql>
<sql id="str_table_friend">
friend f
</sql>
</mapper>
friendMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="friendMapper">
<select id="selectByPeopleId" parameterType="Long" resultType="friend">
select <include refid="baseMapper.str_select_friend"></include>
from <include refid="baseMapper.str_table_friend"></include>
<where>
<if test="_parameter != null and _parameter != ''">
and people_id=#{_parameter}
</if>
</where>
</select>
<insert id="insertFriend" parameterType="friend">
insert into <include refid="baseMapper.str_table_friend"></include>
(<include refid="baseMapper.str_select_friend"></include>)
values(
#{id},#{name},#{people_id}
)
</insert>
</mapper>
oneToMoreMapper.xml
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
53
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="oneToMoreMapper">
<!-- 嵌套查询 -->
<resultMap type="people" id="peopleWithFriend">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<collection property="friends" ofType="friend" column="id"
select="friendMapper.selectByPeopleId">
</collection>
</resultMap>
<select id="selectOneToMore" parameterType="Long" resultMap="peopleWithFriend">
select <include refid="baseMapper.str_select_people"></include>
from <include refid="baseMapper.str_table_people"></include>
<where>
<if test="_parameter != null and _parameter != ''">
and id=#{_parameter}
</if>
</where>
</select>
<!-- 嵌套结果 -->
<sql id="str_select_people_friend">
p.id as pid,p.name as pname,p.age,p.sex,f.id as fid,f.name as fname
</sql>
<sql id="str_table_people_friend">
people p,friend f
</sql>
<resultMap type="people" id="resultPeopleWithFriend">
<id property="id" column="pid"/>
<result property="name" column="pname"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<collection property="friends" ofType="friend">
<id property="id" column="fid"/>
<result property="name" column="fname"/>
<result property="people_id" column="id"/>
</collection>
</resultMap>
<select id="selectResult" parameterType="Long" resultMap="resultPeopleWithFriend">
select <include refid="str_select_people_friend"></include>
from <include refid="str_table_people_friend"></include>
<where>
p.id = f.people_id
<if test="_parameter != null and _parameter != ''">
and p.id=#{_parameter}
</if>
</where>
</select>
</mapper>
peopleMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="peopleMapper">
<select id="selectById" parameterType="Long" resultType="people">
select <include refid="baseMapper.str_select_people"></include>
from <include refid="baseMapper.str_table_people"></include>
<where>
<if test="_parameter != null and _parameter != ''">
and id=#{_parameter}
</if>
</where>
</select>
</mapper>
4.运行结果
queryPeople
1
2
3
4
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
people [id=103,name=aPeopleupdate,age=53,sex=0,friend=null]
queryFriend
1
2
3
4
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[[id=26,name=batchAdd26,people_id=126], [id=62,name=batchAdd62,people_id=126], [id=98,name=batchAdd98,people_id=126]]
queryFriends
1
2
3
4
5
6
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[id=26,name=batchAdd26,people_id=126]
[id=62,name=batchAdd62,people_id=126]
[id=98,name=batchAdd98,people_id=126]
queryPeopleWithFriend
1
2
3
4
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
people [id=102,name=bPeople,age=62,sex=1,friend=[[id=2,name=batchAdd2,people_id=102], [id=38,name=batchAdd38,people_id=102], [id=74,name=batchAdd74,people_id=102]]]
queryPeopleWithResult
1
2
3
4
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
people [id=103,name=aPeopleupdate,age=53,sex=0,friend=[[id=3,name=batchAdd3,people_id=null], [id=39,name=batchAdd39,people_id=null], [id=75,name=batchAdd75,people_id=null]]]
5.总结
查询映射与结果映射这两种一对多关系的实现，根据需要进行选择，如果有已经实现且通过测试的查询，那么应该选择查询映射，如果没有实现的查询，且关联的表多个，那么尽可能使用结果映射。按需选择。
本文由作者按照 CC BY 4.0 进行授权