Mybatis 关联关系的概述(一对一，一对多，多对多)

最新推荐文章于 2021-09-28 00:39:03 发布

ACodeBird

最新推荐文章于 2021-09-28 00:39:03 发布

阅读量369

点赞数

分类专栏： mybatis

原文链接：https://blog.csdn.net/weixin_44117272/article/details/95213167

版权

mybatis 专栏收录该内容

17 篇文章 3 订阅

订阅专栏

1.简介

在这里插入图片描述

2.一对一

在这里插入图片描述

代码演示
首先创建测试用的mysql数据库

use mybatis;
create table tb_idcard(
	id int primary key auto_increment,
    CODE varchar(18)
);

insert into tb_idcard(CODE)values(‘152456985247’);
insert into tb_idcard(CODE)values(‘152963852745’);

create table tb_person(
id int primary key auto_increment,
name varchar(32),
age int,
sex varchar(8),
card_id int unique,
foreign key(card_id) references tb_idcard(id)
);t_usertb_person

insert into tb_person(name,age,sex,card_id) values(‘Rose’,29,‘女’,1);
insert into tb_person(name,age,sex,card_id) values(‘Tom’,29,‘男’,2);

2.创建对应的实体类
Person类

package com.lin.po;

public class Person {

<span class="token keyword">private</span> Integer id<span class="token punctuation">;</span>
<span class="token keyword">private</span> String name<span class="token punctuation">;</span>
<span class="token keyword">private</span> Integer age<span class="token punctuation">;</span>
<span class="token keyword">private</span> String sex<span class="token punctuation">;</span>
<span class="token keyword">private</span> IdCard card<span class="token punctuation">;</span><span class="token comment">// 个人关联证件</span>

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> name<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setName</span><span class="token punctuation">(</span>String name<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>name <span class="token operator">=</span> name<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> Integer <span class="token function">getAge</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> age<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setAge</span><span class="token punctuation">(</span>Integer age<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>age <span class="token operator">=</span> age<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getSex</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> sex<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setSex</span><span class="token punctuation">(</span>String sex<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>sex <span class="token operator">=</span> sex<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> IdCard <span class="token function">getCard</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> card<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setCard</span><span class="token punctuation">(</span>IdCard card<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>card <span class="token operator">=</span> card<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"Person [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", name="</span> <span class="token operator">+</span> name <span class="token operator">+</span> <span class="token string">", age="</span> <span class="token operator">+</span> age <span class="token operator">+</span> <span class="token string">", sex="</span> <span class="token operator">+</span> sex <span class="token operator">+</span> <span class="token string">", card="</span> <span class="token operator">+</span> card <span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

IdCard类


package com.lin.po;

public class IdCard {

<span class="token keyword">private</span> Integer id<span class="token punctuation">;</span>
<span class="token keyword">private</span> String code<span class="token punctuation">;</span>

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getCode</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> code<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setCode</span><span class="token punctuation">(</span>String code<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>code <span class="token operator">=</span> code<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"IdCard [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", code="</span> <span class="token operator">+</span> code <span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

3.创建对应的Mapper.xml文件对数据库进行操作
首先先创建Idcard对应的xml文件

<?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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.lin.mapper.IdCardMapper">
	<!-- 根据Id查询证件信息 -->
	<!-- resultType采用别名方式 -->
	<!-- 
	在mybatis添加 配置扫描包 别名不区分大小写
	<typeAliases>
		<package name="com.lin.po"/>
	</typeAliases>
	 -->
	<select id="findCodeById" parameterType="Integer" resultType="IdCard">
		select * from tb_idcard where id=#{id}
	</select>

</mapper>

然后创建Person的Mapper文件
这里有两种关联查询方式
方式一、嵌套查询:通过执行另外一条SQL映射语句来返回预期的特殊类型，比较耗内存
编写测试类

// 嵌套查询
	@Test
	public void findPersonByIdTest() {
		// 通过工具类，生成SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSqlSession();
	<span class="token comment">// 使用MyBatis嵌套查询的方式查询id为1的人的信息</span>
	Person person <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token string">"com.lin.mapper.PersonMapper.findPersonById"</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 输出查询结果信息</span>
	System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>person<span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 关闭SqlSession</span>
	sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

DEBUG [main] - ==>  Preparing: select * from tb_person where id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from tb_idcard where id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Person [id=1, name=Rose, age=29, sex=女, card=IdCard [id=1, code=152456985247]]

 
 
 
 1
2
3
4
5
6
7

可以看出，执行了两条SQL语句,这是我们最好在mybatis-config.xml配置文件中开启二级缓存

<settings>
		<setting name="cacheEnabled" value="true"/>
	<span class="token comment">&lt;!--打卡延迟加载的开关  --&gt;</span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>setting</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>lazyLoadingEnabled<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>true<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
	<span class="token comment">&lt;!-- 将积极加载改为消极加载，即按需加载 --&gt;</span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>setting</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>aggressiveLazyLoading<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>false<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>settings</span><span class="token punctuation">&gt;</span></span>

这时会按需加载
只要求得要名字
System.out.println(person.getName());
可以看出只执行了一条

DEBUG [main] - ==>  Preparing: select * from tb_person where id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Rose

方式二、嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集

<?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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.lin.mapper.PersonMapper">
	<!-- 嵌套查询:通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
	<select id="findPersonById" parameterType="Integer"
		resultMap="IdCardWithPersonResult">
		select * from tb_person where id=#{id}
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>select</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>resultMap</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Person<span class="token punctuation">"</span></span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>IdCardWithPersonResult<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>id</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>name<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>name<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>age<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>age<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>sex<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>sex<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token comment">&lt;!-- 一对一:association使用select属性引入另外一条SQL语句 --&gt;</span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>association</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>card<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>card_id<span class="token punctuation">"</span></span>
		<span class="token attr-name">javaType</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>IdCard<span class="token punctuation">"</span></span> <span class="token attr-name">select</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>com.lin.mapper.IdCardMapper.findCodeById<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>

	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>association</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>resultMap</span><span class="token punctuation">&gt;</span></span>

<span class="token comment">&lt;!-- 嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 --&gt;</span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>findPersonById2<span class="token punctuation">"</span></span> <span class="token attr-name">parameterType</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Integer<span class="token punctuation">"</span></span>
	<span class="token attr-name">resultMap</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>IdCardWithPersonResult<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
	select p.*,idcard.code from tb_person p,tb_idcard idcard
	where p.card_id=idcard.id and p.id=#{id}
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>select</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>resultMap</span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Person<span class="token punctuation">"</span></span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>IdCardWithPersonResult2<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>id</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>name<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>name<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>age<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>age<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>sex<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>sex<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token comment">&lt;!-- 一对一:association使用select属性引入另外一条SQL语句 --&gt;</span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>association</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>card<span class="token punctuation">"</span></span> <span class="token attr-name">javaType</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>IdCard<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
		<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>id</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>card_id<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
		<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>code<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>code<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
	<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>association</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>resultMap</span><span class="token punctuation">&gt;</span></span>

</mapper>

测试类

// 嵌套结果
		@Test
		public void findPersonByIdTest2() {
			// 通过工具类，生成SqlSession对象
			SqlSession sqlSession = MybatisUtils.getSqlSession();
		<span class="token comment">// 使用MyBatis嵌套查询的方式查询id为1的人的信息</span>
		Person person <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token string">"com.lin.mapper.PersonMapper.findPersonById2"</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token comment">// 输出查询结果信息</span>
		System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>person<span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token comment">// 关闭SqlSession</span>
		sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token punctuation">}</span>

运行结果

DEBUG [main] - ==>  Preparing: select p.*,idcard.code from tb_person p,tb_idcard idcard where p.card_id=idcard.id and p.id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Person [id=1, name=Rose, age=29, sex=女, card=IdCard [id=1, code=152456985247]]

 
 
 
 1
2
3
4

3.一对多

在这里插入图片描述

代码实例
创建两个表

use mybatis;
create table tb_user(
	id int(32) primary key auto_increment,
    username varchar(32),
    address varchar(256)
);

insert into tb_user values(‘1’,‘詹姆斯’,‘克利夫兰’);
insert into tb_user values(‘2’,‘科比’,‘洛杉矶’);
insert into tb_user values(‘3’,‘保罗’,‘洛杉矶’);
insert into tb_user values(‘4’,‘库里’,‘金州’);

create table tb_orders(
id int(32) primary key auto_increment,
number varchar(32) not null,
user_id int(32) not null,
foreign key(user_id) references tb_user(id)
);

insert into tb_orders values(‘1’,‘1000011’,‘1’);
insert into tb_orders values(‘2’,‘1000012’,‘1’);
insert into tb_orders values(‘3’,‘1000013’,‘2’);
insert into tb_orders values(‘4’,‘1000014’,‘4’);

创建对应的实体类:
Users类

package com.lin.po;

import java.util.List;

public class Users {

<span class="token keyword">private</span> Integer id<span class="token punctuation">;</span>
<span class="token keyword">private</span> String username<span class="token punctuation">;</span>
<span class="token keyword">private</span> String address<span class="token punctuation">;</span>
<span class="token keyword">private</span> List<span class="token generics function"><span class="token punctuation">&lt;</span>Orders<span class="token punctuation">&gt;</span></span> ordersList<span class="token punctuation">;</span>

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getUsername</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> username<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setUsername</span><span class="token punctuation">(</span>String username<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>username <span class="token operator">=</span> username<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getAddress</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> address<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setAddress</span><span class="token punctuation">(</span>String address<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>address <span class="token operator">=</span> address<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> List<span class="token generics function"><span class="token punctuation">&lt;</span>Orders<span class="token punctuation">&gt;</span></span> <span class="token function">getOrdersList</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> ordersList<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setOrdersList</span><span class="token punctuation">(</span>List<span class="token generics function"><span class="token punctuation">&lt;</span>Orders<span class="token punctuation">&gt;</span></span> ordersList<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>ordersList <span class="token operator">=</span> ordersList<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"User [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", username="</span> <span class="token operator">+</span> username <span class="token operator">+</span> <span class="token string">", address="</span> <span class="token operator">+</span> address <span class="token operator">+</span> <span class="token string">", ordersList="</span> <span class="token operator">+</span> ordersList
			<span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

Orders 类

package com.lin.po;

public class Orders {

<span class="token keyword">private</span> Integer id<span class="token punctuation">;</span>
<span class="token keyword">private</span> String number<span class="token punctuation">;</span>

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getNumber</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> number<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setNumber</span><span class="token punctuation">(</span>String number<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>number <span class="token operator">=</span> number<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"Orders [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", number="</span> <span class="token operator">+</span> number <span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

创建UserMapper.xml文件对数据库进行操作
一对多的查询：查看某一用户及其关联的订单信息当关联查询出的列相同时，需要使用别用区别
一对多关联映射：collection ofType:表示属性元素中元素的类型，List属性为Orders

<?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">
<!-- namespace表示命名空间 -->
<!-- namespace表示命名空间，目前只要唯一即可 后面会讲到它的重要性 -->
<!-- 写法一 -->
<mapper namespace="com.lin.mapper.UserMapper">
	<!-- 一对多的查询：查看某一用户及其关联的订单信息 当关联查询出的列相同时，需要使用别用区别 -->
	<select id="findUserWithOrders" parameterType="Integer"
		resultMap="UserWithOrdersResult">
		select u.*,o.id as orders_id,o.number from tb_user
		u,tb_orders o where
		u.id=o.user_id and u.id=#{id}
	</select>
	<resultMap type="Users" id="UserWithOrdersResult">
		<id property="id" column="id" />
		<result property="username" column="username" />
		<result property="address" column="address" />
		<!-- 一对多关联映射：collection ofType:表示属性元素中元素的类型，List<Orders>属性为ORders -->
		<collection property="ordersList" ofType="Orders">
			<id property="id" column="orders_id" />
			<result property="number" column="number" />
		</collection>
	</resultMap>
</mapper>

 
 
 
 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

在mybatis-config.xml文件中添加依赖

<mappers>
		<mapper resource="com/lin/mapper/UserMapper.xml"/>
	</mappers>

 
 
 
 1
2
3

单元测试

@Test
	public void findUserByIdTest() {
		// 通过工具类，生成SqlSession对象
	SqlSession sqlSession <span class="token operator">=</span> MybatisUtils<span class="token punctuation">.</span><span class="token function">getSqlSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

	<span class="token comment">// 使用MyBatis嵌套查询的方式查询id为1的人的信息</span>
	Users user <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token string">"com.lin.mapper.UserMapper.findUserWithOrders"</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 输出查询结果信息</span>
	System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>user<span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 关闭SqlSession</span>
	sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

运行结果

DEBUG [main] - ==>  Preparing: select u.*,o.id as orders_id,o.number from tb_user u,tb_orders o where u.id=o.user_id and u.id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 2
User [id=1, username=詹姆斯, address=克利夫兰, ordersList=[Orders [id=1, number=1000011], Orders [id=2, number=1000012]]]

4.多对多

在这里插入图片描述
代码演示
建立数据库

use mybatis;
create table tb_product(
	id int(32) primary key auto_increment,
    NAME varchar(32),
    price double
);

insert into tb_product values(‘1’,‘Java基础入门’,‘44.5’);
insert into tb_product values(‘2’,‘Java Web程序开发入门’,‘38.5’);
insert into tb_product values(‘3’,‘SSM 框架整合实战’,‘50’);

# 创建中间表
create table tb_ordersitem(
id int(32) primary key auto_increment,
orders_id int(32),
product_id int(32),
foreign key(orders_id) references tb_orders(id),
foreign key(product_id) references tb_product(id)
);
insert into tb_ordersitem values(‘1’,‘1’,‘1’);
insert into tb_ordersitem values(‘2’,‘1’,‘3’);
insert into tb_ordersitem values(‘3’,‘3’,‘3’);
#这里的tb_orders是上面一对多的表，如果已经建立不用在建
create table tb_orders(
id int(32) primary key auto_increment,
number varchar(32) not null,
user_id int(32) not null,
foreign key(user_id) references tb_user(id)
);

建立实体类
Product 类

package com.lin.po;

import java.util.List;

public class Product {
private Integer id;
private String name;
private Double price;
private List<Orders> orders;

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> name<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setName</span><span class="token punctuation">(</span>String name<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>name <span class="token operator">=</span> name<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> Double <span class="token function">getPrice</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> price<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setPrice</span><span class="token punctuation">(</span>Double price<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>price <span class="token operator">=</span> price<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> List<span class="token generics function"><span class="token punctuation">&lt;</span>Orders<span class="token punctuation">&gt;</span></span> <span class="token function">getOrders</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> orders<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setOrders</span><span class="token punctuation">(</span>List<span class="token generics function"><span class="token punctuation">&lt;</span>Orders<span class="token punctuation">&gt;</span></span> orders<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>orders <span class="token operator">=</span> orders<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"Product [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", name="</span> <span class="token operator">+</span> name <span class="token operator">+</span> <span class="token string">", price="</span> <span class="token operator">+</span> price <span class="token operator">+</span> <span class="token string">", orders="</span> <span class="token operator">+</span> orders <span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

Orders 类多增加关联商品集合信息
private List productList;

package com.lin.po;

import java.util.List;

public class Orders {

<span class="token keyword">private</span> Integer id<span class="token punctuation">;</span>
<span class="token keyword">private</span> String number<span class="token punctuation">;</span>
<span class="token comment">// 关联商品集合信息</span>
<span class="token keyword">private</span> List<span class="token generics function"><span class="token punctuation">&lt;</span>Product<span class="token punctuation">&gt;</span></span> productList<span class="token punctuation">;</span>

<span class="token keyword">public</span> Integer <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> String <span class="token function">getNumber</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> number<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setNumber</span><span class="token punctuation">(</span>String number<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>number <span class="token operator">=</span> number<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> List<span class="token generics function"><span class="token punctuation">&lt;</span>Product<span class="token punctuation">&gt;</span></span> <span class="token function">getProductList</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> productList<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setProductList</span><span class="token punctuation">(</span>List<span class="token generics function"><span class="token punctuation">&lt;</span>Product<span class="token punctuation">&gt;</span></span> productList<span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">this</span><span class="token punctuation">.</span>productList <span class="token operator">=</span> productList<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> String <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token string">"Orders [id="</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", number="</span> <span class="token operator">+</span> number <span class="token operator">+</span> <span class="token string">", productList="</span> <span class="token operator">+</span> productList <span class="token operator">+</span> <span class="token string">"]"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

方式一
OrdersMapper.xml
多对多嵌套结果查询，查询某订单及其关联的商品详情,效率高

<!-- 多对多嵌套结果查询，查询某订单及其关联的商品详情 -->
<mapper namespace="com.lin.mapper.PersonMapper">
	<select id="findOrderWithProduct2" parameterType="Integer"
		resultMap="OrdersWithProductResult2">
		select o.*,p.id as pid,p.name,p.price from
		tb_orders o,tb_product p,tb_ordersitem oi
		where oi.orders_id=o.id
		and oi.product_id=p.id
		and o.id=#{id}
	</select>
	<!-- 自定义手动映射类型 -->
	<resultMap type="Orders" id="OrdersWithProductResult2">
		<id property="id" column="id" />
		<result property="number" column="number" />
		<!-- 多对多关联映射：collection -->
		<collection property="productList" ofType="Product">
			<id property="id" column="pid"/>
			<result property="name" column="name"/>
			<result property="price" column="price"/>
		</collection>
	</resultMap>
</mapper>

 
 
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

测试类：

	/**
	 * 多对多 嵌套结果
	 */
	@Test
	public void findOrdersByIdTest2() {
		// 通过工具类，生成SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSqlSession();
	<span class="token comment">// 使用MyBatis嵌套查询的方式查询id为1的人的信息</span>
	Orders orders <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token string">"com.lin.mapper.OrdersMapper.findOrderWithProduct2"</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 输出查询结果信息</span>
	System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>orders<span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 关闭SqlSession</span>
	sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

运行结果

DEBUG [main] - ==>  Preparing: select o.*,p.id as pid,p.name,p.price from tb_orders o,tb_product p,tb_ordersitem oi where oi.orders_id=o.id and oi.product_id=p.id and o.id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 2
Orders [id=1, number=1000011, productList=[Product [id=1, name=Java基础入门, price=44.5, orders=null], Product [id=3, name=SSM 框架整合实战, price=50.0, orders=null]]]

方式二
OrdersMapper.xml

<!-- 多对多嵌套查询，通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
<mapper namespace="com.lin.mapper.PersonMapper">
	<select id="findOrderWithProduct" parameterType="Integer"
		resultMap="OrdersWithProductResult">
		select * from tb_orders where id=#{id}
	</select>
	<resultMap type="Orders" id="OrdersWithProductResult">
		<id property="id" column="id" />
		<result property="number" column="number" />
		<collection property="productList" column="id"
			ofType="Product"
			select="com.lin.mapper.ProductMapper.findProductById">
		</collection>
	</resultMap>
</mapper>

 
 
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ProductMapper.xml

<mapper namespace="com.lin.mapper.ProductMapper">
	<select id="findProductById" parameterType="Integer"
		resultType="Product">
		select * from tb_product where id in(
		select product_id from tb_ordersitem where orders_id=#{id}
		)
	</select>
</mapper>

 
 
 
 1
2
3
4
5
6
7
8

/**
	 * 多对多
	 */
	@Test
	public void findOrdersByIdTest() {
		// 通过工具类，生成SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSqlSession();
	<span class="token comment">// 使用MyBatis嵌套查询的方式查询id为1的人的信息</span>
	Orders orders <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token string">"com.lin.mapper.OrdersMapper.findOrderWithProduct"</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 输出查询结果信息</span>
	System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>orders<span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token comment">// 关闭SqlSession</span>
	sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

运行结果：可以看出执行了两次SQL语句

DEBUG [main] - ==>  Preparing: select * from tb_orders where id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from tb_product where id in( select product_id from tb_ordersitem where orders_id=? ) 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 2
Orders [id=1, number=1000011, productList=[Product [id=1, name=Java基础入门, price=44.5, orders=null], Product [id=3, name=SSM 框架整合实战, price=50.0, orders=null]]]

                                </div>
            <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
                </div>
</article>