MybatisPlus的各种查询⽅法MybatisPlus的各种查询⽅法
QueryWrapper与UpdateWrapper共有⽅法
⽅法名说明
allEq基于 map 内容等于=
eq等于 =
ne不等于 <>
gt⼤于 >
ge⼤于等于 >=
lt⼩于 <
le⼩于等于 <=
between BETWEEN 条件语句
notBetween NOT BETWEEN 条件语句
like LIKE '%值%''
notLike NOT LIKE '%值%'
likeLeft LIKE '%值'
likeRight LIKE '值%'
----------------
isNull NULL 值查询
isNotNull NOT NULL 值查询
in IN 查询
notIn NOT IN 查询
inSql IN 查询(sql注⼊式)
notInSql NOT IN 查询(sql注⼊式)
groupBy分组 GROUP BY
orderByAsc ASC 排序 ORDER BY
orderByDesc DESC 排序 ORDER BY
orderBy排序 ORDER BY
having HAVING 关键词(sql注⼊式)
----------------
or or 拼接
apply拼接⾃定义内容(sql注⼊式)
last拼接在最后(sql注⼊式)
exists EXISTS 条件语句(sql注⼊式)
notExists NOT EXISTS 条件语句(sql注⼊式)
----------------
and(Function)AND (嵌套内容)
or(Function)OR (嵌套内容)
nested(Function)(嵌套内容)
QueryWrapper特有⽅法
⽅法名说明
select SQL 查询字段内容，例如:id,name,age(重复设置以最后⼀次为准) UpdateWrapper特有⽅法
⽅法名说明
set SQL SET 字段(⼀个字段使⽤⼀次)
实例
package com.xiao.permission_system;
import ditions.query.LambdaQueryWrapper;
import ditions.query.QueryWrapper;
import sion.service.additional.query.impl.LambdaQueryChainWrapper; sql.cj.util.StringUtils;
import com.xiao.ity.UserInfo;
import com.xiao.permission_system.mapper.UserInfoMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.st.context.SpringBootTest;
import st.context.junit4.SpringRunner;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;;
@RunWith(SpringRunner.class)
@SpringBootTest
public class PermissionSystemApplicationTests {
@Test
public void contextLoads() {
}
@Autowired
private UserInfoMapper userInfoMapper;
/**
* 普通查询
*/
@Test
public void selectById() {
UserInfo userInfo = userInfoMapper.selectById(123);
System.out.println(userInfo);
}
/**
* 批量查询
*/
@Test
public void selectByIds() {
List<Long> ids = Arrays.asList(123L,124L,125L);
List<UserInfo> userInfo = userInfoMapper.selectBatchIds(ids);
System.out.println(userInfo);
}
/**
* 名字包含娟并且年龄⼩⾬30
*/
@Test
public void selectByWrapper() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("username","娟").lt("age",30);
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字包含娟并且年龄⼤⾬等于20且⼩于等于40并且email不为空
*/
@Test
public void selectByWrapper2() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("username","娟").between("age",20,30).isNotNull("email");
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字姓肖或者年量⼤⾬等于20，按照年龄降序排列，年龄相同按照id⽣序排列
*/
@Test
public void selectByWrapper3() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.likeRight("username","肖")
.or().ge("age",20).orderByDesc("age").orderByAsc("id");
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 创建⽇期为2019年10⽉2⽇并且直属上级名字为王姓
*/
@Test
public void selectByWrapper4() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-10-07")
.inSql("parent_id","select id from user where username like '王%'");
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字为王姓并且（年龄⼩于40或邮箱不为空）
*/
@Test
public void selectByWrapper5() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
//注意：这⾥的or在and⾥⾯
//参考：blog.csdn/u011229848/article/details/81902398?utm_source=blogxgwz3        queryWrapper.likeRight("username","王")
.and(wq->wq.lt("age",40).or().isNotNull("email"));
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字为王姓并且（年龄⼩于40并且⼤与20或邮箱不为空）
*/
@Test
public void selectByWrapper6() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.likeRight("username","王")
.and(wq->wq.lt("age",40).gt("age",20).or().isNotNull("email"));
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 年龄23，30，40
*/
@Test
public void selectByWrapper8() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.in("age",Arrays.asList(20,30,40));
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 只返回满⾜条件的第⼀条语句即可, ⽤last拼接limit部分
*/
@Test
public void selectByWrapper9() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.in("age",Arrays.asList(20,30,40)).last("limit 1");
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字中包含⾬并且年龄⼩于40(只取id,username)
*/
@Test
public void selectByWrapper10() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.select("id","username").like("username","⾬").lt("age",40);
equals不等于List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字中包含⾬并且年龄⼩于40(不取create_time,parent_id两个字段，即不列出全部字段)
*/
@Test
public void selectByWrapper11() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("username","⾬").lt("age",40)
.
select(UserInfo.class,info->!Column().equals("create_time")&&
!Column().equals("parent_id"));
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 名字为王姓和邮箱不为空，先判断条件是否满⾜
*/
public void testCondition() {
String username = "王";
String email = "";
condition(username,email);
}
private void condition(String username,String email){
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.likeRight(StringUtils.isNotBlank(username),"name",username)
.like(StringUtils.isNotBlank(email),"email",email);
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 实体作为条件构造器⽅法的参数
*/
@Test
public void selectByWrapperEntity() {
UserInfo whereUser = new UserInfo();
whereUser.setUsername("xiaojuan");
whereUser.setAge(22);
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>(whereUser);
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* AllEq⽤法
*/
@Test
public void selectByWrapperAllEq() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
Map<String, Object> params = new HashMap<String, Object>();
params.put("nuserame","xiaojuan");
params.put("age",null);
queryWrapper.allEq(params);
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* AllEq⽤法(排除不是条件的字段)
*/
@Test
public void selectByWrapperAllEq2() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
Map<String, Object> params = new HashMap<String, Object>();
params.put("nuserame","xiaojuan");
params.put("age",null);
queryWrapper.allEq((k,v)->!k.equals("name"),params);
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* selectMaps
*/
@Test
public void selectByWrapperMaps() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("name","肖").lt("age",40);
List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* 按照直属上级分组，查询每组的平均年龄，最⼤年龄，最⼩年龄。并且只取年龄总和⼩于500的组    */
@Test
public void selectByWrapperMaps2() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.select("avg(age) avg_age","min(min) min_age","max(age) max_age")
.groupBy("parent_id").having("sum(age)<{0}",500);
List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
userInfoList.forEach(System.out::println);
}
/**
* selectObjs
*/
@Test
public void selectByWrapperObjs() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.select("id","name").like("name","肖").lt("age",40);
List<Object> userInfoList = userInfoMapper.selectObjs(queryWrapper);
userInfoList.forEach(System.out::println);
}
/
**
* selectCount
*/
@Test
public void selectByWrapperCount() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("name","肖").lt("age",40);
Integer count = userInfoMapper.selectCount(queryWrapper);
System.out.println(count);
}
/**
* selectOne
*/
@Test
public void selectByWrapperSelectOne() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.like("name","肖").lt("age",40);
UserInfo user = userInfoMapper.selectOne(queryWrapper);
System.out.println(user);
}
/**
* 使⽤Lambda
*/
@Test
public void selectLambda() {
// LambdaQueryWrapper<UserInfo> lambda = new QueryWrapper<UserInfo>().lambda();
LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
lambda.like(UserInfo::getUsername,"娟").lt(UserInfo::getAge,40);
List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
userInfoList.forEach(System.out::println);
}
/**
* 使⽤Lambda，名字为王姓且（年龄⼩于40或邮箱不为空）
*/
@Test
public void selectLambd2() {
LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
lambda.like(UserInfo::getUsername,"娟")
.and(lqw->lqw.lt(UserInfo::getAge,40).or().isNotNull(UserInfo::getEmail));
List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
userInfoList.forEach(System.out::println);
}
/**
* 使⽤Lambda链式
*/
@Test
public void selectLambd3() {
List<UserInfo> userInfoList = new LambdaQueryChainWrapper<UserInfo>(userInfoMapper)                .like(UserInfo::getUsername,"娟").ge(UserInfo::getAge,20).list();
userInfoList.forEach(System.out::println);
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。