MyBatisPlus之条件查询（常规查询、范围查询、模糊查询、null值处理等）

MyBatisPlus之条件查询

1、设置查询条件

1.1 常规格式

  @Test
    public void testInuire() {
        QueryWrapper<User> qw = new QueryWrapper<User>();
         //查询年龄大于等于15岁，小于30岁的用户
        qw.lt("age", 30);
        qw.ge("age", 15);
        List<User> userList = userDao.selectList(qw);
        System.out.println(userList);
    }

1.2 链式编程格式

  @Test
    public void testInuire() {
        QueryWrapper<User> qw = new QueryWrapper<User>();
         //查询年龄大于等于15岁，小于30岁的用户
        qw.lt("age", 30).ge("age", 15);
        List<User> userList = userDao.selectList(qw);
        System.out.println(userList);
    }

1.3 lambda格式1

 @Test
    public void testLambda1() {
        QueryWrapper<User> qw = new QueryWrapper<User>();
         //查询年龄大于等于15岁，小于30岁的用户
        qw.lambda().lt(User::getAge, 30).ge(User::getAge,15);
        List<User> userList = userDao.selectList(qw);
        System.out.println(userList);
    }

1.4 lambda格式2

 @Test
    public void testLambda2() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //查询年龄大于等于15岁，小于30岁的用户
        lqw.lt(User::getAge, 30).ge(User::getAge,15);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

2、组合查询条件

2.1 并且

 @Test
    public void testLambda2() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //查询年龄大于等于18岁，小于30岁的用户
        lqw.lt(User::getAge, 30).gt(User::getAge,18);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

2.2 或者

 //多条件查询
    @Test
    public void testLambda3() {
        //小于18岁或者大于30岁
        LambdaQueryWrapper<User> lqw2 = new LambdaQueryWrapper<User>();
        lqw2.lt(User::getAge, 18).or().gt(User::getAge, 30);
        List<User> userList2 = userDao.selectList(lqw2);
        System.out.println(userList2);
    }

3、条件查询null值判定

3.1 封装User模型的查询条件

观察User类里哪些适合设置上下限

package com.example.maybatisplus_01_quickstart.domain.query;

import com.example.maybatisplus_01_quickstart.domain.User;
import lombok.Data;

@Data
public class UserQuery extends User {
    private Integer age2;
}

3.2 测试类

package com.example.maybatisplus_01_quickstart;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.maybatisplus_01_quickstart.dao.UserDao;
import com.example.maybatisplus_01_quickstart.domain.User;
import com.example.maybatisplus_01_quickstart.domain.query.UserQuery;
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
public class InquireNotNullTest {
    @Autowired
    private UserDao userDao;

    @Test
    public void inqueryNotNull() {
        //模拟页面传递过来的查询数据
        UserQuery uq = new UserQuery();
        uq.setAge(15);
        uq.setAge2(30);

        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        lqw.lt(null != uq.getAge2(), User::getAge, uq.getAge2());
        lqw.gt(null != uq.getAge(), User::getAge, uq.getAge());
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }
}

3.3 null值处理

3.3.1 条件参数控制

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
lqw.lt(null != uq.getAge2(), User::getAge, uq.getAge2());
lqw.gt(null != uq.getAge(), User::getAge, uq.getAge());
List<User> userList = userDao.selectList(lqw);
System.out.println(userList);

3.3.2 条件参数控制（链式编程）

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
lqw.lt(null != uq.getAge2(), User::getAge, uq.getAge2())
    .gt(null != uq.getAge(), User::getAge, uq.getAge());
List<User> userList = userDao.selectList(lqw);
System.out.println(userList);

4、查询投影

4.1 查询结果中包含模型类中 部分属性

 @Test
    public void testQueryProjection1() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //这种方式只适合lambda表达式
        lqw.select(User::getId, User::getName, User::getAge);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
}

4.2 查询结果包含模型类中 未定义的属性

 @Test
    public void testQueryProjection3() {
        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);
 }

4.3 原生mysql查询

如果上述方法都无法实现，只能自己手写mysql代码。

4.4 完整测试样例

package com.example.maybatisplus_01_quickstart;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.maybatisplus_01_quickstart.dao.UserDao;
import com.example.maybatisplus_01_quickstart.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;
import java.util.Map;

@SpringBootTest
public class QueryProjectionTest {
    @Autowired
    private UserDao userDao;

    //查询投影
    @Test
    public void testQueryProjection1() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        lqw.select(User::getId, User::getName, User::getAge);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

    @Test
    public void testQueryProjection2() {
        QueryWrapper<User> lqw = new QueryWrapper<User>();
        lqw.select("id","name","age");
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

    @Test
    public void testQueryProjection3() {
        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);
    }

}

5、查询条件设置（范围匹配、模糊匹配等）

示例

package com.example.maybatisplus_01_quickstart;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.maybatisplus_01_quickstart.dao.UserDao;
import com.example.maybatisplus_01_quickstart.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
public class ConditionalQueryMoreTest {
    @Autowired
    private UserDao userDao;

    //模拟登录操作查询
    @Test
    public void testConditionalQueryMore() {
        //条件查询
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //模拟登录操作
        lqw.eq(User::getName, "西瓜").eq(User::getPassword, "ewfvfv");
        User loginUser = userDao.selectOne(lqw);
        System.out.println(loginUser);
    }

    //范围查询
    @Test
    public void testfindRangeQuery() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //范围查询 lt(不带等号) le(带等号) gt(不带等号) le(带等号) eq between
        //between中前小后大
        lqw.between(User::getAge,10,22);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

    //模糊查询
    @Test
    public void testfuzzyQuery() {
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //模糊匹配
        //likeRight表示搜索是 西%
        //likeLeft表示搜索是 %西
        lqw.likeRight(User::getName,"西");
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);
    }

}