数据库查询时，我们用的比较多的一般是一对一查询还有一对多查询。所谓一对一查询，就是查询对象关联的对象是唯一的，比如图书与作者，一般情况下，一个图书只有一个作者，这就是一对一查询。一对多查询，是指一个查询对象，关联多个对象，比如权限系统中，用户与角色的对应，一个用户可能属于一个角色，也可能属于多个角色，这就是一对多。

下面我们针对介绍一下在mybatis中如何实现一对一及一对多的查询。

创建项目并添加依赖

这块没什么好说的，主要添加依赖如下：

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

配置控制台输出sql

为了查询sql输出信息，我们配置一下输出日志信息，让sql语句在控制台输出

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: root
server:
  port: 8888
  compression:
    enabled: true
mybatis:
  type-aliases-package: net.xiangcaowuyu.mybatis.entity
  mapper-locations: classpath:mapper/*.xml
  configuration:
    cache-enabled: true
logging:
  level:
    net.xiangcaowuyu.mybatis.mapper: DEBUG

1.一对一查询

这里，我们以图书与作者的对应关系为例，约定一个图书只能有一个作者。

1.1 数据库表

图书对应数据库表为book，作者对应数据库表为author，创建表及预制表数据sql如下：

1.1.1 book表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 18/10/2020 01:32:16
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `aid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '《射雕英雄传》', 1);

SET FOREIGN_KEY_CHECKS = 1;

1.1.2 author表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 18/10/2020 01:32:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES (1, '张三', 35);

SET FOREIGN_KEY_CHECKS = 1;

1.2 创建实体

1.2.1 Author.java

/**
 * author
 * @author laughing
 */
@Data
public class Author implements Serializable {
    private Integer id;

    private String name;

    private Integer age;

    private static final long serialVersionUID = 1L;
}

1.2.2 Book.java

因为是图书（book）关联作者（author），所以我们需要在图书（book）实体类中，加入作者（author）属性，用于sql查询映射。

/**
 * book
 * @author laughing
 */
@Data
public class Book implements Serializable {
    private Integer id;

    private String name;

    private Author author;

    private static final long serialVersionUID = 1L;
}

1.3 创建mapper

1.3.1 BookMapper.java

public interface BookMapper {

    /**
     * 查询book
     *
     * @param id 主键
     * @return book
     */
    Book selectByPrimaryKey(Integer id);

}

1.4 创建xml

一对一关联查询的关键在于association关键字，这个节点中的内容，和 resultMap 一样，也是 id，result 等，在这个节点中，我们还可以继续描述一对一。

由于在实际项目中，每次返回的数据类型可能都会有差异，这就需要定义多个 resultMap，而这多个 resultMap 中，又有一部份属性是相同的，所以，我们可以将相同的部分抽出来，做成一个公共的模板，然后通过extends关键字，继承公共的模板。

<?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="net.xiangcaowuyu.mybatis.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Book">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
  </resultMap>
  <resultMap id="BookWithAuthor" type="net.xiangcaowuyu.mybatis.entity.Book" extends="BaseResultMap">
    <association property="author" javaType="net.xiangcaowuyu.mybatis.entity.Author">
      <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
      <id column="name" property="name"/>
      <id column="age" property="age"/>
    </association>
  </resultMap>
  <sql id="Base_Column_List">
    id, `name`, aid
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BookWithAuthor">
    select 
    <include refid="Base_Column_List" />
    from book
    where id = #{id,jdbcType=INTEGER}
  </select>
</mapper>

1.5 创建服务层

为了节省篇幅，我们这里就省略掉接口代码，具体服务层代码如下：

/**
 * @author laughing
 * @date 2020-10-16
 */
@Service
public class BookServiceImpl implements BookService {

    @Resource
    BookMapper bookMapper;

    /**
     * 查询book
     *
     * @param id 主键
     * @return book
     */
    @Override
    public Book selectByPrimaryKey(Integer id) {
        return bookMapper.selectByPrimaryKey(id);
    }
}

1.6 创建controller

创建一个rest接口，测试我们的查询

/**
 * @author laughing
 * @date 2020-10-16
 */
@RestController
@RequestMapping("book")
public class BookController {

    private final BookService bookService;

    public BookController(BookService bookService){
        this.bookService=bookService;
    }

    /**
     * 查询book
     *
     * @param id 主键
     * @return book
     */
    @GetMapping("selectByPrimaryKey/{id}")
    public Book selectByPrimaryKey(@PathVariable("id") Integer id) {
        return bookService.selectByPrimaryKey(id);
    }

}

