MybatisPlus條件查詢的具體使用
更新時(shí)間：2024年01月11日 10:50:18 作者：我也有夢(mèng)想呀
MybatisPlus通過條件構(gòu)造器可以組裝復(fù)雜的查詢條件,本文主要介紹了MybatisPlus條件查詢的具體使用,具有一定的參考價(jià)值,感興趣的可以了解一下
1、是什么？
MybatisPlus通過條件構(gòu)造器可以組裝復(fù)雜的查詢條件，寫一些復(fù)雜的SQL語句，從而簡化我們的開發(fā)提升我們的開發(fā)效率
# 可以簡單的理解為就是我們寫SQL語句時(shí)where后面的條件 where xxx...
2、怎么玩？
(1) 獲取條件構(gòu)造器的幾種方式
/**
* 獲取普通條件構(gòu)造器的幾種方式
*/
@Test
public void testGetWrapper() {
Wrapper<Employee> wrapper = new QueryWrapper<Employee>();
Wrapper<Employee> query = Wrappers.<Employee>query();
}
/**
* 獲取Lambda條件構(gòu)造器的幾種方式
*/
@Test
public void testGetLambdaWrapper() {
Wrapper<Employee> wrapper = new LambdaQueryWrapper<>();
Wrapper<Employee> query = Wrappers.<Employee>lambdaQuery();
}
(2) eq 等于 =
例如：我想查詢姓名為張三的員工信息
- mysql
select * from employee where name = '張三';
- MP
/**
* 等于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* eq(R column, Object val) : select * from employee where name = '張三';
* <p>
*/
@Test
public void testEq() {
Wrapper<Employee> wrapper = Wrappers
.<Employee>lambdaQuery()
.eq(Employee::getName, "張三");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在eq基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* eq(boolean condition, R column, Object val);
*/
@Test
public void testEqCondition() {
// String keyword = "張三";
String keyword = "";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.eq(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(3) ne 不等于 <>
例如：我想查詢姓名不為張三的員工信息
- mysql
select * from employee where name <> '張三';
- MP
/**
* 不等于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* ne(R column, Object val)
*/
@Test
public void testNe() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.ne(Employee::getName, "張三");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在ne基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* ne(boolean condition, R column, Object val);
*/
@Test
public void testNeCondition() {
String keyword = "張三";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.ne(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(4) gt 大于 >
例如：我想查詢id大于3的員工信息
- mysql
select * from employee where id > 3;
- MP
/**
* 大于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* gt(R column, Object val)
*/
@Test
public void testGt() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.gt(Employee::getId, 3);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在gt基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* gt(boolean condition, R column, Object val)
*/
@Test
public void testGtCondition() {
Integer keyword = 3;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.gt(keyword < 5, Employee::getId, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(5) ge 大于等于 >=
例如：我想查詢id大于等于3的員工信息
- mysql
select * from employee where id > 3;
- MP
/**
* 大于等于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* ge(R column, Object val)
*/
@Test
public void testGe() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.ge(Employee::getId, 3);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在ge基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* ge(boolean condition, R column, Object val)
*/
@Test
public void testGeCondition() {
Integer keyword = 3;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.ge(keyword < 5, Employee::getId, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(6) lt 小于 <
例如：我想查詢id小于3員工信息
- mysql
select * from employee where id < 3;
- MP
/**
* 小于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* lt(R column, Object val)
*/
@Test
public void testLt() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.lt(Employee::getId, 3);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在lt基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* lt(boolean condition, R column, Object val)
*/
@Test
public void testLtCondition() {
Integer keyword = 3;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.lt(keyword < 5, Employee::getId, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(7) le 小于等于 <=
例如：我想查詢id小于等于3的員工信息
- mysql
select * from employee where id <= 3;
- MP
/**
* 小于等于條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* le(R column, Object val)
*/
@Test
public void testLe() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.le(Employee::getId, 3);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在le基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* le(boolean condition, R column, Object val)
*/
@Test
public void testLeCondition() {
Integer keyword = 3;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.le(keyword < 5, Employee::getId, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(8) between 范圍條件 BETWEEN xxx and xxx
例如：我想查詢id在2-4的員工信息
- mysql
select * from employee WHERE id BETWEEN 2 and 4;
- MP
/**
* 范圍條件，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接(但是沒有意義)；可以使用or連接
* between(R column, Object val1, Object val2)
*/
@Test
public void testBetween() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.between(Employee::getId, 2, 4)
.or()
.between(Employee::getId, 2, 4);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在between基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* between(boolean condition, R column, Object val1, Object val2)
*/
@Test
public void testBetweenCondition() {
Integer keyword1 = 2;
Integer keyword2 = 4;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.between(keyword1 < 3 || keyword2 > 5, Employee::getId, keyword1, keyword2);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(9) notBetween 范圍條件 NOT BETWEEN xxx and xxx
例如：我想查詢id不在2-4的員工信息
- mysql
select * from employee WHERE id NOT BETWEEN 2 and 4;
- MP
/**
* 不在范圍條件中，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接(但是沒有意義)；可以使用or連接
* notBetween(R column, Object val1, Object val2)
*/
@Test
public void testNotBetween() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notBetween(Employee::getId, 2, 4);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在notBetween基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* notBetween(boolean condition, R column, Object val1, Object val2)
*/
@Test
public void testNotBetweenCondition() {
Integer keyword1 = 2;
Integer keyword2 = 4;
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notBetween(keyword1 < 3 || keyword2 > 5, Employee::getId, keyword1, keyword2);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(10) like 全模糊查詢條件 like %xxx%
例如：我想查詢姓名包含張的員工信息
- mysql
select * from employee where name like '%張%';
- MP
/**
* 全模糊查詢，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* like(R column, Object val)
*/
@Test
public void testLike() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.like(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在like基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* like(boolean condition, R column, Object val)
*/
@Test
public void testLikeCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.like(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(11) like 不在全模糊查詢條件中 not like %xxx%
例如：我想查詢姓名不包含張的員工信息
- mysql
select * from employee where name not like '%張%';
- MP
/**
* 不在全模糊查詢條件中，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* notLike(R column, Object val)
*/
@Test
public void testNotLike() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLike(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在notLike基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* notLike(boolean condition, R column, Object val)
*/
@Test
public void testNotLikeCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLike(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(12) likeLife 左模糊查詢條件 like %xxx
例如：我想查詢姓名以張結(jié)尾的員工信息
- mysql
select * from employee where name like '%張';
- MP
/**
* 左模糊查詢，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* likeLife(R column, Object val)
*/
@Test
public void testLikeLife() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.likeLeft(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在likeLeft基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* likeLeft(boolean condition, R column, Object val)
*/
@Test
public void testLikeLifeCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.likeLeft(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(13) notLikeLife 左模糊查詢條件 not like %xxx
例如：我想查詢姓名不以張結(jié)尾的員工信息
- mysql
select * from employee where name not like '%張';
- MP
/**
* 不在左模糊查詢條件中，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* notLikeLeft(R column, Object val)
*/
@Test
public void testNotLikeLife() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLikeLeft(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在notLikeLeft基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* notLikeLeft(boolean condition, R column, Object val)
*/
@Test
public void testNotLikeLifeCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLikeLeft(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(14) likeRight 右模糊查詢條件 like xxx%
例如：我想查詢姓名以張開頭的員工信息
- mysql
select * from employee where name like '張%';
- MP
/**
* 右模糊查詢，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* likeRight(R column, Object val)
*/
@Test
public void testLikeRight() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.likeRight(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在likeRight基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* likeRight(boolean condition, R column, Object val)
*/
@Test
public void testLikeRightCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.likeRight(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(15) notLikeRight 不在右模糊查詢條件中 not like xxx%
例如：我想查詢姓名不以張開頭的員工信息
- mysql
select * from employee where name not like '張%';
- MP
/**
* 不在右模糊查詢條件中，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* notLikeRight(R column, Object val)
*/
@Test
public void testNotLikeRight() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLikeRight(Employee::getName, "張");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在notLikeRight基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* notLikeRight(boolean condition, R column, Object val)
*/
@Test
public void testNotLikeRightCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notLikeRight(StringUtils.isNotBlank(keyword), Employee::getName, keyword);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(16) isNull field為nul查詢條件 is null
例如：我想查詢姓名為null的員工信息
- mysql
select * from employee where name is null;
- MP
/**
* 查詢field為null，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* isNull(R column, Object val)
*/
@Test
public void testIsNull() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.isNull(Employee::getName);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在isNull基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* isNull(boolean condition, R column)
*/
@Test
public void testIsNullCondition() {
String keyword = "張";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.isNull(StringUtils.isNotBlank(keyword), Employee::getName);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(17) isNotNull field不為nul查詢條件 is not null
例如：我想查詢姓名不為null的員工信息
- mysql
select * from employee where name is not null;
- MP
/**
* 查詢field不為null，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* isNotNull(R column, Object val) : select * from employee where name is not null;
*/
@Test
public void testIsNotNull() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.isNotNull(Employee::getName);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在IsNotNull基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* IsNotNull(boolean condition, R column)
*/
@Test
public void testIsNotNullCondition() {
String keyword = "劉";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.isNotNull(StringUtils.isNotBlank(keyword), Employee::getName);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(18) inSql in操作 in (xxx,xxx,xxx)
例如：我想查詢id在1,3,5中的員工信息
- mysql
select * from employee where id in(1,3,5);
- MP
/**
* in操作，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* inSql(R column, String inValue)
*/
@Test
public void testInSql() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.inSql(Employee::getId, "1,3,5");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在inSql基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* inSql(boolean condition, R column, String inValue)
*/
@Test
public void testInSqlCondition() {
// String str = Arrays.asList(1,3,5).toString();
// String ids = str.substring(1, str.length() - 1);
String ids = StringUtils.joinWith(",",1,3,5);
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.inSql(StringUtils.isNotBlank(ids), Employee::getId, ids);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(19) notInSql not in操作 not in (xxx,xxx,xxx)
例如：我想查詢id不在1,3,5中的員工信息
- mysql
select * from employee where id not in(1,3,5);
- MP
/**
* not in操作，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* notInSql(R column, String inValue)
*/
@Test
public void testNotInSql() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notInSql(Employee::getId, "1,3,5");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在notInSql基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* notInSql(boolean condition, R column, String inValue)
*/
@Test
public void testNotInSqlCondition() {
String ids = StringUtils.joinWith(",", 1,3,5);
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notInSql(StringUtils.isNotBlank(ids), Employee::getId, ids);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(20) groupBy 分組操作 groupBy xxx
例如：我想按照性別分組統(tǒng)計(jì)員工個(gè)數(shù)
- mysql
select COUNT(*) AS emp_count from employee GROUP BY `sex`;
- MP
/**
* 分組操作，可以存在多個(gè)，多個(gè)條件之間默認(rèn)使用and連接
* groupBy(R column)
*/
@Test
public void testGroupBy() {
Wrapper<Employee> wrapper = Wrappers.<Employee>query()
.select("count(id) as emp_count")
.groupBy("sex");
List<Map<String, Object>> listMaps = employeeService.listMaps(wrapper);
log.info("listMaps:{}", listMaps);
}
/**
* 在groupBy基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* groupBy(boolean condition, R column)
*/
@Test
public void testGroupByCondition() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>query()
.select("count(id) as emp_count")
.groupBy(StringUtils.isNotBlank(keyword), "sex");
List<Map<String, Object>> listMaps = employeeService.listMaps(wrapper);
log.info("listMaps:{}", listMaps);
}
注意我用的是普通的條件構(gòu)造器
(21) orderByAsc 排序操作: 升序 order by xxx asc
例如：我想按照員工編號(hào)升序查詢員工信息
- mysql
select * from employee order by id asc;
- MP
/**
* 排序操作: 升序
* orderByAsc(R column)
*/
@Test
public void testOrderByAsc() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.orderByAsc(Employee::getId);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在orderByAsc基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* orderByAsc(boolean condition, R column)
*/
@Test
public void testOrderByAscCondition() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.orderByAsc(StringUtils.isNotBlank(keyword), Employee::getId);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(22) orderByDesc 排序操作: 降序 order by xxx desc
例如：我想按照員工編號(hào)降序查詢員工信息
- mysql
select * from employee order by id desc;
- MP
/**
* 排序操作: 降序
* orderByDesc(R column)
*/
@Test
public void testOrderByDesc() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.orderByDesc(Employee::getId);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
/**
* 在orderByDesc基礎(chǔ)上，滿足condition為true的情況，才會(huì)拼接條件
* orderByDesc(boolean condition, R column)
*/
@Test
public void testOrderByDescCondition() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.orderByDesc(StringUtils.isNotBlank(keyword), Employee::getId);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(23) orderBy 排序操作: 自定義升、降序 order by xxx asc,xxx desc
例如：我想按照姓名升序，員工編號(hào)降序查詢員工信息
- mysql
select * from employee order by name asc , id desc;
- MP
/**
* 排序操作: 自定義升、降序
* orderBy(boolean condition, boolean isAsc, R column) : select * from employee ORDER BY id DESC ;
* 參數(shù)解釋:
* param1: 滿足condition為true的情況，才會(huì)拼接條件
* param2: 是否升序 true:升序 false:降序
* param3: 排序字段
* <p>
* 排序字段是可以為多個(gè)的
*/
@Test
public void testOrder() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.orderBy(StringUtils.isNotBlank(keyword), true, Employee::getId)
.orderBy(StringUtils.isNotBlank(keyword), false, Employee::getName);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(24) having 分組后操作 having xxx …
例如：我想查詢姓名為張三的員工信息
- mysql
select sex , COUNT(*) as emp_count from employee GROUP BY sex HAVING emp_count >=3;
- MP
/**
* 分組后操作
* orderBy(boolean condition, boolean isAsc, R column)
*/
@Test
public void testHaving() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>query()
.select("sex,count(*) as emp_count")
.groupBy("sex")
.having("emp_count >= 3");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(25) func 內(nèi)嵌邏輯 類似于在mysql中做條件判斷
例如：如果條件存在查詢id為1的員工信息，否則查詢id不等于2的員工信息;
當(dāng)然這個(gè)是可以多重判斷的
- MP
/**
* 內(nèi)嵌邏輯：可以在滿足某些條件|不滿足某些條件時(shí)，添加查詢條件
* func(Consumer<Children> consumer)
* 類似于MySQL中的If語句
* IF search_condition THEN
* statement_list
* ELSE
* statement_list
* END IF;
*/
@Test
public void testFunc() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().func(w -> {
if (StringUtils.isNotBlank(keyword)) {
w.eq(Employee::getId, 1);
} else {
w.ne(Employee::getId, 2);
}
});
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(26) or 或操作
- MP
/**
* 或操作
* or SELECT * FROM employee WHERE (name = ? OR id = ?)
*/
@Test
public void testOr() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.eq(Employee::getName, "張三")
.or()
.eq(Employee::getId, 2);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(27) and 與操作
- MP
/**
* 與操作
* and SELECT * FROM employee WHERE (name = ? AND id = ?)
*/
@Test
public void testAnd() {
String keyword = "ly";
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.eq(Employee::getName, "張三")
.and(e -> {
e.eq(Employee::getId,2);
});
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(28) nested 正常嵌套 不帶 AND 或者 OR
- MP
/**
* 正常嵌套 不帶 AND 或者 OR
* nested(Consumer<Param> consumer) SELECT * FROM employee WHERE ((id = ? OR id = ?))
*/
@Test
public void testNested() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.nested(w -> {
w.eq(Employee::getId, 1)
.or()
.eq(Employee::getId, 2);
});
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(29) apply 自定義查詢條件
- MP
/**
* 自定義查詢條件
* apply(String applySql, Object... values) SELECT * FROM employee WHERE (id = ?)
*/
@Test
public void testApply() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.apply("id = {0}", 2);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(30) last 無視優(yōu)化規(guī)則直接拼接到 sql 的最后
- MP
/**
* 無視優(yōu)化規(guī)則直接拼接到 sql 的最后
* last(String lastSql) SELECT * FROM employee limit 0 , 1
*/
@Test
public void testLast() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.last("limit 0,1");
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(31) exists mysql中的exists 語句,當(dāng)子查詢中的存在查詢結(jié)果時(shí),我們的主查詢結(jié)果才會(huì)顯示
- MP
/**
* exists 語句,當(dāng)子查詢中的存在查詢結(jié)果時(shí),我們的主查詢結(jié)果才會(huì)顯示
* exists(String existsSql, Object... values) SELECT id,name,password,sex,birthday,address,email FROM employee WHERE (EXISTS (select id from employee where id = 10))
*/
@Test
public void testExists() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.exists("select id from employee where id = {0}", 10);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}
(32) notExists mysql中的notExists 語句,當(dāng)子查詢中的不存在查詢結(jié)果時(shí),我們的主查詢結(jié)果才會(huì)顯示
- MP
/**
* notExists 語句,當(dāng)子查詢中的不存在查詢結(jié)果時(shí),我們的主查詢結(jié)果才會(huì)顯示
* notExists(String existsSql, Object... values) SELECT id,name,password,sex,birthday,address,email FROM employee WHERE (EXISTS (select id from employee where id = 10))
*/
@Test
public void testNotExists() {
Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()
.notExists("select id from employee where id = {0} or id = {1}", 10, 1);
List<Employee> employees = employeeService.list(wrapper);
log.info("employees:{}", employees);
}到此這篇關(guān)于MybatisPlus條件查詢的具體使用的文章就介紹到這了,更多相關(guān)MybatisPlus條件查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- MybatisPlus多表查詢及分頁查詢完整代碼
- SpringBoot整合Mybatis-Plus實(shí)現(xiàn)關(guān)聯(lián)查詢
- mybatis-plus分頁查詢的實(shí)現(xiàn)實(shí)例
- 深入解析MybatisPlus多表連接查詢
- mybatis-plus @select動(dòng)態(tài)查詢方式
- mybatis-plus多表查詢操作方法
- SpringBoot使用mybatis-plus分頁查詢無效的問題解決
- SpringBoot整合mybatis-plus實(shí)現(xiàn)分頁查詢功能
- Mybatisplus集成springboot完成分頁查詢功能(示例代碼)
- MybatisPlus處理大表查詢的實(shí)現(xiàn)步驟
相關(guān)文章
這篇文章主要介紹了mybatis-plus查詢方式,部分字段沒有值問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
這篇文章主要介紹了微信支付java版本之獲取Access_token，java如何獲取Access_token，感興趣的小伙伴們可以參考一下2016-08-08
Java基于logback?MessageConverter實(shí)現(xiàn)日志脫敏方案分析
本文介紹了一種日志脫敏方案,即基于logbackMessageConverter和正則匹配的方法,該方法的優(yōu)點(diǎn)是侵入性低,工作量少,只需修改xml配置文件,適用于老項(xiàng)目,感興趣的朋友跟隨小編一起看看吧2024-10-10
java webApp異步上傳圖片實(shí)現(xiàn)代碼
這篇文章主要為大家詳細(xì)介紹了java webApp異步上傳圖片實(shí)現(xiàn)代碼,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-11-11
這篇文章主要介紹了SpringBoot之如何指定配置文件啟動(dòng)問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。2023-04-04
使用內(nèi)省相對(duì)于直接使用反射更加安全可靠，Java的反射機(jī)制比較特殊，它不同于一般的編程方式，稍不小心就容易破壞類的封裝性。練的不好，就容易走火入魔。沒關(guān)系，很多時(shí)候我們還可以使用Java的內(nèi)省機(jī)制哦2021-07-07
Java Spring使用hutool的HttpRequest發(fā)送請(qǐng)求的幾種方式
文章介紹了Hutool庫中用于發(fā)送HTTP請(qǐng)求的工具,包括添加依賴、發(fā)送GET和POST請(qǐng)求的方法,以及GET請(qǐng)求的不同參數(shù)傳遞方式,感興趣的朋友跟隨小編一起看看吧2024-11-11
使用java技術(shù)抓取網(wǎng)站上彩票雙色球信息詳解
這篇文章主要介紹了使用java技術(shù)抓取網(wǎng)站上彩票雙色球信息詳解,web結(jié)果由html+js+css組成，html結(jié)構(gòu)都有一定的規(guī)范，數(shù)據(jù)動(dòng)態(tài)交互可以通過js實(shí)現(xiàn)。,需要的朋友可以參考下2019-06-06
SpringBoot自動(dòng)裝配之Condition深入講解
@Conditional表示僅當(dāng)所有指定條件都匹配時(shí)，組件才有資格注冊(cè)。該@Conditional注釋可以在以下任一方式使用：作為任何@Bean方法的方法級(jí)注釋、作為任何類的直接或間接注釋的類型級(jí)別注釋@Component，包括@Configuration類、作為元注釋，目的是組成自定義構(gòu)造型注釋2023-01-01
JavaWeb入門:ServletContext詳解和應(yīng)用
這篇文章主要介紹了Java ServletContext對(duì)象用法解析,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2021-07-07