[华为云在线课程][MyBatis入门][四][查询结果高级映射][学习笔记]

举报
John2021 发表于 2022/10/30 14:08:19 2022/10/30
【摘要】 1.查询结果一对一映射实际开发中，经常会遇到一对一查询，一对多查询等。例如：每本书都有一个作者，作者都有自己的属性，定义两个实体类。package org.example.mybatis02.model;public class Book { private Integer id; private String name; private Author author; ...

1.查询结果一对一映射

实际开发中，经常会遇到一对一查询，一对多查询等。例如：每本书都有一个作者，作者都有自己的属性，定义两个实体类。

package org.example.mybatis02.model;

public class Book {
    private Integer id;
    private String name;
    private Author author;

    public Book() {
    }

    public Book(Integer id, String name, Author author) {
        this.id = id;
        this.name = name;
        this.author = author;
    }

    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 Author getAuthor() {
        return author;
    }

    public void setAuthor(Author author) {
        this.author = author;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author=" + author +
                '}';
    }
}

package org.example.mybatis02.model;

public class Author {
    private Integer id;
    private String name;
    private Integer age;

    public Author() {
    }

    public Author(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    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 Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Author{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

在数据库里新建两张表，author表和book表

drop table if exists `author`;

create table `author`(
    `id` int(11) not null auto_increment,
    `name` varchar(255) default null,
    `age` int(11) default null,
    primary key (`id`)
)engine =InnoDB default charset =utf8mb4;

drop table if exists `book`;

create table `book`(
    `id` int(11) not null auto_increment,
    `name` varchar(255) default null,
    `aid` int(11) default null,
    primary key (`id`)
)engine =InnoDB default charset =utf8mb4;

表创建完成后，我们新建一个BookMapper

package org.example.mybatis02.mapper;

import org.example.mybatis02.model.Book;

public interface BookMapper {
    Book getBookById(Integer id);
}

BookMapper中定义一个查询Book的方法，但我希望查出来Book的同时也能查出它的Author。接下来定义一个BookMapper.xml：

<mappers>
    <mapper resource="org/example/mybatis02/mapper/BookMapper.xml"/>
</mappers>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/schema/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis02.mapper.BookMapper">
    <resultMap id="BookWithAuthor" type="org.example.mybatis02.model.Book">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="author" javaType="org.example.mybatis02.model.Author">
            <id column="aid" property="id"/>
            <result column="aname" property="name"/>
            <result column="aage" property="age"/>
        </association>
    </resultMap>

    <select id="getBookById" resultMap="BookWithAuthor">
        SELECT b.*,a.`age` AS aage,a.`id` AS aid,a.`name` AS aname
        FROM book b,author a
        WHERE b.`aid`=a.`id` AND b.`id`=#{id}
    </select>
</mapper>

在这个查询SQL中，要做好一对一查询，然后返回值一定要定义成resultMap。然后在resultMap中来定义查询结果的映射关系。
其中association节点用来描述一对一的关系。这个节点中的内容和resultMap一样，也是id，result等。
由于在实际项目中，每次返回的数据类型可能都会有差异，这就需要定义多个resultMap，由于有一部分属性是相同的，所以我们可以把公共部分抽取出来作为一个公共resultMap，被其他resultMap继承，下面是经过优化的BookMapper：

<resultMap id="BaseResultMap" type="org.example.mybatis02.model.Book">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>
<resultMap id="BookWithAuthor" type="org.example.mybatis02.model.Book" extends="BaseResultMap">
    <association property="author" javaType="org.example.mybatis02.model.Author">
        <id column="aid" property="id"/>
        <result column="aname" property="name"/>
        <result column="aage" property="age"/>
    </association>
</resultMap>
<select id="getBookById" resultMap="BookWithAuthor">
    SELECT b.*,a.`age` AS aage,a.`id` AS aid,a.`name` AS aname
    FROM book b,author a
    WHERE b.`aid`=a.`id` AND b.`id`=#{id}
</select>

package org.example;

import org.apache.ibatis.session.SqlSession;
import org.example.mybatis01.utils.SqlSessionFactoryUtils;
import org.example.mybatis02.mapper.BookMapper;
import org.example.mybatis02.model.Book;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class TestBookMapper01 {
    private SqlSession sqlSession;
    @Before
    public void before() {
        sqlSession= SqlSessionFactoryUtils.getInstance().openSession();
    }

    @Test
    public void getAllBooks() {
//        List<Book> selectList = sqlSession.selectList("org.example.mybatis02.mapper.BookMapper.getBookById",1);
//        System.out.println(selectList);
        BookMapper bookMapper = sqlSession.getMapper(BookMapper.class);
        Book mapperBookById = bookMapper.getBookById(1);
        System.out.println(mapperBookById);
        /*
        * <==      Total: 1
            [Book{id=1, name='张三日记', author=Author{id=1, name='张三', age=10}}]
        * */
    }

    @After
    public void after() {
        sqlSession.close();
    }
}

2.查询结果一对一映射【懒加载】

上面这种加载方式，是一次性读取所有数据。然后在resultMap中映射。如果一对一的属性使用不是很频繁只是偶尔用一下，这种情况下，我们可以使用懒加载这种方式。
懒加载，就是先查询book，查询book的过程中，不去查询author，当用户第一次调用book中的author属性后，再去查询author。下面是演示例子。
首先定义一个book的查询方法：

Book getBookById2(Integer id);
Author getAuthorById(Integer id);

接下来在XML中定义SQL：

<resultMap id="BaseResultMap" type="org.example.mybatis02.model.Book">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>
<resultMap id="BookWithAuthor2" type="org.example.mybatis02.model.Book" extends="BaseResultMap">
    <association property="author" javaType="org.example.mybatis02.model.Author"
        select="org.example.mybatis02.mapper.BookMapper.getAuthorById" column="aid" fetchType="lazy"/>
</resultMap>
<select id="getBookById2" resultMap="BookWithAuthor2">
    select * from book where id=#{id};
</select>
<select id="getAuthorById" resultType="org.example.mybatis02.model.Author">
    select * from author where id=#{aid};
</select>

定义association的时候，不直接指定映射的字段，而是指定要执行的方法，通过select字段来指定，column表示执行方法时传递的参数字段，最后的fetchType表示开启懒加载。
要使用懒加载，还需在全局配置中开启：

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

@Test
public void getAllBooks() {
    BookMapper bookMapper = sqlSession.getMapper(BookMapper.class);
    Book mapperBookById = bookMapper.getBookById2(1);
    System.out.println(mapperBookById);
    /*
    * <==      Total: 1
        [Book{id=1, name='张三日记', author=Author{id=1, name='张三', age=10}}]
    * */
}

3.查询结果一对多映射

一对多查询也是非常经典的场景。比如用户和角色的关系，一个用户可以具备多个角色。首先要准备三张表来演示，分别是role表，user表，user_role表。

drop table if exists `role`;
create table `role`(
    `id` int(11) not null auto_increment,
    `name` varchar(32) default null,
    `nameZh` varchar(32) default null,
    primary key (`id`)
)engine =InnoDB auto_increment=4 default charset =utf8mb4;
insert into `role`
values ('1', 'dba', '数据库管理员'),
       ('2', 'admin', '系统管理员'),
       ('3', 'user', '用户');

drop table if exists `user1`;
create table `user1`(
    `id` int(11) not null auto_increment,
    `username` varchar(32) default null,
    `password` varchar(255) default null,
    `enabled` tinyint(1) default null,
    `locked` tinyint(1) default null,
    primary key (`id`)
)engine =InnoDB auto_increment=4 default charset =utf8mb4;

insert into `user1`
values ('1', 'root', '1234', '1', '0'),
       ('2', 'admin', '1234', '1', '0'),
       ('3', 'hello', '1234', '1', '0');

drop table if exists `user_role`;
create table `user_role`(
    `id` int(11) not null auto_increment,
    `uid` int(11) default null,
    `rid` int(11) default null,
    primary key (`id`)
)engine =InnoDB auto_increment=5 default charset =utf8mb4;

insert into `user_role`
values ('1','1','1'),
       ('2','1','2'),
       ('3','2','2'),
       ('4','3','3');
set foreign_key_checks =1;

这三个表有用户表，角色表以及用户角色关联表，其中用户角色关联表用来描述用户和角色之间的关系，他们是一对多的关系。然后创建两个实体类：

package org.example.mybatis03.model;

import java.util.List;

public class User {
    private Integer id;
    private String username;
    private String password;
    private List<Role> roles;

    public User() {
    }

    public User(Integer id, String username, String password, List<Role> roles) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.roles = roles;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", roles=" + roles +
                '}';
    }
}

package org.example.mybatis03.model;

public class Role {
    private Integer id;
    private String name;
    private String nameZh;

    public Role() {
    }

    public Role(Integer id, String name, String nameZh) {
        this.id = id;
        this.name = name;
        this.nameZh = nameZh;
    }

    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 String getNameZh() {
        return nameZh;
    }

    public void setNameZh(String nameZh) {
        this.nameZh = nameZh;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", nameZh='" + nameZh + '\'' +
                '}';
    }
}

接下来在Mapper定义一个根据id查用户的方法

package org.example.mybatis03.mapper;

import org.example.mybatis03.model.User;

public interface UserMapper {
    User getUserById(Integer id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/schema/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis03.mapper.UserMapper">
    <resultMap id="UserWithRole" type="org.example.mybatis03.model.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="roles" ofType="org.example.mybatis03.model.Role">
            <id property="id" column="rid"/>
            <result property="name" column="rname"/>
            <result property="nameZh" column="rnameZh"/>
        </collection>
    </resultMap>

    <select id="getUserById" resultMap="UserWithRole">
        SELECT u.*,r.`id` AS rid,r.`name` AS rname,r.`nameZh` AS rnameZh
        FROM user1 u,role r,user_role ur
        WHERE u.`id`=ur.`uid` AND ur.`rid`=r.`id` AND u.`id`=#{id}
    </select>
</mapper>

<mappers>
    <mapper resource="org/example/mybatis03/mapper/UserMapper.xml"/>
</mappers>

@Test
public void getAllUserInfo() {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User userById = userMapper.getUserById(3);
    System.out.println(userById);
    /*
    * <==      Total: 1
        User{id=3, username='hello', password='1234',
    *   roles=[Role{id=3, name='user', nameZh='用户'}]}
    * */
}

在resultMap中，通过collection节点来描述集合的映射关系。在映射时，会自动将一的一方数据集合并，多的一方放到集合中，就是靠id属性来实现这一点。

4.查询结果一对多映射【懒加载】

要实现懒加载的方式，首先提供一个角色查询的方法：

List<Role> getRolesByUid(Integer id);

在XML中编写懒加载方式：

<resultMap id="UserWithRole" type="org.example.mybatis03.model.User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <collection property="roles" select="org.example.mybatis03.mapper.UserMapper.getRolesByUid"
                column="id" fetchType="lazy"/>
</resultMap>
<select id="getUserById" resultMap="UserWithRole">
    SELECT *
    FROM user1
    WHERE id=#{id};
</select>
<select id="getRolesByUid" resultType="org.example.mybatis03.model.Role">
    SELECT r.*
    FROM role r,user_role ur
    WHERE r.`id`=ur.`rid` and ur.`uid`=#{id};
</select>

@Test
public void getUserById() {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User userById = userMapper.getUserById(1);
    System.out.println(userById);
    /*
    * <==      Total: 2
        User{id=1, username='root', password='1234', 
        roles=[Role{id=1, name='dba', nameZh='数据库管理员'}, 
        Role{id=2, name='admin', nameZh='系统管理员'}]}
    * */
}
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MyBatis
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入