Mybatis巧用@One注解一个SQL联合查询语句实现一对一查询

MyBatis中常用的一对一查询是通过xml方式里的resultMap和association 标签来配置的.

有没有只需要纯注解方式就能搞定一对一查询呢?通过反复阅读 @One 注解源码,发现里面有resultMap字段,这就跟XML方式的ResultMap对应上了.

ResultMap描述了如何从数据库结果集中加载对象，是最复杂也是最强大的元素

package org.apache.ibatis.annotations;

import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import org.apache.ibatis.mapping.FetchType;

/**
 * The annotation that specify the nested statement for retrieving single object.
 *
 * @see Result
 * @see Results
 * @author Clinton Begin
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface One {
  /**
   * Returns the columnPrefix.
   *
   * @return the columnPrefix.
   * @since 3.5.5
   */
  String columnPrefix() default "";

  /**
   * Returns the result map id used to map single object.
   *
   * @return the result map id
   * @since 3.5.5
   */
  String resultMap() default "";

  /**
   * Returns the statement id that retrieves single object.
   *
   * @return the statement id
   */
  String select() default "";

  /**
   * Returns the fetch strategy for nested statement.
   *
   * @return the fetch strategy
   */
  FetchType fetchType() default FetchType.DEFAULT;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

再有 @Results 注解的 id 属性对应上了XML里的ResultMap 的 id.这样一来通过注解就能实现一条SQL语句完成 一对一 的查询了.

废话少说,直接上源码!

💡提示: 因为咱们是社会主义国家,法律规定只能一夫一妻制,所以老白就用 丈夫 & 妻子 来作为示例.

数据表结构

1. 丈夫表(hubby)

CREATE TABLE `hubby` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) NOT NULL COMMENT '用户名',
  `nickname` varchar(20) NOT NULL COMMENT '昵称',
  `birthday` date NOT NULL COMMENT '生日',
  `status` varchar(2) NOT NULL DEFAULT '0' COMMENT '状态 ',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
);

INSERT INTO mybatis.hubby (id,name,nickname,birthday,status,create_time,update_time) VALUES
	 (1,'李明','小明','1995-05-05','0','2022-09-16 10:58:16.0','2022-09-16 10:58:16.0'),
	 (2,'张亮','小亮','1996-06-06','1','2022-09-16 11:58:16.0','2022-09-16 11:58:16.0');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 妻子表(wife)

