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

发布于 2022-4-28 17:31
浏览
0收藏

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

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

1

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();
    }
}

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

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

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

 

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

我们从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列。现在,我们来看看效果,

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

 

Spring Boot整合Mybatis的基础使用-复杂对象一对多映射-开源基础软件社区

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

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

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