Mybatis Plus 查询方法

一、普通查询

@SpringBootTest
public class QueryTest {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void selectById() {
        User user = userMapper.selectById(1094592041087729666L);
        System.out.println(user);
    }

    @Test
    public void selectByIds() {
        List<Long> ids = Arrays.asList(1094592041087729666L, 1094590409767661570L);
        List<User> list = userMapper.selectBatchIds(ids);
        list.forEach(System.out::println);
    }

    @Test
    public void selectByMap() {
        // 注意：key存的是数据库中的列名，而不是类中的属性名称
        Map<String, Object> columMap = new HashMap<>();
        columMap.put("name", "王天风");
        columMap.put("age", 25);
        List<User> list = userMapper.selectByMap(columMap);
        list.forEach(System.out::println);
    }
}

二、以条件构造器为参数的查询

/**
 * 1、名字中包含雨并且年龄小于40
 * name like '%雨%' and age<40
 */
@Test
public void selectByWrapper() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //QueryWrapper<User> queryWrapper = Wrappers.<User>query();
    queryWrapper.like("name", "雨").lt("age", 40);
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空
 * name like '%雨%' and age between 20 and 40 and email is not null
 */
@Test
public void selectByWrapper2() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 3、名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列
 * name like '王%' or age>=25 order by age desc,id asc
 */
@Test
public void selectByWrapper3() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "王").or().ge("age", 25)
            .orderByDesc("age").orderByAsc("id");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 4、创建日期为2019年2月14日并且直属上级为名字为王姓
 * date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
 */
@Test
public void selectByWrapper4() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
            .inSql("manager_id", "select id from user where name like '王%'");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 5、名字为王姓并且（年龄小于40或邮箱不为空）
 * name like '王%' and (age<40 or email is not null)
 */
@Test
public void selectByWrapper5() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "王")
            .and(wq -> wq.lt("age", 40).or().isNotNull("email"));
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 6、名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）
 * name like '王%' or (age<40 and age>20 and email is not null)
 */
@Test
public void selectByWrapper6() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name", "王")
            .or(wq -> wq.lt("age", 40).gt("age", 20).isNotNull("email"));
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 7、（年龄小于40或邮箱不为空）并且名字为王姓
 * (age<40 or email is not null) and name like '王%'
 */
@Test
public void selectByWrapper7() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email"))
            .likeRight("name", "王");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 8、年龄为30、31、34、35
 * age in (30、31、34、35)
 */
@Test
public void selectByWrapper8() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age", Arrays.asList(30, 31, 34, 35));
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 9、只返回满足条件的其中一条语句即可
 * limit 1
 */
@Test
public void selectByWrapper9() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

三、select 中字段不全部出现

/**
 * 1、名字中包含雨并且年龄小于40
 * select id,name from user where name like '%雨%' and age<40
 */
@Test
public void selectByWrapperSupper() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //QueryWrapper<User> queryWrapper = Wrappers.<User>query();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

/**
 * 1、名字中包含雨并且年龄小于40
 * select id,name,age,email from user where name like '%雨%' and age<40
 */
