MyBatis之一对多关联查询

员工表跟订单表之间的一对多关联映射:

配置文件:

<resultMap type="person" id="selectPersonByPersonIdRM" extends="BaseResultMap">
  <!-- 
    collection: 一对多关联映射标签
    property: 一的一端多的属性名称
    ofType: 一的一端多的属性的泛型的数据类型
    jdbcType: 可写可不写
   -->
        <collection property="orderList" ofType="com.rl.model1.Orders">
            <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
            <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
            <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
            <result column="ADDR" property="addr" jdbcType="VARCHAR" />
        </collection>
    </resultMap>
    <select id="selectPersonByPersonId" parameterType="int" resultMap="selectPersonByPersonIdRM">
        select * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = 1
    </select>

测试代码(部分):

@Test
    public void test() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Person person = sqlSession.selectOne("com.rl.mapper.PersonMapper.selectPersonByPersonId", 1);
            System.out.println(person);
        } finally{
            sqlSession.close();
        }
    }

结果:

Person [personId=1, name=李四, gender=1, personAddr=天津, birthday=Fri Sep 07 00:00:00 CST 2018, orderList=[com.rl.model1.Orders@66386e, com.rl.model1.Orders@102d8a2]]

员工表跟订单表, 订单表跟订单明细表的一对多嵌套关联映射:

配置文件:

<resultMap type="person" id="selectOrderAndDetailByPersonIdRM" extends="BaseResultMap">
        <collection property="orderList" ofType="com.rl.model1.Orders">
            <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
            <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
            <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
            <result column="ADDR" property="addr" jdbcType="VARCHAR" />
            
            <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>
        </collection>
    </resultMap>
    <select id="selectOrderAndDetailByPersonId" parameterType="int" resultMap="selectOrderAndDetailByPersonIdRM">
        select * from person p, orders o, order_detail od where 
									        p.PERSON_ID = o.PERSON_ID 
									        and o.ORDER_ID = od.ORDER_ID 
									        and p.PERSON_ID = #{personId}
    </select>

测试代码:

@Test
    public void test1() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Person person = sqlSession.selectOne("com.rl.mapper.PersonMapper.selectOrderAndDetailByPersonId", 1);
            System.out.println(person);
        } finally{
            sqlSession.close();
        }
    }

输出结果:

Person [personId=1, name=李四, gender=1, personAddr=天津, birthday=Fri Sep 07 00:00:00 CST 2018, orderList=[Orders [orderId=1, personId=1, totalPrice=300.0, addr=中腾建华, detailList=[OrderDetail [detailId=1, orderId=1, itemName=小霸王学习机, price=200.0, quantity=1], OrderDetail [detailId=2, orderId=1, itemName=四合一卡, price=50.0, quantity=2]]], Orders [orderId=2, personId=1, totalPrice=400.0, addr=金燕龙, detailList=[OrderDetail [detailId=3, orderId=2, itemName=手柄, price=200.0, quantity=2]]]]]