mybatis之多对多映射查询(十二)

本是古典何须时尚 2022-06-10 13:37 184阅读 0赞

多对多映射就是一对多映射的特例

需求  
查询用户信息，关联查询该用户购买的商品信息

sql语句

SELECT u.username, 
           u.sex ,
           u.address,
           o.*,
           od.id od_id, 
           od.orders_id, 
           od.items_id,
           i.id i_id, 
           i.name, 
           i.price
    FROM orders o
      LEFT JOIN USER u
        ON u.id = o.user_id
      LEFT JOIN orderdetail od
        ON o.id = od.orders_id
      LEFT JOIN items i
        ON i.id = od.items_id

修改pojo类  
在User类中添加List ordersList;

public class User {
        private int id;
        private String username;// 用户姓名
        private String sex;// 性别
        private Date birthday;// 生日
        private String address;// 地址
    
        private List<Orders> ordersList;
        //set/get方法省略
    }

在Orders类中添加List OrderdetailList;

public class Orders {
        private Integer id;
    
        private Integer userId;
    
        private String number;
    
        private Date createtime;
    
        private String note;
    
        private List<Orderdetail> OrderdetailList;
        //set/get方法省略
    }

在Orderdetail中添加Items item；

public class Orderdetail {
        private Integer id;
    
        private Integer ordersId;
    
        private Integer itemsId;
    
        private Integer itemsNum;
    
        private Items item;
        //set/get方法省略
    }

OrdersMapper接口

public interface OrdersMapper {
        List<User> findUserAndItem();
    }

OrdersMapper.xml

<?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="com.test.mapper.OrdersMapper">
       <resultMap type="com.test.domain.User" id="findUserAndItem">
            
            <id property="id" column="user_id"/>
            <result property="username" column="username"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>
            
            <collection property="ordersList" ofType="com.test.domain.Orders">
                <id column="id" property="id"/>
                <result column="user_id" property="userId"/>
                <result column="number" property="number"/>
                <result column="createtime" property="createtime"/>
                <result column="note" property="note"/>
                
                <collection property="OrderdetailList" ofType="com.test.domain.Orderdetail">
                    <id property="id" column="od_id"/>
                    <result property="itemsId" column="items_id"/>
                    <result property="ordersId" column="orders_id"/>
                    <result property="itemsNum" column="items_num"/>
                    
                    <association property="item" javaType="com.test.domain.Items">
                        <id property="id" column="i_id"/>
                        <result property="name" column="name"/>
                        <result property="price" column="price"/>
                    </association>
                </collection>
            </collection>
        </resultMap>
        <select id="findUserAndItem" resultMap="findUserAndItem">
            SELECT u.username, 
                   u.sex ,
                   u.address,
                   o.*,
                   od.id od_id, 
                   od.orders_id, 
                   od.items_id,
                   i.id i_id, 
                   i.name, 
                   i.price
            FROM orders o
              LEFT JOIN USER u
                ON u.id = o.user_id
              LEFT JOIN orderdetail od
                ON o.id = od.orders_id
              LEFT JOIN items i
                ON i.id = od.items_id
        </select>
    </mapper>

测试代码

@Test
        public void test_02(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
    
            OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    
            List<User> userList = mapper.findUserAndItem();
    
            System.out.println(userList);
        }