Spring-Boot整合Mybatis的基础使用-复杂对象一对多映射

在我们日常开发中，经常会遇到查询一对多的业务场景，比如：订单，用户权限，用户菜单，文章评论，以前没有用到ORM的时候，都是批量取出再重新匹配或者循环取出，这在代码里面实现起来还是有些繁琐。在现今Mybatis、Hibernate等ORM框架里，都给我们提供方便的一对多的映射功能，使得我们的开发效率得到了提高。今天，我们就来演示一下订单一对多的关系映射。

我们先创建两张表，分别为td_order(订单表)和td_order_line(订单行表):

CREATE TABLE `mybatis`.`td_order`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(30) NULL,
  `amount` decimal(10, 2) NULL,
  `create_time` datetime NULL DEFAULT now(),
  PRIMARY KEY (`id`)
);

CREATE TABLE `mybatis`.`td_order_line`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NULL,
  `product_id` int NULL,
  `product_name` varchar(100) NULL,
  `order_number` int NULL,
  `price` decimal(10, 2) NULL,
  PRIMARY KEY (`id`)
);

INSERT td_order (order_no,amount) values ('202108310001','100');
INSERT td_order (order_no,amount) values ('202108310002','102');
INSERT td_order (order_no,amount) values ('202108310003','106');


insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(1,1,'商品1',2,35);
insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(1,2,'商品2',2,15);
insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(2,3,'商品3',3,15);
insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(2,4,'商品4',2,28.5);
insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(3,5,'商品5',4,15);
insert td_order_line(order_id,product_id,product_name,order_number,price) VALUES(3,6,'商品6',2,23);

该表结构仅为演示所用，并不规范。

接下来继续新建两个类:

@Getter
@Setter
public class Order {
    private Integer id;
    private String orderNo;
    private BigDecimal amount;
    private Date createTime;
    private List<OrderLine> lines;
}

@Getter
@Setter
public class OrderLine {
    private Integer id;
    private Integer productId;
    private String productName;
    private Integer orderNumber;
    private BigDecimal price;
}

然后新建一个OrderMapper接口，接下来就该创建一对多的映射关系了，这里主要介绍两种方式，第一种主映射和子映射在一个resultMap中：

<resultMap id="OrderResult" type="com.mybatis.demo.dto.Order">
        <result property="id" column="id"></result>
        <result property="orderNo" column="order_no"></result>
        <result property="amount" column="amount"></result>
        <result property="createTime" column="create_time"></result>
        <collection property="lines" javaType="java.util.List" ofType="com.mybatis.demo.dto.OrderLine">
            <result column="sub_id" property="id"></result>
            <result column="product_id" property="productId"></result>
            <result column="product_name" property="productName"></result>
            <result column="order_number" property="orderNumber"></result>
            <result column="price" property="price"></result>

        </collection>
    </resultMap>

还有另外一种就是主映射和子映射分为两个resultMap:

<resultMap id="OrderResult" type="com.mybatis.demo.dto.Order">
    <result property="id" column="id"></result>
    <result property="orderNo" column="order_no"></result>
    <result property="amount" column="amount"></result>
    <result property="createTime" column="create_time"></result>
    <collection property="lines" javaType="java.util.List" resultMap="OrderLineResult"></collection>
</resultMap>

<resultMap id="OrderLineResult" type="com.mybatis.demo.dto.OrderLine">
    <result column="sub_id" property="id"></result>
    <result column="product_id" property="productId"></result>
    <result column="product_name" property="productName"></result>
    <result column="order_number" property="orderNumber"></result>
    <result column="price" property="price"></result>
</resultMap>

现在，我们开始编写SQL来把数据查询出来

<select id="queryOrder" resultMap="OrderResult">
       select o.id,o.amount,o.create_time,o.order_no,l.id sub_id,l.order_number,l.price,l.product_id,l.product_name from td_order o left join td_order_line l on o.id=l.order_id
   </select>

为了方便后面做分页测试，我们就新增一个Controller来看看调用效果:

@RestController
@RequestMapping(value = "order")
public class OrderController {

    @Autowired
    OrderMapper orderMapper;

    @GetMapping
    public List<Order> orderList(){
        return orderMapper.queryOrder();
    }
}

我从上面打印的查询结果可以看出，Mybatis把我们LEFT JOIN 查询出来的数据进行了聚合分组,但是这样会有个弊端，就是在分页时计算出总记录数会有问题，什么问题，其实就是LEFT JOIN 关联出来的总记录数，但是其实我们只是想要主表的总记录数。我们来看看问题的现象吧

我们从SQL可以看得出，它是直接基于我们SQL来COUNT的，如果主表和从表是一对一的关系则不会出现这种问题，那么遇到一对多的分页该怎么办呢？接下来就是需要换种方式，采用主子表分别查询。直接看xml文件吧

<mapper namespace="com.mybatis.demo.mapper.OrderMapper">
    <resultMap id="OrderResult" type="com.mybatis.demo.dto.Order">
        <result property="id" column="id"></result>
        <result property="orderNo" column="order_no"></result>
        <result property="amount" column="amount"></result>
        <result property="createTime" column="create_time"></result>
        <collection property="lines" javaType="java.util.List" select="queryOrderLine" column="id"></collection>
    </resultMap>

    <resultMap id="OrderLineResult" type="com.mybatis.demo.dto.OrderLine">
        <result column="sub_id" property="id"></result>
        <result column="product_id" property="productId"></result>
        <result column="product_name" property="productName"></result>
        <result column="order_number" property="orderNumber"></result>
        <result column="price" property="price"></result>
    </resultMap>

    <select id="queryOrder" resultMap="OrderResult">
        select * from td_order
    </select>
    <select id="queryOrderLine" resultMap="OrderLineResult">
        select * from td_order_line where order_id=#{id}
    </select>

</mapper>

这里需要注意的是，collection和子查询的sql中只能存在一个resultMap，否则会报错

Caused by: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'file [G:\mybatis\demo\target\classes\mapper\OrderMapper.xml]'. Cause: java.lang.IllegalStateException: Cannot define both nestedQueryId and nestedResultMapId in property lines
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:123) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.parse(XMLMapperBuilder.java:95) ~[mybatis-3.5.7.jar:3.5.7]
	at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:611) ~[mybatis-spring-2.0.6.jar:2.0.6]
	... 58 common frames omitted
Caused by: java.lang.IllegalStateException: Cannot define both nestedQueryId and nestedResultMapId in property lines
	at org.apache.ibatis.mapping.ResultMapping$Builder.validate(ResultMapping.java:147) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.mapping.ResultMapping$Builder.build(ResultMapping.java:140) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.MapperBuilderAssistant.buildResultMapping(MapperBuilderAssistant.java:446) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.buildResultMappingFromContext(XMLMapperBuilder.java:393) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:280) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:254) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElements(XMLMapperBuilder.java:246) ~[mybatis-3.5.7.jar:3.5.7]
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:119) ~[mybatis-3.5.7.jar:3.5.7]

collection里需要指定column来传递到子查询中，这里的id指的是主表查询结果中的id列。现在，我们来看看效果，


可以从上面的SQL看到，它是先查询主表的总记录数，取出主表当前分页的记录，再循环查询子表。

collection里指定column除了上面的单个字段，还可以指定多个字段如column="{id=id,year=year}",指定的列表在主表查询出来的结果必须存在。