概要
MP封装了一些常用的查询方法以select开头
image
一、查询基础
1、根据主键查询单条记录
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n6" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
@Resource
UserMapper userMapper;
@Test
public void batchIds() {
User user = userMapper.selectById(1);
log.error(user.toString());
}
}
//SELECT * FROM user WHERE uid=? </pre>
2、根据主键批量查询
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n8" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
@Resource
UserMapper userMapper;
@Test
public void batchIdsTest() {
List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3, 4, 5));
users.forEach(user -> log.debug(user.getUsername()));
}
}
// sql语句
// SELECT * FROM user WHERE uid IN ( ? , ? , ? , ? , ? ) </pre>
3、Entity作为条件查询数据
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n10" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">public class UserMapperTest {
@Resource
UserMapper userMapper;
@Test
public void selectByEntity() {
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("username", "qq123456");
columnMap.put("locked", 0);
List<User> users = userMapper.selectByMap(columnMap);
users.forEach(user -> log.trace(user.getUsername()));
}
}</pre>
4、Map 作为条件查询数据
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n12" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
@Resource
UserMapper userMapper;
@Test
public void selectByMapTest() {
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("username", "qq123456");
columnMap.put("locked", 0);
List<User> users = userMapper.selectByMap(columnMap);
users.forEach(user -> log.trace(user.getUsername()));
}
}
// sql语句
// SELECT * FROM user WHERE username=? and locked=?</pre>
二、Wrapper
1、说明
上面我们讲的都是一些比较简单的查询,当通用的查询无法满足时,比如 过滤列,限定符, 分组,排序,关联查询等等
QueryWrapper封装了很多方法来实现复杂的查询
2、条件查询对象介绍
| 类 | 说明 |
|---|---|
| Wrapper | 条件查询对象抽象类，最顶端父类 |
| AbstractWrapper | 用于查询条件封装，生成 sql 的 where 条件 |
| AbstractLambdaWrapper | Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column |
| LambdaQueryWrapper | Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column |
| LambdaUpdateWrapper | Lambda 更新封装Wrapper |
| QueryWrapper | 实体对象封装操作类 |
| UpdateWrapper | Update 条件封装，用于实体对象更新操作 |
3、QueryWrapper常用的方法
| 查询方式 | 说明 |
|---|---|
| select | 设置 SELECT 查询字段 |
| and | AND 语句，拼接 + AND 字段=值
|
| or | OR 语句，拼接 + OR 字段=值
|
| eq | 等于= |
| allEq | 基于 map 内容等于= |
| ne | 不等于<> |
| gt | 大于> |
| ge | 大于等于>= |
| lt | 小于< |
| le | 小于等于<= |
| like | 模糊查询 LIKE |
| notLike | 模糊查询 NOT LIKE |
| in | IN 查询 |
| notIn | NOT IN 查询 |
| isNull | NULL 值查询 |
| isNotNull | IS NOT NULL |
| groupBy | 分组 GROUP BY |
| having | HAVING 关键词 |
| orderBy | 排序 ORDER BY |
| orderByAsc | ASC 排序 ORDER BY |
| orderByDesc | DESC 排序 ORDER BY |
| exists | EXISTS 条件语句 |
| notExists | NOT EXISTS 条件语句 |
| between | BETWEEN 条件语句 |
| notBetween | NOT BETWEEN 条件语句 |
| last | 拼接在最后 |
4、栗子
selectOne与QueryWrapper
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n128" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> @Test
public void selectOne() {
QueryWrapper<User> qw = new QueryWrapper<>();
qw.select("uid", "username", "phone").eq("uid", 2)
.eq("locked", 0);
User user = userMapper.selectOne(qw);
log.debug(user.getUsername());
}
</pre>
注意: 当查询的结果返回多个对象的时候会抛出异常
selectList
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n131" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">// SELECT uid,username,password FROM user WHERE username = ? OR phone = ? OR email = ? ORDER BY reg_time DESC
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
@Resource
UserMapper userMapper;
@Test
public void selectByQueryWrap() {
QueryWrapper<User> qw = new QueryWrapper<>();
qw.select("uid", "username", "password")
.eq("username", "qq123456")
.or()
.eq("phone", "12345678")
.or()
.eq("email", "123456@qq.com")
.orderByDesc("reg_time");
List<User> users = userMapper.selectList(qw);
users.forEach(user -> log.debug(user.getUsername()));
}
}</pre>
selectCount查询总条数
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" cid="n133" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">@Test
public void selectCount() {
QueryWrapper<User> qw = new QueryWrapper<>();
Integer count = userMapper.selectCount(qw.eq("locked", 0));
log.debug("总条数{}", count);
}
</pre>
分组操作
<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n135" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> //SELECT uid,username,password,phone,sex,email,mark,last_login,login_ip,head,reg_time,locked FROM user WHERE locked = ? GROUP BY sex
@Test
public void selectGroupBy() {
QueryWrapper<User> qw = new QueryWrapper<>();
qw.eq("locked", 0).groupBy("sex");
List<User> users = userMapper.selectList(qw);
users.forEach(user -> log.debug(user.getUsername()));
}</pre>
三、分页查询
1、注册PaginationInterceptor分页插件
<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="java" cid="n138" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">@Configuration
public class MybatisPlusConfiguration {
@Bean
public PaginationInterceptor paginationInterceptor() {
// paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不
return new PaginationInterceptor();
}
}</pre>
2、selectPage
<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="java" cid="n140" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 30px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> @Test
public void selectPage() {
IPage<User> page = userMapper.selectPage(new Page<>(1, 10), new QueryWrapper<User>().eq("locked", 0));
log.debug("总条数:{}", page.getTotal());
log.debug("总页数:{}", page.getPages());
log.debug("当前第{}页", page.getCurrent());
log.debug("当前条数:{}", page.getSize());
// 获取当前分页数据
List<User> records = page.getRecords();
records.forEach(user -> log.debug(user.getUid() + ""));
}</pre>
网友评论