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

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

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

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.priceFROM 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);    }