【MyBatis框架点滴】——MyBatis多对多查询

本是古典何须时尚 2022-08-23 00:59 230阅读 0赞

[上篇文章][Link 1]说了MyBatis中的一对多的查询方法，这里总结一下MyBatis中多对多的查询。

业务还用[上篇文章][Link 1]中的订单业务来分析，表结构如下：

![这里写图片描述][20160603145933952]

一个用户可以购买多种商品，一种商品也可以由多个用户购买。这里以用户表为主查询表，查询用户信息及其该用户所购买的商品信息。

由于用户表和商品表没有直接联系，所以只能通过它们之间的两个媒介——订单表和订单明细表来映射。

对于实体的要求，在User中需要定义Orders的集合，在Orders中需要定义OrderDetail的集合，在OrderDetail中需要定义Item的实例：

user→orders→orderdetail→item

User.java

private int id;
        private String username;
        private int sex;
        private Date birthday;
        private String address;
        private List<Orders> orders;
        //getter、setter
    }

Orders.java

public class Orders {
        private Integer id;
        private Integer userId;
        private String number;
        private Date createtime;
        private String note;
        private List<OrderDetail> orderDetails;
        //getter、setter
    }

OrderDetail.java

public class OrderDetail {
        private Integer id;
        private Integer ordersId;
        private Integer itemsId;
        private Integer itemsNum;
        private Item item;
        //getter、setter
    }

Item.java

public class Item{
        private Integer id;
        private String name;
        private float price;
        private String detail;
        private String pic;
        private Date createTime;
        //getter、setter
    }

映射文件UserMapper.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.danny.mybatis.mapper.UserMapper" >
    
        <resultMap type="com.danny.mybatis.po.User" id="UserItemResultMap">
              
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
    
            
            <collection property="orders" ofType="com.danny.mybatis.po.Orders">
                <id column="id" property="id"/>
                <result column="number" property="number"/>
                <result column="note" property="note"/>
                <result column="createtime" property="createtime"/>
    
                
                <collection property="orderDetails" ofType="com.danny.mybatis.po.OrderDetail">
                    <id column="orderdetail_id" property="id"/>
                    <result column="items_id" property="itemsId"/>
                    <result column="items_num" property="itemsNum"/>
                    <result column="orders_id" property="ordersId"/>
    
                    
                    <association property="item" javaType="com.danny.mybatis.po.Item">
                        <id column="item_id" property="id"/>
                        <result column="item_name" property="name"/>
                        <result column="item_price" property="price"/>
                        <result column="item_detail" property="detail"/>
                    </association>
                </collection>
            </collection>
        </resultMap>
    
        <select id="findUserItemResultMap" resultMap="UserItemResultMap" >
            select
                orders.*,
                user.username,
                user.sex,
                user.address,
                orderdetail.id orderdetail_id,
                orderdetail.items_id,
                orderdetail.items_num,
                orderdetail.orders_id,
                item.id item_id,
                item.name item_name,
                item.detail item_detail,
                item.price item_price
                from orders,user,orderdetail,item
                where orders.user_id=user.id
            and orders.id=orderdetail.orders_id
            and orderdetail.items_id=item.id
        </select>
    </mapper>

如上，配置resultMap的方式跟前面[一对一][Link 2]和[一对多][Link 1]两篇文章中一样，用`<collection></collection>`和`<association></association>`分别对集合和实体进行关联映射，而且它们层层嵌套的方式就跟实体之间层层嵌套的方式一样：user中包含orders，orders中包含orderdetail，orderdetail中包含item。

mapper接口

public interface UserMapper {
        List<User> findUserItemResultMap() throws Exception;
    }

测试

@Test
        public void findUserItemResultMap(){
            SqlSession sqlSession=sqlSessionFactory.openSession();
            UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
            try {
                List<User> list=userMapper.findUserItemResultMap();
                if (list!=null) {
                    System.out.println(list.size());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println();
        }

数据库中同样是上篇文章中的8条数据，断点调试查看list中的内容如下：

![这里写图片描述][20160605165710265]

到此，MyBatis的关联查询就算总结完成了，具体用那种关联查询，用resultType还是resultMap指定输出类型，需要根据具体业务来定。

--------------------

【 转载请注明出处——胡玉洋[《【MyBatis框架点滴】——MyBatis多对多查询》][MyBatis_MyBatis]】

[Link 1]: http://blog.csdn.net/huyuyang6688/article/details/51588119
[20160603145933952]: /images/20220722/4e5e08a03e37403191c31409d2e62713.png
[Link 2]: http://blog.csdn.net/huyuyang6688/article/details/51578010
[20160605165710265]: /images/20220722/33e571bfba424b8f8117c41abb028af1.png
[MyBatis_MyBatis]: http://blog.csdn.net/huyuyang6688/article/details/51588818