Mybatis从入门到精通系列 15 —— 嵌套查询与嵌套结果

╰+攻爆jí腚メ 2023-10-11 15:42 15阅读 0赞

Mybatis 在映射文件中加载关联关系对象主要通过两种方式：嵌套查询与嵌套结果。

1.  **嵌套查询是指通过执行另外一条 SQL 映射语句来返回预期的复杂类型；**
2.  **嵌套结果是使用嵌套结果映射来处理重复的联合结果的子集。**

本文我们针对 Mybatis 的多对多的嵌套查询与嵌套结果进行详细分析。

![在这里插入图片描述][20210519115233329.png_pic_center]

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

#### 文章目录 ####

*  一、环境准备
 *   *  1.1 建立数据库
     *  1.2 项目工程初始化
 *  二、多对多实现嵌套查询
 *  三、多对多实现嵌套结果

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

## 一、环境准备 ##

### 1.1 建立数据库 ###

下面拿商品表和订单表举例说明如何实现嵌套查询与嵌套结果。一个订单包含多种商品，一个商品也可以属于多个订单。具体关系如下：  
![在这里插入图片描述][7309c8bf281b498488b356637935d56d.png_pic_center]

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

下面在 mysql 中建立 t\_goods 表、t\_orders 表以及 t\_goods\_orders 表，并在 t\_goods\_orders 设立外键。

![在这里插入图片描述][0cc565a6499d4a23a982152c098a169a.png_pic_left]  
![在这里插入图片描述][702fab5045074c5fb5bf7291a5662b43.png_pic_left]

**添加数据：**

![在这里插入图片描述][5857f35fa7b94b559f27a95e5bc4d679.png_pic_left]

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

### 1.2 项目工程初始化 ###

**工程目录：**  
![在这里插入图片描述][f17aa38443bc4da3a45b23b916706fc2.png_pic_left]

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

**导入依赖：**

在pom文件中带入以下依赖：

<dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
    
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.16</version>
        </dependency>
    
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

**主配置文件：**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
    <configuration>
        
        <properties resource="jdbcConfig.properties" />
    
        
        <settings>
            <setting name="cacheEnabled" value="true" />
        </settings>
    
        
        <typeAliases>
            <package name="com.axy.domain" />
        </typeAliases>
    
        
        <environments default="mysql">
            
            <environment id="mysql">
                
                <transactionManager type="JDBC" />
                
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
          <package name="com.axy.dao" />
        </mappers>
    </configuration>

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

**数据库外部配置文件：**

jdbc.driver=com.mysql.cj.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/mybatisdb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=CST
    jdbc.username=root
    jdbc.password=123456

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

**log4j.properties：**

# Set root category priority to INFO and its only appender to CONSOLE.
    #log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
    log4j.rootCategory=debug, CONSOLE, LOGFILE
    
    # Set the enterprise logger category to FATAL and its only appender to CONSOLE.
    log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
    
    # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
    log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
    log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
    log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
    
    # LOGFILE is set to be a File appender using a PatternLayout.
    log4j.appender.LOGFILE=org.apache.log4j.FileAppender
    log4j.appender.LOGFILE.File=d:\axis.log
    log4j.appender.LOGFILE.Append=true
    log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
    log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

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

**商品和订单实体类：**

public class Goods implements Serializable {
        
    
        private Integer id;
        private String name;
        private Float price;
        //一对多映射
        private List<Orders> ordersList;
    
        public Integer getId() {
        
            return id;
        }
        public void setId(Integer id) {
        
            this.id = id;
        }
        public String getName() {
        
            return name;
        }
        public void setName(String name) {
        
            this.name = name;
        }
        public Float getPrice() {
        
            return price;
        }
        public void setPrice(Float price) {
        
            this.price = price;
        }
        public List<Orders> getOrdersList() {
        
            return ordersList;
        }
        public void setOrdersList(List<Orders> ordersList) {
        
            this.ordersList = ordersList;
        }
    
        @Override
        public String toString() {
        
            return "Goods{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", price=" + price +
                    '}';
        }
    }

public class Orders implements Serializable {
        
    
        private Integer id;
        private String info;
        private Date createTime;
        //一对多映射
        private List<Goods> goodsList;
    
        public void setId(Integer id) {
        
            this.id = id;
        }
        public void setInfo(String info) {
        
            this.info = info;
        }
        public void setCreateTime(Date createTime) {
        
            this.createTime = createTime;
        }
        public void setGoodsList(List<Goods> goodsList) {
        
            this.goodsList = goodsList;
        }
        public Integer getId() {
        
            return id;
        }
        public String getInfo() {
        
            return info;
        }
        public Date getCreateTime() {
        
            return createTime;
        }
        public List<Goods> getGoodsList() {
        
            return goodsList;
        }
    
        @Override
        public String toString() {
        
            return "Orders{" +
                    "id=" + id +
                    ", info='" + info + '\'' +
                    ", createTime=" + createTime +
                    '}';
        }
    }

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

## 二、多对多实现嵌套查询 ##

**需求：** 实现查询一个订单，将其所包含的商品也查询出来。

**分析：**  
  查询订单我们需要用到 t\_goods 表，但订单分配的商品的信息我们并不能直接找到商品信息，而是要通过中间表( t\_goods\_order 表)才能关联到商品信息。

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

分别在 IOrdersDao 和 IGoodsDao 添加以下方法：

**IOrdersDao .java：**

public interface IOrdersDao {
        
        /**
         * 嵌套查询：根据订单Id查询当前订单所对应的商品信息
         */
        public Orders findOrdersNestedQueryById(Integer id);
    }

**IGoodsDao.java：**

public interface IGoodsDao {
        
        /**
         * 根据 id 查询商品
         */
        public Goods findGoodsById(Integer id);
    }

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

