四、mybatis关联映射（多表联合查询）

老去的90后

已于 2023-04-04 09:33:36 修改

阅读量2.6k

点赞数 1

分类专栏： mybatis系列教程文章标签： mybatis 数据库 java

于 2022-11-23 15:42:31 首次发布

本文链接：https://blog.csdn.net/yuehaoyy/article/details/128001571

版权

mybatis系列教程专栏收录该内容

7 篇文章 5 订阅

订阅专栏

文章目录

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 mapper
        PUBLIC "-//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 mapper
        PUBLIC "-//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 mapper
        PUBLIC "-//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_orders
        where user_id = #{uid};
    </select>
</mapper>

TUserMapper.java

<select id="findUserOrders" resultMap="finUserOrderMapper">
        select *
        from tb_user
        where 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 o
    where 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 mapper
        PUBLIC "-//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_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.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<Order> 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  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.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);
}