示例:
查询订单表及订单明细信息。
编写sql查询语句:
SELECT
orders.*, USER .username ,USER .sex,
USER .address,
orderdetail.id as orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id
FROM
orders,
USER,
orderdetail
WHERE
orders.user_id = USER .id
AND
orderdetail.orders_id=orders.id
结果如下:
order定义:
resultMap配置:
<!-- 定义订单查询关联用户的resultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
<!-- 配置映射的订单信息 -->
<id column="id" property="id" /><!-- 订单信息的唯一标识 --><!-- 如果有多个列,组成唯一标识,配置多个id -->
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<!-- 配置映射的关联的用户信息 -->
<!-- association:用于映射关联查询单个对象的信息 property:将关联查询的用户信息映射到orders的那个属性中 -->
<association property="user" javaType="cn.itcast.mybatis.po.User">
<!-- 关联查询的用户的唯一标识 column:指定唯一标识用户信息的列 -->
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 查询订单及订单明细的resultMap -->
<!-- extends:
使用继承就不用配置订单信息和用户信息的映射了
-->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
<!-- 订单信息 -->
<!-- <id column="id" property="id" />订单信息的唯一标识如果有多个列,组成唯一标识,配置多个id
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
用户信息
<association property="user" javaType="cn.itcast.mybatis.po.User">
关联查询的用户的唯一标识 column:指定唯一标识用户信息的列
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association> -->
<!-- 明细信息 一个订单关联查询除了多条明细信息 要使用collection来进行映射 collection:实现了对关联查询到的多条记录进行映射
property:将关联查询到的多条记录映射到cn.itcast.mybatis.po.Orders的哪个属性 ofType:要映射到集合属性中pojo的类型 -->
<collection property="orderDetials" ofType="cn.itcast.mybatis.po.OrderDetial">
<!-- 订单明细的唯一标识 -->
<!-- id:订单明细的唯一标识 -->
<id column="orderdetail_id" property="id" />
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<result column="orders_id" property="ordersId"/>
</collection>
</resultMap>
<!-- 查询订单关联用户及订单明细 -->
<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
SELECT
orders.*, USER .username ,USER .sex,
USER .address,
orderdetail.id as orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id
FROM
orders,
USER,
orderdetail
WHERE
orders.user_id = USER .id
AND
orderdetail.orders_id=orders.id
</select>
小结:
1,使用collection来映射一对多中的List属性;
2,使用extends来继承其他resultMap;