Mybatis primary practice 5: one to one association query

Programmer Xin Chen 2021-01-21 08:53:18
mybatis primary practice association query


Welcome to visit mine GitHub

https://github.com/zq2599/blog_demos

Content : All original articles classified summary and supporting source code , involve Java、Docker、Kubernetes、DevOPS etc. ;

An overview of this article

  • This article is about 《MyBatis Primary combat 》 Part five of the series , Getting data from multiple tables is a common scenario , There are generally two ways :
  • League table query :join operation , One query complete
  • Multiple queries : Use the result of the first query as a condition , Make further inquiry (MyBatis Nested queries are called nested queries )
  • The content of this article is to learn MyBatis Support for both queries , The paper consists of the following chapters :
  1. Prepare the data ;
  2. This actual battle java engineering
  3. The simplest table is ( The data of the two tables are stored in different fields of an entity class );
  4. One to one table query ( The data of the two tables are stored in different entity classes , The assumption is A and B,A yes B Member variables of )
  5. One to one nested queries ( The data of the two tables are stored in different entity classes , The assumption is A and B,A yes B Member variables of )

Source download

  1. If you don't want to code , Can be in GitHub Download all the source code , The address and link information is shown in the following table (https://github.com/zq2599/blog_demos):
name link remarks
Project home page https://github.com/zq2599/blog_demos The project is in progress. GitHub Home page on
git Warehouse address (https) https://github.com/zq2599/blog_demos.git The warehouse address of the source code of the project ,https agreement
git Warehouse address (ssh) git@github.com:zq2599/blog_demos.git The warehouse address of the source code of the project ,ssh agreement
  1. This git Multiple folders in project , The application of this chapter in mybatis Under the folder , As shown in the red box below :

 Insert picture description here
3. mybatis It's a parent project , There are several sub projects in it , The source code of this article is in relatedoperation In the subproject , As shown in the red box below :

 Insert picture description here

Prepare the data

  1. This actual battle , In a mybatis Create two tables in the database of ( As like as two peas in the previous articles. ):user and log surface ;
  2. user Table records user information , It's simple , There are only three fields : Primary key 、 name 、 Age
  3. log Table records user behavior , Four fields : Primary key 、 user id、 Description of behavior 、 Behavior time
  4. user and log The relationship is as follows :

 Insert picture description here
5. The statements for creating tables and adding data are as follows :

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');

This actual battle java engineering

  1. In the parent project mybatis Next new sub project relatedoperation,pom.xml as follows :
<?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. Basic configuration file application.yml:
server:
port: 8080
spring:
#1.JDBC data source
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. Connection pool configuration
druid:
# Number of connections to initialize the connection pool size , Minimum , Maximum
initial-size: 5
min-idle: 5
max-active: 20
# Configure the timeout time for getting connection waiting
max-wait: 60000
# Configure how often to test , Detects idle connections that need to be closed , In milliseconds
time-between-eviction-runs-millis: 60000
# Configure the minimum lifetime of a connection in the pool , In milliseconds
min-evictable-idle-time-millis: 30000
# Configure the maximum lifetime of a connection in the pool , In milliseconds
max-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM user
test-while-idle: true
test-on-borrow: true
test-on-return: false
# Whether the cache preparedStatement, That is to say PSCache The official advice MySQL The next suggestion is to close If you want to use SQL A firewall Suggest opening
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# Configure monitoring statistics interception filters, Remove the monitoring interface sql Unable to statistics ,'wall' For firewalls
filters: stat,wall,slf4j
filter:
stat:
merge-sql: true
slow-sql-millis: 5000
#3. Basic monitoring configuration
web-stat-filter:
enabled: true
url-pattern: /*
# Which statistics are not set 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
# Set the login name and password of the monitoring page
login-username: admin
login-password: admin
allow: 127.0.0.1
#deny: 192.168.1.100
# mybatis To configure
mybatis:
# The location of the configuration file
config-location: classpath:mybatis-config.xml
# The location of the mapping file
mapper-locations: classpath:mappers/*Mapper.xml
# Log configuration
logging:
level:
root: INFO
com:
bolingcavalry:
relatedoperation:
mapper: debug
  1. And then prepare the name application-test.yml Configuration file for , This is used for unit testing , and application.yml The difference is spring.datasource.druid.web-stat-filter.enabled The configuration is set to false;
  2. mybatis Configuration file for mybatis-config.xml as follows :
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<!-- The class in the mapping file does not need to write the full path -->
<package name="com.bolingcavalry.relatedoperation.entity"/>
</typeAliases>
</configuration>
  1. Data source configuration class 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;
@Configuration
public 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 Connection pool configuration
*/
@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 Configuration class :
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
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.tags(new Tag("UserController", " Customer service "), new Tag("LogController", " The log service "))
.select()
// Current package path
.apis(RequestHandlerSelectors.basePackage("com.bolingcavalry.relatedoperation.controller"))
.paths(PathSelectors.any())
.build();
}
// structure api Document details function , Notice which of the notes here refers to
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
// The page title
.title("MyBatis CURD operation ")
// founder
.contact(new Contact(" Xinchen, programmer ", "https://github.com/zq2599/blog_demos", "zq2599@gmail.com"))
// Version number
.version("1.0")
// describe
.description("API describe ")
.build();
}
}
  1. springboot Boot class :
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. The entity class of the user table :
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 = " User entity class ")
public class User {
@ApiModelProperty(value = " user ID")
private Integer id;
@ApiModelProperty(value = " user name ", required = true)
private String name;
@ApiModelProperty(value = " Address of the user ", required = false)
private Integer age;
}
  1. The entity class of the log table :
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 = " Log entity class ")
public class Log {
@ApiModelProperty(value = " journal ID")
private Integer id;
@ApiModelProperty(value = " user ID")
private Integer userId;
@ApiModelProperty(value = " Log contents ")
private String action;
@ApiModelProperty(value = " Creation time ")
private Date createTime;
}
  • The above is the preparation code for this article , Next, on this basis, we implement a variety of multi table associated queries

