欢送拜访我的GitHub

https://github.com/zq2599/blog_demos

内容：所有原创文章分类汇总及配套源码，波及Java、Docker、Kubernetes、DevOPS等；

本篇概览

  • 本文是《MyBatis高级实战》系列的第五篇，从多表获取数据是个常见的场景，个别有以下两种形式：
  • 联表查问：join操作，一次查问实现
  • 屡次查问：用第一次查问的后果作为条件，再做查问（MyBatis中叫做嵌套查问）
  • 本篇的内容就是学习MyBatis对上述两种查问的反对，全文由以下章节组成：
  1. 筹备数据；
  2. 本次实战的java工程
  3. 最简略的联表(两个表的数据保留在一个实体类的不同字段)；
  4. 一对一联表查问(两个表的数据别离保留在不同实体类，假如是A和B，A是B的成员变量)
  5. 一对一嵌套查问(两个表的数据别离保留在不同实体类，假如是A和B，A是B的成员变量)

源码下载

  1. 如果您不想编码，能够在GitHub下载所有源码，地址和链接信息如下表所示(https://github.com/zq2599/blo...：
名称链接备注
我的项目主页https://github.com/zq2599/blo...该我的项目在GitHub上的主页
git仓库地址(https)https://github.com/zq2599/blo...该我的项目源码的仓库地址，https协定
git仓库地址(ssh)git@github.com:zq2599/blog_demos.git该我的项目源码的仓库地址，ssh协定
  1. 这个git我的项目中有多个文件夹，本章的利用在<font color="blue">mybatis</font>文件夹下，如下图红框所示：

  1. mybatis是个父工程，外面无数个子工程，本篇的源码在<font color="blue">relatedoperation</font>子工程中，如下图红框所示：

筹备数据

  1. 本次实战，在名为mybatis的数据库中建设两个表(和后面几篇文章中的表构造截然不同)：user和log表；
  2. user表记录用户信息，非常简单，只有三个字段：主键、名称、年龄
  3. log表记录用户行为，四个字段：主键、用户id、行为形容、行为工夫
  4. user和log的关系如下图：

  1. 建表和增加数据的语句如下：
use mybatis;DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` int(32) NOT NULL AUTO_INCREMENT,  `name` varchar(32) NOT NULL,  `age` int(32) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `log`;CREATE TABLE `log` (  `id` int(32) NOT NULL AUTO_INCREMENT,  `user_id` int(32),  `action` varchar(255) NOT NULL,  `create_time` datetime not null,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;INSERT INTO mybatis.user (id, name, age) VALUES (3, 'tom', 11);INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (3, 3, 'read book', '2020-08-07 08:18:16');INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (4, 3, 'go to the cinema', '2020-09-02 20:00:00');INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (5, 3, 'have a meal', '2020-10-05 12:03:36');INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (6, 3, 'have a sleep', '2020-10-06 13:00:12');INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (7, 3, 'write', '2020-10-08 09:21:11');

本次实战的java工程

  1. 在父工程mybatis下新建子工程<font color="blue">relatedoperation</font>，pom.xml如下：
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">    <modelVersion>4.0.0</modelVersion>    <parent>        <groupId>com.bolingcavalry</groupId>        <artifactId>mybatis</artifactId>        <version>1.0-SNAPSHOT</version>        <relativePath>../pom.xml</relativePath>    </parent>    <groupId>com.bolingcavalry</groupId>    <artifactId>relatedoperation</artifactId>    <version>0.0.1-SNAPSHOT</version>    <name>relatedoperation</name>    <description>Demo project for Mybatis related operation in Spring Boot</description>    <properties>        <java.version>1.8</java.version>    </properties>    <dependencies>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>        </dependency>        <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>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <scope>runtime</scope>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>        <dependency>            <groupId>io.springfox</groupId>            <artifactId>springfox-swagger2</artifactId>        </dependency>        <!-- swagger-ui -->        <dependency>            <groupId>io.springfox</groupId>            <artifactId>springfox-swagger-ui</artifactId>        </dependency>        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid-spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <scope>test</scope>        </dependency>    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>            </plugin>        </plugins>    </build></project>
  1. 根本配置文件application.yml：
server:  port: 8080spring:  #1.JDBC数据源  datasource:    username: root    password: 123456    url: jdbc:mysql://192.168.50.43:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC    driver-class-name: com.mysql.cj.jdbc.Driver    #2.连接池配置    druid:      #初始化连接池的连贯数量 大小，最小，最大      initial-size: 5      min-idle: 5      max-active: 20      #配置获取连贯期待超时的工夫      max-wait: 60000      #配置距离多久才进行一次检测，检测须要敞开的闲暇连贯，单位是毫秒      time-between-eviction-runs-millis: 60000      # 配置一个连贯在池中最小生存的工夫，单位是毫秒      min-evictable-idle-time-millis: 30000      # 配置一个连贯在池中最大生存的工夫，单位是毫秒      max-evictable-idle-time-millis: 300000      validation-query: SELECT 1 FROM user      test-while-idle: true      test-on-borrow: true      test-on-return: false      # 是否缓存preparedStatement，也就是PSCache  官网倡议MySQL下倡议敞开   集体倡议如果想用SQL防火墙 倡议关上      pool-prepared-statements: true      max-pool-prepared-statement-per-connection-size: 20      # 配置监控统计拦挡的filters，去掉后监控界面sql无奈统计，'wall'用于防火墙      filters: stat,wall,slf4j      filter:        stat:          merge-sql: true          slow-sql-millis: 5000      #3.根底监控配置      web-stat-filter:        enabled: true        url-pattern: /*        #设置不统计哪些URL        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"        session-stat-enable: true        session-stat-max-count: 100      stat-view-servlet:        enabled: true        url-pattern: /druid/*        reset-enable: true        #设置监控页面的登录名和明码        login-username: admin        login-password: admin        allow: 127.0.0.1        #deny: 192.168.1.100# mybatis配置mybatis:  # 配置文件所在位置  config-location: classpath:mybatis-config.xml  # 映射文件所在位置  mapper-locations: classpath:mappers/*Mapper.xml# 日志配置logging:  level:    root: INFO    com:      bolingcavalry:        relatedoperation:          mapper: debug
  1. 再筹备名为<font color="blue">application-test.yml</font>的配置文件，这是执行单元测试时用到的，和application.yml的不同之处是<font color="red">spring.datasource.druid.web-stat-filter.enabled</font>配置设置成<font color="red">false</font>；
  2. mybatis的配置文件mybatis-config.xml如下：
<!DOCTYPE configuration        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>    <typeAliases>        <!-- 映射文件中的类不必写全门路了-->        <package name="com.bolingcavalry.relatedoperation.entity"/>    </typeAliases></configuration>
  1. 数据源配置类DruidConfig.java：
package com.bolingcavalry.relatedoperation;import com.alibaba.druid.pool.DruidDataSource;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Value;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;@Configurationpublic class DruidConfig {    private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);    @Value("${spring.datasource.url}")    private String dbUrl;    @Value("${spring.datasource.username}")    private String username;    @Value("${spring.datasource.password}")    private String password;    @Value("${spring.datasource.driver-class-name}")    private String driverClassName;    @Value("${spring.datasource.druid.initial-size}")    private int initialSize;    @Value("${spring.datasource.druid.max-active}")    private int maxActive;    @Value("${spring.datasource.druid.min-idle}")    private int minIdle;    @Value("${spring.datasource.druid.max-wait}")    private int maxWait;    @Value("${spring.datasource.druid.pool-prepared-statements}")    private boolean poolPreparedStatements;    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")    private int maxPoolPreparedStatementPerConnectionSize;    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")    private int timeBetweenEvictionRunsMillis;    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")    private int minEvictableIdleTimeMillis;    @Value("${spring.datasource.druid.max-evictable-idle-time-millis}")    private int maxEvictableIdleTimeMillis;    @Value("${spring.datasource.druid.validation-query}")    private String validationQuery;    @Value("${spring.datasource.druid.test-while-idle}")    private boolean testWhileIdle;    @Value("${spring.datasource.druid.test-on-borrow}")    private boolean testOnBorrow;    @Value("${spring.datasource.druid.test-on-return}")    private boolean testOnReturn;    @Value("${spring.datasource.druid.filters}")    private String filters;    @Value("{spring.datasource.druid.connection-properties}")    private String connectionProperties;    /**     * Druid 连接池配置     */    @Bean    public DruidDataSource dataSource() {        DruidDataSource datasource = new DruidDataSource();        datasource.setUrl(dbUrl);        datasource.setUsername(username);        datasource.setPassword(password);        datasource.setDriverClassName(driverClassName);        datasource.setInitialSize(initialSize);        datasource.setMinIdle(minIdle);        datasource.setMaxActive(maxActive);        datasource.setMaxWait(maxWait);        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);        datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);        datasource.setValidationQuery(validationQuery);        datasource.setTestWhileIdle(testWhileIdle);        datasource.setTestOnBorrow(testOnBorrow);        datasource.setTestOnReturn(testOnReturn);        datasource.setPoolPreparedStatements(poolPreparedStatements);        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);        try {            datasource.setFilters(filters);        } catch (Exception e) {            logger.error("druid configuration initialization filter", e);        }        datasource.setConnectionProperties(connectionProperties);        return datasource;    }}
  1. swagger配置类：
package com.bolingcavalry.relatedoperation;import springfox.documentation.service.Contact;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import springfox.documentation.builders.ApiInfoBuilder;import springfox.documentation.builders.PathSelectors;import springfox.documentation.builders.RequestHandlerSelectors;import springfox.documentation.service.ApiInfo;import springfox.documentation.service.Tag;import springfox.documentation.spi.DocumentationType;import springfox.documentation.spring.web.plugins.Docket;import springfox.documentation.swagger2.annotations.EnableSwagger2;@Configuration@EnableSwagger2public class SwaggerConfig {    @Bean    public Docket createRestApi() {        return new Docket(DocumentationType.SWAGGER_2)                .apiInfo(apiInfo())                .tags(new Tag("UserController", "用户服务"), new Tag("LogController", "日志服务"))                .select()                // 以后包门路                .apis(RequestHandlerSelectors.basePackage("com.bolingcavalry.relatedoperation.controller"))                .paths(PathSelectors.any())                .build();    }    //构建 api文档的详细信息函数,留神这里的注解援用的是哪个    private ApiInfo apiInfo() {        return new ApiInfoBuilder()                //页面题目                .title("MyBatis CURD操作")                //创建人                .contact(new Contact("程序员欣宸", "https://github.com/zq2599/blog_demos", "zq2599@gmail.com"))                //版本号                .version("1.0")                //形容                .description("API 形容")                .build();    }}
  1. springboot疏导类：
package com.bolingcavalry.relatedoperation;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication@MapperScan("com.bolingcavalry.relatedoperation.mapper")public class RelatedOperationApplication {    public static void main(String[] args) {        SpringApplication.run(RelatedOperationApplication.class, args);    }}
  1. 用户表的实体类：
package com.bolingcavalry.relatedoperation.entity;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.NoArgsConstructor;@Data@NoArgsConstructor@ApiModel(description = "用户实体类")public class User {    @ApiModelProperty(value = "用户ID")    private Integer id;    @ApiModelProperty(value = "用户名", required = true)    private String name;    @ApiModelProperty(value = "用户地址", required = false)    private Integer age;}
  1. 日志表的实体类：
package com.bolingcavalry.relatedoperation.entity;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.NoArgsConstructor;import java.sql.Date;@Data@NoArgsConstructor@ApiModel(description = "日志实体类")public class Log {    @ApiModelProperty(value = "日志ID")    private Integer id;    @ApiModelProperty(value = "用户ID")    private Integer userId;    @ApiModelProperty(value = "日志内容")    private String action;    @ApiModelProperty(value = "创立工夫")    private Date createTime;}
  • 以上就是本篇的筹备代码，接下来在此基础上实现各种多表关联查问

最简略的联表

  • 先实战的是最一般的联表，如下图所示，查问后果是名为LogExtend的实体类，这个类有5个字段，其中四个来自日志表log，一个来自用户表user：

  • 下图是开发步骤：

  1. 实体类LogExtend的源码如下，可见和Log相比多了个<font color="blue">userName</font>字段：
package com.bolingcavalry.relatedoperation.entity;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.NoArgsConstructor;@Data@NoArgsConstructor@ApiModel(description = "日志实体类(含用户表的字段)")public class LogExtend extends Log {    @ApiModelProperty(value = "用户名")    private String userName;}
  1. 新建log表对应的映射文件<font color="blue">LogMapper.xml</font>，如下所示，外面是通过<font color="blue">left join</font>语法执行的简略的联表查问，以及查问后果对应的resultMap定义：
<?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.bolingcavalry.relatedoperation.mapper.LogMapper">    <!--联表查问，返回log对象，该对象有个userName字段，值是user表的user_name字段-->    <select id="oneObjectSel" parameterType="int" resultMap="logExtendResultMap">        select l.id as id,               l.user_id as user_id,               l.action as action,               l.create_time as create_time,               u.name as user_name        from log as l                 left join user as u                           on l.user_id = u.id        where l.id = #{id}    </select>    <resultMap id="logExtendResultMap" type="logExtend">        <id property="id" column="id"/>        <result column="user_id" jdbcType="INTEGER" property="userId"/>        <result column="action" jdbcType="VARCHAR" property="action"/>        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>        <result column="user_name" jdbcType="VARCHAR" property="userName"/>    </resultMap></mapper>
  1. mapper接口代码：
package com.bolingcavalry.relatedoperation.mapper;import com.bolingcavalry.relatedoperation.entity.LogAssociateUser;import com.bolingcavalry.relatedoperation.entity.LogExtend;import org.springframework.stereotype.Repository;@Repositorypublic interface LogMapper {    LogExtend oneObjectSel(int id);}
  1. service层的代码在LogService.java文件中：
package com.bolingcavalry.relatedoperation.service;import com.bolingcavalry.relatedoperation.entity.LogAssociateUser;import com.bolingcavalry.relatedoperation.entity.LogExtend;import com.bolingcavalry.relatedoperation.mapper.LogMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;@Servicepublic class LogService {    @Autowired    LogMapper logMapper;    public LogExtend oneObjectSel(int id){        return logMapper.oneObjectSel(id);    }}
  1. controller层的代码在LogController.java文件中：
@RestController@RequestMapping("/log")@Api(tags = {"LogController"})public class LogController {    @Autowired    private LogService logService;    @ApiOperation(value = "依据ID查找日志记录，带userName字段，该字段通过联表查问实现", notes="依据ID查找日志记录，带userName字段，该字段通过联表查问实现")    @ApiImplicitParam(name = "id", value = "日志ID", paramType = "path", required = true, dataType = "Integer")    @RequestMapping(value = "/aggregate/{id}", method = RequestMethod.GET)    public LogExtend oneObjectSel(@PathVariable int id){        return logService.oneObjectSel(id);    }
  1. 编写单元测试的代码ControllerTest.java，因为明天的测试波及到user和log两个表，因而在测试类ControllerTest的外部筹备了两个外部类，别离用于测试user和log表：
package com.bolingcavalry.relatedoperation.controller;import lombok.extern.slf4j.Slf4j;import org.junit.jupiter.api.*;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.http.MediaType;import org.springframework.test.context.ActiveProfiles;import org.springframework.test.web.servlet.MockMvc;import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;@SpringBootTest@DisplayName("Web接口的单元测试")@AutoConfigureMockMvc@ActiveProfiles("test")@Slf4jpublic class ControllerTest {    /**     * 查问形式：联表     */    final static String SEARCH_TYPE_LEFT_JOIN = "leftjoin";    /**     * 查问形式：嵌套     */    final static String SEARCH_TYPE_NESTED = "nested";    final static int TEST_USER_ID = 3;    final static String TEST_USER_NAME = "tom";    @Autowired MockMvc mvc;    @Nested    @TestMethodOrder(MethodOrderer.OrderAnnotation.class)    @DisplayName("用户服务")    class User {        }        @Nested    @TestMethodOrder(MethodOrderer.OrderAnnotation.class)    @DisplayName("日志服务")    class Log {        final static int TEST_LOG_ID = 5;        @Test        @DisplayName("通过日志ID获取日志信息,带userName字段，该字段通过联表查问实现")        @Order(1)        void oneObjectSel() throws Exception {            mvc.perform(MockMvcRequestBuilders.get("/log/aggregate/" + TEST_LOG_ID)                    .accept(MediaType.APPLICATION_JSON))                    .andExpect(status().isOk())                    .andExpect(jsonPath("$.id").value(TEST_LOG_ID))                    .andExpect(jsonPath("$.userName").value(TEST_USER_NAME))                    .andDo(print());        }    }}
  1. 执行上述单元测试办法，后果如下图，红框中就是controller层返回的数据，可见已通过Mybatis胜利获得LogExtend实例：

  • 下一站是一对一联表查问；

对于一对一关联的两种形式

  • 后面的查问有个特点：只管查问了两个表，但后果都在同一实体类的不同字段，而更合乎业务逻辑的关系应该是log类中有个user类的成员变量，即如下模式：
@Data@NoArgsConstructor@ApiModel(description = "日志实体类")public class LogAssociateUser {    @ApiModelProperty(value = "日志ID")    private Integer id;    @ApiModelProperty(value = "用户对象")    private User user;    @ApiModelProperty(value = "日志内容")    private String action;    @ApiModelProperty(value = "创立工夫")    private Date createTime;}
  • 接下来的实战就是如何用MyBatis查问失去上述<font color="blue">LogAssociateUser</font> 类型的后果；
  • 一对一关联的实现有<font color="blue">联表</font>和<font color="blue">嵌套查问</font>两种，它们的差别在Mybatis中体现在association的子节点上：
  1. 联表时，association内应用<font color="red">result</font>子节点，将联表查问的后果映射到关联对象；
  2. 嵌套时，association内应用<font color="red">select</font>子节点，触发一次新的查问；

一对一（联表）

所谓一对一，就是一个对象关联了另一个对象，例如一条log记录中，带有对应的user信息；

  1. 上面是新的实体类LogAssociateUser，该类对应的是log表记录，有个user字段，类型是User对象：
@Data@NoArgsConstructor@ApiModel(description = "日志实体类")public class LogAssociateUser {    @ApiModelProperty(value = "日志ID")    private Integer id;    @ApiModelProperty(value = "用户对象")    private User user;    @ApiModelProperty(value = "日志内容")    private String action;    @ApiModelProperty(value = "创立工夫")    private Date createTime;}
  1. 映射文件<font color="blue">LogMapper.xml</font>中，sql和resultMap如下，可见查问的时候将user表的字段都查出来了，而后在resultMap中用association节点去解决sql中查出的user表的数据，通过<font color="blue">javaType</font>属性转为User类的实例：
    <!--联表查问，返回log对象，它的成员变量中有user对象-->    <select id="leftJoinSel" parameterType="int" resultMap="leftJoinResultMap">        select l.id as log_id,               l.action as log_action,               l.create_time as log_create_time,               u.id as user_id,               u.name as user_name,               u.age as user_age        from log as l               left join user as u               on l.user_id = u.id        where l.id = #{id}    </select>    <resultMap id="leftJoinResultMap" type="LogAssociateUser">        <id property="id" column="log_id"/>        <result  property="action" column="log_action" jdbcType="VARCHAR"/>        <result property="createTime" column="log_create_time" jdbcType="TIMESTAMP" />        <association property="user" javaType="User">            <id property="id" column="user_id"/>            <result property="name" column="user_name"/>            <result property="age" column="user_age"/>        </association>    </resultMap>
  1. 以上就是一对一（联表）的关键点，接下来循序渐进的在LogMapper、LogService、LogController中增加办法即可，上面是LogController中对应的web接口，稍后会在单元测试中调用这个接口进行验证：
    @ApiOperation(value = "依据ID查找日志记录，带用户对象，联表查问实现", notes="依据ID查找日志记录，带用户对象，联表查问实现")    @ApiImplicitParam(name = "id", value = "日志ID", paramType = "path", required = true, dataType = "Integer")    @RequestMapping(value = "/leftjoin/{id}", method = RequestMethod.GET)    public LogAssociateUser leftJoinSel(@PathVariable int id){        return logService.leftJoinSel(id);    }
  1. 最初是单元测试的代码（ControllerTest.java文件），用来测试上述代码是否无效，留神上面的<font color="red">queryAndCheck</font>公有办法，该办法中发动申请并验证后果：
        /**         * 通过日志ID获取日志信息有两种形式：联表和嵌套查问，         * 从客户端来看，仅一部分path不同，因而将申请和查看封装到一个通用办法中，         * 调用办法只须要指定不同的那一段path         * @param subPath         * @throws Exception         */        private void queryAndCheck(String subPath) throws Exception {            String queryPath = "/log/" + subPath + "/" + TEST_LOG_ID;            log.info("query path [{}]", queryPath);            mvc.perform(MockMvcRequestBuilders.get(queryPath)                    .accept(MediaType.APPLICATION_JSON))                    .andExpect(status().isOk())                    .andExpect(jsonPath("$.id").value(TEST_LOG_ID))                    .andExpect(jsonPath("$.user.id").value(TEST_USER_ID))                    .andDo(print());        }        @Test        @DisplayName("通过日志ID获取日志信息（关联了用户），联表查问")        @Order(2)        void leftJoinSel() throws Exception {            queryAndCheck(SEARCH_TYPE_LEFT_JOIN);        }
  1. 执行单元测试后果如下，可见：外部嵌套了一个json对象，就是user表的数据：

一对一（嵌套）

  1. 接下来试试嵌套的形式；
  2. LogMapper.xml中对应的sql：
    <!--嵌套-->    <select id="nestedSel" parameterType="int" resultMap="nestedResultMap">        select            l.id as log_id,            l.user_id as log_user_id,            l.action as log_action,            l.create_time as log_create_time        from mybatis.log as l        where l.id = #{id}    </select>
  1. 上述sql对应的resultMap如下，可见association节点中有个<font color="blue">select</font>属性，这就是MyBatis反对嵌套查问的要害，该属性的值是个select节点：
    <!-- association节点的select属性会触发嵌套查问-->    <resultMap id="nestedResultMap" type="LogAssociateUser">        <!-- column属性中的log_id，来自后面查问时的"l.id as log_id" -->        <id property="id" column="log_id"/>        <!-- column属性中的log_action，来自后面查问时的"l.action as log_action" -->        <result  property="action" column="log_action" jdbcType="VARCHAR"/>        <!-- column属性中的log_create_time，来自后面查问时的"l.create_time as log_create_time" -->        <result property="createTime" column="log_create_time" jdbcType="TIMESTAMP" />        <!-- select属性，示意这里要执行嵌套查问，将log_user_id传给嵌套的查问 -->        <association property="user" column="log_user_id" select="selectUserByUserId"></association>    </resultMap>
  1. 上述节点中select属性的值，对应一个select节点，如下：
    <select id="selectUserByUserId" parameterType="int" resultType="User">        select            u.id,            u.name,            u.age        from mybatis.user as u        where u.id = #{log_user_id}    </select>
  1. 以上就是一对一（嵌套）的关键点，接下来循序渐进的在LogMapper、LogService、LogController中增加办法即可，上面是LogController中对应的web接口，稍后会在单元测试中调用这个接口进行验证：
    @ApiOperation(value = "依据ID查找日志记录，带用户对象，嵌套查问实现", notes="依据ID查找日志记录，带用户对象，嵌套查问实现")    @ApiImplicitParam(name = "id", value = "日志ID", paramType = "path", required = true, dataType = "Integer")    @RequestMapping(value = "/nested/{id}", method = RequestMethod.GET)    public LogAssociateUser nestedSel(@PathVariable int id){        return logService.nestedSel(id);    }
  1. 最初是单元测试的代码（ControllerTest.java文件），用来测试上述代码是否无效，如下可见，间接调用了后面的<font color="blue">queryAndCheck</font>来验证：
        @Test        @DisplayName("通过日志ID获取日志信息（关联了用户），嵌套查问")        @Order(3)        void nestedSel() throws Exception {            queryAndCheck(SEARCH_TYPE_NESTED);        }
  1. 执行上述单元测试代码，后果如下，可见嵌套查问的形式也能将user表的数据胜利获取，放入log实例的成员变量中：

  1. 最初是比照联表和嵌套查问的差别，先看联表查问的MyBatis日志，如下图红框所示，只有一次sql查问：

  1. 再看嵌套查问的日志，如下图，红框是第一次查问，后果中的userid作为绿框中的第二次查问的条件：

  • 至此，一对一的多表查问实战就实现了，本篇的逻辑是一条log记录关联一条user记录，下一篇文章，咱们学习一对多关联，即一个user有多条log记录；

你不孤独，欣宸原创一路相伴

  1. Java系列
  2. Spring系列
  3. Docker系列
  4. kubernetes系列
  5. 数据库+中间件系列
  6. DevOps系列

欢送关注公众号：程序员欣宸

微信搜寻「程序员欣宸」，我是欣宸，期待与您一起畅游Java世界...
https://github.com/zq2599/blog_demos