mybatis高级查询1.一对一2.一对多3.多对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<!-- 订单查询关联用户的resultMap -->
    <resultMap id="OrdersUserResultMap" type="nuc.edu.rwenjie.pojo.Orders">
        <!-- 配置要映射的订单信息 -->

        <!-- id:配置查询列中的唯一标识， 订单信息中的唯一标识，如果有多个列组成唯一标识，配置多个id
            column：订单信息的唯一标识的列
            property：订单信息的唯一标识的列 所映射到Orders中的哪个属性
        -->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!-- 配置映射的关联的用户信息-->
        <!-- association:用于映射关联查询单个对象的信息
            property:要将关联查询对象的用户信息映射到Orders中的哪个属性
        -->
         <association property="user" javaType="nuc.edu.rwenjie.pojo.Users">
            <!-- id:关联查询的用户的唯一标识 -->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

<!-- 一对一 查询订单及订单对应的用户信息 -->
    <select id="findOrdersUser" resultMap="OrdersUserResultMap">
        SELECT
	        orders.*,
	        Users.username,
	        users.sex,
	        users.address
        FROM
	        orders, users
        WHERE orders.user_id = users.id
    </select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<!-- 查询订单及订单明细的resultMap
    使用extend继承，不在配置订单信息和用户信息的映射
    <resultMap id="findOrdersAndOrderDetailResultMap" type="nuc.edu.rwenjie.pojo.Orders" extends="OrdersUserResultMap">
    -->
    <resultMap id="findOrdersAndOrderDetailResultMap" type="nuc.edu.rwenjie.pojo.Orders" >
        <!-- 订单信息 -->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!-- 订单用户信息 -->
        <association property="user" javaType="nuc.edu.rwenjie.pojo.Users">
            <!-- id:关联查询的用户的唯一标识 -->
            <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：将关联对象查询到的多条记录映射到nuc.edu.rwenjie.pojo.Orders
        ofType:指定映射到list集合属性中pojo的类型
        -->
        <!--         上面的这两部分可以用extends实现             -->

        <collection property="orderDetailList" ofType="nuc.edu.rwenjie.pojo.OrderDetail">


            <!-- id:订单明细的唯一标识
                property:要将订单明细的唯一标识映射到nuc.eud.rwenjie.pojo.OrderDetail的那个属性
            -->

            <!--column:SQL语句中的别名  property：OrderDetail中的唯一标识-->
            <id column="orderdetail_id" property="i_d" />
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
        </collection>

    </resultMap>


    <!-- 一对多
       查询顶和订单明细
     -->
    <select id="findOrdersAndOrderDetail" resultMap="findOrdersAndOrderDetailResultMap">
        SELECT
	        orders.*,
	        Users.username,
	        users.sex,
	        users.address,
	        orderdetail.id orderdetail_id,
	        orderdetail.items_id,
	        orderdetail.items_num,
	        orderdetail.orders_id
        FROM
	        orders, users, orderdetail
        WHERE orders.user_id = users.id AND orderdetail.orders_id = orders.id
    </select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    <!-- 查询用户及购买的商品 -->
    <resultMap id="findUserAndItemsResultMap" type="nuc.edu.rwenjie.pojo.Users">
        <!-- 用户信息 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 用户所购买的订单信息
            一个用户对应多个订单， 使用collection
         -->
        <collection property="ordersList" ofType="nuc.edu.rwenjie.pojo.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>

            <!-- 订单明细 一个订单包含多个订单明细-->
            <!-- collection相当于一个循环 订单明细应该在内循环中 -->
            <collection property="orderDetailList" ofType="nuc.edu.rwenjie.pojo.OrderDetail">
                <id column="orderdetail_id" property="i_d"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>

                <!-- 商品信息  一个订单明细对应一个商品信息 -->
                <association property="items" javaType="nuc.edu.rwenjie.pojo.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_price" property="price"/>
                    <result column="items_datetime" property="datetime"/>
                    <result column="items_datail" property="detail"/>
                </association><!--Items-->
            </collection><!--OrderDrtail-->
        </collection><!-- Orders-->
    </resultMap><!-- Users-->

    <!-- 查询用户及用户所购买的商品信息 -->
    <select id="findUserAndItems" resultMap="findUserAndItemsResultMap">
        SELECT
	        orders.*,
	        Users.username,
	        users.sex,
	        users.address,
	        orderdetail.id orderdetail_id,
	        orderdetail.items_id,
	        orderdetail.items_num,
	        orderdetail.orders_id,
		items.name items_name,
		items.price items_price,
		items.datetime items_datetime,
		items.detail items_datail
        FROM orders, users, orderdetail, items
        WHERE orders.user_id = users.id AND orderdetail.orders_id = orders.id AND orderdetail.items_id = items.id
    </select>