mybatis : 一对多关联查询

系统管理员 2024-04-08 09:09 13阅读 0赞

### 业务背景 ###

根据客户id查询客户基本信息，以及客户存在的订单信息

### 两张数据表 ###

*  客户表

![0e134a79992473c809994bd8c65f62fa.png][]

*  订单表

![793414cdb8cb0e511ee659b713a5e014.png][]

### 实体类 ###

*  客户实体类：Customer

private Integer id;
        private String name;
        private Integer age;
    
        //封装存在的订单信息
        List<Order> orders = new ArrayList<>();

*  订单实体类：Order

private Integer id;
       private String orderNumber;
       private Double orderPrice;

### CustomerMapper.java接口 ###

//根据客户id查询客户基本信息，以及客户存在的订单信息
        Customer getCustomerById(Integer id);

### CustomerMapper.xml映射文件 ###

<resultMap id="customerMap" type="customer">
            
            <id property="id" column="cid"/>
            
            <result property="name" column="name"/>
            <result property="age" column="age"/>
    
            
            <collection property="orders" ofType="order">
                
                <id property="id" column="oid"/>
                <result property="orderNumber" column="orderNumber"/>
                <result property="orderPrice" column="orderPrice" />
            </collection>
        </resultMap>
    
    
        
        <select id="getCustomerById" parameterType="int" resultMap="customerMap">
                select
                    c.id cid, name, age, o.id oid, orderNumber, orderPrice, customer_id
                from
                    customers c
                left join
                    orders o
                on
                    c.id=o.customer_id
                where
                    c.id=#{id}
        </select>

### 测试 ###

//SqlSession对象
        SqlSession sqlSession;
    
        //获取CustomerMapper的mybatis动态代理对象
        CustomerMapper customerMapper;
    
        //获取SqlSession
        @Before
        public void getSqlSession() throws IOException {
            //读取核心配置文件
            InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //创建SqlSessionFactory对象
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            //获取SqlSession
            sqlSession = factory.openSession();
            //获取各Mapper接口的mybatis动态代理对象
            customerMapper = sqlSession.getMapper(CustomerMapper.class);
        }
    
        //归还SqlSession
        @After
        public void closeSession(){
            sqlSession.close();
        }
    
    	//测试查询标签
        @Test
        public void testGetCustomerById(){
            Customer customer = customerMapper.getCustomerById(1);
            System.out.println(customer);
        }

### 结果 ###

==>  Preparing:
    
    select 
        c.id cid, name, age, o.id oid, orderNumber, orderPrice, customer_id 
    from
        customers c 
    left join 
        orders o 
    on 
        c.id=o.customer_id 
    where 
        c.id=?
        
    ==> Parameters: 1(Integer)
        
    <==    Columns: cid, name, age, oid, orderNumber, orderPrice, customer_id
    <==        Row: 1, 荷包蛋, 22, 11, 20, 22.22, 1
    <==        Row: 1, 荷包蛋, 22, 12, 60, 16.66, 1
    <==      Total: 2
        
    Customer{id=1, 
             name='荷包蛋', 
             age=22, 
             orders=[
             Order{id=11, orderNumber='20', orderPrice=22.22}, 
             Order{id=12, orderNumber='60', orderPrice=16.66}
             ]
            }

### 结果分析 ###

*  sql语句的查询结果

<==    Columns: cid, name, age, oid, orderNumber, orderPrice, customer_id
    <==        Row: 1, 荷包蛋, 22, 11, 20, 22.22, 1
    <==        Row: 1, 荷包蛋, 22, 12, 60, 16.66, 1
    <==      Total: 2

*  实际注入到实体类中的数据

Customer{id=1, 
             name='荷包蛋', 
             age=22, 
             orders=[
             Order{id=11, orderNumber='20', orderPrice=22.22}, 
             Order{id=12, orderNumber='60', orderPrice=16.66}
             ]
            }

*  mybatis框架对查询结果会自动去重，按照查询结果的映射规则，完成数据向实体类的注入操作
    
     *  将"1, 荷包蛋, 22 "分别注入到实体类Customer的前三个简单属性中，只注入一组
     *  将关联查询到的两条订单数据分别注入到Order实体类中的对应属性中
        
         *  并将Order对象封装到集合中
     *  最后将Customer的三个属性值和orders集合封装成一个Customer对象返回
     *  由于在数据映射标签中没有指明对customer\_id的映射规则，所以在查询时会显示该字段数据，但是并没有被注入到实体类中

### 注意 ###

在一对多关联查询时，注意根据实际业务需求选择合适的连接查询语句，在本例中选择：左外连接

如果选择内连接，当用户未下订单时，查询不到用户信息

*  外连接查询结果：无订单信息，且用户信息可以正常显示

*  内连接查询结果：无订单信息，则用户信息也无法正常显示

==>  Preparing: select c.id cid, name, age, o.id oid, orderNumber, orderPrice, customer_id from customers c join orders o on c.id=o.customer_id where c.id=?
    ==> Parameters: 3(Integer)
    <==      Total: 0
    null

[0e134a79992473c809994bd8c65f62fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/92be845189c046ddb0e7f314c19c7221.png
[793414cdb8cb0e511ee659b713a5e014.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/10b2d26303f84d0ab9eff76a4d952cb6.png