MyBatis复杂查询——一对一、一对多

目录

（一）复杂查询：1对1关系

【任务】数据库里有学生表(student)和学生证信息表(student_card)，表结构如下所示，要求使用MyBatis框架查询所有的学生信息以及每位学生的学生证信息

解决方案1：关联查询实现

定义实体类

定义接口

配置SQL并完成数据映射

执行SQL

解决方案2：嵌套查询实现

定义实体类和接口

配置SQL并完成数据映射

执行SQL

（二）复杂查询：1对多关系

【任务】数据库里有客户表(customer)和订单表(orders)，表结构如下所示，要求用MyBatis框架查询所有的客户信息以及每位客户的订单信息

解决方案1：关联查询实现

定义实体类

定义接口

配置SQL并完成数据映射

执行SQL

解决方案2：嵌套查询实现

（一）复杂查询：1对1关系

在前面的课程中，我们已经学习了使用MyBatis框架实现单表的数据查询。在实际的开发中，我们遇到的需求更多的是需要多表进行关联查询获取需要的数据。

【任务】数据库里有学生表(student)和学生证信息表(student_card)，表结构如下所示，要求使用MyBatis框架查询所有的学生信息以及每位学生的学生证信息

学生表（student）

学生证信息表(student_card)

【分析】

• 题目中要求我们查出所有的学生以及学生证信息，因此需要涉及两张表的关联查询
• 通过表结构分析，学生表和学生证表之间是1对1的关系，拥有相同的主键stu_id。
• 两表关联查询的SQL语句如下：

select stu.*,
card.stu_id as card_stu_id,card.class_info,card.major 
from student stu 
LEFT JOIN student_card card 
on stu.stu_id=card.stu_id

• 查询结果如下

【实现步骤】

1. 定义实体类
2. 定义接口
3. 配置SQL并完成数据映射
4. 执行SQL

解决方案1：关联查询实现

定义实体类

基于表结构创建两个实体类：学生类（Student）和学生证类（StudentCard），因为学生实体和学生证实体是1对1的关系，所以在学生类中加入一个学生证类型的属性，表示一个学生拥有一个学生证。

@Data
//学生证实体类
public class StudentCard {
    private int stuId;
    private String classInfo;
    private String major;
}

//学生实体类
@Data
public class Student {
    private int stuId;
    private String stuNo;
    private String stuName;
    //一个学生拥有一个学生证，体现1对1关系
    private StudentCard studentCard;
}

定义接口

在com.cg.mapper下新建一个接口StudentMapper，接口中加入方法

public interface StudentMapper {
    /**
     * 查询学生信息（包含学生证）
     * @return 学生信息列表
     */
    List<Student> selectStudents();
}

配置SQL并完成数据映射

在src/main/resources/mapper下新建一个SQL映射文件StudentMapper.xml，完成SQL配置：

<?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.cg.mapper.StudentMapper">
    <!--定义查询结果和Student实体类映射关系-->
    <resultMap id="studentCardMap" type="Student">
        <!-- 先设置Student自身属性和字段的对应关系 -->
        <id column="stu_id" property="stuId"/>
        <result column="stu_no" property="stuNo"/>
        <result column="stu_name" property="stuName"/>
        <!-- 使用association标签配置“一对一”关联关系，也就是实体类型属性的映射关系 -->
        <!-- property属性：实体类型属性名 -->
        <!-- javaType属性：实体类型属性全类名 -->
        <association property="studentCard" javaType="StudentCard">
           <!-- 配置StudentCard类的属性和字段的对应关系 -->
            <id column="card_stu_id" property="stuId"/>
            <result column="class_info" property="classInfo"/>
            <result column="major" property="major"/>
        </association>
    </resultMap>
    <select id="selectStudents" resultMap="studentCardMap">
        select stu.*,
               card.stu_id as card_stu_id,card.class_info,card.major
        from student stu
        LEFT JOIN student_card card
        on stu.stu_id=card.stu_id
    </select>
