文章目录

• • 1 环境准备
  • • 1.1 新建数据库表
    • 1.2 新建实体类
  • 2 一对一查询
  • • 2.1 分步查询
    • 2.2 同步查询
    • 2.3 测试方法
  • 3 一对多查询
  • • 3.1 分步查询
    • 3.2 同步查询
    • 3.3 测试方法
  • 4 多对多查询
  • • 4.1 分步查询
    • 4.2 同步查询
    • 4.3 测试方法
• mybatis基础教程【5小时36讲全套】

多个表的联合查询操作

案例：联合查询用户表，身份编码表，订单表，商品表

其中：

• 用户与身份编号表，一对一关系，一个用户对应一个编码，一个编码对应唯一一个用户。
• 用户与订单，一对多关系，一个用户对应多个订单，一个订单对应一个用户。
• 订单表与商品表，多对多关系，一个订单对应多个商品，一个商品可以对应多个订单。
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
  

1 环境准备

1.1 新建数据库表

use mybatis;
# 创建一个编码表
CREATE TABLE  tb_idcard( id INT PRIMARY KEY AUTO_INCREMENT,CODE VARCHAR(18)
);
INSERT INTO tb_idcard(CODE) VALUES('111111111111111');
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);# 创建一个订单表表
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);#创建一个商品表
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');

1.2 新建实体类

1 用户类

public class TUser {int uid;String uname;int uage;String usex;Idcard uidcard;List<Order> orderList;
}

2编码类

public class Idcard {private Integer id;private String code;
}

3 订单类

public class Order {int or_id;String order_num ;int user_id;List<Product> productList;
}

4 商品类

public class Product {int  pd_id;String book_name;double price;List<Order> orderList;
}

2 一对一查询

根据用户ID，查询用户信息，包括用户编号，需要用到tb_user与tb_idcard两张表

2.1 分步查询

嵌套查询的方式

1. 接口文件

public interface IdCardMapper {Idcard findIdcardById(int id);
}

public interface TUserMapper {TUser findTUserById(int id);
}

2. 映射文件

DOCTYPE mapperPUBLIC "-//mybatis.org//DTD mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yiwu.mapper.TUserMapper"><select id="findTUserById" resultMap="findIdCardByIDMapper">select * from tb_user where uid = #{uid}select><resultMap id="findIdCardByIDMapper" type="com.yiwu.pojo.TUser"><association property="uidcard" column="ucard_id" select="com.yiwu.mapper.IdCardMapper.findIdcardById"/>resultMap>
mapper>

<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yiwu.mapper.IdCardMapper">
<select id="findIdcardById" resultType="com.yiwu.pojo.Idcard">select * from tb_idcard where id = #{id}
</select>
</mapper>

2.2 同步查询

嵌套结果查询

1 接口文件

public interface TUserMapper {TUser findTUserById2(int id);
}

2 映射文件

<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.yiwu.pojo.TUser"><id property="uid" column="uid"/><result property="uname" column="uname"/><result property="uage" column="uage"/><result property="usex" column="usex"/><association property="uidcard" javaType="com.yiwu.pojo.Idcard"><id property="id" column="cardID"/><result property="code" column="ucard"/>association>
resultMap>

2.3 测试方法

@Test
public void test01(){TUser tUser = mapper.findTUserById2(1);System.out.println(tUser);
}

3 一对多查询

• 使用用户表（tb_user）与订单表（tb_orders），通过用户id查询该用户的信息，及其关联的订单信息

• 一个用户包含0条或多条订单信息。

3.1 分步查询

嵌套结果查询的方式

1. 接口文件

OrderMapper.java

package com.yiwu.mapper;
import com.yiwu.pojo.Order;
import java.util.List;
public interface OrderMapper {List<Order> findOrdersByUserId(int id);
}

TUserMapper.java

public interface TUserMapper {TUser findUserOrders(int id);
}

2 映射文件

orderMapper.xml

DOCTYPE mapperPUBLIC "-//mybatis.org//DTD mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yiwu.mapper.OrderMapper"><select id="findOrdersByUserId" resultType="com.yiwu.pojo.Order">select *from tb_orderswhere user_id = #{uid};select>
mapper>

