MyBatis 模糊查询

模糊查询也是数据库SQL中使用频率很高的SQL语句,使用MyBatis来进行更加灵活的模糊查询。

一、数据准备

1.1 sql

相见mybatis-demo-like.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DROP TABLE IF EXISTS `user_info_like`;
CREATE TABLE `user_info_like` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_name` varchar(100) NOT NULL COMMENT '账户名称',
`pass_word` varchar(100) NOT NULL COMMENT '登录密码',
`nick_name` varchar(30) NOT NULL COMMENT '昵称',
`mobile` varchar(30) NOT NULL COMMENT '手机号',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_update` timestamp NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT 'Mybatis like';

-- ----------------------------
-- Records of user_info_like
-- ----------------------------
BEGIN;
INSERT INTO `user_info_like` VALUES (1, 'Van', '123456', '风尘', '12580', NULL, '2020-02-01 14:52:12', NULL);
INSERT INTO `user_info_like` VALUES (2, 'zhangsan', '123456', '张三', '12580', NULL, '2020-01-02 14:52:12', NULL);
INSERT INTO `user_info_like` VALUES (3, 'lisi', '123456', '李四', '12580', NULL, '2020-02-01 14:52:12', NULL);
INSERT INTO `user_info_like` VALUES (4, 'wanger', '123456', '王二', '12580', NULL, '2020-02-02 15:48:34', NULL);
INSERT INTO `user_info_like` VALUES (5, 'wangwu', '123456', '王五', '12580', NULL, '2020-02-02 15:48:56', NULL);
COMMIT;

1.2 UserInfoLikeDO.java

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
public class UserInfoLikeDO implements Serializable {
private Long id;

private String userName;

private String passWord;

private String nickName;

private String mobile;

private String email;

private LocalDateTime gmtCreate;

private LocalDateTime gmtUpdate;

private static final long serialVersionUID = 1L;

public Long getId() {
return id;
}

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

public String getUserName() {
return userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

public String getPassWord() {
return passWord;
}

public void setPassWord(String passWord) {
this.passWord = passWord;
}

public String getNickName() {
return nickName;
}

public void setNickName(String nickName) {
this.nickName = nickName;
}

public String getMobile() {
return mobile;
}

public void setMobile(String mobile) {
this.mobile = mobile;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public LocalDateTime getGmtCreate() {
return gmtCreate;
}

public void setGmtCreate(LocalDateTime gmtCreate) {
this.gmtCreate = gmtCreate;
}

public LocalDateTime getGmtUpdate() {
return gmtUpdate;
}

public void setGmtUpdate(LocalDateTime gmtUpdate) {
this.gmtUpdate = gmtUpdate;
}

@Override
public String toString() {
return "UserInfoLikeDO{" +
"id=" + id +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
", nickName='" + nickName + '\'' +
", mobile='" + mobile + '\'' +
", email='" + email + '\'' +
", gmtCreate=" + gmtCreate +
", gmtUpdate=" + gmtUpdate +
'}';
}
}

二、模糊查询的四种方式

2.1 直接传参法

将要查询的关键字keyword,在代码中拼接好要查询的格式,如%keyword%,然后直接作为参数传入mapper.xml的映射文件中。

  • 测试代码
1
2
3
4
5
6
@Test
public void selectTest() {
String nickName = "%" + "王" + "%";
List<UserInfoLikeDO> list = userInfoLikeMapper.selectByKeyWord(nickName);
log.info("UserList:{}", list);
}
  • Mapper 接口
1
List<UserInfoDO> selectByKeyWord(String nickName);
  • Mapper 映射文件
1
2
3
4
5
6
7
8
9
10
<select id="selectByKeyWord" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from user_info_like
<where>
<if test="nickName != '' and nickName != null">
and nick_name like #{nickName}
</if>
</where>
</select>

2.2 使用${...}代替#{...}

我们都知道,${}解析过来的参数值不带单引号,#{}解析传过来参数带单引号。

  • 测试代码
1
2
3
4
5
6
@Test
public void selectByWordTest() {
String nickName ="王";
List<UserInfoLikeDO> list = userInfoLikeMapper.selectByWord(nickName);
log.info("UserList:{}", list);
}
  • Mapper 接口
1
List<UserInfoDO> selectByWord(String nickName);
  • Mapper 映射文件
1
2
3
4
5
6
7
8
9
10
<select id="selectByWord" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from user_info_like
<where>
<if test="nickName != '' and nickName != null">
and nick_name like '%${nickName}%'
</if>
</where>
</select>

2.3 CONCAT()函数

MySQLCONCAT()函数用于将多个字符串连接成一个字符串,是最重要的mysql函数之一。

  • 测试代码
1
2
3
4
5
6
@Test
public void concatTest() {
String nickName = "王";
List<UserInfoLikeDO> list = userInfoLikeMapper.selectForConcat(nickName);
log.info("UserList:{}", list);
}
  • Mapper 接口
1
List<UserInfoDO> selectForConcat(String nickName);
  • Mapper 映射文件
1
2
3
4
5
6
7
8
9
10
<select id="selectForConcat" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from user_info_like
<where>
<if test="nickName != '' and nickName != null">
and nick_name like CONCAT('%',#{nickName},'%')
</if>
</where>
</select>

2.4 Mybatisbind

bind:可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值。

  • 测试代码
1
2
3
4
5
6
@Test
public void bindTest() {
String nickName = "王";
List<UserInfoLikeDO> list = userInfoLikeMapper.selectForBind(nickName);
log.info("UserList:{}", list);
}
  • Mapper 接口
1
List<UserInfoDO> selectForBind(String nickName);
  • Mapper 映射文件
1
2
3
4
5
6
7
8
9
10
11
<select id="selectForBind" resultMap="BaseResultMap">
<bind name="keyWord" value="'%' + nickName + '%'" />
select
<include refid="Base_Column_List" />
from user_info_like
<where>
<if test="nickName != '' and nickName != null">
and nick_name like #{keyWord}
</if>
</where>
</select>
  • 说明

文中省略了的BaseResultMap/Base_Column_List等,详见UserInfoLikeMapper.xml

三、总结

  1. 推荐使用CONCAT()函数或者 bind 的方式;
  2. 注意关键词中有%_这些特殊字符如何处理。

更多 Java 笔记,详见【Java 知识笔记本】,欢迎提供想法建议。

Github 示例代码

Van wechat
最新文章,欢迎您扫一扫上面的微信公众号!
-------------    本文结束  感谢您的阅读    -------------