</mapper>

注：

• 一定要记得通过namespace关联接口
• 使用association标签配置“一对一”关联关系，也就是实体类型属性的映射关系
• 新建的SQL映射文件需要在全局配置文件中配置地址

<!--SQL映射文件地址配置-->
    <mappers>
        <mapper resource="mapper/StudentMapper.xml" />
    </mappers>

• 配置优化：在以上配置中，很多属性名称和列都是符合驼峰命名规范的，这些属性和列的映射关系可以省略不写，但是需要在全局配置文件（mybatis-config.xml）中配置如下内容

<settings>
        <!--配置控制台日志输出-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰式命名自动映射驼峰式命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--开启自动映射-->
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>

开启了MyBatis自动映射之后，<resultMap>标签的配置可以简化为如下：

<!--定义查询结果和Student实体类映射关系-->
    <resultMap id="studentCardMap" type="Student">
        <!--通过association元素定义实体类型属性的映射关系-->
        <association property="studentCard" javaType="StudentCard">
            <id column="card_stu_id" property="stuId"/>
        </association>
    </resultMap>

执行SQL

在测试方法中调用MyBatis API执行SQL，完成数据查询。

    @Test
    public void test1() throws IOException {
        //1.加载MyBatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        //2.获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //3.获取SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4.执行SQL配置
        StudentMapper studentMapper= sqlSession.getMapper(StudentMapper.class);
        List<Student> students = studentMapper.selectStudents();
        for (Student student : students) {
            System.out.println(student);
        }
        //6.关闭数据库会话
        sqlSession.close();
    }

控制台打印结果如下：

解决方案2：嵌套查询实现

【实现原理】嵌套查询就是将原来多表查询中的关联查询语句拆成单个表的查询，再使用MyBatis的语法嵌套在一起。结合本任务，首先执行主查询“select * from student”，查询出所有的学生信息列表，然后遍历每一条学生记录，根据学生信息的主键列（stu_id）的值执行子查询“select * from student_card where stu_id=?”查询出每位学生的学生证信息，然后将查询出的学生和学生证数据映射到到Student实体类对象的属性中完成数据映射。

定义实体类和接口

实体类和接口的设计内容和解决方案1是一致的。

配置SQL并完成数据映射

结合嵌套查询的实现原理可知，我们需要完成如下两个SQL配置（主查询和子查询），然后将他们关联起来。

1. 主查询：select * from student
2. 子查询：select * from student_card where stu_id=?

首先子查询的配置如下,子查询的查询结果需要和StudentCard建立映射关系。

<!--定义映射关系-->
    <resultMap id="cardMap" type="StudentCard">
        <id column="stu_id" property="stuId"/>
        <result column="class_info" property="classInfo"/>
        <result column="major" property="major"/>
    </resultMap>
    <!--子查询配置，根据stuId查询学生证信息并映射到实体类（StudentCard）上-->
    <select id="selectCardByStuId" resultMap="cardMap">
        select * from student_card where stu_id=#{stuId}
    </select>

然后配置主查询，在主查询映射关系配置中，需要在<association>标签中，通过select属性关联要执行的子查询的SQL配置id，通过column属性指定子查询所需参数的来源字段，来源于主查询结果中的stu_id字段的值。

<!--定义映射关系-->
    <resultMap id="studentCardMap" type="Student">
        <!-- 先设置Student自身属性和字段的对应关系 -->
        <id column="stu_id" property="stuId"/>
        <result column="stu_no" property="stuNo"/>
        <result column="stu_name" property="stuName"/>
        <!-- 使用association标签配置实体类型属性的映射关系
             通过select关联子查询SQL，通过column指定子查询参数来源字段-->
        <association property="studentCard"
                     javaType="StudentCard"
                     select="selectCardByStuId"
                     column="stu_id"></association>
    </resultMap>
    <!--主查询配置，将查询结果映射到Student实体类上-->
    <select id="selectStudents" resultMap="studentCardMap">
        select * from student
    </select>

