一、关联映射概述
对于MyBatis环境配置可以参考我另外两篇博客
MyBatis基础环境配置
https://blog.csdn.net/qq_49873907/article/details/129845464?spm=1001.2014.3001.5501
MyBatis动态SQLhttps://blog.csdn.net/qq_49873907/article/details/129868837?spm=1001.2014.3001.5501
在关系型数据库中，表与表之间存在着3种关联映射关系，分别为一对一、一对多、多对多。
如图所示：
一对一：一个数据表中的一条记录最多可以与另一个数据表中的一条记录相关。列如学生与学号就属于一对一关系。
一对多：主键数据表中的一条记录可以与另外一个数据表任意数量的记录相关，另外一个数据表中的一条记录只能与主键数据表中的某一条记录相关。列如班级与学生就属于一对多关系。
多对多：一个数据表中的一条记录可以与另外一个数据表任意数量的记录相关，另外一个数据表中的一条记录也可以与本数据表中任意数量的记录相关。列如学生和老师就属于多对多关系。
二、查询操作
1. 一对一查询
在MyBatis中，通过<association>元素来处理一对一关联关系。<association>元素提供了一系列属性用于维护数据表之间的关系。<association>元素中的属性如下表所示：
| 属性 | 说明 |
|---|---|
| property | 用于指定映射到实体类对象的属性，与表字段一一对应 |
| column | 用于指定表中对应的字段 |
| javaType | 用于指定映射到实体对象的属性的类型 |
| jdbcType | 用于指定数据表中对应字段的类型 |
| fetchType | 用于指定再关联查询时是否启用延迟加载。fetchType属性有lazy和eager两个属性值，默认值为lazy（即默认关联映射延迟加载） |
| select | 用于指定引入嵌套查询的子SQL语句，该属性用于关联映射中的嵌套查询 |
| autoMapping | 用于指定是否自动映射 |
| typeHandler | 用于指定一个类型处理器 |
1.嵌套查询方式
嵌套查询也叫分布查询，是指通过执行另外一条SQL映射语句来返回预期的复杂类型。
（1） 创建数据库mybatis，并且在数据库中创建编码表tb_idcard 和 用户表 tb_user，同时预先插入三条数据，具体sql语句如下：
use mybatis;
# 创建一个编码表
CREATE TABLE tb_idcard(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18)
);
INSERT INTO tb_idcard(CODE) VALUES('11111111111111');
INSERT INTO tb_idcard(CODE) VALUES('22222222222222');
INSERT INTO tb_idcard(CODE) VALUES('33333333333333');
# 创建一个用户表
create table tb_user(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(32),
uage INT,
usex VARCHAR(8),
ucard_id INT UNIQUE,
FOREIGN KEY(ucard_id) REFERENCES tb_idcard(id)
);
insert into tb_user(uname,uage,usex,ucard_id) values('张三',20,'男',2);
insert into tb_user(uname,uage,usex,ucard_id) values('李四',18,'男',3);
insert into tb_user(uname,uage,usex,ucard_id) values('王五',22,'女',1);
数据表如图所示：
（2）在项目的 com.houjinqiao.pojo包下创建持久化类Idcard和TUser用于封装编码信息和用户信息。
Idcard.java
package com.houjinqiao.pojo;
public class Idcard {
private Integer id;
private String code;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
@Override
public String toString() {
return "Idcard{" +
"id=" + id +
", code='" + code + '\'' +
'}';
}
}TUser.java
package com.houjinqiao.pojo;
public class TUser {
int uid;
String uname;
int uage;
String usex;
Idcard uidcard;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public int getUage() {
return uage;
}
public void setUage(int uage) {
this.uage = uage;
}
public String getUsex() {
return usex;
}
public void setUsex(String usex) {
this.usex = usex;
}
public Idcard getUidcard() {
return uidcard;
}
public void setUidcard(Idcard uidcard) {
this.uidcard = uidcard;
}
@Override
public String toString() {
return "TUser{" +
"uid=" + uid +
", uname='" + uname + '\'' +
", uage=" + uage +
", usex='" + usex + '\'' +
", uidcard=" + uidcard +
'}';
}
}（3）在com.houjinqiao.mapper包中，创建映射文件TUserMapper.xml和IdCardMapper.xml，并在映射文件中编写一对一关联映射查询的配制信息。
IdCardMapper.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.IdCardMapper">
<!-- *************一对一查询 分布查询***************-->
<select id="findIdcardById" resultType="com.houjinqiao.pojo.Idcard">
select * from tb_idcard where id = #{id}
</select>
</mapper>TUserMapper.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.TUserMapper">
<!-- *************一对一查询 分布查询***************-->
<select id="findTUserById" resultMap="findIdCardByIDMapper">
select * from tb_user where uid = #{uid}
</select>
<resultMap id="findIdCardByIDMapper" type="com.houjinqiao.pojo.TUser">
<association property="uidcard" column="ucard_id" select="com.houjinqiao.mapper.IdCardMapper.findIdcardById"/>
</resultMap>
</mapper>（4）在核心配制文件mybatis-config.xml中，引入TUserMapper.xml和IdCardMapper.xml映射文件。
<mappers>
<mapper resource="com/houjinqiao/mapper/TUserMapper.xml"/>
<mapper resource="com/houjinqiao/mapper/IdCardMapper.xml"/>
</mappers>（5）写接口
在com.houjinqiao.mapper文件夹下创建两个接口文件IdCardMapper和TUserMapper
注意：接口文件的路径和配制映射文件的路径不一样，详情见下图：
IdCardMapper.java
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.Idcard;
public interface IdCardMapper {
Idcard findIdcardById(int id);
} TUserMapper.java
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.TUser;
public interface TUserMapper {
TUser findTUserById(int id);
TUser findTUserById2(int id);
}(6) 测试类
//一对一查询 分步查询
@Test
public void TUser1(){
SqlSession sqlSession = MyBatisUtils.getSession();
TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
TUser tUser = mapper.findTUserById(1);
System.out.println(tUser);
sqlSession.commit();
sqlSession.close();
}测试结果
[DEBUG] [main] c.h.m.T.findTUserById - ==> Preparing: select * from tb_user where uid = ?
[DEBUG] [main] c.h.m.T.findTUserById - ==> Parameters: 1(Integer)
[DEBUG] [main] c.h.m.I.findIdcardById - ====> Preparing: select * from tb_idcard where id = ?
[DEBUG] [main] c.h.m.I.findIdcardById - ====> Parameters: 2(Integer)
[DEBUG] [main] c.h.m.I.findIdcardById - <==== Total: 1
[DEBUG] [main] c.h.m.T.findTUserById - <== Total: 1
TUser{uid=1, uname='张三', uage=20, usex='男', uidcard=Idcard{id=2, code='22222222222222'}}
2.嵌套结果方式
嵌套结果方式又叫做同步查询方式，是使用嵌套结果映射来处理重复的联合结果的子集。
由于我们以及完成了数据库、实体类、的创建，现在只需要创建findIdCardByIDMapper2.xml
映射文件和测试类即可。
findIdCardByIDMapper2.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.TUserMapper">
<!-- *************一对一查询 同布查询***************-->
<select id="findTUserById2" resultMap="findIdCardByIDMapper2">
select u.*,card.id as cardID,card.code as ucard
from tb_user u,tb_idcard card
where u.uid = #{uid} and u.ucard_id = card.id
</select>
<resultMap id="findIdCardByIDMapper2" type="com.houjinqiao.pojo.TUser">
<id property="uid" column="uid"/>
<result property="uname" column="uanme"/>
<result property="uage" column="uage"/>
<result property="usex" column="usex"/>
<association property="uidcard" javaType="com.houjinqiao.pojo.Idcard">
<id property="id" column="cardID"/>
<result property="code" column="ucard"/>
</association>
</resultMap>
</mapper>测试类 TUser2
//一对一查询 同步查询
@Test
public void TUser2(){
SqlSession sqlSession = MyBatisUtils.getSession();
TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
TUser tUser = mapper.findTUserById2(2);
System.out.println(tUser);
sqlSession.commit();
sqlSession.close();
}测试结果
Preparing: select u.*,card.id as cardID,card.code as ucard from tb_user u,tb_idcard card where u.uid = ? and u.ucard_id = card.id
[DEBUG] [main] c.h.m.T.findTUserById2 - ==> Parameters: 2(Integer)
[DEBUG] [main] c.h.m.T.findTUserById2 - <== Total: 1
TUser{uid=2, uname='null', uage=18, usex='男', uidcard=Idcard{id=3, code='33333333333333'}}
2. 一对多查询
对于一对多查询，也分为嵌套查询和嵌套结果（分布查询和同步查询），接下来我们对这两种方式进行介绍：
1.嵌套查询（分布查询）
（1） 创建数据表
# 创建一个订单表表
CREATE TABLE tb_orders (
or_id int(32) PRIMARY KEY AUTO_INCREMENT,
order_num varchar(32) NOT NULL,
user_id int(32) NOT NULL,
FOREIGN KEY(user_id) REFERENCES tb_user(uid)
);
INSERT INTO tb_orders(order_num,user_id) VALUES('20211111',1);
INSERT INTO tb_orders(order_num,user_id) VALUES('202222222',1);
INSERT INTO tb_orders(order_num,user_id) VALUES('202233333',2);
INSERT INTO tb_orders(order_num,user_id) VALUES('2022444444',3);
（2）在项目的 com.houjinqiao.pojo包下创建Order持久化类用于封装订单信息，同时再TUser.java文件中增加一个属性 List<Order> orderList;并对其添加get、set方法和重写tostring方法。
Order.java
package com.houjinqiao.pojo;
import java.util.List;
public class Order {
int or_id;
String order_num ;
int user_id;
List<Product> productList;
public int getOr_id() {
return or_id;
}
public void setOr_id(int or_id) {
this.or_id = or_id;
}
public String getOrder_num() {
return order_num;
}
public void setOrder_num(String order_num) {
this.order_num = order_num;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public List<Product> getProductList() {
return productList;
}
public void setProductList(List<Product> productList) {
this.productList = productList;
}
@Override
public String toString() {
return "Order{" +
"or_id=" + or_id +
", order_num='" + order_num + '\'' +
", user_id=" + user_id +
", productList=" + productList +
'}';
}
}TUser.java
package com.houjinqiao.pojo;
import java.util.List;
public class TUser {
int uid;
String uname;
int uage;
String usex;
Idcard uidcard;
List<Order> orderList;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public int getUage() {
return uage;
}
public void setUage(int uage) {
this.uage = uage;
}
public String getUsex() {
return usex;
}
public void setUsex(String usex) {
this.usex = usex;
}
public Idcard getUidcard() {
return uidcard;
}
public void setUidcard(Idcard uidcard) {
this.uidcard = uidcard;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "TUser{" +
"uid=" + uid +
", uname='" + uname + '\'' +
", uage=" + uage +
", usex='" + usex + '\'' +
", uidcard=" + uidcard +
", orderList=" + orderList +
'}';
}
}（3）写接口
在com.houjinqiao.mapper包下创建接口文件OrderMapper。
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.Order;
import java.util.List;
public interface OrderMapper{
//*************一对多查询 分布查询***************
List<Order> findOrdersByUserId(int id);
}
（4）在com.houjinqiao.mapper文件夹中，创建映射文件OrderMapper.xml，并在映射文件中编写一对多关联映射查询的配制信息。同时在TUserMapper.xml中写入相关sql语句。
OrderMapper.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.OrderMapper">
<!-- *************一对多查询 分布查询***************-->
<select id="findOrdersByUserId" resultType="com.houjinqiao.pojo.Order">
select *
from tb_orders
where user_id = #{uid};
</select>
</mapper>TUserMapper.xml
<!-- *************一对多查询 分布查询***************-->
<select id="findUserOrders" resultMap="finUserOrderMapper">
select *
from tb_user
where uid = #{uid};
</select>
<resultMap id="finUserOrderMapper" type="com.houjinqiao.pojo.TUser">
<id property="uid" column="uid"/>
<result property="uname" column="uname"/>
<result property="uage" column="uage"/>
<result property="usex" column="usex"/>
<collection property="orderList" column="uid" select="com.houjinqiao.mapper.OrderMapper.findOrdersByUserId"/>
</resultMap>（5）在核心配制文件mybatis-config.xml中，引入OrderMapper.xml映射文件。
<mapper resource="com/houjinqiao/mapper/OrderMapper.xml"/>(6) 测试类
TUser2.java
//一对多查询 分步查询
@Test
public void TUser3(){
SqlSession sqlSession = MyBatisUtils.getSession();
TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
TUser Orders = mapper.findUserOrders(2);
System.out.println(Orders);
sqlSession.commit();
sqlSession.close();
}测试结果：
[DEBUG] [main] c.h.m.T.findUserOrders - ==> Preparing: select * from tb_user where uid = ?;
[DEBUG] [main] c.h.m.T.findUserOrders - ==> Parameters: 2(Integer)
[DEBUG] [main] c.h.m.O.findOrdersByUserId - ====> Preparing: select * from tb_orders where user_id = ?;
[DEBUG] [main] c.h.m.O.findOrdersByUserId - ====> Parameters: 2(Integer)
[DEBUG] [main] c.h.m.O.findOrdersByUserId - <==== Total: 1
[DEBUG] [main] c.h.m.T.findUserOrders - <== Total: 1
TUser{uid=2, uname='李四', uage=18, usex='男', uidcard=null, orderList=[Order{or_id=3, order_num='202233333', user_id=2, productList=null}]}
2.嵌套结果方式
（1）写接口
TUserMapper.java
//*************一对多查询 同布查询***************
TUser findUserOrders2(int id);（2）写配置文件
TUserMapper.java
<!-- *************一对多查询 同布查询***************-->
<select id="findUserOrders2" resultMap="finUserOrderMapper2">
select u.*,o.*
from tb_user u,tb_orders o
where u.uid = #{id} and o.user_id = u.uid
</select>
<resultMap id="finUserOrderMapper2" type="com.houjinqiao.pojo.TUser">
<id property="uid" column="uid"/>
<result property="uname" column="uname"/>
<result property="uage" column="uage"/>
<result property="usex" column="usex"/>
<collection property="orderList" ofType="com.houjinqiao.pojo.Order">
<id property="or_id" column="or_id"/>
<result property="order_num" column="order_number"/>
</collection>
</resultMap>（3） 写测试类
TUser2.java
//一对多查询 同步查询
@Test
public void TUser4(){
SqlSession sqlSession = MyBatisUtils.getSession();
TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
TUser Orders = mapper.findUserOrders2(2);
System.out.println(Orders);
sqlSession.commit();
sqlSession.close();
}测试结果
[DEBUG] [main] c.h.m.T.findUserOrders2 - ==> Preparing: select u.*,o.* from tb_user u,tb_orders o where u.uid = ? and o.user_id = u.uid
[DEBUG] [main] c.h.m.T.findUserOrders2 - ==> Parameters: 2(Integer)
[DEBUG] [main] c.h.m.T.findUserOrders2 - <== Total: 1
TUser{uid=2, uname='李四', uage=18, usex='男', uidcard=null, orderList=[Order{or_id=3, order_num='null', user_id=0, productList=null}]}
2. 多对多查询
对于多对多查询，也分为嵌套查询和嵌套结果（分布查询和同步查询），接下来我们对这两种方式进行介绍：
1.嵌套查询（分布查询）
（1） 创建数据表
#创建一个商品表
CREATE TABLE tb_product (
pd_id INT(32) PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(32),
price DOUBLE
);
INSERT INTO tb_product(book_name,price) VALUES ('Java基础', '20');
INSERT INTO tb_product(book_name,price) VALUES ('前端技术', '30');
INSERT INTO tb_product(book_name,price) VALUES ('SSM框架', '4');
# 创建一个中间表
CREATE TABLE tb_ordersitem (
id INT(32) PRIMARY KEY AUTO_INCREMENT,
or_id INT(32),
pd_id INT(32),
FOREIGN KEY(or_id) REFERENCES tb_orders(or_id),
FOREIGN KEY(pd_id) REFERENCES tb_product(pd_id)
);
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('1', '1');
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('1', '3');
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('2', '2');
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('3', '1');
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('3', '2');
INSERT INTO tb_ordersitem(or_id,pd_id) VALUES ('3', '3');
（2）在项目的 com.houjinqiao.pojo包下创建Product持久化类用于封装商品信息。
Product.java
package com.houjinqiao.pojo;
import java.util.List;
public class Product {
int pd_id;
String book_name;
double price;
List<Order> orderList;
public int getPd_id() {
return pd_id;
}
public void setPd_id(int pd_id) {
this.pd_id = pd_id;
}
public String getBook_name() {
return book_name;
}
public void setBook_name(String book_name) {
this.book_name = book_name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "Product{" +
"pd_id=" + pd_id +
", book_name='" + book_name + '\'' +
", price=" + price +
", orderList=" + orderList +
'}';
}
}（3）写接口
在com.houjinqiao.mapper包下创建接口文件ProductMapper。
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.Product;
public interface ProductMapper {
//*************多对多查询 分布查询***************
Product findProductbyId(int pdId);
}（4）在com.houjinqiao.mapper文件夹中，创建映射文件ProductMapper.xml，并在映射文件中编写多对多关联映射查询的配制信息。同时在OrderMapper.xml中写入相关sql语句。
ProductMapper.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.ProductMapper">
<!-- *************多对多查询 分布查询***************-->
<select id="findProductbyId" resultType="com.houjinqiao.pojo.Product">
select *
from tb_product
where pd_id in (select pd_id from tb_ordersitem where or_id = #{or_id})
</select>
</mapper>OrderMapper.xml
<!-- *************多对多查询 分布查询***************-->
<select id="findOrderById" resultMap="FindOrderByIdMap">
select *
from tb_orders
where or_id = #{id};
</select>
<resultMap id="FindOrderByIdMap" type="com.houjinqiao.pojo.Order">
<id property="or_id" column="or_id"/>
<result property="order_num" column="order_num"/>
<collection property="productList" column="or_id" select="com.houjinqiao.mapper.ProductMapper.findProductbyId"/>
</resultMap>（5）在核心配制文件mybatis-config.xml中，引入ProductMapper.xml映射文件。
<mapper resource="com/houjinqiao/mapper/ProductMapper.xml"/>(6) 测试类
TUser2.java
//多对多查询 分步查询
@Test
public void TUser5(){
SqlSession sqlSession = MyBatisUtils.getSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order Orders = mapper.findOrderById(1);
System.out.println(Orders);
sqlSession.commit();
sqlSession.close();
}测试结果：
Preparing: select * from tb_orders where or_id = ?;
[DEBUG] [main] c.h.m.O.findOrderById - ==> Parameters: 1(Integer)
[DEBUG] [main] c.h.m.P.findProductbyId - ====> Preparing: select * from tb_product where pd_id in (select pd_id from tb_ordersitem where or_id = ?)
[DEBUG] [main] c.h.m.P.findProductbyId - ====> Parameters: 1(Integer)
[DEBUG] [main] c.h.m.P.findProductbyId - <==== Total: 2
[DEBUG] [main] c.h.m.O.findOrderById - <== Total: 1
Order{or_id=1, order_num='20211111', user_id=1, productList=[Product{pd_id=1, book_name='Java基础', price=20.0, orderList=null}, Product{pd_id=3, book_name='SSM框架', price=4.0, orderList=null}]}
2.嵌套结果方式
（1）写接口
OrderMapper.java
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.Order;
import java.util.List;
public interface OrderMapper{
//*************多对多查询 分布查询***************
Order findOrderById(int id);
}
（2）写配置文件
OrderMapper.xml
<!-- *************多对多查询 同布查询***************-->
<select id="findOrderById2" resultMap="findOrderById2Map">
select o.*,p.*
from tb_orders o,tb_product p,tb_ordersitem op
where o.or_id = #{id} and op.or_id = o.or_id and op.pd_id = p.pd_id
</select>
<resultMap id="findOrderById2Map" type="com.houjinqiao.pojo.Order">
<id property="or_id" column="or_id"/>
<result property="order_num" column="order_num"/>
<collection property="productList" ofType="com.houjinqiao.pojo.Product">
<id property="pd_id" column="pd_id"/>
<result property="book_name" column="book_name"/>
<result property="price" column="price"/>
</collection>
</resultMap>（3） 写测试类
TUser2.java
//多对多查询 同步查询
@Test
public void TUser6(){
SqlSession sqlSession = MyBatisUtils.getSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order Orders = mapper.findOrderById2(1);
System.out.println(Orders);
sqlSession.commit();
sqlSession.close();
}测试结果
Preparing: select o.*,p.* from tb_orders o,tb_product p,tb_ordersitem op where o.or_id = ? and op.or_id = o.or_id and op.pd_id = p.pd_id
[DEBUG] [main] c.h.m.O.findOrderById2 - ==> Parameters: 1(Integer)
[DEBUG] [main] c.h.m.O.findOrderById2 - <== Total: 2
Order{or_id=1, order_num='20211111', user_id=0, productList=[Product{pd_id=1, book_name='Java基础', price=20.0, orderList=null}, Product{pd_id=3, book_name='SSM框架', price=4.0, orderList=null}]}
1299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