分别在 IOrdersDao.xml 和 IGoodDao.xml 中编写如下代码：

**IOrdersDao.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.axy.dao.IOrdersDao">
    
        
        <resultMap id="OrdersMap1" type="orders">
            <id property="id" column="id" jdbcType="INTEGER" />
            <result property="info" column="info" jdbcType="VARCHAR" />
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
            <collection property="goodsList" column="id" ofType="goods"
                        select="com.axy.dao.IGoodsDao.findGoodsById" />
        </resultMap>
    
        <select id="findOrdersNestedQueryById" resultMap="OrdersMap1">
            select * from t_orders where id = #{id}
        </select>
    
    </mapper>

在 `<resultMap>` 中使用了 `<collection>` 元素来映射多对多关联关系，其中 property 属性表示订单持久化类的商品属性，ofType 属性表示集合中的数据为商品类型，而 column 的属性值会作为参数执行 IGoodDao 中定义的 id 为 findGoodsById 的执行语句来查询订单中的商品信息。

**IGoodsDao.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.axy.dao.IGoodsDao">
    
        <select id="findGoodsById" parameterType="Integer" resultType="Goods">
            select * from t_goods where id in(
                select good_id from t_goods_orders where order_id = #{id}
            )
        </select>
    
    </mapper>

在 IGoodsDao 中 id 为 findGoodsById 的执行语句中，该语句的 SQL 会根据订单的 id 查询与该订单所关联的商品信息，当然这里是借助了中间表查询的商品信息。

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

**编写测试类：**

public class Client {
        
        private InputStream in;
        private SqlSession sqlSession;
        private IOrdersDao ordersDao;
        private IGoodsDao goodsDao;
    
        @Before//单元测试之前执行
        public void init() throws Exception{
        
            //1.读取配置文件
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2.获取SqlSessionFactory
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            //3.获取SqlSession对象
            sqlSession = factory.openSession();
            //4.获取dao的代理对象
            ordersDao = sqlSession.getMapper(IOrdersDao.class);
            goodsDao = sqlSession.getMapper(IGoodsDao.class);
        }
    
        @After//单元测试之后执行
        public void destroy() throws Exception {
        
            //提交事务
            sqlSession.commit();
            //释放资源
            sqlSession.close();
            in.close();
        }
    
        /**
         * 测试嵌套查询：根据订单Id查询当前订单所对应的商品信息
         */
        @Test
        public void testNestedQuery(){
        
            Orders orders = ordersDao.findOrdersNestedQueryById(1);
            System.out.println("------------------订单编号为 1 的订单以及商品信息如下：----------------");
            System.out.println(orders);
            System.out.println(orders.getGoodsList());
        }
    }

**测试结果：**

![在这里插入图片描述][6b2110b68c154ae7b7b951a343ce5444.png]

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

## 三、多对多实现嵌套结果 ##

**IOrdersDao.java：**

public interface IOrdersDao {
        
        /**
         * 嵌套结果：根据订单Id查询当前订单所对应的商品信息
         */
        public Orders findOrdersNestedResultsById(Integer id);
    }

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

**IOrdersDao.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.itheima.dao.IOrdersDao">
    
        
        <resultMap id="OrdersMap2" type="orders">
            <id property="id" column="id" jdbcType="INTEGER" />
            <result property="info" column="info" jdbcType="VARCHAR" />
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
            <collection property="goodsList" ofType="goods">
                <id property="id" column="gid" />
                <result property="name" column="name" />
                <result property="price" column="price" />
            </collection>
        </resultMap>
    
        
        <select id="findOrdersNestedResultsById" resultMap="OrdersMap2">
            select o.*, g.id as gid, g.name, g.price
            from t_orders o, t_goods g, t_goods_orders og
            where g.id = og.good_id
              and og.order_id = o.id
              and o.id = #{id}
        </select>
     
    </mapper>

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

**添加测试方法：**

/**
     *  测试嵌套结果：根据订单Id查询当前订单所对应的商品信息
     */
    @Test
    public void testNestedResult(){
        
        Orders orders = ordersDao.findOrdersNestedResultsById(1);
        System.out.println("------------------订单编号为 1 的订单以及商品信息如下：----------------");
        System.out.println(orders);
        System.out.println(orders.getGoodsList());
    }

**测试结果：**

![在这里插入图片描述][54a8a4c833aa437eaff9d4083a837469.png]

[20210519115233329.png_pic_center]: https://img-blog.csdnimg.cn/20210519115233329.png#pic_center
[7309c8bf281b498488b356637935d56d.png_pic_center]: https://img-blog.csdnimg.cn/7309c8bf281b498488b356637935d56d.png#pic_center
[0cc565a6499d4a23a982152c098a169a.png_pic_left]: https://img-blog.csdnimg.cn/0cc565a6499d4a23a982152c098a169a.png#pic_left
[702fab5045074c5fb5bf7291a5662b43.png_pic_left]: https://img-blog.csdnimg.cn/702fab5045074c5fb5bf7291a5662b43.png#pic_left
[5857f35fa7b94b559f27a95e5bc4d679.png_pic_left]: https://img-blog.csdnimg.cn/5857f35fa7b94b559f27a95e5bc4d679.png#pic_left
[f17aa38443bc4da3a45b23b916706fc2.png_pic_left]: https://img-blog.csdnimg.cn/f17aa38443bc4da3a45b23b916706fc2.png#pic_left
[6b2110b68c154ae7b7b951a343ce5444.png]: https://img-blog.csdnimg.cn/6b2110b68c154ae7b7b951a343ce5444.png
[54a8a4c833aa437eaff9d4083a837469.png]: https://img-blog.csdnimg.cn/54a8a4c833aa437eaff9d4083a837469.png