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 6 of the series , Continue to practice getting data from multiple tables ;

  • Review the last one , We have practiced the one-to-one relationship with multiple tables , As shown in the figure below , When looking for a log record , Find out the corresponding user information :

  • This article is to practice the one to many relationship : When querying user records , Find out all the log records of the user , The logical relationship is shown in the figure below :

  • When one to many queries are implemented by specific coding , It is implemented in two ways: linked table and nested , Each method follows the steps in the figure below :

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 :



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 :

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 :



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

Two ways of multi table associated query

  • There are two kinds of implementation of multi table associated query: linked table and nested query , The difference between them is Mybatis It is reflected in resultMap By definition :
  1. When combining tables ,resultMap Internal use collection Child node , Maps the results of a join table query to a collection of associated objects ;
  2. Nesting ,resultMap Internal use association Child node ,association Of select Property triggers a new query ;
  • These two methods can get the query results successfully , Then try it one by one ;

League table query

  1. This article continues to use the subprojects created in the previous article relatedoperation;
  2. Entity class UserWithLogs.java as follows , Visible member variables logs Is the collection used to save all logs of the user :
package com.bolingcavalry.relatedoperation.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List; @Data
@NoArgsConstructor
@ApiModel(description = " User entity class ( Including behavior log collection )")
public class UserWithLogs { @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; @ApiModelProperty(value = " Behavior log ", required = false)
private List<Log> logs;
}
  1. preservation SQL Of UserMapper.xml as follows , First of all, query the linked table SQL Write out , The result is in the

    leftJoinResultMap Of resultMap In dealing with :
<?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.UserMapper"> <select id="leftJoinSel" parameterType="int" resultMap="leftJoinResultMap">
select
u.id as user_id,
u.name as user_name,
u.age as user_age,
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.user as u
left join mybatis.log as l
on u.id = l.user_id
where u.id = #{id}
</select>
</mapper>
  1. leftJoinResultMap This resultMap It's the one to many key , Inside collection take log All records of are mapped to logs Collection :
 <resultMap id="leftJoinResultMap" type="UserWithLogs">
<id property="id" column="user_id"/>
<result property="name" column="user_name" jdbcType="VARCHAR"/>
<result property="age" column="user_age" jdbcType="INTEGER" />
<collection property="logs" ofType="Log">
<id property="id" column="log_id"/>
<result property="userId" column="log_user_id" jdbcType="INTEGER" />
<result property="action" column="log_action" jdbcType="VARCHAR" />
<result property="createTime" column="log_create_time" jdbcType="TIMESTAMP" />
</collection>
</resultMap>
  1. Interface definition UserMapper.java :
@Repository
public interface UserMapper {
UserWithLogs leftJoinSel(int id);
}
  1. service layer :
@Service
public class UserService {
@Autowired
UserMapper userMapper; public UserWithLogs leftJoinSel(int id) {
return userMapper.leftJoinSel(id);
}
}
  1. controller There is a little bit more code in the layer , It's because you want to swagger Make the information as complete as possible :
