目录
1 ge、gt、le、lt、isNull、isNotNull
4 like、notLike、likeLeft、likeRight
5 in、notIn、inSql、notinSql、exists、notExists
8 orderBy、orderByDesc、orderByAsc
一 wapper介绍
Wrapper : 条件构造抽象类,最顶端父类
AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
QueryWrapper : 查询条件封装
UpdateWrapper : Update 条件封装
AbstractLambdaWrapper : 使用Lambda 语法
LambdaQueryWrapper :用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper : Lambda 更新封装Wrapper
二 查询方式
查询方式 |
说明 |
setSqlSelect |
设置 SELECT 查询字段 |
where |
WHERE 语句,拼接 + WHERE 条件 |
and |
AND 语句,拼接 + AND 字段=值 |
andNew |
AND 语句,拼接 + AND (字段=值) |
or |
OR 语句,拼接 + OR 字段=值 |
orNew |
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 |
orderAsc |
ASC 排序 ORDER BY |
orderDesc |
DESC 排序 ORDER BY |
exists |
EXISTS 条件语句 |
notExists |
NOT EXISTS 条件语句 |
between |
BETWEEN 条件语句 |
notBetween |
NOT BETWEEN 条件语句 |
addFilter |
自由拼接 SQL |
last |
拼接在最后,例如:last(“LIMIT 1”) |
三 测试用例
1 ge、gt、le、lt、isNull、isNotNull
/**
* 功能描述:ge、gt、le、lt、isNull、isNotNull 测试
*
* @author cakin
* @date 2020/11/15
*/
@Test
public void testDelete() {
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper
.isNull("name") // 为Null
.ge("age", 12) // 大于等于
.isNotNull("email"); // 不为Null
int result = userMapper.delete(queryWrapper);
System.out.println("删除了" + result + "行");
}
测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@36dfbdaf] was not registered for synchronization because synchronization is not active
2020-11-15 16:09:40.044 INFO 2464 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-11-15 16:09:40.522 INFO 2464 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1880869198 wrapping com.mysql.cj.jdbc.ConnectionImpl@3d8b319e] will not be managed by Spring
==> Preparing: UPDATE user SET deleted=1 WHERE deleted=0 AND (name IS NULL AND age >= ? AND email IS NOT NULL)
==> Parameters: 12(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@36dfbdaf]
删除了1行
2 eq、ne
注意