The simplest table is

  • The most common joint table is the first one , As shown in the figure below , The query result is named LogExtend The entity class , This class has a 5 A field , Four of them are from the log table log, One from the user table user:

 Insert picture description here

  • The following figure shows the development steps :

 Insert picture description here

  1. Entity class LogExtend The source code is as follows , Visible and Log Compared with more userName Field :
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 = " Log entity class ( Fields with user tables )")
public class LogExtend extends Log {
@ApiModelProperty(value = " user name ")
private String userName;
}
  1. newly build log The mapping file corresponding to the table LogMapper.xml, As shown below , It's through left join A simple join table query executed by syntax , And the corresponding query results resultMap Definition :
<?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">
<!-- League table query , return log object , The object has a userName Field , The value is user Tabular user_name Field -->
<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 Interface code :
package com.bolingcavalry.relatedoperation.mapper;
import com.bolingcavalry.relatedoperation.entity.LogAssociateUser;
import com.bolingcavalry.relatedoperation.entity.LogExtend;
import org.springframework.stereotype.Repository;
@Repository
public interface LogMapper {
LogExtend oneObjectSel(int id);
}
  1. service The code of the layer is in LogService.java In file :
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;
@Service
public class LogService {
@Autowired
LogMapper logMapper;
public LogExtend oneObjectSel(int id){
return logMapper.oneObjectSel(id);
}
}
  1. controller The code of the layer is in LogController.java In file :