2.一对多查询

一对多查询与一对一查询类似，区别主要在于实体的关联关系及xml文件的配置。

我们以权限系统常见的用户、角色对应关系为例。约定一个用户可以关联多个角色。

2.1 数据库表

我们数据库有三张表，user用户存储用户信息，role用于存储角色信息，user_role用于存储用户与角色的关联关系，相关表结构及数据如下

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 18/10/2020 02:02:01
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nameZh` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, 'dba', '数据库管理员');
INSERT INTO `role` VALUES (2, 'admin', '系统管理员');
INSERT INTO `role` VALUES (3, 'user', '用户');

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `enabled` tinyint(1) NULL DEFAULT NULL,
  `locked` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'root', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0);
INSERT INTO `user` VALUES (2, 'admin', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0);
INSERT INTO `user` VALUES (3, 'sang', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0);

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NULL DEFAULT NULL,
  `rid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 1, 1);
INSERT INTO `user_role` VALUES (2, 1, 2);
INSERT INTO `user_role` VALUES (3, 2, 2);
INSERT INTO `user_role` VALUES (4, 3, 3);

SET FOREIGN_KEY_CHECKS = 1;

1.2 创建实体

1.2.1 User.java

/**
 * user
 *
 * @author laughing
 */
@Data
@JsonIgnoreProperties(value = { "handler"})
public class User implements Serializable {
    private Integer id;

    private String username;

    private String password;

    private Boolean enabled;

    private Boolean locked;

    private List<Role> roleList;

    private static final long serialVersionUID = 1L;
}

1.2.2 Role.java

/**
 * role
 * @author laughing
 */
@Data
@JsonIgnoreProperties(value = { "handler"})
public class Role implements Serializable {
    private Integer id;

    private String name;

    private String namezh;

    private static final long serialVersionUID = 1L;
}

1.3 创建mapper

public interface UserMapper {

    /**
     * 根据Id查找
     * @param id
     * @return
     */
    User selectByPrimaryKey(Integer id);

    /**
     * 根据用户查找角色列表
     * @param uid
     * @return
     */
    List<Role> getRolesByUid(@Param("uid") Integer uid);
}

1.4 创建xml

一对一的查询通过association，一对多的查询与此类似，只是通过collection关键字，替换association关键字。

1.4.1 方式1

<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="enabled" jdbcType="BOOLEAN" property="enabled"/>
        <result column="locked" jdbcType="BOOLEAN" property="locked"/>
        <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role">
            <result column="rid" property="id" javaType="java.lang.Integer"/>
            <result column="rname" property="name" javaType="java.lang.String"/>
            <result column="rnameZh" property="namezh" javaType="java.lang.String"/>
        </collection>
    </resultMap>
    <sql id="Base_Column_List">
        id, username, `password`, enabled, locked
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        user.*,role.id as rid,role.name as rname,role.nameZh as rnameZh
        from user,role,user_role
        where user.id = user_role.uid and role.id = user_role.rid and user.id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

1.4.2 懒加载方式

通过fetchType="lazy"实现懒加载

<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="enabled" jdbcType="BOOLEAN" property="enabled"/>
        <result column="locked" jdbcType="BOOLEAN" property="locked"/>
        <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role" column="id" select="getRolesByUid" fetchType="lazy">
        </collection>
    </resultMap>
    <sql id="Base_Column_List">
        id, username, `password`, enabled, locked
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where user.id = #{id,jdbcType=INTEGER}
    </select>
    <select id="getRolesByUid" resultType="net.xiangcaowuyu.mybatis.entity.Role">
        SELECT r.* FROM role r,user_role ur WHERE r.`id`=ur.`rid` AND ur.`uid`=#{uid}
    </select>
</mapper>

1.5 创建服务层

服务层我们依然省略掉接口代码

/**
 * @author laughing
 * @date 2020-10-17
 */
@Service
public class UserServiceImpl implements UserService {

    @Resource
    UserMapper userMapper;

    /**
     * 根据Id查找
     *
     * @param id
     * @return
     */
    @Override
    public User selectByPrimaryKey(Integer id) {
        return userMapper.selectByPrimaryKey(id);
    }
}

1.6 创建controller

创建rest接口，测试代码

/**
 * @author laughing
 * @date 2020-10-17
 */
@RestController
@RequestMapping("user")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService){
        this.userService=userService;
    }

    @RequestMapping("selectByPrimaryKey/{id}")
    public User selectByPrimaryKey(@PathVariable("id") Integer id) {
        return userService.selectByPrimaryKey(id);
    }

}