Mybatis plus 不通过XML、使用注解@Select，@Results实现一对多关联，多对多关联、对象里有List。甚至是三表join

一、表结构

二、要实现的sql语句

1.查产品类别并附带上该类别下的所有产品id和name

select pc.*,p.product_id,p.product_name from product_category pc
left join product p on p.category_id = pc.category_id

2.查单个产品类别时附带所有产品并附带每个产品下的图片

select * from product_category 
left join product p 
on p.category_id = category_id
left join product_img pg 
on p.product_id = pg.product_id 

三、pojo类

1.ProductCategory

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class ProductCategory implements Serializable {
  @TableId(type = IdType.AUTO)
  private Long categoryId;
  private String categoryName;
  private String categoryDesc;
  @TableField(exist = false)//一定要加
  private List<Product> productList;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtCreate;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtModified;
}

2.Product

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product implements Serializable {
  @TableId(type = IdType.AUTO)
  private Long productId;
  private String productName;
  private String productSummary;
  private String productDesc;
  private Long categoryId;
  @TableField(exist = false)
  private List<ProductImg> productImgList;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtCreate;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtModified;
}

3.ProductImg

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class ProductImg implements Serializable {
  @TableId(type = IdType.AUTO)
  private Long imgId;
  private String imgUrl;
  private String imgTitle;
  private Long productId;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtCreate;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private String gmtModified;
}

四、dao层

1.ProductCategoryMapper

@Component
public interface ProductCategoryMapper extends BaseMapper<ProductCategory> {
    @Select({"select * from product_category where category_id = #{categoryId}"})
    @Results(id = "categoryMap", value = {
    		//只需要主键和外键，其他字段自动映射
            @Result(column = "category_id", property = "categoryId", id = true),
            @Result(column = "category_id", property = "productList", javaType = ArrayList.class,
            		//全类名.方法名
                    many = @Many(select = "com.dao.ProductMapper.selectByCategoryId")
            )
    })
    ProductCategory SelectById(Long categoryId);

    /**
     *
     * @return 查全部就只要product的id与name
     */
    @Select("select * from product_category")
    @Results(id = "categoryMap2", value = {
            @Result(column = "category_id", property = "categoryId", id = true),
            @Result(column = "category_id", property = "productList", javaType = ArrayList.class,
                    many = @Many(select = "com.dao.ProductMapper.selectByCategoryId2")
            )
    })
    List<ProductCategory> SelectAllCategory();
}

2.ProductMapper

/**
 * @author yhf
 * @since 2022-06-28 8:49
 */
@Component
public interface ProductMapper extends BaseMapper<Product> {
   @Select("select * from product where category_id = #{categoryId}")
   @Results(id = "productMap", value = {
           @Result(column = "product_id", property = "productId", id = true),
           @Result(column = "product_id", property = "productImgList", javaType = ArrayList.class,
                   many = @Many(select = "com.dao.ProductImgMapper.selectByProductId")
           )
   })
   List<Product> selectByCategoryId(Long categoryId);

   @Select("select product_id,product_name from product where category_id = #{categoryId}")
   List<Product> selectByCategoryId2(Long categoryId);
}

3.ProductMapper

/**
 * @author yhf
 * @since 2022-06-28 8:50
 */
@Component
public interface ProductImgMapper extends BaseMapper<ProductImg> {
    @Select("select * from product_img where product_id = #{productId}")
    List<ProductImg> selectByProductId(Long productId);
}

五、测试

查所有：

{
  "success": true,
  "code": 200,
  "message": "成功",
  "data": {
    "categoryList": [
      {
        "categoryId": 1,
        "categoryName": "熔炼与浇注数据采集管理系统",
        "categoryDesc": "",
        "productList": [
          {
            "productId": 1,
            "productName": "配料单发布系统",
            "productSummary": null,
            "productDesc": null,
            "categoryId": null,
            "productImgList": null,
            "gmtCreate": null,
            "gmtModified": null
          },
          ......
        ],
        "gmtCreate": "2022-06-22 16:03:41.0",
        "gmtModified": "2022-06-22 16:03:41.0"
      },
      ......
    ]
  }
}

查单个：

{
  "success": true,
  "code": 200,
  "message": "成功",
  "data": {
    "productCategory": {
      "categoryId": 1,
      "categoryName": "熔炼与浇注数据采集管理系统",
      "categoryDesc": "",
      "productList": [
        {
          "productId": 1,
          "productName": "配料单发布系统",
          "productSummary": "test",
          "productDesc": "<p style=\"font-size:50px;color:red;font-weight:bold;\">富文本测试</p>",
          "categoryId": 1,
          "productImgList": [
            {
              "imgId": 24,
              "imgUrl": "/imgs/product/配料单发布.png",
              "imgTitle": "配料单发布",
              "productId": 1,
              "gmtCreate": "2022-06-23 07:46:21.0",
              "gmtModified": "2022-06-23 07:46:21.0"
            }
          ],
          "gmtCreate": "2022-06-23 07:44:40.0",
          "gmtModified": "2022-06-23 07:44:40.0"
        },
        ......
      ],
      "gmtCreate": "2022-06-22 16:03:41.0",
      "gmtModified": "2022-06-22 16:03:41.0"
    }
  }
}