目录 前言 普通查询 批量查询 条件查询 前言 使用的数据库和相关代码见springboot整合mybatisplus与druid详情。 使用test来进行使用: 普通查询 @Autowired private StudentDao studentDao; @Test void sele
目录
- 前言
- 普通查询
- 批量查询
- 条件查询
前言
使用的数据库和相关代码见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内容请搜索自由互联以前的文章或继续浏览下面的相关文章希望大家以后多多支持自由互联!