mybatis : 多对一关联查询

业务背景

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

两张数据表

• 客户表

• 订单表

实体类

• 客户实体类：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映射文件

<!--

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

        Order实体类:
        private Integer id;
        private String orderNumber;
        private Double orderPrice;
        Customer customer = new Customer();

        Customer实体类:
        private Integer id;
        private String name;
        private Integer age;

    -->

    <resultMap id="orderMap" type="order">
        <!-- 主键映射 -->
        <id property="id" column="oid"/>
        <!-- 非主键映射 -->
        <result property="orderNumber" column="orderNumber"/>
        <result property="orderPrice" column="orderPrice" />

        <!-- 关于Customer实体类的映射 -->
        <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指代此类型

<!-- 关于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=[]
          }
     }

注意

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