执行SQL

调用MyBatis API执行接口方法，代码和解决方案1是一样的，控制台打印内容如下，从打印的内容可以看到，MyBatis底层首先执行了一次主查询SQL获取学生信息列表，然后遍历查询结果的每一条记录，根据记录stu_id字段的值执行子查询SQL获取学生证信息，从而完成数据的映射，因为有4个学生记录，因此嵌套查询共执行了1+4次select查询。

==>  Preparing: select * from student
==> Parameters: 
<==    Columns: stu_id, stu_no, stu_name
<==        Row: 5, 189000101, 张哲
====>  Preparing: select * from student_card where stu_id=?
====> Parameters: 5(Integer)
<====    Columns: stu_id, class_info, major
<====        Row: 5, 18级软工1班, 软件工程
<====      Total: 1
<==        Row: 6, 189000102, 王霜
====>  Preparing: select * from student_card where stu_id=?
====> Parameters: 6(Integer)
<====    Columns: stu_id, class_info, major
<====        Row: 6, 18级软工2班, 数字媒体技术
<====      Total: 1
<==        Row: 7, 189000103, 赵锋
====>  Preparing: select * from student_card where stu_id=?
====> Parameters: 7(Integer)
<====    Columns: stu_id, class_info, major
<====        Row: 7, 18级软工3班, 软件工程
<====      Total: 1
<==        Row: 8, 189000104, 李娜
====>  Preparing: select * from student_card where stu_id=?
====> Parameters: 8(Integer)
<====      Total: 0
<==      Total: 4
Student(stuId=5, stuNo=189000101, stuName=张哲, studentCard=StudentCard(stuId=5, classInfo=18级软工1班, major=软件工程))
Student(stuId=6, stuNo=189000102, stuName=王霜, studentCard=StudentCard(stuId=6, classInfo=18级软工2班, major=数字媒体技术))
Student(stuId=7, stuNo=189000103, stuName=赵锋, studentCard=StudentCard(stuId=7, classInfo=18级软工3班, major=软件工程))
Student(stuId=8, stuNo=189000104, stuName=李娜, studentCard=null)

（二）复杂查询：1对多关系

【任务】数据库里有客户表(customer)和订单表(orders)，表结构如下所示，要求用MyBatis框架查询所有的客户信息以及每位客户的订单信息

客户表（customer）

订单表（orders）

【分析】

• 题目中要求我们查出所有的客户以及客户的订单信息，因此需要涉及两张表的关联查询
• 通过表结构分析，订单表通过外键customer_id与客户表关联，一个客户可以拥有多个订单，因此客户与订单是1对多的关系
• 两表关联查询的SQL语句如下：

select c.*,
		o.id as order_id,o.order_no,o.price,o.customer_id
    from customer c
    left join orders o 
    on c.id=o.customer_id

【实现步骤】

1. 定义实体类
2. 定义接口
3. 配置SQL并完成数据映射
4. 执行SQL

解决方案1：关联查询实现

定义实体类

基于表结构创建两个实体类：客户类（Customer）和订单类（Order），因为客户实体和订单实体是1对多的关系，所以在客户类中加入订单类型的集合（List<Order>）属性，表示一个客户拥有多个订单。

@Data
//订单实体类
public class Order {
    private int id;
    private String orderNo;
    private BigDecimal price;
    private int customerId;
}

//客户实体类
@Data
public class Customer {
    private int id;
    private String account;
    private String username;
    //一个客户拥有多个订单，体现1对多关系
    private List<Order> orders;
}

定义接口

在com.cg.mapper下新建一个接口CustomerMapper，接口中加入方法

public interface CustomerMapper {
    /**
     * 查询所有的客户列表（包含客户的订单列表）
     * @return 客户列表
     */
    List<Customer> selectCustomers();
}

配置SQL并完成数据映射

在src/main/resources/mapper下新建一个SQL映射文件CustomerMapper.xml，完成SQL配置：