@RestController
@RequestMapping("/log")
@Api(tags = {"LogController"})
public class LogController {
@Autowired
private LogService logService;
@ApiOperation(value = " according to ID Find the log record , belt userName Field , This field is realized through the joint table query ", notes=" according to ID Find the log record , belt userName Field , This field is realized through the joint table query ")
@ApiImplicitParam(name = "id", value = " journal 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. Write unit test code ControllerTest.java, Because today's test involves user and log Two tables , So in the test class ControllerTest Two inner classes are prepared inside the , They are used to test user and log surface :
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 Unit test of interface ")
@AutoConfigureMockMvc
@ActiveProfiles("test")
@Slf4j
public class ControllerTest {
/**
* A query : Linked list
*/
final static String SEARCH_TYPE_LEFT_JOIN = "leftjoin";
/**
* A query : nesting
*/
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(" Customer service ")
class User {
}
@Nested
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@DisplayName(" The log service ")
class Log {
final static int TEST_LOG_ID = 5;
@Test
@DisplayName(" Through the log ID Get log information , belt userName Field , This field is realized through the joint table query ")
@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. Perform the above unit test method , The results are as follows , In the red box is controller The data returned by the layer , It can be seen that it has passed Mybatis To succeed in LogExtend example :

 Insert picture description here

  • The next station is a one-to-one table query ;

Two ways of one-to-one association

  • The previous query has a feature : Although two tables are queried , But the results are all in different fields of the same entity class , A more logical relationship to the business should be log There is a in the class user Member variables of class , In the following form :
@Data
@NoArgsConstructor
@ApiModel(description = " Log entity class ")
public class LogAssociateUser {
@ApiModelProperty(value = " journal ID")
private Integer id;
@ApiModelProperty(value = " User object ")
private User user;
@ApiModelProperty(value = " Log contents ")
private String action;
@ApiModelProperty(value = " Creation time ")
private Date createTime;
}
  • The next real battle is how to use MyBatis The query shows the above LogAssociateUser Result of type ;
  • The implementation of one-to-one association is Linked list and nested queries Two kinds of , The difference between them is Mybatis It is reflected in association On the child nodes of :
  1. When combining tables ,association Internal use result Child node , Map the result of the join table query to the associated object ;
  2. Nesting ,association Internal use select Child node , Trigger a new query ;

one-on-one ( Linked list )

One on one , It's one object associated with another , For example, a log On record , With corresponding user Information ;

  1. Here's the new entity class LogAssociateUser, This class corresponds to log Table record , There is one user Field , The type is User object :
@Data
@NoArgsConstructor
@ApiModel(description = " Log entity class ")
public class LogAssociateUser {
@ApiModelProperty(value = " journal ID")
private Integer id;
@ApiModelProperty(value = " User object ")
private User user;
@ApiModelProperty(value = " Log contents ")
private String action;
@ApiModelProperty(value = " Creation time ")
private Date createTime;
}
  1. The mapping file LogMapper.xml in ,sql and resultMap as follows , Visible query will be user All the fields in the table are found , And then in resultMap of use association Node processing sql We found out that user The data table , adopt javaType Property changed to User Class :
 <!-- League table query , return log object , Its member variables contain user object -->
<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. That's one-on-one ( Linked list ) The key point of , Next, step by step, we will be in the LogMapper、LogService、LogController Add the method in , Here is LogController Corresponding web Interface , This interface will be called later in the unit test. :
 @ApiOperation(value = " according to ID Find the log record , With user objects , Linked table query implementation ", notes=" according to ID Find the log record , With user objects , Linked table query implementation ")
@ApiImplicitParam(name = "id", value = " journal 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. Finally, the code for the unit tests (ControllerTest.java file ), To test whether the above code is valid , Note the following queryAndCheck Private method , In this method, the request is initiated and the result is verified :
 /**
* Through the log ID There are two ways to get log information : Join tables and nested queries ,
* From the client side , Only part of it path Different , Therefore, the request and check are encapsulated in a common method ,
* To call a method, you only need to specify a different segment 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(" Through the log ID Get log information ( Associated with users ), League table query ")
@Order(2)
void leftJoinSel() throws Exception {
queryAndCheck(SEARCH_TYPE_LEFT_JOIN);
}
  1. The execution unit test results are as follows , so : There's a... Inside json object , Namely user The data table :

 Insert picture description here

one-on-one ( nesting )

  1. Next try nesting ;
  2. LogMapper.xml Corresponding sql:
 <!-- nesting -->
<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. Above sql Corresponding resultMap as follows , so association There's a node select attribute , This is it. MyBatis Key to supporting nested queries , The value of this property is select node :
 <!-- association Node select Property triggers nested queries -->
<resultMap id="nestedResultMap" type="LogAssociateUser">
<!-- column Attribute log_id, From the previous query "l.id as log_id" -->
<id property="id" column="log_id"/>
<!-- column Attribute log_action, From the previous query "l.action as log_action" -->
<result property="action" column="log_action" jdbcType="VARCHAR"/>
<!-- column Attribute log_create_time, From the previous query "l.create_time as log_create_time" -->
<result property="createTime" column="log_create_time" jdbcType="TIMESTAMP" />
<!-- select attribute , Indicates that a nested query is to be executed here , take log_user_id Pass to nested query -->
<association property="user" column="log_user_id" select="selectUserByUserId"></association>
</resultMap>
  1. In the above nodes select The value of the property , Corresponding to one select node , as follows :
 <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. That's one-on-one ( nesting ) The key point of , Next, step by step, we will be in the LogMapper、LogService、LogController Add the method in , Here is LogController Corresponding web Interface , This interface will be called later in the unit test. :
 @ApiOperation(value = " according to ID Find the log record , With user objects , Nested query implementation ", notes=" according to ID Find the log record , With user objects , Nested query implementation ")
@ApiImplicitParam(name = "id", value = " journal 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. Finally, the code for the unit tests (ControllerTest.java file ), To test whether the above code is valid , As can be seen below , Directly called the previous queryAndCheck To verify :
 @Test
@DisplayName(" Through the log ID Get log information ( Associated with users ), nested queries ")
@Order(3)
void nestedSel() throws Exception {
queryAndCheck(SEARCH_TYPE_NESTED);
}
  1. Execute the above unit test code , give the result as follows , It can be seen that nested queries can also change user The data of table was successfully obtained , Put in log Instance's member variables :

 Insert picture description here
8. Finally, compare the difference between join table and nested query , First look at the linked table query MyBatis journal , As shown in the red box below , For once sql Inquire about :

 Insert picture description here
9. Let's look at the nested query log , Here's the picture , The red box is the first query , The results of the userid As the condition of the second query in the green box :

 Insert picture description here

  • thus , One to one multi table query is completed , The logic of this article is log The record is associated with a user Record , Next article , Let's learn one to many connections , That is, a user There are many log Record ;

You are not alone , Xinchen's original works are accompanied all the way

  1. Java series
  2. Spring series
  3. Docker series
  4. kubernetes series
  5. database + Middleware family
  6. DevOps series

Welcome to the official account : Xinchen, programmer

WeChat search 「 Xinchen, programmer 」, I'm Xinchen , Looking forward to traveling with you Java The world ...
https://github.com/zq2599/blog_demos

版权声明
本文为[Programmer Xin Chen]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210121085218012k.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云