CREATE TABLE `wife` (
  `wid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `hid` int(11) NOT NULL COMMENT '丈夫的id',
  `name` varchar(255) NOT NULL COMMENT '用户名',
  `nickname` varchar(20) NOT NULL COMMENT '昵称',
  `birthday` date NOT NULL COMMENT '生日',
  PRIMARY KEY (`wid`)
);

INSERT INTO mybatis.wife (wid,hid,name,nickname,birthday) VALUES
	 (1,1,'张丽','小丽','1997-07-07'),
	 (2,2,'赵琴','小琴','1998-08-08');
1
2
3
4
5
6
7
8
9
10
11
12

实体类:

1. 丈夫类(Hubby)

package wjw.test.mybatisplus.master.onetoone;

import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalDateTime;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("hubby")
public class Hubby implements Serializable {

  private static final long serialVersionUID = 1L;

  @TableId(
      type = IdType.AUTO)
  /**
   * 主键
   */
  @TableField("id")
  private Integer id;

  /**
   * 用户名
   */
  @TableField("name")
  private String name;

  /**
   * 昵称
   */
  @TableField("nickname")
  private String nickname;

  /**
   * 生日
   */
  @TableField("birthday")
  private LocalDate birthday;

  /**
   * 状态
   */
  @TableField("status")
  private String status;

  /**
   * 创建时间
   */
  @TableField("create_time")
  private LocalDateTime createTime;

  /**
   * 更新时间
   */
  @TableField("update_time")
  private LocalDateTime updateTime;

  @TableField(exist = false /* 指明此字段不是表的列 */)  // <1>
  private Wife wife;

  public Hubby() {
  }

  public Integer getId() {
    return id;
  }

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

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getNickname() {
    return nickname;
  }

  public void setNickname(String nickname) {
    this.nickname = nickname;
  }

  public LocalDate getBirthday() {
    return birthday;
  }

  public void setBirthday(LocalDate birthday) {
    this.birthday = birthday;
  }

  public String getStatus() {
    return status;
  }

  public void setStatus(String status) {
    this.status = status;
  }

  public LocalDateTime getCreateTime() {
    return createTime;
  }

  public void setCreateTime(LocalDateTime createTime) {
    this.createTime = createTime;
  }

  public LocalDateTime getUpdateTime() {
    return updateTime;
  }

  public void setUpdateTime(LocalDateTime updateTime) {
    this.updateTime = updateTime;
  }

  public Wife getWife() {
    return wife;
  }

  public void setWife(Wife wife) {
    this.wife = wife;
  }

  @Override
  public String toString() {
    StringBuilder builder = new StringBuilder();
    builder.append("Hubby [id=");
    builder.append(id);
    builder.append(", name=");
    builder.append(name);
    builder.append(", nickname=");
    builder.append(nickname);
    builder.append(", birthday=");
    builder.append(birthday);
    builder.append(", status=");
    builder.append(status);
    builder.append(", createTime=");
    builder.append(createTime);
    builder.append(", updateTime=");
    builder.append(updateTime);
    builder.append(", wife=");
    builder.append(wife);
    builder.append("]");
    return builder.toString();
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

📝注意: <1>: 在wife字段上要加上@TableField(exist = false /* 指明此字段不是表的列 */) 注解

2. 妻子类(Wife):

package wjw.test.mybatisplus.master.onetoone;

import java.io.Serializable;
import java.time.LocalDate;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("wife")
public class Wife implements Serializable {

  private static final long serialVersionUID = 1L;

  @TableId(
      type = IdType.AUTO)
  /**
   * 主键
   */
  @TableField("id")
  private Integer wid;

  /**
   * 丈夫的id
   */
  @TableField("hid")
  private Integer hid;

  /**
   * 用户名
   */
  @TableField("name")
  private String name;

  /**
   * 昵称
   */
  @TableField("nickname")
  private String nickname;

  /**
   * 生日
   */
  @TableField("birthday")
  private LocalDate birthday;

  public Wife() {
  }

  public Integer getWid() {
    return wid;
  }

  public void setWid(Integer wid) {
    this.wid = wid;
  }

  public Integer getHid() {
    return hid;
  }

  public void setHid(Integer hid) {
    this.hid = hid;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getNickname() {
    return nickname;
  }

  public void setNickname(String nickname) {
    this.nickname = nickname;
  }

  public LocalDate getBirthday() {
    return birthday;
  }

  public void setBirthday(LocalDate birthday) {
    this.birthday = birthday;
  }

  @Override
  public String toString() {
    StringBuilder builder = new StringBuilder();
    builder.append("Wife [wid=");
    builder.append(wid);
    builder.append(", hid=");
    builder.append(hid);
    builder.append(", name=");
    builder.append(name);
    builder.append(", nickname=");
    builder.append(nickname);
    builder.append(", birthday=");
    builder.append(birthday);
    builder.append("]");
    return builder.toString();
  }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

Mapper类:

HubbyMapper:

package wjw.test.mybatisplus.master.onetoone;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

@Mapper
public interface HubbyMapper extends BaseMapper<Hubby> {
  //OneToOne一对一查询
  @Select("SELECT h.id, h.name, h.nickname, h.birthday, h.status, h.create_time, h.update_time, "
      + "w.wid w_wid, w.hid w_hid, w.name w_name, w.nickname w_nickname, w.birthday w_birthday "
      + "FROM hubby h left join wife w on h.id =w.hid where h.name =#{name}")
  @Results({
    @Result(column = "id", property = "id", id = true),                  // <1>
    @Result(column = "name",property = "name"),
    @Result(column = "nickname",property = "nickname"),
    @Result(column = "birthday",property = "birthday"),
    @Result(column = "password",property = "password"),
    @Result(column = "status",property = "status"),
    @Result(column = "create_time",property = "createTime"),
    @Result(column = "update_time",property = "updateTime"),
    @Result(property = "wife", one = @One(resultMap = "WifeMap", columnPrefix = "w_")) // <2>
  })
  List<Hubby> getHubbyList(@Param("name") String name);    

  @Select("select wid, hid, name, nickname, birthday from wife where wid=#{wid}")
  @Results(id = "WifeMap", 
    value={
      @Result(column = "wid", property = "wid", id = true),  
      @Result(column = "hid", property = "hid"),  
      @Result(column = "name",property = "name"),
      @Result(column = "nickname",property = "nickname"),
      @Result(column = "birthday",property = "birthday")
    })
  Wife getWifeById(@Param("wid")Integer wid);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

<1>: @Result(column=“id”,property=“Id”, id = true),这个可以不写，也不会报错，但是会导致我们查询结果列表里每个实体的Id等属性没有值。

<2>: 这个@Result(property = "wife", one = @One(resultMap = "WifeMap", columnPrefix = "w_"))中

property = "wife"，就是Hubby类中的wife字段。

resultMap = "WifeMap" 指向getWifeById方法上的@Results注解里的id值.

事实上，getWifeById这个方法并没有被真的调用，甚至上面 Select 注解中的 SQL 语句也不会被执行。定义这个方法只是因为 @Results 注解必需要依存于一个方法，换句话来说，这个方法只是占位符而已。

columnPrefix, 会为 WifeMap中的所有列名都加上一个 w_ 前缀，这样一来就能匹配上联合查询 SQL 语句中实际返回的列名（例如 w_wid）了。

测试

package wjw.test.mybatisplus;

import java.util.List;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import wjw.test.mybatisplus.master.onetoone.Hubby;
import wjw.test.mybatisplus.master.onetoone.HubbyMapper;
import wjw.test.mybatisplus.master.onetoone.Wife;

@SpringBootTest(webEnvironment = WebEnvironment.NONE)
public class MybatisPlusTestOneToOne {
  @Autowired
  private HubbyMapper hubbyMapper;

  public MybatisPlusTestOneToOne() {
    //
  }

  @BeforeEach
  public void setup() {
    //
  }

  @AfterEach
  public void stop() {
  }

  @Test
  public void testOneToOne(){
    List<Hubby> hubbys = hubbyMapper.getHubbyList("李明");
    System.out.println(hubbys);
  }
  
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

输出结果:

w.t.m.master.onetoone.HubbyMapper.getHubbyList - [debug,137] - ==>  Preparing: SELECT h.id, h.name, h.nickname, h.birthday, h.status, h.create_time, h.update_time, w.wid w_wid, w.hid w_hid, w.name w_name, w.nickname w_nickname, w.birthday w_birthday FROM hubby h left join wife w on h.id =w.hid where h.name =?
w.t.m.master.onetoone.HubbyMapper.getHubbyList - [debug,137] - ==> Parameters: 李明(String)
w.t.m.master.onetoone.HubbyMapper.getHubbyList - [trace,143] - <==    Columns: id, name, nickname, birthday, status, create_time, update_time, w_wid, w_hid, w_name, w_nickname, w_birthday
w.t.m.master.onetoone.HubbyMapper.getHubbyList - [trace,143] - <==        Row: 1, 李明, 小明, 1995-05-05, 0, 2022-09-16 10:58:16.0, 2022-09-16 10:58:16.0, 1, 1, 张丽, 小丽, 1997-07-07
w.t.m.master.onetoone.HubbyMapper.getHubbyList - [debug,137] - <==      Total: 1
[Hubby [id=1, name=李明, nickname=小明, birthday=1995-05-05, status=0, createTime=2022-09-16T10:58:16, updateTime=2022-09-16T10:58:16, wife=Wife [wid=1, hid=1, name=张丽, nickname=小丽, birthday=1997-07-07]]]

1
2
3
4
5
6
7

经过测试，上面的 Mapper 完美地完成了任务。值得一提的是，引用的 ResultMap 不一定要定义在同一个 Mapper 中，也可以用全限定名去引用 Mapper 外部的 ResultMap。