集天地之...咳咳咳，集百家之长，借先贤之悟，望不堪之忆，补己身之缺，展后之装之业。
然己能力缺土，记性似鱼，学不能以致用，借此良机录制后用，愿皆遇道合友人，更进一步。
目录
一、分金定穴
1、寻龙分金看缠山
模糊查询 .like() LIKE ‘%值%’ .likeLeft() LIKE ‘%值’ .likeRight() LIKE ‘值%’
范围查询 .gt() >值 .ge() >= 值 .lt() <值 .le() <= 值 .between() BETWEEN 值1 AND 值2
精确查询 .eq() = 值 .ne() != 值
排序 .orderByAsc() ORDER BY 字段,… ASC .orderByDesc() ORDER BY 字段, … DESC
// 条件
LambdaQueryWrapper<RepositoryInfo> queryWrapper = new LambdaQueryWrapper<RepositoryInfo>()
.like(page.getRepositoryName() != null, RepositoryInfo::getRepositoryName, page.getRepositoryName())
.eq(!StringUtils.isEmpty(page.getRepositoryCode()), RepositoryInfo::getRepositoryCode, page.getRepositoryCode())
.ge(page.getRepositoryStartDateFrom() != null, RepositoryInfo::getRepositoryStartDate, page.getRepositoryStartDateFrom())
.le(page.getRepositoryStartDateTo() != null, RepositoryInfo::getRepositoryStartDate, page.getRepositoryStartDateTo())
.eq(page.getRepositoryState() != null && page.getRepositoryState() != -1, RepositoryInfo::getRepositoryState, page.getRepositoryState())
.orderByAsc(RepositoryInfo::getRepositoryPhone);
// 查询
List<RepositoryInfo> list = repositoryInfoService.list(queryWrapper);
// SQL
SELECT
repository_id,
repository_name,
repository_code,
repository_phone,
repository_state,
repository_address,
repository_start_date
FROM
repository_info
WHERE
( repository_name LIKE ? AND repository_code = ? AND repository_start_date >= ? AND repository_start_date <= ? AND repository_state = ? )
ORDER BY
repository_phone ASC
空值查询 .isNull() 字段 IS NULL .isNotNull() 字段 IS NOT NULL
筛选查询 .in() 字段 IN (values.get(0), values.get(1), …) .notIn() 字段 NOT IN (values.get(0), values.get(1), …)
.inSql() 字段 NOT IN ( sql语句 )
追加SQL .last( sql 语句 ) 将 sql 语句 拼接到 sql 的最后
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.isNotNull( BrandInfo::getBrandCnName)
.in(page.getNums() != null && page.getNums().size() > 0, BrandInfo::getBrandNum, page.getNums())
.last( " limit 10 " );
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( brand_cn_name IS NOT NULL AND brand_num IN ( ?,?,? ) )
LIMIT 10
2、一重缠是一重关
.and() : 相连的条件必须同时满足, 默认都是 and 相连
.or() : 主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
两个配合通常可以 建立比较 复杂的查询
.nested() : 正常嵌套 不带 AND 或者 OR
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.eq( num != null, BrandInfo::getBrandNum, num )
.like( !StringUtils.isEmpty(name), BrandInfo::getBrandCnName, name)
.or()
.like( !StringUtils.isEmpty(name), BrandInfo::getBrandEnName, name);
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( brand_num = 2 AND brand_cn_name LIKE %hua% OR brand_en_name LIKE %hua% )
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.eq(num != null, BrandInfo::getBrandNum, num )
.or(i->i.like( !StringUtils.isEmpty(name), BrandInfo::getBrandCnName, name)
.or()
.like( !StringUtils.isEmpty(name), BrandInfo::getBrandEnName, name)
);
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( brand_num = 2 OR ( brand_cn_name LIKE %hua% OR brand_en_name LIKE %hua% ) )
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.eq(num != null, BrandInfo::getBrandNum, num )
.and(i->i.like( !StringUtils.isEmpty(name), BrandInfo::getBrandCnName, name)
.or()
.like( !StringUtils.isEmpty(name), BrandInfo::getBrandEnName, name)
);
// 进行查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( brand_num = 2 AND ( brand_cn_name LIKE %hua% OR brand_en_name LIKE %hua% ) )
// 条件
String name = "hua";
Integer num = 2;
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.nested(i->i.eq( BrandInfo::getBrandNum, num).like(BrandInfo::getBrandCnName, name))
.or()
.nested(i->i.like(BrandInfo::getBrandEnName, name));
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( ( brand_num = ? AND brand_cn_name LIKE ? ) OR ( brand_en_name LIKE ? ) )
3、关门如有八重险,不出阴阳八卦形
.func(i->{ if(条件) { i.条件(); } else{ i.条件(); } }) : 根据 条件 动态调整SQL 结构
.select() : 相当于定义 select 查询的字段
.lambda() : 将 QueryWrapper() 转换成 LambdaQueryWrapper() , 就可以使用 Lambda 语法
.groupBy() : 分组
.having( sql ) : 对 分组结果 进行筛选, 可以使用 SQL 调用 聚合函数
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new QueryWrapper<BrandInfo>()
.select("brand_num", " count(0) count")
.lambda()
.groupBy(BrandInfo::getBrandNum)
.having( " count(0) > 2");
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
System.out.println("list = " + list);
SELECT brand_num, count( 0 ) count FROM brand_info GROUP BY brand_num HAVING count( 0 ) > 2
.func(i->{ if(条件) { i.条件(); } else{ i.条件(); } }) : 根据 条件 动态调整SQL 结构
String name = "hua";
Integer num = 2;
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.func(i->{if( num > 2 ){
i.like(BrandInfo::getBrandCnName, name);
}else{
i.like(BrandInfo::getBrandEnName, name);
}});
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( brand_en_name LIKE ? )
.apply( sql , params ) : sql 包含 函数 , 可以使用 {index} 进行占位 , params 为 传入的值
String date = "2020-09-08";
// 条件
LambdaQueryWrapper<BrandInfo> queryWrapper = new LambdaQueryWrapper<BrandInfo>()
.apply( "date_format(brand_founded_date,'"2020-09-08"') = {0} " , date );
// 查询
List<BrandInfo> list = brandInfoService.list(queryWrapper);
// SQL
SELECT
brand_id,
brand_cn_name,
brand_en_name,
brand_logo_path,
brand_web_url,
brand_num,
brand_info,
brand_founded_date
FROM
brand_info
WHERE
( date_format( brand_founded_date, '%Y-%m-%d' ) = '2020-09-08' )
二、寻龙千万里
以下条件构造器的方法入参中的column均表示数据库字段
1、左手罗盘、右手铲
/**
* 名字包含静并且年龄小于40
* <p>
* WHERE name LIKE '%静%' AND age < 40
*/
@Test
public void selectByWrapperOne() {
QueryWrapper<User> wrapper = new QueryWrapper();
wrapper.like("name", "静").lt("height", 160);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 名字包含静
* 身高大于150小于160
* qq不能为空
* <p>
* WHERE name LIKE '%静%' AND height BETWEEN 150 AND 160 AND qq IS NOT NULL
*/
@Test
public void selectByWrapperTwo() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.like("name", "静").between("height", 150, 160).isNotNull("qq");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 名字为苏性
* 或者身高大于等于160
* 按照身高降序排序，身高相同按照id升序排序
* <p>
* WHERE name LIKE '苏%' OR height >= 160 ORDER BY height DESC , id ASC
*/
@Test
public void selectByWrapperThree() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "苏").or()
.ge("height", 160).orderByDesc("height").orderByAsc("id");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询创建时间为2019年2月14
* 并且上级领导姓苏
* <p>
* WHERE date_format(create_time,'%Y-%m-%d') = '2019-02-14' AND manager_id IN (select id from user where name like '苏%')
*/
@Test
public void selectByWrapperFour() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
.inSql("manager_id", "select id from user where name like '苏%'");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询苏姓
* 并且身高小于160或者qq不为空
* <p>
* WHERE name LIKE '苏%' AND ( height < 160 OR qq IS NOT NULL )
*/
@Test
public void selectByWrapperFive() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "苏").and(qw -> qw.lt("height", 160).or().isNotNull("qq"));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询苏姓
* 并且身高大于150、身高小于160、邮箱不能为空
* <p>
* WHERE name LIKE ? OR ( height BETWEEN ? AND ? AND qqIS NOT NULL )
*/
@Test
public void selectByWrapperSix() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.likeRight("name", "苏").or(
qw -> qw.between("height", 150, 160).isNotNull("qq")
);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* (身高小于150或者qq不为空) 并且名字姓苏
* WHERE ( height < 40 OR qq IS NOT NULL ) AND name LIKE '苏%'
*/
@Test
public void selectByWrapperSeven() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.nested(qw -> qw.lt("height", 150).or().isNotNull("qq"))
.likeRight("name", "苏");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询height为150、160、170
* WHERE height IN (?,?,?)
*/
@Test
public void selectByWrapperEight() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.in("height", Arrays.asList(150, 160, 170));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 查询一条数据
* limit 1
*/
@Test
public void selectByWrapperNine() {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.in("height", Arrays.asList(150, 160, 170)).last("limit 1");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}2、泼天富贵在眼前
1)、ge、gt、le、lt、isNull、isNotNull
@Test
public void testDelete() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.isNull("name")
.ge("height", 160)
.isNotNull("qq");
int result = userMapper.delete(queryWrapper);
System.out.println("delete return count = " + result);
}UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND height >= ? AND qq IS NOT NULL
2)、eq、ne
/**
* eletOne返回的是一条实体记录，当出现多条时会报错
*/
@Test
public void testSelectOne() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "小苏");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}3)、between、notBetween
/**
* 包含大小边界
*/
@Test
public void testSelectCount() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("height", 150, 160);
Integer count = userMapper.selectCount(queryWrapper);
System.out.println(count);
}SELECT COUNT(1) FROM user WHERE deleted=0 AND height BETWEEN ? AND ?
4)、allEq
@Test
public void testSelectList() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("name", "苏");
map.put("height", 180);
queryWrapper.allEq(map);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}SELECT id,name,height,qq,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ? AND id = ? AND height = ?
5)、like、notLike、likeLeft、likeRight
@Test
public void testSelectMaps() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.notLike("name", "苏")
.likeRight("qq", "0");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
maps.forEach(System.out::println);
}6)、in、notIn、inSql、notinSql、exists、notExists
@Test
public void testSelectObjs() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//queryWrapper.in("id", 1, 2, 3);
queryWrapper.inSql("id", "select id from user where id < 3");
List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
objects.forEach(System.out::println);
}7)、指定要查询的列
@Test
public void testSelectListColumn() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name", "height");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}......
三、口诀牢记不心慌
天赋为前，手脑知后，键盘犹剑，鼠标似刀，
目光如炬，心如细发，勤学苦练，先贤共进。
扫一扫
2648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
