【MybatisPlus】MP来实现一些特殊的查询

文章目录

一.查询投影

查询投影又称查询指定字段

@Test
void testGetAll07(){
    LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>();
    lqw.select(Users::getId,Users::getName,Users::getAge);
    List<Users> userList = userDao.selectList(lqw);
    System.out.println(userList);
}

比如下面查询的结果就隐藏了password、tel字段
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP

二.聚合查询

@SpringBootTest
class Mybatisplus{

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll(){
        QueryWrapper<User> lqw = new QueryWrapper<User>();
        //lqw.select("count(*) as count");
        //SELECT count(*) as count FROM user
        //lqw.select("max(age) as maxAge");
        //SELECT max(age) as maxAge FROM user
        //lqw.select("min(age) as minAge");
        //SELECT min(age) as minAge FROM user
        //lqw.select("sum(age) as sumAge");
        //SELECT sum(age) as sumAge FROM user
        lqw.select("avg(age) as avgAge");
        //SELECT avg(age) as avgAge FROM user
        List<Map<String, Object>> userList = userDao.selectMaps(lqw);
        System.out.println(userList);
    }
}

【MybatisPlus】MP来实现一些特殊的查询-LMLPHP
为了在做结果封装的时候能够更简单，我们将上面的聚合函数都起了个名称，方面后期来获取这些数据

三.分组查询

@SpringBootTest
class Mybatisplus{

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll09(){
        QueryWrapper<Users> lqw = new QueryWrapper<Users>();
        lqw.select("count(*) as count,age");
        lqw.groupBy("age");
        List<Map<String, Object>> list = userDao.selectMaps(lqw);
        System.out.println(list);
    }
}

groupBy为分组，最终的sql语句为:
SELECT count(*) as count,age FROM users GROUP BY age
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP
注:
聚合与分组查询，无法使用lambda表达式来完成
MP只是对MyBatis的增强，如果MP实现不了，我们可以直接在DAO接口中使用MyBatis的方式实现

四.查询条件

4.1 等值查询

@SpringBootTest
class Mybatisplus{

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll13(){
        LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>();
        lqw.eq(Users::getName, "懒羊羊").eq(Users::getPassword, "123456");
        Users users = userDao.selectOne(lqw);
        System.out.println(users);
    }
}

eq()：相当于 =,对应的sql语句为
SELECT id,name,password,age,tel FROM user WHERE (name = ? AND password = ?)
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP
selectList：查询结果为多个或者单个
selectOne：查询结果为单个

4.2 范围查询

@SpringBootTest
class Mybatisplus{

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll12(){
        LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>();
        lqw.between(Users::getAge, 10, 30);
        List<Users> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }
}

与之对应的SQL为：
SELECT id,name,password,age,tel FROM user WHERE (age BETWEEN ? AND ?)
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP

4.3 模糊查询

@SpringBootTest
class Mybatisplus {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll10(){
        LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>();
        lqw.like(Users::getName, "美");
        List<Users> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }
}

最后也是通过一个字查到了美羊羊
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP
对应的SQL为：
SELECT id,name,password,age,tel FROM user WHERE (name LIKE ?)

like():前后加百分号,如 %美%
likeLeft():前面加百分号,如 %美
likeRight():后面加百分号,如美%

4.4 排序查询

@SpringBootTest
class Mybatisplus{

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll11(){
        LambdaQueryWrapper<Users> lwq = new LambdaQueryWrapper<>();
        lwq.orderBy(true,true, Users::getId);
        List<Users> users = userDao.selectList(lwq);
        System.out.println(users);
    }
}

查询结果如下：
【MybatisPlus】MP来实现一些特殊的查询-LMLPHP
condition ：条件，返回boolean，当condition为true，进行排序，如果为false，则不排序
isAsc：是否为升序，true为升序，false为降序
columns：需要操作的列

懒羊羊.java