Advanced mapping many-to-many mapping

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

Suppose we now have a need to know what a customer has purchased, but by looking at the Customers table and the Products table we find that the two tables are not related? From the previous example, we saw that an indirect relationship can be established between the Customers table and the Products table through the Orders and OrderItems tables.

We are using the Customer table as the primary table, so the query statement should be

SELECT c.*, o.*, oi.*, p.*
FROM customers AS c, orders AS o, orderitems AS oi, products AS p
WHERE c.cust_id=o.cust_id
AND o.order_num=oi.order_num
AND oi.prod_id=p.prod_id
Copy the code

As explained in the previous section, if we use resultType to define the return type, the POJO class defined will contain many similar parts. Therefore, it is better to use resultMap to define the return type, so that we can extend the attributes on the original Customer class to store the order. The order property will contain the order item, which will be associated with the specific item. The association is easy to see from the diagram above.

So let’s take a look at what we define a class to look like.

POJO defined

public class Customer { private Integer custId; private String custName; private String custAddress; private String custCity; private String custState; private String custZip; private String custCountry; private String custContact; private String custEmail; // Add orderList to store Orders placed by the customer List<Orders> ordersList;Copy the code

In the Orders class, we delete the original Customer attribute and leave everything else untouched

Orders contains the OrderItems property, which is defined as follows:

public class OrderItems { private Integer orderNum; private Integer orderItem; private String prodId; private Integer quantity; private Double itemPrice; Private Products Products; private Products Products; private Products Products;Copy the code

Define the resultMap

<resultMap id="CustomerAndProductsMap" type="com.shuqing28.pojo.Customer">
        <id column="cust_id" property="custId"/>
        <result column="cust_name" property="custName"/>
        <result column="cust_address" property="custAddress"/>
        <result column="cust_city" property="custCity"/>
        <result column="cust_state" property="custState"/>
        <result column="cust_zip" property="custZip"/>
        <result column="cust_country" property="custCountry"/>
        <result column="cust_contact" property="custContact"/>
        <result column="cust_email" property="custEmail"/>

        <collection property="ordersList" ofType="com.shuqing28.pojo.Orders">
            <id column="order_num" property="orderNum"/>
            <result column="order_date" property="orderDate"/>
            <result column="cust_id" property="custId"/>
            <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"/>
                <association property="products" javaType="com.shuqing28.pojo.Products">
                    <id column="prod_id" property="prodId"/>
                    <result column="vend_id" property="vendId"/>
                    <result column="prod_name" property="prodName"/>
                    <result column="prod_price" property="prodPrice"/>
                    <result column="prod_desc" property="prodDesc"/>
                </association>
            </collection>
        </collection>
    </resultMap>
Copy the code

From the definition of resultMap, we can see the nesting relationship from Customer to Products. Let’s continue with the query definition:

<select id="findCustomerProducts" resultMap="CustomerAndProductsMap">
        SELECT c.*, o.*, oi.*, p.*
        FROM customers AS c, orders AS o, orderitems AS oi, products AS p
        WHERE c.cust_id=o.cust_id
        AND o.order_num=oi.order_num
        AND oi.prod_id=p.prod_id
    </select>
Copy the code

Through a series of internal links, the products associated with customer are queried.

Define the interface:

public List<Customer> findCustomerProducts();
Copy the code

Test code:

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

The query results

This is called a many-to-many query, with more nested relationships.