TUserMapper.java

<select id="findUserOrders" resultMap="finUserOrderMapper">select *from tb_userwhere uid = #{uid};
select><resultMap id="finUserOrderMapper" type="com.yiwu.pojo.TUser"><id property="uid" column="uid"/><result property="uname" column="uanme"/><result property="uage" column="uage"/><result property="usex" column="usex"/><collection property="orderList" column="uid" select="com.yiwu.mapper.OrderMapper.findOrdersByUserId"/>
resultMap>

3.2 同步查询

嵌套结果查询

1. 接口文件

TUserMapper.java

package com.yiwu.mapper;import com.yiwu.pojo.TUser;
public interface TUserMapper {TUser findUserOrders(int id);TUser findUserOrders1(int id);
}

2 映射文件

TUserMapper.xml

<select id="findUserOrders1" resultMap="finUserOrderMapper2">select u.*,o.*from tb_user u,tb_orders owhere u.uid = #{id} and o.user_id = u.uid
select><resultMap id="finUserOrderMapper2" type="com.yiwu.pojo.TUser"><id property="uid" column="uid"/><result property="uname" column="uanme"/><result property="uage" column="uage"/><result property="usex" column="usex"/><collection property="orderList" ofType="com.yiwu.pojo.Order"><id property="or_id" column="or_id"/><result property="order_num" column="order_number"/>collection>
resultMap>

3.3 测试方法

@Test
public void test02(){//TUser tUser = mapper.findUserOrders1(1);TUser tUser = mapper.findUserOrders(1);System.out.println(tUser);
}

4 多对多查询

使用订单表（tb_orders）与商品表（tb_product）借助中间表（tb_ordersitem）

实现多对多查询，根据产品订单，查询该订单所包含的商品。

4.1 分步查询

嵌套结果查询的方式

1. 接口文件

ProductMapper.java

public interface ProductMapper {Product findProductbyId(int pdId);
}

OrderMapper.java

public interface OrderMapper {List<Order> findOrdersByUserId(int id);Order findOrderById(int id);
}

2 映射文件

ProductMapper.xml

DOCTYPE mapperPUBLIC "-//mybatis.org//DTD mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yiwu.mapper.ProductMapper"><select id="findProductbyId" resultType="com.yiwu.pojo.Product">select *from tb_productwhere 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_orderswhere or_id = #{id};
select>
<resultMap id="FindOrderByIdMap" type="com.yiwu.pojo.Order"><id property="or_id" column="or_id"/><result property="order_num" column="order_num"/><collection property="productList" column="or_id" select="com.yiwu.mapper.ProductMapper.findProductbyId"/>
resultMap>

4.2 同步查询

嵌套结果查询

1 接口文件

package com.yiwu.mapper;
import com.yiwu.pojo.Order;
import java.util.List;public interface OrderMapper {//List findOrdersByUserId(int id);// Order findOrderById(int id);Order findOrderById2(int id);
}

2 映射文件

<select id="findOrderById2" resultMap="findOrderById2Map">select o.*,p.*from tb_orders o,tb_product p,tb_ordersitem  opwhere o.or_id = #{id} and op.or_id = o.or_id and op.pd_id = p.pd_id
select><resultMap id="findOrderById2Map" type="com.yiwu.pojo.Order"><id property="or_id" column="or_id"/><result property="order_num" column="order_num"/><collection property="productList" ofType="com.yiwu.pojo.Product"><id property="pd_id" column="pd_id"/><result property="book_name" column="book_name"/><result property="price" column="price"/>collection>
resultMap>

4.3 测试方法

public void test03(){SqlSession sqlSession = MyBatisUtils.geSqlSession();OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);//Order order = mapper.findOrderById2(2);Order order = mapper.findOrderById(2);System.out.println(order);
}

mybatis基础教程【5小时36讲全套】

重录版-推荐（36讲，无法分享组，点击前往bilibili查看全集）

19关联映射测试表的介绍

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！