<?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.cg.mapper.CustomerMapper">
    <!--定义查询结果和Customer实体类映射关系-->
    <resultMap id="customerOrderMap" type="Customer">
        <!-- 先设置Customer自身属性和字段的对应关系 -->
        <id column="id" property="id"/>
        <result column="account" property="account"/>
        <result column="username" property="username"/>
        <!-- 使用collection标签配置“一对多”关联关系，也就是集合类型属性的映射关系 -->
        <!-- property属性：集合类型属性名 -->
        <!-- ofType属性：集合中的实体全类名 -->
        <collection property="orders" ofType="Order">
            <!-- 配置Order实体类的属性和字段的对应关系 -->
            <id column="order_id" property="id"/>
            <result column="order_no" property="orderNo"/>
            <result column="price" property="price"/>
            <result column="customer_id" property="customerId"/>
        </collection>
    </resultMap>
    <!--关联查询SQL配置-->
    <select id="selectCustomers" resultMap="customerOrderMap">
        select c.*,
               o.id as order_id,o.order_no,o.price,o.customer_id
        from customer c
        left join orders o
        on c.id=o.customer_id
    </select>
</mapper>

注：

• 一定要记得通过namespace关联接口
• 使用collection标签配置“一对多”关联关系，也就是集合类型属性的映射关系
• 新建的SQL映射文件需要在全局配置文件中配置地址

<!--SQL映射文件地址配置-->
    <mappers>
        <mapper resource="mapper/CustomerMapper.xml" />
    </mappers>

执行SQL

在测试方法中调用MyBatis API执行SQL，完成数据查询。

@Test
    public void test2() throws IOException {
        //1.加载MyBatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        //2.获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //3.获取SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4.执行SQL配置
        CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customers= customerMapper.selectCustomers();
        //5.处理结果
        for (Customer customer : customers) {
            System.out.println(customer);
        }
        //6.关闭数据库会话
        sqlSession.close();
    }

控制台打印结果如下，可以看到客户刘丹拥有两个订单，客户李义拥有1个订单，客户王伟拥有两个订单，客户张庆没有订单。

==>  Preparing: select c.*, o.id as order_id,o.order_no,o.price,o.customer_id from customer c left join orders o on c.id=o.customer_id
==> Parameters: 
<==    Columns: id, account, username, order_id, order_no, price, customer_id
<==        Row: 1, 13996065424, 刘丹, 1, 10001, 234.00, 1
<==        Row: 2, 13996068885, 李义, 2, 10002, 115.00, 2
<==        Row: 1, 13996065424, 刘丹, 3, 10003, 214.00, 1
<==        Row: 4, 13996063954, 王伟, 4, 10004, 321.00, 4
<==        Row: 4, 13996063954, 王伟, 5, 10005, 115.00, 4
<==        Row: 3, 13996062222, 张庆, null, null, null, null
<==      Total: 6
Customer(id=1, account=13996065424, username=刘丹, orders=[Order(id=1, orderNo=10001, price=234.00, customerId=1), Order(id=3, orderNo=10003, price=214.00, customerId=1)])
Customer(id=2, account=13996068885, username=李义, orders=[Order(id=2, orderNo=10002, price=115.00, customerId=2)])
Customer(id=4, account=13996063954, username=王伟, orders=[Order(id=4, orderNo=10004, price=321.00, customerId=4), Order(id=5, orderNo=10005, price=115.00, customerId=4)])
Customer(id=3, account=13996062222, username=张庆, orders=[])

解决方案2：嵌套查询实现

【练习】参考1对1关系任务中的解决方案2中嵌套查询的实现原理，思考本任务如何使用嵌套查询来实现？

【提示】结合本任务，首先执行主查询“select * from customer”，查询出所有的客户信息列表，然后遍历每一条客户记录，根据客户信息的主键列（id）的值执行子查询“select * from orders where customer_id=?”查询出每位客户的订单列表，然后将查询出的客户以及订单数据映射到到Customer实体类对象的属性中完成数据映射。