MyBatis的关联映射

布满荆棘的人生 2024-03-23 15:43 47阅读 0赞

结果映射（resultMap）–主要使用这两个标签

association – 一个复杂类型的关联；许多结果将包装成这种类型  
嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用  
collection – 一个复杂类型的集合  
嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用

*  多对一：查询的多个对象中有一个属性包含一个复杂对象，使用association标签嵌套
 *  一对多：查询的单个对象中的一个属性包含多个其他对象，使用collection标签嵌套
 *  javaType为实体类中属性的类型，为常用基本数据类型时可以省略
 *  ofType则是用来指定到List或集合中的实体类类型，泛型中的约束类型

## 1.一对一查询 ##

问题：根据用户Id查询Person的信息，包括IdCard的信息，用户和IdCard是一对一的关系

### 1.1 数据库准备 ###

USE mybatis;
    # 创建一个名称为tb_idcard的表
    CREATE TABLE  tb_idcard( 
         id INT PRIMARY KEY AUTO_INCREMENT,
         CODE VARCHAR(18)
    );
    # 插入2条数据
    INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
    INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
    # 创建一个名称为tb_person的表
    CREATE TABLE  tb_person( 
         id INT PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(32),
         age INT,
         sex VARCHAR(8),
         card_id INT UNIQUE,     
         FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
    );
    # 插入2条数据
    INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
    INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',2);

### 1.2 实体类 ###

#### 1.2.1 IdCard.java ####

package com.biem.pojo;
    
    import lombok.*;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class IdCard {
        
        private Integer id;
        private String code;
    }

#### 1.2.2 Person.java ####

package com.biem.pojo;
    
    import lombok.*;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class Person {
        
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
        private IdCard card;
    }

### 1.3 用户配置接口 ###

#### 1.3.1 IdCardMapper.java ####

package com.biem.mapper;
    
    public interface IdCardMapper{
        
    }

#### 1.3.2 Person.java ####

package com.biem.mapper;
    
    public interface PersonMapper {
        
    }

### 1.4 用户配置文件 ###

#### 1.4.1 IdCardMapper.xml ####

#### 1.4.2 PersonMapper.xml ####

### 1.5 解决方法 ###

#### 1.5.1 嵌套查询方式 ####

##### 1.5.1.1 IdCardMapper.java #####

package com.biem.mapper;
    
    import com.biem.pojo.IdCard;
    
    public interface IdCardMapper {
        
        public IdCard findCodeById(Integer id);
    }

##### 1.5.1.2 PersonMapper.java #####

package com.biem.mapper;
    
    import com.biem.pojo.Person;
    
    public interface PersonMapper {
        
        public Person findPersonById(Integer id);
    }

##### 1.5.1.3 IdCardMapper.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.biem.mapper.IdCardMapper">
    
        
        <select id="findCodeById" parameterType="Integer" resultType="IdCard">
            select * from tb_idcard where id=#{id}
        </select>
    </mapper>

##### 1.5.1.4 PersonMapper.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.biem.mapper.PersonMapper">
        
        <resultMap id="IdCardWithPersonResult" type="Person">
            <id property="id" column="id"></id>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            <result property="sex" column="sex"/>
            
            <association property="card" column="card_id" javaType="IdCard"
                         select="com.biem.mapper.IdCardMapper.findCodeById"/>
        </resultMap>
    
        <select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
            select * from tb_person where id = #{id}
        </select>
    </mapper>

##### 1.5.1.5 PersonTest.java #####

package com.biem.test;
    
    import com.biem.mapper.PersonMapper;
    import com.biem.pojo.Person;
    import com.biem.util.MyBatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    public class PersonTest {
        
    
        @Test
        public void testFindPersonById(){
        
            SqlSession session = MyBatisUtil.openSession();
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            Person person = mapper.findPersonById(1);
            System.out.println(person);
            session.close();
        }
    }

##### 1.5.1.6 测试结果 #####

DEBUG 04-21 17:35:29,353 ==>  Preparing: select * from tb_person where id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:35:29,412 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:35:29,458 ====>  Preparing: select * from tb_idcard where id=?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:35:29,458 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:35:29,462 <====      Total: 1 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:35:29,465 <==      Total: 1 (BaseJdbcLogger.java:159) 
    Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))

