前言
使用的数据库和相关代码见springboot整合mybatis plus与druid详情。
使用test来进行使用：
普通查询
@Autowired
private StudentDao studentDao;
@Test
void selectById() {
studentDao.selectById(1000);
}
批量查询
@SpringBootTest
class Spring1ApplicationTests {
@Autowired
private StudentDao studentDao;
@Test
void selectByIds() {
List<Integer> ids = Arrays.asList(1000,1002);
List<student> st = studentDao.selectBatchIds(ids);
}
}
条件查询
主要使用了QueryWrapper方法：
- queryWrapper.lt（）——小于
- queryWrapper.le（）——小于等于
- queryWrapper.gt（）——大于
- queryWrapper.ge（）——大于等于
- queryWrapper.eq（）——等于
- queryWrapper.ne（）——不等于
- queryWrapper.betweeen（“age”,10,20）——age在值10到20之间
- queryWrapper.notBetweeen（“age”,10,20）——age不在值10到20之间
- queryWrapper.like（“属性”,“值”）——模糊查询匹配值‘%值%’
- queryWrapper.notLike（“属性”,“值”）——模糊查询不匹配值‘%值%’
- queryWrapper.likeLeft（“属性”,“值”）——模糊查询匹配最后一位值‘%值’
- queryWrapper.likeRight（“属性”,“值”）——模糊查询匹配第一位值‘值%’
- queryWrapper.isNull（）——值为空或null
- queryWrapper.isNotNull（）——值不为空或null
- queryWrapper.in（“属性”，条件，条件 ）——符合多个条件的值
- queryWrapper.notIn(“属性”，条件，条件 )——不符合多个条件的值
- queryWrapper.or（）——或者
- queryWrapper.and（）——和
- queryWrapper.orderByAsc(“属性”)——根据属性升序排序
- queryWrapper.orderByDesc(“属性”)——根据属性降序排序
- queryWrapper.inSql(“sql语句”)——符合sql语句的值
- queryWrapper.notSql(“sql语句”)——不符合SQL语句的值
- queryWrapper.esists（“SQL语句”）——查询符合SQL语句的值
- queryWrapper.notEsists（“SQL语句”）——查询不符合SQL语句的值
查询通过姓名：
@Autowired
private StudentDao studentDao;
@Test
void selectByName() {
QueryWrapper<student> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name","Li Ming");
List<student> userInfoList = studentDao.selectList(queryWrapper);
}
查询方法详细代码：
@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-02")
.inSql("parent_id","select id from user where username like '王%'");
List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
userInfoList.forEach(System.out::println);
}
//上面的日期查询使用的是占位符的形式进行查询，目的就是为了防止SQL注入的风险
/**
* 名字为王姓并且（年龄小于40或邮箱不为空）
*/
@Test
public void selectByWrapper5() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
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);
}
/**
* （年龄小于40并且大与20或邮箱不为空）并且名字为王姓
*/
@Test
public void selectByWrapper7() {
QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
queryWrapper.nested(wq->wq.lt("age",40).gt("age",20))
.or().isNotNull("email")
.likeRight("username","王");
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);
}
/**
* 只返回满足条件的其中一条语句即可
*/
@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);
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->!info.getColumn().equals("create_time")&&
!info.getColumn().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.like(StringUtils.isNullOrEmpty(username),"name",username)
.like(StringUtils.isNullOrEmpty(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);
}到此这篇关于spring boot 使用Mybatis-plus查询方法解析的文章就介绍到这了,更多相关spring boot Mybatis-plus内容请搜索Devmax以前的文章或继续浏览下面的相关文章希望大家以后多多支持Devmax！