QueryWrapper查询

package cn.bitqian;

import cn.bitqian.entity.User;
import cn.bitqian.mapper.UserMapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

/**
 * @author echo lovely
 * @date 2020/11/17 08:19
 */

@SpringBootTest
public class WrapperDemo {
    
    


    @Autowired
    private UserMapper userMapper;

    // 测试普通的条件查询
    @Test
    void test1() {
    
    

        // 条件构造器
        QueryWrapper queryWrapper = new QueryWrapper<>();
        // 查询name为jack的人 并且年龄大于等于3岁
        queryWrapper.eq(true,"name", "Jack").
                ge("age", 3).
                between("age", 10, 20);

        User user = userMapper.selectOne(queryWrapper);
        System.out.println(user);

        queryWrapper.clear();


    }


    // 测试模糊查询 demo
    @Test
    void test2() {
    
    
        // 查询构造器
        QueryWrapper userWrapper = new QueryWrapper<>();

        // condition 条件为 false 不包含该条件查询
        // LIKE '值%'
        userWrapper.like("name", "J").likeRight(false, "name", "e");

        List users = userMapper.selectList(userWrapper);
        users.forEach(System.out::println);
    }

    // 测试子查询
    @Test
    void test3() {
    
    
        // ==>  Preparing: SELECT id,name,age,email,version,
        // deleted,gmt_create,gmt_modify FROM user WHERE deleted=0
        // AND (id IN (select id from user where id < 3))
        QueryWrapper userWrapper = new QueryWrapper<>();
        userWrapper.inSql("id", "select id from user where id < 3");

        List users = userMapper.selectObjs(userWrapper);
        users.forEach(System.out::println);
    }

    // order by ..
    @Test
    void test4() {
    
    

        QueryWrapper userWrapper = new QueryWrapper<>();

        userWrapper.orderByDesc("id");

        List users = userMapper.selectList(userWrapper);

        users.forEach(System.out::println);
    }



}

查询方式

说明

setSqlSelect

设置 SELECT 查询字段

where

WHERE 语句，拼接 +?WHERE 条件

and

AND 语句，拼接 +?AND 字段=值

andNew

AND 语句，拼接 +?AND (字段=值)

or

OR 语句，拼接 +?OR 字段=值

orNew

OR 语句，拼接 +?OR (字段=值)

eq

等于=

allEq

基于 map 内容等于=

ne

不等于<>

gt

大于>

ge

大于等于>=

lt

小于<

le

小于等于<=

like

模糊查询 LIKE

notLike

模糊查询 NOT LIKE

in

IN 查询

notIn

NOT IN 查询

isNull

NULL 值查询

isNotNull

IS NOT NULL

groupBy

分组 GROUP BY

having

HAVING 关键词

orderBy

排序 ORDER BY

orderAsc

ASC 排序 ORDER BY

orderDesc

DESC 排序 ORDER BY

exists

EXISTS 条件语句

notExists

NOT EXISTS 条件语句

between

BETWEEN 条件语句

notBetween

NOT BETWEEN 条件语句

addFilter

自由拼接 SQL

last

拼接在最后，例如：last(“LIMIT 1”)

 

    public User getByIdNumber(Long idNumber) {
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.lambda()
                .eq(User::getIdNumber, idNumber)
                .gt(User::getIdAuthStatus, 0);
        return userMapper.selectOne(queryWrapper);
    }
and ( pid = 0 or pid is null)  用法

        QueryWrapper<Resource> queryWrapper = new QueryWrapper();
        queryWrapper.and(wrapper -> wrapper.eq("pid", "0").or().isNull("pid"));
        queryWrapper.orderByAsc("sort");
        List<Resource> list = resourceMapper.selectList(queryWrapper);
 
————————————————
版权声明：本文为CSDN博主「秋名山车神不开车」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_34503526/article/details/111725076