需求:查询订单及订单明细的信息
- sql语句
- 确定主表:订单表
- 确定馆关联查询表:订单明细表
- 在一对一基础查询之上添加订单明细表即可
SELECT
orders.*, USER . NAME,
USER .address,
USER .birthday,
USER .sex,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.ordes_id
FROM
orders,
USER,
orderdetail
WHERE
orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id
使用resultType将上边的查询结果集映射到pojo中,订单信息的就是重复,对orders映射不能出现重复记录
- 在orders.java类中添加List orderDetails属性
最终会将订单信息映射到orders中,订单所对应的订单明细映射到orders中的orderDetails属性中,在orders中添加list订单明细属性
//订单明细
private List<Orderdetail> orderDetails;
- 定义resultType
<!-- 订单以及订单明细
使用extends继承,不用再配置用户及订单信息
-->
<resultMap type = "com.shagou.mybatis.po.Orders" id ="OrdersAndOrderDetailResultMap" extends ="OrdersUserResultMap">
<!-- 订单明细信息
一个订单关联查询出了多条明细,要使用collection进行映射
collection:对关联查询到多条记录映射到集合对象中
property:将关联查询到多条记录映射到com.shagou.mybatis.po.Orders哪个属性
ofType:指定映射list集合属性中pojo类型
-->
<collection property = "orderDetails" ofType = "com.shagou.mybatis.po.Orderdetail">
<!-- id:订单明细唯一标识
property:要将订单明细的唯一标识映射到com.shagou.mybatis.po.Orderdetail哪个属性
-->
<id column = "orderdetail_id" property = "id"/>
<result column = "orders_id" property = "orders_id"/>
<result column = "items_id" property = "items_id"/>
<result column = "items_num" property = "items_num"/>
</collection>
</resultMap>
- 配置xml
<!-- 查询订单关联查询用户及订单明细,使用resultMap -->
<select id = "findOrdersAndOrderDetailResultMap" resultMap = "OrdersAndOrderDetailResultMap">
SELECT
orders.*, USER . NAME,
USER .address,
USER .birthday,
USER .sex,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.ordes_id
FROM
orders,
USER,
orderdetail
WHERE
orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id
</select>
- 编写接口
//查询订单关联查询用户查询订单明细
public List<Orders> findOrdersAndOrderDetailResultMap()throws Exception;
- 测试类
//关联查询订单信息及订单明细
@Test
public void findOrdersAndOrderDetailResultMap() throws Exception {
SqlSession sqlSession = SqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<Orders> list = ordersMapperCustom.findOrdersAndOrderDetailResultMap();
System.out.println(list);
sqlSession.close();
}
小结:
mybatis使用collection对关联查询的多条记录映射到一个list集合属性中