【Mybatis】级联查询之多对多级联查询

引言

在Mybatis中，多对多的级联查询通常会转成两个一对多级联查询来实现
应用场景举例：用户与商品之间的关系为多对多， 一个订单可以有多种商品，使用一个中间表（订单记录表）就可以将多对多级联转换成两个一对多的关系

创建工程和数据库表

创建Spring整合Mybatis工程 【Spring】Spring整合Mybatis案例

创建orders表、product表和 order_product表

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderinfo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `price` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `order_product`;
CREATE TABLE `order_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

项目结构如下：

创建实体类和数据库操作接口

Order 类

public class Order {
    private int id;
    private String orderinfo;
    // 多对多中的另一个一对多
    private List<Product> products;
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
    public String getOrderinfo() {
        return orderinfo;
    }

    public void setOrderinfo(String orderinfo) {
        this.orderinfo = orderinfo;
    }

    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderinfo='" + orderinfo + '\'' +
                ", products=" + products +
                '}';
    }
}

Product 类

public class Product {
    private int id;
    private String name;
    private double price;
    // 多对多中的一个一对多
    private List<Order> orders;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", orders=" + orders +
                '}';
    }
}

OrderMapper 类

@Repository("orderMapper")
@Mapper
public interface OrderMapper {
    List<Order> findAllOrdersAndProduct();
}

ProductMapper 类

@Repository("productMapper")
@Mapper
public interface ProductMapper {
    List<Product> findAllProductAndOrders();
}

创建配置文件

mybatis-config.xml

<?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>
    <settings>
        <!--在使用MyBatis嵌套查询方式进行关联查询时，使用MyBatis的延迟加载可以在一定程度上提高查询效率-->
        <!--打开延迟加载的开关-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--将积极加载改为按需加载-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <mappers>
        <mapper resource="ProductMapper.xml"/>
        <mapper resource="OrderMapper.xml"/>
    </mappers>

</configuration>

OrderMapper.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" >
<mapper namespace="com.lucas.mybatis.mapper.OrderMapper">

    <resultMap id="orderAndProducts" type="com.lucas.mybatis.model.Order">
        <id property="id" column="id"></id>
        <result property="orderinfo" column="orderinfo"></result>
        <collection property="products" ofType="com.lucas.mybatis.model.Product"  >
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="price" column="price"></result>
        </collection>
    </resultMap>
    <select id="findAllOrdersAndProduct"   resultMap="orderAndProducts">
      SELECT orders.*,product.id,product.`name`,product.price  FROM orders,product,order_product WHERE orders.id = order_product.order_id AND order_product.product_id = product.id;
    </select>
</mapper>

ProductMapper.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" >
<mapper namespace="com.lucas.mybatis.mapper.ProductMapper">

    <resultMap id="productAndOrders" type="com.lucas.mybatis.model.Product">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="price" column="price"></result>
        <collection property="orders" ofType="com.lucas.mybatis.model.Order"  >
            <id property="id" column="id"></id>
            <result property="orderinfo" column="orderinfo"></result>
        </collection>
    </resultMap>
    <select id="findAllProductAndOrders"   resultMap="productAndOrders">
     SELECT product.*,orders.id,orders.orderinfo FROM orders,product,order_product WHERE orders.id = order_product.order_id AND order_product.product_id = product.id;
    </select>
</mapper>

添加测试代码

public class App {
    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        OrderMapper orderMapper = (OrderMapper) applicationContext.getBean("orderMapper");
        System.out.println("----订单列表-----");
        List<Order> orders = orderMapper.findAllOrdersAndProduct();
        for (Order order : orders) {
            System.out.println(order);
        }
        System.out.println("----商品列表列表-----");
        ProductMapper productMapper = (ProductMapper) applicationContext.getBean("productMapper");
        List<Product> products = productMapper.findAllProductAndOrders();
        for (Product product : products) {
            System.out.println(product);
        }
    }
}

运行结果如下：