MyBatis之多对一关联查询

订单表跟人员表的关系就是多对一的关系

配置文件:

<resultMap type="orders" id="selectPersonByOrderIdRM" extends="BaseResultMap">
  <!-- 
    association: 多对一关联标签
    property: 多的一端一的属性名称
    javaType: 多的一端一的属性的数据类型
   -->
    <association property="person" javaType="person">
        <id column="person_id" property="personId"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="person_addr" property="personAddr"/>
        <result column="birthday" property="birthday"/>
    </association>
  </resultMap>
  <select id="selectPersonByOrderId" parameterType="int" resultMap="selectPersonByOrderIdRM">
    select * from orders o, person p where o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = #{orderId}
  </select>

测试代码:

@Test
    public void test() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Orders order = sqlSession.selectOne("com.rl.mapper.OrdersMapper.selectPersonByOrderId", 1);
            System.out.println(order);
        } finally{
            sqlSession.close();
        }
    }

需求: 通过查询订单表, 查询出订单的所属人员和订单的明细(涉及到多对一和一对多的关联映射)

配置文件:

<resultMap type="orders" id="selectPersonAndDetailByOrderIdRM" extends="BaseResultMap">
    <association property="person" javaType="person">
        <id column="person_id" property="personId"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="person_addr" property="personAddr"/>
        <result column="birthday" property="birthday"/>
    </association>
    <collection property="detailList" ofType="com.rl.model1.OrderDetail">
        <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />
	    <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
	    <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />
	    <result column="PRICE" property="price" jdbcType="REAL" />
	    <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />
    </collection>
  </resultMap>
  <select id="selectPersonAndDetailByOrderId" parameterType="int" resultMap="selectPersonAndDetailByOrderIdRM">
    select * from orders o, person p, order_detail od where 
    o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = od.ORDER_ID and o.ORDER_ID = #{orderId}
  </select>

测试代码:

@Test
    public void test1() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Orders order = sqlSession.selectOne("com.rl.mapper.OrdersMapper.selectPersonAndDetailByOrderId", 1);
            System.out.println(order);
        } finally{
            sqlSession.close();
        }
    }

注意: 此时配置文件中的"association"和"collection"必须有上下之分: "association"在上, "collection"在下

另外, 可以精简配置文件:

<resultMap type="orders" id="selectPersonAndDetailByOrderIdRM1" extends="selectPersonByOrderIdRM">
    <collection property="detailList" ofType="com.rl.model1.OrderDetail">
        <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />
	    <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
	    <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />
	    <result column="PRICE" property="price" jdbcType="REAL" />
	    <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />
    </collection>
  </resultMap>

由于之前定义过id为"selectPersonByOrderIdRM"的resultMap, 所以, 直接继承其即可, 手写的部分只有"一对多"