一 一對多查詢-概述
1 需求
- 查詢訂單及訂單明細(xì)
2 sql語句：
- 確定主查詢表：訂單表 orders
- 確定關(guān)聯(lián)查詢表：訂單明細(xì)表 Orderdetail
SELECT
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.item_num,
orderdetail.orders_id
FROM
orders,
USER,
orderdetail
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
sql語句執(zhí)行結(jié)果
3 要求
- 對orders映射不能出現(xiàn)重復(fù)記錄
4 思路
- 在orders.java類中添加List<orderDetail>orderDetail屬性
- 最終會將訂單信息映射到orders中譬重，訂單所對應(yīng)的訂單明細(xì)映射到orders中的ordersDetail屬性中邻梆。
- 映射成的orders記錄數(shù)為三條（orders信息不重復(fù)）
- 每個orders中的orderDetails屬性存儲了該訂單所對應(yīng)的訂單明細(xì)
二 一對多查詢-實現(xiàn)
1 在orders.java類中添加List<OrderdetailBean>OrderdetailBean屬性拆魏，并生成get和set方法
OrdersBean添加List集合
2 OrdersMapperCustom
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.OrdersMapperCustom">
<!--訂單查詢關(guān)聯(lián)用戶的resultMap-->
<resultMap id="OrdersUserResultMap" type="entity.OrdersBean">
<!--配置映射的訂單信息-->
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!--配置映射的關(guān)聯(lián)的用戶信息-->
<!--association:用于映射關(guān)聯(lián)查詢單個對象的信息
property:要將關(guān)聯(lián)查詢的用戶信息映射到Orders中哪個屬性-->
<association property="userBean" javaType="entity.UserBean">
<!--id:關(guān)聯(lián)查詢用戶的唯一標(biāo)識
column：指定唯一標(biāo)識用戶信息的列
javaType：映射到user的那個屬性-->
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!--查詢訂單關(guān)聯(lián)用戶及訂單明細(xì)，使用resultMap-->
<resultMap id="OrdersAndOrderdetailResultMap" type="entity.OrdersBean" extends="OrdersUserResultMap">
<!--訂單信息-->
<!--用戶信息-->
<!--明細(xì)信息
一個訂單關(guān)聯(lián)查詢出多條明細(xì)，要使用collection進(jìn)行映射
collection：對關(guān)聯(lián)查詢到的多條記錄映射到集合對象
property：將關(guān)聯(lián)查詢到的多條記錄映射到OrdersBean屬性
ofType:指定映射到集合屬性的poio的類型
-->
<collection property="orderdetailBeans" ofType="entity.OrderdetailBean">
<!--id:訂單明細(xì)唯一標(biāo)識-->
<id column="orderdetail_id" property="id"></id>
<result column="items_id" property="orders_id"></result>
<result column="items_id" property="items_id"></result>
<result column="orders_id" property="orders_id"></result>
</collection>
</resultMap>
<!--查詢訂單關(guān)聯(lián)查詢用戶信息，使用resultType-->
<select id="findOrdersUser" resultType="pojo.OrdersCustom">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
<!--查詢訂單關(guān)聯(lián)查詢用戶信息舶掖，使用resultMap-->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
<!--查詢訂單關(guān)聯(lián)用戶及訂單明細(xì)，使用resultMap-->
<select id="findOrdersAndOrderdetailResultMap" resultMap="OrdersAndOrderdetailResultMap">
SELECT
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.item_num,
orderdetail.orders_id
FROM
orders,
USER,
orderdetail
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
</select>
</mapper>
3 OrdersMapperCustom接口
public List<OrdersBean> findOrdersAndOrderdetailResultMap() throws Exception;
4 OrdersMapperTest 測試
@Test
public void findOrdersAndOrderdetailResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<OrdersBean> ordersBeans = ordersMapperCustom.findOrdersAndOrderdetailResultMap();
System.out.println(ordersBeans);
}
測試結(jié)果
三 多對多查詢-概述
1 需求
- 查詢用戶及用戶購買商品信息
2 sql語句
查詢主表是：用戶表
關(guān)聯(lián)表：由于用戶和商品沒有直接關(guān)聯(lián)尔店，通過訂單和訂單明細(xì)進(jìn)行關(guān)聯(lián)访锻，所以關(guān)聯(lián)表orders，orderdetail闹获，items
SELECT
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.item_num,
orderdetail.orders_id,
items.name items_name,
items.detail items_detail,
items.price items_price
FROM
orders,
USER,
orderdetail,
items
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
3 思路
- 將用戶信息映射到user中期犬。
- 在UserBean類中添加訂單列表屬性List<OrdersBean> ordersBean，將用戶創(chuàng)建的訂單映射到ordersBean避诽。
- 在OdersBean中添加訂單明細(xì)列表屬性List<OrderDetailBean> orderDetailBeans龟虎，將訂單的明細(xì)映射到orderDetailBeans。
- 在OrderDetail中添加items屬性ItemsBean itemsBean沙庐，將訂單明細(xì)所對應(yīng)的商品映射到itemsBean鲤妥。
四 多對多查詢-實現(xiàn)
1 配置映射
將用戶信息映射到user中
在Oders中添加訂單明細(xì)列表
在OrderDetail中添加items屬性
2 OrdersMapperCustom
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.OrdersMapperCustom">
<!--查詢用戶及用戶購買商品信息-->
<resultMap id="UserAndItemsResultMap" type="entity.UserBean">
<!--用戶信息-->
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--訂單信息-->
<collection property="ordersBeans" ofType="entity.OrdersBean">
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!--訂單明細(xì)-->
<collection property="orderdetailBeans" ofType="entity.OrderdetailBean">
<id column="orderdetail_id" property="id"></id>
<result column="items_id" property="orders_id"></result>
<result column="items_id" property="items_id"></result>
<result column="orders_id" property="orders_id"></result>
<!--商品信息-->
<association property="itemsBean" javaType="entity.ItemsBean">
<id column="items_id" property="id"></id>
<result column="items_name" property="name"></result>
<result column="items_detail" property="detail"></result>
<result column="items_price" property="price"></result>
</association>
</collection>
</collection>
</resultMap>
<!--查詢用戶及用戶購買商品信息-->
<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
SELECT
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.item_num,
orderdetail.orders_id,
items.name items_name,
items.detail items_detail,
items.price items_price
FROM
orders,
USER,
orderdetail,
items
WHERE
orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
</select>
</mapper>
3 接口
public List<UserBean> findUserAndItemsResultMap() throws Exception;
4 測試
@Test
public void findUserAndItemsResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<UserBean> userBeans = ordersMapperCustom.findUserAndItemsResultMap();
System.out.println(userBeans);
}
執(zhí)行結(jié)果
