mybatis : 多对一关联查询

分手后的思念是犯贱 2024-04-08 09:11 11阅读 0赞

### 业务背景 ###

根据订单id查询订单的信息，以及该订单所属的客户的基本信息(不包括该客户自己的订单信息)

### 两张数据表 ###

*  客户表

![549538642c566da23b7bcc06062c1fff.png][]

*  订单表

![a03ca1c437479f24667a09d962123ab6.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;
    
       //封装订单所属的客户的基本信息
       Customer customer = new Customer();

### CustomerMapper.java接口 ###

//根据订单id查询订单的信息，以及该订单所属的客户的基本信息
        Order getOrderById(Integer id);

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

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

*  关于< association >中对Customer实体类的映射
    
     *  这里虽然两个属性的值都是customer，但是意义不同
     *  property="customer"：是因为Order实体类的一个成员变量的名称为customer，指明该标签的映射规则对应到哪个成员变量
     *  javaType="customer"：是因为该成员变量的类型是Customer，由于我们在SqlMapConfig.xml为实体类注册了包级别名，这里用customer指代此类型

<association property="customer" javaType="customer">
            <id property="id" column="cid"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
        </association>

### 测试 ###

//SqlSession对象
        SqlSession sqlSession;
    
        //获取OrderMapper的mybatis动态代理对象
        OrderMapper orderMapper;
    
        //获取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动态代理对象
            orderMapper = sqlSession.getMapper(OrderMapper.class);
        }
    
        //归还SqlSession
        @After
        public void closeSession(){
            sqlSession.close();
        }
    
        //测试：根据订单id查询订单的信息，以及该订单所属的客户的基本信息
        @Test
        public void testGetOrderById(){
            Order order = orderMapper.getOrderById(11);
            System.out.println(order);
        }

### 结果 ###

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

### 结果分析 ###

*  sql语句的查询结果

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

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

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

### 注意 ###

*  由于每个订单必定对应一个客户，所以本例中连接语句使用内连接也正确
 *  本例并未查询客户对应的订单信息，所以订单对应的客户的自己的订单信息都是空
 *  一对一和多对多关联查询，可由一对多和多对一查询推导出

[549538642c566da23b7bcc06062c1fff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/356973cd811a4787afe11dbdb7dc1500.png
[a03ca1c437479f24667a09d962123ab6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/001655cb08be42a796ff5c77a93513fc.png