#### 1.5.2 嵌套结果方式 ####

##### 1.5.2.1 PersonMapper.java 添加 #####

public Person findPersonByIdWithNestedResults(Integer id);

##### 1.5.2.2 PersonMapper.xml添加 #####

<resultMap id="IdCardWithPersonResult2" type="Person">
            <id property="id" column="id"></id>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            <result property="sex" column="sex"/>
    
            <association property="card" javaType="IdCard">
                <id property="id" column="card_id"></id>
                <result property="code" column="code"></result>
            </association>
        </resultMap>
        <select id="findPersonByIdWithNestedResults" parameterType="Integer" resultMap="IdCardWithPersonResult">
            select p.*, idcard.code
            from tb_person p, tb_idcard idcard
            where p.card_id = idcard.id and p.id = #{id}
        </select>

##### 1.5.2.3 PersonTest.java添加 #####

@Test
        public void testFindPersonByIdWithNestedResults(){
        
            SqlSession session = MyBatisUtil.openSession();
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            Person person = mapper.findPersonByIdWithNestedResults(1);
            System.out.println(person);
            session.close();
        }

##### 1.5.2.4 测试结果 #####

DEBUG 04-21 17:31:48,431 ==>  Preparing: select p.*, idcard.code from tb_person p, tb_idcard idcard where p.card_id = idcard.id and p.id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:31:48,479 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:31:48,529 ====>  Preparing: select * from tb_idcard where id=?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:31:48,530 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:31:48,533 <====      Total: 1 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 17:31:48,536 <==      Total: 1 (BaseJdbcLogger.java:159) 
    Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))

## 2.一对多查询 ##

问题：查询用户以及关联的订单，一个用户有多个订单

### 2.1 数据库准备 ###

USE mybatis;
    # 创建一个名称为tb_user的表
    CREATE TABLE tb_user (
      id int(32) PRIMARY KEY AUTO_INCREMENT,
      username varchar(32),
      address varchar(256)
     );
    # 插入3条数据
    INSERT INTO tb_user VALUES ('1', '小明', '北京');
    INSERT INTO tb_user VALUES ('2', '李华', '上海');
    INSERT INTO tb_user VALUES ('3', '李刚', '上海');
    # 创建一个名称为tb_orders的表
    CREATE TABLE tb_orders (
      id int(32) PRIMARY KEY AUTO_INCREMENT,
      number varchar(32) NOT NULL,
      user_id int(32) NOT NULL,
      FOREIGN KEY(user_id) REFERENCES tb_user(id)
    );
    # 插入3条数据
    INSERT INTO tb_orders VALUES ('1', '1000011', '1');
    INSERT INTO tb_orders VALUES ('2', '1000012', '1');
    INSERT INTO tb_orders VALUES ('3', '1000013', '2');

### 2.2 实体类 ###

#### 2.2.1 Orders.java ####

package com.biem.pojo;
    
    import lombok.*;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class Orders {
        
        private Integer id;
        private String number;
        private Integer userId;
    }

#### 2.2.2 Users.java ####

package com.biem.pojo;
    
    import lombok.*;
    
    import java.util.List;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class Users {
        
        private Integer id;
        private String username;
        private String address;
        private List<Orders> ordersList;
    }

### 2.3 用户配置接口 ###

#### 2.3.1 OrdersMapper.java ####

package com.biem.mapper;
    
    public interface OrdersMapper {
        
    }

#### 2.3.2 UsersMapper.java ####

package com.biem.mapper;
    
    public interface UsersMapper {
        
    }

### 2.4 用户配置文件 ###

#### 2.4.1 OrdersMapper.xml ####

#### 2.4.2 UsersMapper.xml ####

### 2.5 解决办法 ###

#### 2.5.1 嵌套查询方式 ####

##### 2.5.1.1 OrdersMapper.java #####

package com.biem.mapper;
    
    public interface OrdersMapper {
        
    	public List<Orders> selectOrders(Integer uid);
    }

##### 2.5.1.2 UsersMapper.java #####

package com.biem.mapper;
    
    import com.biem.pojo.User;
    
    public interface UserMapper {
        
        public User findUserById(int id);
    }

