mybatis之一对多映射查询(十一)

旧城等待， 2022-06-10 13:26 211阅读 0赞

需求:查询订单信息，关联查询订单明细信息及用户信息

分析需求  
查询订单信息,关联查询用户信息,所以我们可以发现主信息为orders,从信息为user,orderdetail

sql语句

SELECT u.id uid, o.*, od.orders_id, od.items_id FROM orders o LEFT JOIN USER u ON u.id = o.user_id LEFT JOIN orderdetail od ON o.id = od.orders_id LEFT JOIN items i ON i.id = od.items_id

修改扩展类  
添加订单明细集合属性

package com.test.domain;
    
    import java.util.List;
    
    public class OrdersExt extends Orders { 
    
        private User user;
    
        private List<Orderdetail> orderdetailList;
    
        public List<Orderdetail> getOrderdetailList() {
            return orderdetailList;
        }
        public void setOrderdetailList(List<Orderdetail> orderdetailList) {
            this.orderdetailList = orderdetailList;
        }
        public User getUser() {
            return user;
        }
        public void setUser(User user) {
            this.user = user;
        }
    
    }

映射文件  
extends属性为继承,值为resultMap 的id,在mybatis映射文件中也可以和java类一样可以继承

<?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.test.mapper.OrdersMapper">
    
        <resultMap type="com.test.domain.OrdersExt" id="ordersAndUserResultMap">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
    
            <association property="user" javaType="com.test.domain.User">
                <id column="user_id" property="id"/>
                <result column="username" property="username"/>
                <result column="sex" property="sex"/>
                <result column="address" property="address"/>
            </association>
        </resultMap>
    
        <resultMap type="com.test.domain.OrdersExt" id="ordersAndUserDetialResultMap" extends="ordersAndUserResultMap">
            <collection property="orderdetailList" ofType="com.test.domain.Orderdetail">
                <id property="id" column="od_id"/>
                <result property="itemsId" column="items_id"/>
                <result property="ordersId" column="orders_id"/>
                <result property="itemsNum" column="items_num"/>
            </collection>
        </resultMap>
        <select id="findOrdersAndUserAndOrderdetail" resultMap="ordersAndUserDetialResultMap">
            SELECT u.username, 
                   u.sex ,
                   u.address,
                   o.*, 
                   od.id od_id,
                   od.orders_id, 
                   od.items_id, 
                   od.items_num
            FROM orders o
              LEFT JOIN USER u
                ON u.id = o.user_id
              LEFT JOIN orderdetail od
                ON o.id = od.orders_id
        </select>
    </mapper>

OrdersMapper接口

package com.test.mapper;
    
    import java.util.List;
    
    import com.test.domain.OrdersExt;
    
    public interface OrdersMapper {
        List<OrdersExt> findOrdersAndUserAndOrderdetail();
    }

测试代码

@Test
        public void test_02(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
    
            OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    
            List<OrdersExt> ordersExtList = mapper.findOrdersAndUserAndOrderdetail();
    
            System.out.println(ordersExtList);
        }