MyBatis中实现多层嵌套映射

书接上回，为了实现更复杂的需求，如多层嵌套映射，我们可以通过在 MyBatis 中嵌套多个 resultMap 来实现。例如，假设我们现在有三个表，它们之间存在多层嵌套关系：Category（类别）、Product（产品）和 Review（评论）。一个 Category 有多个 Product，每个 Product 又有多个 Review，这样就形成了多层嵌套的结构。

示例场景

假设有以下三个表：

`category` 表：

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

`product` 表：

CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES category(id)
);

`review` 表：

CREATE TABLE review (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(255),
    rating INT,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES product(id)
);

1. 实体类设计

`Category` 实体类

package com.example.entity;

import lombok.Data;
import java.util.List;

@Data
public class Category {
    private Integer id;
    private String name;
    private List<Product> products; // 一对多关系，产品列表
}

`Product` 实体类

package com.example.entity;

import lombok.Data;
import java.util.List;

@Data
public class Product {
    private Integer id;
    private String name;
    private Double price;
    private Integer categoryId;
    private List<Review> reviews; // 一对多关系，评论列表
}

`Review` 实体类

package com.example.entity;

import lombok.Data;

@Data
public class Review {
    private Integer id;
    private String content;
    private Integer rating;
    private Integer productId;
}

2. Mapper 接口设计

`CategoryMapper.java`

package com.example.mapper;

import com.example.entity.Category;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CategoryMapper {

    // 查询所有类别，包括其产品和产品的评论信息（多层嵌套）
    @Select("SELECT * FROM category")
    List<Category> findAllCategoriesWithProductsAndReviews();
}

3. MyBatis 配置文件

我们需要在 CategoryMapper.xml 中定义嵌套的 resultMap 来处理多层嵌套的关系。

`CategoryMapper.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.example.mapper.CategoryMapper">

    <!-- 定义嵌套映射：Category 包含 Product，Product 包含 Review -->
    <resultMap id="CategoryProductReviewMap" type="com.example.entity.Category">
        <!-- Category 映射 -->
        <id column="category_id" property="id"/>
        <result column="category_name" property="name"/>

        <!-- 嵌套映射 Product -->
        <collection property="products" ofType="com.example.entity.Product">
            <id column="product_id" property="id"/>
            <result column="product_name" property="name"/>
            <result column="price" property="price"/>
            <result column="category_id" property="categoryId"/>

            <!-- 嵌套映射 Review -->
            <collection property="reviews" ofType="com.example.entity.Review">
                <id column="review_id" property="id"/>
                <result column="content" property="content"/>
                <result column="rating" property="rating"/>
                <result column="product_id" property="productId"/>
            </collection>
        </collection>
    </resultMap>

    <!-- 查询所有类别及其产品及评论 -->
    <select id="findAllCategoriesWithProductsAndReviews" resultMap="CategoryProductReviewMap">
        SELECT 
            c.id AS category_id, 
            c.name AS category_name, 
            p.id AS product_id, 
            p.name AS product_name, 
            p.price AS product_price, 
            p.category_id AS product_category_id,
            r.id AS review_id, 
            r.content AS review_content, 
            r.rating AS review_rating, 
            r.product_id AS review_product_id
        FROM 
            category c
        LEFT JOIN 
            product p 
        ON 
            c.id = p.category_id
        LEFT JOIN 
            review r 
        ON 
            p.id = r.product_id;
    </select>
</mapper>

4. Service 层与 Controller 实现

`CategoryService.java`

package com.example.service;

import com.example.entity.Category;
import com.example.mapper.CategoryMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CategoryService {

    @Autowired
    private CategoryMapper categoryMapper;

    public List<Category> findAllCategoriesWithProductsAndReviews() {
        return categoryMapper.findAllCategoriesWithProductsAndReviews();
    }
}

`CategoryController.java`

package com.example.controller;

import com.example.entity.Category;
import com.example.service.CategoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class CategoryController {

    @Autowired
    private CategoryService categoryService;

    @GetMapping("/categories")
    public List<Category> getCategoriesWithProductsAndReviews() {
        return categoryService.findAllCategoriesWithProductsAndReviews();
    }
}

5. 测试

启动应用后，访问 http://localhost:8080/categories，你将会得到包含 Category、Product 和 Review 的完整多层嵌套的 JSON 结果。例如，返回的数据格式如下：

[
  {
    "id": 1,
    "name": "Electronics",
    "products": [
      {
        "id": 1,
        "name": "Laptop",
        "price": 1000.00,
        "categoryId": 1,
        "reviews": [
          {
            "id": 1,
            "content": "Great laptop!",
            "rating": 5,
            "productId": 1
          },
          {
            "id": 2,
            "content": "Not bad.",
            "rating": 4,
            "productId": 1
          }
        ]
      }
    ]
  }
]

总结

通过这个复杂的多层嵌套映射示例，可以看到如何在 MyBatis 中实现一个 Category -> Product -> Review 的多对多层级关系。我们使用了 resultMap 和 collection 来处理每个层级的映射，确保每个实体类的关系能够被准确解析。通过这种方式，MyBatis 可以灵活地处理多层嵌套的查询和数据映射。

MyBatis中实现多层嵌套映射

示例场景

category 表：

product 表：

review 表：

1. 实体类设计

Category 实体类

Product 实体类

Review 实体类