@RestController
@RequestMapping("/user")
@Api(tags = {"UserController"})
public class UserController {
@Autowired
private UserService userService; @ApiOperation(value = " according to ID lookup user Record ( Include behavior logs ), League table query ", notes=" according to ID lookup user Record ( Include behavior logs ), League table query ")
@ApiImplicitParam(name = "id", value = " user ID", paramType = "path", required = true, dataType = "Integer")
@RequestMapping(value = "/leftjoin/{id}", method = RequestMethod.GET)
public UserWithLogs leftJoinSel(@PathVariable int id){
return userService.leftJoinSel(id);
}
}
  1. Finally, unit testing , Created earlier ControllerTest.java New inner class in User be used for user Table related unit tests , You can see that it encapsulates a private method queryAndCheck Responsible for request and validation results , Later nested queries will also be used :
 @Nested
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@DisplayName(" Customer service ")
class User { /**
* Through users ID There are two ways to get user information :left join 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 = "/user/" + subPath + "/" + TEST_USER_ID; log.info("query path [{}]", queryPath); mvc.perform(MockMvcRequestBuilders.get(queryPath).accept(MediaType.APPLICATION_JSON))
.andExpect(status().isOk())
.andExpect(jsonPath("$.id").value(TEST_USER_ID))
.andExpect(jsonPath("$..logs.length()").value(5))
.andDo(print());
} @Test
@DisplayName(" Through users ID Get user information ( Include behavior logs ), League table query ")
@Order(1)
void leftJoinSel() throws Exception {
queryAndCheck(SEARCH_TYPE_LEFT_JOIN);
}
}
  1. Perform the above unit test method leftJoinSel, The results are as follows :

  1. For the convenience of observation , I'm going to take the one in the red box above JSON The data is formatted , As shown below , so log The five records in the table are linked , As a whole user A field of an object :
{
"id": 3,
"name": "tom",
"age": 11,
"logs": [
{
"id": 3,
"userId": 3,
"action": "read book",
"createTime": "2020-08-07"
},
{
"id": 4,
"userId": 3,
"action": "go to the cinema",
"createTime": "2020-09-02"
},
{
"id": 5,
"userId": 3,
"action": "have a meal",
"createTime": "2020-10-05"
},
{
"id": 6,
"userId": 3,
"action": "have a sleep",
"createTime": "2020-10-06"
},
{
"id": 7,
"userId": 3,
"action": "write",
"createTime": "2020-10-08"
}
]
}
  1. The above is to obtain the one to many association results through the way of joint table , Next, let's try nested queries ;

nested queries

  1. The basic idea of nested query is to merge the results of multiple queries , The key point is still in SQL and resultMap The configuration of , Let's look at the nested query SQL, stay UserMapper.xml In file , as follows , Visible, only query user surface , Does not relate to log surface :
 <select id="nestedSel" parameterType="int" resultMap="nestedResultMap">
select
u.id as user_id,
u.name as user_name,
u.age as user_age
from mybatis.user as u
where u.id = #{id}
</select>
  1. above SQL The display results are saved in the nestedResultMap Of resultMap in , Look at this resultMap, as follows , Of visible entity class logs Field corresponds to a association node , Of this node select Property represents that this is a subquery , The query condition is user_id:
 <!-- association Node select Property triggers nested queries -->
<resultMap id="nestedResultMap" type="UserWithLogs">
<!-- column Attribute user_id, From the previous query "u.id as user_id" -->
<id property="id" column="user_id"/>
<!-- column Attribute user_name, From the previous query "u.name as user_name" -->
<result property="name" column="user_name" jdbcType="VARCHAR"/>
<!-- column Attribute user_age, From the previous query "u.age as user_age" -->
<result property="age" column="user_age" jdbcType="INTEGER" />
<!-- select attribute , Indicates that a nested query is to be executed here , take user_id Pass to nested query -->
<association property="logs" column="user_id" select="selectLogByUserId"></association>
</resultMap>
  1. be known as selectLogByUserId Of SQL and resultMap as follows , I.e. inquiry log surface :
 <select id="selectLogByUserId" parameterType="int" resultMap="log">
select
l.id,
l.user_id,
l.action,
l.create_time
from mybatis.log as l
where l.user_id = #{user_id}
</select> <resultMap id="log" type="log">
<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>
  1. These are the key points of nested queries , 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 lookup user Record ( Include behavior logs ), nested queries ", notes=" according to ID lookup user Record ( Include behavior logs ), nested queries ")
@ApiImplicitParam(name = "id", value = " user ID", paramType = "path", required = true, dataType = "Integer")
@RequestMapping(value = "/nested/{id}", method = RequestMethod.GET)
public UserWithLogs nestedSel(@PathVariable int id){
return userService.nestedSel(id);
}
  1. The code for unit testing is simple , Call the previously encapsulated queryAndCheck The method can :
 @Test
@DisplayName(" Through users ID Get user information ( Include behavior logs ), nested queries ")
@Order(2)
void nestedSel() throws Exception {
queryAndCheck(SEARCH_TYPE_NESTED);
}
  1. The results of executing the unit test are shown in the red box below , It is the same as the previous join table query :

  • Two ways of one to many associated queries have been tried , Let's look at the difference ;

The difference between join table and nesting

  1. The first is the log of joint table query , as follows , Only one query :
2020-10-21 20:25:05.754 INFO 15408 --- [ main] c.b.r.controller.ControllerTest : query path [/user/leftjoin/3]
2020-10-21 20:25:09.910 INFO 15408 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-10-21 20:25:09.925 DEBUG 15408 --- [ main] c.b.r.mapper.UserMapper.leftJoinSel : ==> Preparing: select u.id as user_id, u.name as user_name, u.age as user_age, 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.user as u left join mybatis.log as l on u.id = l.user_id where u.id = ?
2020-10-21 20:25:10.066 DEBUG 15408 --- [ main] c.b.r.mapper.UserMapper.leftJoinSel : ==> Parameters: 3(Integer)
2020-10-21 20:25:10.092 DEBUG 15408 --- [ main] c.b.r.mapper.UserMapper.leftJoinSel : <== Total: 5
  1. Let's look at the logs of nested queries , two :
2020-10-21 20:37:29.648 INFO 24384 --- [ main] c.b.r.controller.ControllerTest : query path [/user/nested/3]
2020-10-21 20:37:33.867 INFO 24384 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-10-21 20:37:33.880 DEBUG 24384 --- [ main] c.b.r.mapper.UserMapper.nestedSel : ==> Preparing: select u.id as user_id, u.name as user_name, u.age as user_age from mybatis.user as u where u.id = ?
2020-10-21 20:37:34.018 DEBUG 24384 --- [ main] c.b.r.mapper.UserMapper.nestedSel : ==> Parameters: 3(Integer)
2020-10-21 20:37:34.041 DEBUG 24384 --- [ main] c.b.r.m.UserMapper.selectLogByUserId : ====> Preparing: select l.id, l.user_id, l.action, l.create_time from mybatis.log as l where l.user_id = ?
2020-10-21 20:37:34.043 DEBUG 24384 --- [ main] c.b.r.m.UserMapper.selectLogByUserId : ====> Parameters: 3(Integer)
2020-10-21 20:37:34.046 DEBUG 24384 --- [ main] c.b.r.m.UserMapper.selectLogByUserId : <==== Total: 5
2020-10-21 20:37:34.047 DEBUG 24384 --- [ main] c.b.r.mapper.UserMapper.nestedSel : <== Total: 1
  • thus ,MyBatis The common multi table associated query is completed , I hope I can give you some reference , The next article , Let's continue to experience MyBatis Bring us all kinds of characteristics .

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

MyBatis Primary combat six : One to many association query more related articles

  1. 7.mybatis One to many associated queries

    And the 5 Section one-to-one queries are similar to , But here's the difference , One on one uses association, And one to many uses collection. example : 1 A class Class, Corresponding 1 A teacher Teacher, Corresponding to multiple students Student 1. ...

  2. MyBatis From getting started to giving up four : One to many associated queries

    Preface In the last part, I learned one-to-one association query , In this article, we learn about one to many association queries . The key point of one to many association query is still configuration resultMap, stay resultMap Middle configuration collection attribute , Don't neglect ofType attribute . Build development ...

  3. MyBatis: One to many associated queries

    MyBatis From getting started to giving up four : One to many associated queries Preface In the last part, I learned one-to-one association query , In this article, we learn about one to many association queries . The key point of one to many association query is still configuration resultMap, stay resultMap Middle configuration collecti ...

  4. MyBatis Relational query , One to many associated queries

    Entity relation diagram , One country corresponds to many cities One to many association queries can be implemented in three ways : Single step query , utilize collection Tags assign values to cascading properties : Step by step query : utilize association Tag for step-by-step query : utilize collect ...

  5. mybatis One to many associated queries ——( Nine )

    1. demand : Query all order information and order details under the order . The relationship between order information and order details is one to many . 2.      sql sentence Determine the main query table : The order sheet Determine the associated query table : Order details Add order details based on one-to-one query ...

  6. mybatis One to many associated queries +pagehelper-&gt; Paging error

    mybatis One to many associated queries +pagehelper-> Paging error . The phenomenon : Other people on the Internet have similar problems :https://segmentfault.com/q/1010000009692585 solve : ...

  7. mybatis collection One to many associated queries , Summary of the problem of single side paging !

    If you want to go straight through sql To achieve multi-level association query table structure must have 2 There are two essential fields :id ,parentId,levelId id: Primary key id, parentId: Father id levelId: What level is it ( When the table itself is associated with a query ...

  8. MyBatis For one more , One to many , Many to many , One to many associated queries

    One .Person Entity class 1 public class Person { 2 private Integer personId; 3 private String name; 4 private Int ...

  9. MyBitis(iBitis) The fifth in a series of essays : Multiple tables ( One to many associated queries )

    MyBitis(iBitis) One of a series of essays :MyBitis Introductory example MyBitis(iBitis) The second in a series of essays : Type the alias (typeAliases) And watch - Mapping objects (ORM) MyBitis(iBitis ...

  10. Mybatis Frame learning summary - Table Association query

    One to one connection Create tables and data : Create a teacher table and a class table , Suppose a teacher is only responsible for teaching one class , So the relationship between the teacher and the class is a one-to-one relationship . CREATE TABLE teacher( t_id INT PRIM ...

Random recommendation

  1. Powershell Connect Office 365 The method of each component

    Reference resources : http://www.exchangecn.com/office365/20150108_540.html 1. Apply to IT professionals RTW Of Microsoft Online Serv ...

  2. Quick sort Quick Sort

    Write your own code , Make a note of . Two kinds of partition Methods . public class QuickSort { public static void quickSort(int[] nums, i ...

  3. How to make wechat apps quickly access to 7niuyun

    If you're sure to run the app with 7niu , Let's share a 10% discount code :61d1fd4d1 month 9 Japan Wechat app officially released , The app finally unveils its mystery , Developers are more enthusiastic about small programs . From the popular application scenarios of small programs ...

  4. 04 Django REST Framework authentication 、 Permissions and restrictions

    at present , our API There are no restrictions on who can edit or delete code snippets . We want to have more advanced behavior , In order to ensure that : Code snippets are always associated with the Creator . Only authenticated users can create fragments . Only the creator of the code fragment can update or delete it . not ...

  5. Volunteer Recruitment [NOI2008] [ Ghost animal network flow ]

    Description After the successful bid for the Olympic Games , Bubu made unremitting efforts , Finally became the head of Human Resources Department of the company under the Olympic Organizing Committee . As soon as Bubu took office, he encountered a difficult problem : Recruit a group of short-term volunteers for the upcoming Olympic new projects . It is estimated that , This project needs N Genius can do it ...

  6. Round545div2B(1138B)

    One . Topic link https://codeforces.com/problemset/problem/1138/B Two . Ideas Greed can't be . Set up a meeting $[1,0]$ There's a container for people $p_1$ Inside , Meeting $[0,1 ...

  7. The first season of the scandal / The complete Scandal Thunder download

    Scandal Season one Scandal Season 1 (2011) What's new this season :“ Mother of grey ” Sandra · REMIS (Shonda Rhimes) The latest political public relations series < Scandal > Because the broadcast is about to increase the propaganda offensive , most ...

  8. Consistent hash algorithm (consistent hashing)( turn )

    Link to the original text : Make a little progress every day —— Five minutes to understand the consistency hash algorithm (consistent hashing)   The consistency hash algorithm 1997 A distributed hash proposed by MIT in (DHT) Implementation algorithm , The design goal is to solve the Internet ...

  9. Service Fabric Cluster Manager

    author : Pan Gang (Van Pan)@ Microsoft We go back to Service Fabric The bottom topic , Talk about Service Fabric How does it work . First , Let's go back to the document below , have a look Service F ...

  10. Scoi 2010 Lucky Numbers

    [ Title Description ] In China, , Many people put 6 and 8 As a lucky number !lxhgww I think so too , So he defines his own “ Lucky number ” The decimal representation contains only numbers 6 and 8 Those numbers of , such as 68,666,888 All are “ Lucky number ”! But this kind of “ Her love ...