MyBatisPlus---DQL编程控制

MP将书写复杂的SQL查询条件进行了封装，使用编程的形式完成查询条件的组合。

一、条件查询

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        //方式一：查询age字段小于18。
//        QueryWrapper qw = new QueryWrapper();
//        qw.lt("age", 18);
//        List<User> users = userDao.selectList(qw);
//        System.out.println(users);


        //方式二：lambda格式查询age字段小于18
//        QueryWrapper<User> qw = new QueryWrapper();
//        qw.lambda().lt(User::getAge, 18);
//        List<User> users = userDao.selectList(qw);
//        System.out.println(users);

        //方式三：lambda格式查询age字段小于18
//        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
//        lqw.lt(User::getAge, 18);
//        List<User> users = userDao.selectList(lqw);
//        System.out.println(users);


//        lambda格式多条件查询age字段小于(lt)18, 大于(gt)5
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //5到18岁之间：链式编程
//        lqw.lt(User::getAge, 18);
//        lqw.gt(User::getAge, 5);
//        lqw.lt(User::getAge, 18).gt(User::getAge, 5);

        //小于5岁，或者大于18岁
        lqw.lt(User::getAge, 5).or().gt(User::getAge, 18);
        List<User> users = userDao.selectList(lqw);
        System.out.println(users);

    }

}

二、条件查询--null值处理

//模拟页面传递过来的查询数据
        UserQuery uq = new UserQuery();
//        uq.setAge(10);
        uq.setAge2(30);

        //null判定
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
//        lqw.lt(User::getAge, uq.getAge2());
        //先判定第一个参数是否为true,如果为true连接当前条件
        lqw.lt(uq.getAge2()!=null, User::getAge, uq.getAge2());
//        lqw.gt(User::getAge, uq.getAge());
        lqw.gt(uq.getAge()!=null, User::getAge, uq.getAge());

        List<User> users = userDao.selectList(lqw);
        System.out.println(users);

三、查询投影

 //查询投影
//        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
//        lqw.select(User::getId, User::getName, User::getAge);
//        QueryWrapper<User> lqw = new QueryWrapper<User>();
//        lqw.select("id", "name", "age", "tel");
//        List<User> users = userDao.selectList(lqw);
//        System.out.println(users);

        
        //查所有，分组查询
        QueryWrapper<User> lqw = new QueryWrapper<User>();
        lqw.select("count(*) as count, tel");
        lqw.groupBy("tel");
        List<Map<String, Object>> userList = userDao.selectMaps(lqw);
        System.out.println(userList);

//条件查询
        //查询name为Jerry、密码为jerry的用户是否存在
//        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
//        lqw.eq(User::getName, "Jerry").eq(User::getPassword, "jerry");
//        User user = userDao.selectOne(lqw);
//        System.out.println(user);


        //范围查询 lt(<) le(<=) gt()> ge(>=) eq(==) between
//        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
//        lqw.between(User::getAge, 10, 30);
//        List<User> users = userDao.selectList(lqw);
//        System.out.println(users);

        //模糊匹配
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //likeLeft: "%J"   likeRight: "J%"
        lqw.likeLeft(User::getName, "J");
        List<User> users = userDao.selectList(lqw);
        System.out.println(users);