MyBatis Study Notes (1) — Usage

MyBatis learning Notes (2) — Mapping relations

One-to-one mapping for advanced mapping

Mybatis Learning Notes (4) – Advanced mapping one of multiple mappings

Advanced mapping many-to-many mapping (MANy-to-many mapping)

This section follows the previous section MyBatis Learning Notes (3) – advanced mapping of one-to-one mapping, using the same data table. Recall the tables from the previous section and think about which tables have a one-to-many relationship.

It is clear that orders and orderItems have a one-to-many relationship:

orders
order_num
orders
orderitems
orderitems
order_num
orders
order_num

SQL statement to prepare query first:

SELECT o.*, oi.order_item, oi.prod_id, oi.quantity, oi.item_price
FROM orders AS o, orderitems AS oi
WHERE o.order_num=oi.order_num
Copy the code

If we follow the routine of the previous section, we should first explain the return value to use resultType. However, if the return value is the POJO defined by resultType, there is no doubt that the first half of the POJO information queried will contain a large number of repeated order information, so at this time, we had better return resultMap. The idea is to add the List orderitems attribute to the Orders class, so that when you query an order, the orderitems it contains are reflected in its orderitems attribute.

So Orders contains the following attributes:

public class Orders { private Integer orderNum; private Date orderDate; private Integer custId; private Customer customer; private List<OrderItems> orderItems; .Copy the code

Define the ResultMap

Since we already defined OrdersCustomerResultMap in the previous section, now we just add an orderItems property, so we can inherit the ResultMap from the previous section as follows:

<resultMap id="OrderAndOrderItemsResultMap" type="com.shuqing28.pojo.Orders" extends="OrdersCustomerResultMap">
        <collection property="orderItems" ofType="com.shuqing28.pojo.OrderItems">
            <id column="order_num" property="orderNum" />
            <id column="order_item" property="orderItem"/>
            <result column="prod_id" property="prodId"/>
            <result column="quantity" property="quantity"/>
            <result column="item_price" property="itemPrice"/>
        </collection>
</resultMap>
Copy the code

Because the added orderItems is a List collection object, we define the new element with collection, and the extends at the end of the first line also reflects that we are inheritors of the OrdersCustomerResultMap from the previous section.

Defining a SELECT statement

Since we inherit the resultMap from the previous section, our query still has Customer information attached, so the final SQL is as follows:

<select id="findOrderAndItemsMap" resultMap="OrderAndOrderItemsResultMap">
    SELECT o.order_num,o.order_date, c.*, oi.order_item, oi.prod_id, oi.quantity, oi.item_price
    FROM orders AS o, customers AS c, orderitems AS oi
    WHERE o.cust_id = c.cust_id
    AND o.order_num=oi.order_num
</select>
Copy the code

This is the 3 table joint check, also slightly expanded our scenario.

Of course we also have to declare the interface in the DAO class

Declare interface:

public List<Orders> findOrderAndItemsMap();
Copy the code

test

 @Test
    public void findOrderAndItemsMap(){ SqlSession sqlSession = sqlSessionFactory.openSession(); try { OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class); List<Orders> orders = ordersDao.findOrderAndItemsMap(); System.out.println(orders); } finally { sqlSession.close(); }}Copy the code

The test results

You can see that there are still five orders. Look at the first one, which contains four orderItems, and then the individual properties in the specific orderItems also have values.