订单表跟人员表的关系就是多对一的关系
配置文件:
<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, 所以, 直接继承其即可, 手写的部分只有"一对多"