十一 . mybatis-plus(查询)

概要

MP封装了一些常用的查询方法以select开头

一、查询基础

1、根据主键查询单条记录

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
    @Resource
    UserMapper userMapper;
      @Test
    public void batchIds() {
        User user = userMapper.selectById(1);
        log.error(user.toString());
    }
}
//SELECT * FROM user WHERE uid=? 

2、根据主键批量查询

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
    @Resource
    UserMapper userMapper;
      @Test
    public void batchIdsTest() {
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3, 4, 5));
        users.forEach(user -> log.debug(user.getUsername()));
    }
}
// sql语句
// SELECT * FROM user WHERE uid IN ( ? , ? , ? , ? , ? ) 

3、Entity作为条件查询数据

public class UserMapperTest {
    @Resource
    UserMapper userMapper;
    @Test
    public void selectByEntity() {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("username", "qq123456");
        columnMap.put("locked", 0);
        List<User> users = userMapper.selectByMap(columnMap);
        users.forEach(user -> log.trace(user.getUsername()));
    }
}

4、Map 作为条件查询数据

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
    @Resource
    UserMapper userMapper;
  
      @Test
    public void selectByMapTest() {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("username", "qq123456");
        columnMap.put("locked", 0);
        List<User> users = userMapper.selectByMap(columnMap);
        users.forEach(user -> log.trace(user.getUsername()));
    }
}
// sql语句
// SELECT * FROM user WHERE username=? and locked=?

二、Wrapper

1、说明

上面我们讲的都是一些比较简单的查询,当通用的查询无法满足时,比如 过滤列,限定符, 分组,排序,关联查询等等

QueryWrapper封装了很多方法来实现复杂的查询

2、条件查询对象介绍

类	说明
Wrapper	条件查询对象抽象类，最顶端父类
AbstractWrapper	用于查询条件封装，生成 sql 的 where 条件
AbstractLambdaWrapper	Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column
LambdaQueryWrapper	Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column
LambdaUpdateWrapper	Lambda 更新封装Wrapper
QueryWrapper	实体对象封装操作类
UpdateWrapper	Update 条件封装，用于实体对象更新操作

3、QueryWrapper常用的方法

查询方式	说明
select	设置 SELECT 查询字段
and	AND 语句，拼接 + AND 字段=值
or	OR 语句，拼接 + OR 字段=值
eq	等于=
allEq	基于 map 内容等于=
ne	不等于<>
gt	大于>
ge	大于等于>=
lt	小于<
le	小于等于<=
like	模糊查询 LIKE
notLike	模糊查询 NOT LIKE
in	IN 查询
notIn	NOT IN 查询
isNull	NULL 值查询
isNotNull	IS NOT NULL
groupBy	分组 GROUP BY
having	HAVING 关键词
orderBy	排序 ORDER BY
orderByAsc	ASC 排序 ORDER BY
orderByDesc	DESC 排序 ORDER BY
exists	EXISTS 条件语句
notExists	NOT EXISTS 条件语句
between	BETWEEN 条件语句
notBetween	NOT BETWEEN 条件语句
last	拼接在最后

4、栗子

selectOne与QueryWrapper

  @Test
    public void selectOne() {
        QueryWrapper<User> qw = new QueryWrapper<>();
        qw.select("uid", "username", "phone").eq("uid", 2)
                .eq("locked", 0);
        User user = userMapper.selectOne(qw);
        log.debug(user.getUsername());
    }

**注意: ** 当查询的结果返回多个对象的时候会抛出异常

selectList

// SELECT uid,username,password FROM user WHERE username = ? OR phone = ? OR email = ? ORDER BY reg_time DESC 
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserMapperTest {
    @Resource
    UserMapper userMapper;  
    @Test
    public void selectByQueryWrap() {
        QueryWrapper<User> qw = new QueryWrapper<>();
        qw.select("uid", "username", "password")
                .eq("username", "qq123456")
                .or()
                .eq("phone", "12345678")
                .or()
                .eq("email", "123456@qq.com")
                .orderByDesc("reg_time");
        List<User> users = userMapper.selectList(qw);
        users.forEach(user -> log.debug(user.getUsername()));
    }
 }

selectCount查询总条数

@Test
public void selectCount() {
  QueryWrapper<User> qw = new QueryWrapper<>();
  Integer count = userMapper.selectCount(qw.eq("locked", 0));
  log.debug("总条数{}", count);
}

分组操作

   //SELECT uid,username,password,phone,sex,email,mark,last_login,login_ip,head,reg_time,locked FROM user WHERE locked = ? GROUP BY sex
    @Test
    public void selectGroupBy() {
        QueryWrapper<User> qw = new QueryWrapper<>();
        qw.eq("locked", 0).groupBy("sex");
        List<User> users = userMapper.selectList(qw);
        users.forEach(user -> log.debug(user.getUsername()));

    }

三、分页查询

1、注册PaginationInterceptor分页插件

@Configuration
public class MybatisPlusConfiguration {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        // paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不
        return new PaginationInterceptor();
    }
}

2、selectPage

    @Test
    public void selectPage() {
        IPage<User> page = userMapper.selectPage(new Page<>(1, 10), new QueryWrapper<User>().eq("locked", 0));
        log.debug("总条数:{}", page.getTotal());
        log.debug("总页数:{}", page.getPages());
        log.debug("当前第{}页", page.getCurrent());
        log.debug("当前条数:{}", page.getSize());
        //  获取当前分页数据
        List<User> records = page.getRecords();
        records.forEach(user -> log.debug(user.getUid() + ""));
    }