##### 2.5.1.3 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.biem.mapper.OrdersMapper">
        
        <select id="selectOrders" parameterType="Integer" resultType="Orders">
            select * from tb_orders where user_id=#{userId}
        </select>
    </mapper>

##### 2.5.1.4 UsersMapper.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.biem.mapper.UsersMapper">
        <resultMap id="usersWithOrders1" type="Users">
            <id property="id" column="id"></id>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
            
            
            <collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
        </resultMap>
    
        
        <select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
            select * from tb_user where id = #{id}
        </select>
    </mapper>

##### 2.5.1.5 UsersTest.java #####

import org.junit.Test;
    
    public class UsersTest {
        
    
        @Test
        public void testFindUserById(){
        
            SqlSession session = MyBatisUtil.openSession();
            UsersMapper mapper = session.getMapper(UsersMapper.class);
            Users user = mapper.findUserById(1);
            System.out.println("user = " + user);
            session.close();
        }
    }

##### 2.5.1.6 测试结果 #####

DEBUG 04-21 20:39:59,758 ==>  Preparing: select * from tb_user where id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 20:39:59,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 20:39:59,962 ====>  Preparing: select * from tb_orders where user_id=?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 20:39:59,963 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 20:39:59,967 <====      Total: 2 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 20:39:59,971 <==      Total: 1 (BaseJdbcLogger.java:159) 
    user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])

#### 2.5.2 嵌套结果方式 ####

##### 2.5.2.1 UsersMapper.java添加 #####

public Users findUserByIdWithNestedResults(Integer id);

##### 2.5.2.2 UsersMapper.xml添加 #####

<select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
            select u.*, o.id as orders_id , o.number, o.user_id
            from tb_user u, tb_orders o
            where u.id = o.user_id and u.id=#{id}
        </select>
        <resultMap id="UsersWithOrdersResult" type="Users">
            <id property="id" column="id"></id>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
            <collection property="ordersList" ofType="Orders">
                <id property="id" column="orders_id"></id>
                <result property="number" column="number"/>
                <result property="userId" column="user_id"/>
            </collection>
        </resultMap>

##### 2.5.2.3 UsersTest.java添加 #####

@Test
        public void testFindUserByIdWithNestedResults(){
        
            SqlSession session = MyBatisUtil.openSession();
            UsersMapper mapper = session.getMapper(UsersMapper.class);
            Users user = mapper.findUserByIdWithNestedResults(1);
            System.out.println("user = " + user);
            session.close();
        }

##### 2.5.2.4 测试结果 #####

DEBUG 04-21 21:13:51,214 ==>  Preparing: select u.*, o.id as orders_id , o.number, o.user_id from tb_user u, tb_orders o where u.id = o.user_id and u.id=?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 21:13:51,289 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 21:13:51,380 <==      Total: 2 (BaseJdbcLogger.java:159) 
    user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])

## 3.多对一查询 ##

解决user\_Id为某个值的所有orders的信息

### 3.1 环境配置 ###

2.1-2.4配置  
对于Users–>Orders 1对多  
对于Orders->Users 多对1

#### Orders.java添加属性User ####

### 3.2 解决方案 ###

#### 3.2.1 嵌套查询方式 ####

##### 3.2.1.1 UsersMapper.java 添加 #####

public Users getUsersById(Integer id);

##### 3.2.1.2 OrdersMapper.java 添加 #####

public List<Orders> getOrders1(Integer userId);

##### 3.2.1.3 UsersMappers.xml #####

<select id="getUsersById" resultType="users">
            select * from tb_user where `id` = #{id}
        </select>

##### 3.2.1.4 OrdersMapper.xml #####

<select id="getOrders1" resultMap="ordersResult1">
            select * from tb_orders where user_id = #{userId}
        </select>
        <resultMap id="ordersResult1" type="orders">
            <result column="id" property="id"/>
            <result column="number" property="number"/>
            
            <association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/>
        </resultMap>

##### 3.2.1.5 OrdersTest.java #####

