概要

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、条件查询对象介绍

3、QueryWrapper常用的方法

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() + ""));
    }