@Test
public void selectByWrapperSupper2() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //QueryWrapper<User> queryWrapper = Wrappers.<User>query();
    queryWrapper.like("name", "雨").lt("age", 40)
            .select(User.class, info -> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id"));
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

四、条件构造器中condition作用

如果condition为true，才会添加条件到sql语句中

@Test
public void testCondition(){
    String name = "王";
    String email = "";
    condition(name,email);
}

public void condition(String name, String email) {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /*if (!ObjectUtils.isEmpty(name)) {
        queryWrapper.like("name", name);
    }
    if (!ObjectUtils.isEmpty(email)) {
        queryWrapper.like("email", email);
    }*/
    queryWrapper.like(!ObjectUtils.isEmpty(name),"name", name)
            .like(!ObjectUtils.isEmpty(email),"email", email);
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

五、创建条件构造器时传入实体对象

传入的实体对象的属性会当作条件，和Wrapper的其他条件不冲突

@Test
public void selectByWrapperEntity(){
    User whereUser = new User();
    whereUser.setName("刘红雨");
    whereUser.setAge(32);

    QueryWrapper<User> queryWrapper = new QueryWrapper<>(whereUser);
    queryWrapper.like("name","雨").lt("age",40);

    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

控制台输出：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name=? AND age=? AND name LIKE ? AND age < ? 
DEBUG==> Parameters: 刘红雨(String), 32(Integer), %雨%(String), 40(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1094592041087729666, 刘红雨, 32, lhm@baomidou.com, 1088248166370832385, 2019-01-14 09:48:16
DEBUG<==      Total: 1
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=Mon Jan 14 09:48:16 CST 2019, remark=null)

有时候根据实体类查询不是完全匹配，可能是模糊查询，可以使用@TableField中condition属性

@TableField(condition = SqlCondition.LIKE)
private String name;

@Test
public void selectByWrapperEntity(){
    User whereUser = new User();
    whereUser.setName("刘红雨");
    whereUser.setAge(32);

    QueryWrapper<User> queryWrapper = new QueryWrapper<>(whereUser);
   
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

控制台输出：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE CONCAT('%',?,'%') AND age=? 
DEBUG==> Parameters: 刘红雨(String), 32(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1094592041087729666, 刘红雨, 32, lhm@baomidou.com, 1088248166370832385, 2019-01-14 09:48:16
DEBUG<==      Total: 1
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=Mon Jan 14 09:48:16 CST 2019, remark=null)

默认的几种SqlCondition不满足条件，也可以自定义，注意需要用转义后的字符

@TableField(condition = "%s<#{%s}")
private int age;

六、条件构造器中allEq用法

@Test
public void selectByWrapperAllEq() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> params = new HashMap<>();
    params.put("name", "王天风");
    params.put("age", null);
    //queryWrapper.allEq(params);// 会加入AND age IS NULL
    //queryWrapper.allEq(params,false);// 为null不加入条件语句中
    queryWrapper.allEq((k, v) -> !k.equals("name"), params);// 过滤器，该代码意思是：name字段不加入到条件语句中
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

七、其他以条件构造器为参数的查询方法

@Test
public void selectByWrapperMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);

    List<Map<String, Object>> mapList = userMapper.selectMaps(queryWrapper);
    mapList.forEach(System.out::println);
}

/**
 * 11、按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。
 * 并且只取年龄总和小于500的组。
 * select avg(age) avg_age,min(age) min_age,max(age) max_age
 * from user
 * group by manager_id
 * having sum(age) <500
 */
@Test
public void selectByWrapperMaps2() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
            .groupBy("manager_id").having("sum(age) < {0}", 500);
    List<Map<String, Object>> mapList = userMapper.selectMaps(queryWrapper);
    mapList.forEach(System.out::println);
}

@Test
public void selectByWrapperObjs() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("name", "id").like("name", "雨").lt("age", 40);

    List<Object> objectList = userMapper.selectObjs(queryWrapper);
    objectList.forEach(System.out::println);
}

@Test
public void selectByWrapperCount() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "雨").lt("age", 40);

    Integer count = userMapper.selectCount(queryWrapper);
    System.out.println("记录数：" + count);
}

@Test
public void selectByWrapperOne() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "刘红雨").lt("age", 40);

    User user = userMapper.selectOne(queryWrapper);// 如果查询出不止一条数据，就会报错
    System.out.println(user);
}

八、Lambda条件构造器

@Test
public void selectLambda() {
    //LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
    //LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
    LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
    lambdaQuery.like(User::getName, "雨").lt(User::getAge, 40);
    List<User> list = userMapper.selectList(lambdaQuery);
    list.forEach(System.out::println);
}

/**
 * 5、名字为王姓并且（年龄小于40或邮箱不为空）
 * name like '王%' and (age<40 or email is not null)
 */
@Test
public void selectLambda2() {
    LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
    lambdaQuery.likeRight(User::getName, "王")
            .and(lqw -> lqw.lt(User::getAge, 40).or().isNotNull(User::getEmail));
    List<User> list = userMapper.selectList(lambdaQuery);
    list.forEach(System.out::println);
}

@Test
public void selectLambda3() {
    List<User> list = new LambdaQueryChainWrapper<User>(userMapper)
            .like(User::getName, "雨").ge(User::getAge, 20).list();
    list.forEach(System.out::println);
}