package com.biem.test;
    
    import com.biem.mapper.OrdersMapper;
    import com.biem.pojo.Orders;
    import com.biem.util.MyBatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    
    public class OrdersTest {
        
    
        @Test
        public void testGetOrders1(){
        
            SqlSession session = MyBatisUtil.openSession();
            OrdersMapper mapper = session.getMapper(OrdersMapper.class);
            List<Orders> ordersList = mapper.getOrders1(1);
            System.out.println("ordersList = " + ordersList);
        }
    }

##### 3.2.1.6 测试结果 #####

DEBUG 04-21 22:11:09,143 ==>  Preparing: select * from tb_orders where user_id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,203 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,251 ====>  Preparing: select * from tb_user where `id` = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,252 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,254 <====      Total: 1 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,258 ====>  Preparing: select * from tb_user where `id` = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,259 ====> Parameters: 2(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,262 <====      Total: 1 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:11:09,262 <==      Total: 2 (BaseJdbcLogger.java:159) 
    ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=2, username=李华, address=上海, ordersList=null))]

#### 3.2.2 嵌套结果方式 ####

##### 3.2.2.1 OrderMapper.java 添加 #####

public List<Orders> getOrders1ByNestedResult(Integer userId);

##### 3.2.2.1 OrderMapper.xml添加 #####

<select id="getOrders1ByNestedResult" resultMap="ordersResult2">
            
            select o.id oid, o.number number, o.user_id uid , u.username username, u.address address
            from tb_orders o,tb_user u
            where o.user_id=u.id and o.user_id = #{userId}
        </select>
        <resultMap id="ordersResult2" type="orders">
            <result column="oid" property="id"/>
            <result column="number" property="number"/>
            <result column="uid" property="userId"/>
            <association property="users" javaType="users">
                
                <result property="id" column="uid"/>
                <result property="username" column="username"/>
                <result property="address" column="address"/>
            </association>
        </resultMap>

##### 3.2.2.3 OrderTest.java 添加 #####

@Test
        public void testGetOrders2(){
        
            SqlSession session = MyBatisUtil.openSession();
            OrdersMapper mapper = session.getMapper(OrdersMapper.class);
            List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);
            System.out.println("ordersList = " + ordersList);
        }

##### 3.2.2.4 测试结果 #####

DEBUG 04-21 22:36:45,304 ==>  Preparing: select o.id oid, o.number number, o.user_id uid , u.username username, u.address address from tb_orders o,tb_user u where o.user_id=u.id and o.user_id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:36:45,394 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 22:36:45,438 <==      Total: 2 (BaseJdbcLogger.java:159) 
    ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null))]

## 4 多对多查询 ##

### 4.1 数据库准备 ###

USE mybatis;
    # 创建一个名称为tb_product的表
    CREATE TABLE tb_product (
      id INT(32) PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(32),
      price DOUBLE 
     );
    # 插入3条数据
    INSERT INTO tb_product VALUES ('1', 'Java基础入门', '44.5');
    INSERT INTO tb_product VALUES ('2', 'Java Web程序开发入门', '38.5');
    INSERT INTO tb_product VALUES ('3', 'SSM框架整合实战', '50');
    # 创建一个名称为tb_ordersitem 的中间表
    CREATE TABLE tb_ordersitem (
        id INT(32) PRIMARY KEY AUTO_INCREMENT,
        orders_id INT(32),
        product_id INT(32),
        FOREIGN KEY(orders_id) REFERENCES tb_orders(id),
    FOREIGN KEY(product_id) REFERENCES tb_product(id)
    );
    # 插入3条数据
    INSERT INTO tb_ordersitem  VALUES ('1', '1', '1');
    INSERT INTO tb_ordersitem  VALUES ('2', '1', '3');
    INSERT INTO tb_ordersitem  VALUES ('3', '3', '3');
    # 订单表tb_orders由于在上面已经创建,所以这里不用再次创建

### 4.2 实体类 ###

#### 4.2.1 Orders.java ####

已经创建所以不用再次创建  
添加一个属性

private List<Product> productList;

变成：

package com.biem.pojo;
    
    import lombok.*;
    
    import java.util.List;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class Orders {
        
        private Integer id;
        private String number;
        private Integer userId;
        private Users users;
        private List<Product> productList;
    }

#### 4.2.2 Product.java ####

package com.biem.pojo;
    
    import lombok.*;
    
    import java.util.List;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @ToString
    public class Product {
        
        private Integer id;
        private String name;
        private Double price;
        private List<Orders> ordersList;
    }

### 4.3 用户配置接口 ###

#### 4.3.1 OrdersMapper.java ####

已经创建

#### 4.3.2 ProductMapper.java ####

package com.biem.mapper;
    
    public interface ProductMapper {
        
    }

### 4.4 用户配置文件 ###

#### 4.4.1 OrdersMapper.xml ####

已经创建

#### 4.4.2 ProductMapper.xml ####

### 4.5 解决方案 ###

#### 4.5.1 嵌套查询方式 ####

##### 4.5.1.1 OrdersMapper.java 添加 #####

public List<Orders> findOrdersWithProduct(Integer id);

##### 4.5.1.2 ProductMapper.java 添加 #####

public List<Product> findProductById(Integer id);

##### 4.5.1.3 OrdersMapper.xml 添加 #####

<select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
            select * from tb_orders where id = #{id}
        </select>
        <resultMap id="OrdersWithProductResult" type="Orders">
            <id property="id" column="id"></id>
            <result property="number" column="number"></result>
            <collection property="productList" column="id" ofType="Product" select="com.biem.mapper.ProductMapper.findProductById">
            </collection>
        </resultMap>

##### 4.5.1.4 ProductMapper.xml 添加 #####

<select id="findProductById" parameterType="Integer" resultType="Product">
            select * from tb_product where id in(
                select product_id from tb_ordersitem where orders_id = #{id}
                )
        </select>

##### 4.5.1.5 OrderTest.java添加 #####

@Test
        public void testFindOrdersWithProduct(){
        
            SqlSession session = MyBatisUtil.openSession();
            OrdersMapper mapper = session.getMapper(OrdersMapper.class);
            List<Orders> ordersList = mapper.findOrdersWithProduct(1);
            System.out.println("ordersList = " + ordersList);
        }

##### 4.5.1.6 测试结果 #####

DEBUG 04-21 23:21:46,851 ==>  Preparing: select * from tb_orders where id = ?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:21:46,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:21:46,959 ====>  Preparing: select * from tb_product where id in( select product_id from tb_ordersitem where orders_id = ? )  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:21:46,960 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:21:46,966 <====      Total: 2 (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:21:46,970 <==      Total: 1 (BaseJdbcLogger.java:159) 
    ordersList = [Orders(id=1, number=1000011, userId=1, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]

#### 4.5.2 嵌套结果查询 ####

##### 4.5.2.1 OrderMapper.java 添加 #####

public List<Orders> findOrdersWithProductByNestedResult(Integer id);

##### 4.5.2.2 OrdersMapper.xml 添加 #####

<select id="findOrdersWithProductByNestedResult" parameterType="Integer" resultMap="OrdersWithProductResult2">
            select o.*, p.id as pid, p.name, p.price
            from tb_orders o, tb_product p, tb_ordersitem oi
            where oi.orders_id=o.id and oi.product_id=p.id and o.id=#{id}
        </select>
        <resultMap id="OrdersWithProductResult2" type="Orders">
            <id property="id" column="id"></id>
            <result property="number" column="number"></result>
            <collection property="productList" ofType="product">
                <id property="id" column="pid"/>
                <result property="name" column="name"/>
                <result property="price" column="price"/>
            </collection>
        </resultMap>

##### 4.5.2.3 OrderTest.java 添加 #####

@Test
        public void findOrdersWithProductByNestedResult(){
        
            SqlSession session = MyBatisUtil.openSession();
            OrdersMapper mapper = session.getMapper(OrdersMapper.class);
            List<Orders> ordersList = mapper.findOrdersWithProductByNestedResult(1);
            System.out.println("ordersList = " + ordersList);
        }

##### 4.5.2.4 测试结果 #####

DEBUG 04-21 23:38:47,365 ==>  Preparing: select o.*, p.id as pid, p.name, p.price from tb_orders o, tb_product p, tb_ordersitem oi where oi.orders_id=o.id and oi.product_id=p.id and o.id=?  (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:38:47,438 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
    DEBUG 04-21 23:38:47,485 <==      Total: 2 (BaseJdbcLogger.java:159) 
    ordersList = [Orders(id=1, number=1000011, userId=null, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]