Mybatis-Plus条件构造器

别团等shy哥发育

发布于 2023-02-25 16:08:50

1.1K0

发布于 2023-02-25 16:08:50

文章被收录于专栏：全栈开发那些事

Mybatis-Plus条件构造器

1、条件构造器

说明：

以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中，例如：query.like(StringUtils.isNotBlank(name), Entity::getName, name) .eq(age!=null && age >= 0, Entity::getAge, age)
以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为true
以下出现的泛型Param均为Wrapper的子类实例(均具有AbstractWrapper的所有方法)
以下方法在入参中出现的R为泛型,在普通wrapper中是String,在LambdaWrapper中是函数(例:Entity::getId,Entity为实体类,getId为字段id的getMethod)
以下方法入参中的R column均表示数据库字段,当R具体类型为String时则为数据库字段名(字段名是数据库关键字的自己用转义符包裹!)!而不是实体类数据字段名!!!,另当R具体类型为SFunction时项目runtime不支持eclipse自家的编译器!!!
以下举例均为使用普通wrapper,入参为Map和List的均以json形式表现!
使用中如果入参的Map或者List为空,则不会加入最后生成的sql中!!!

2、AbstractWrapper

说明：

QueryWrapper(LambdaQueryWrapper) 和UpdateWrapper(LambdaUpdateWrapper) 的父类用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件
注意: entity 生成的 where 条件与使用各个 api 生成的 where 条件没有任何关联行为条件参数如下：

2.1 allEq

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

全部eq(或个别isNull)

个别参数说明: params : key为数据库字段名,value为字段值 null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的

例1: allEq({id:1,name:"老王",age:null})—>id = 1 and name = '老王' and age is null
例2: allEq({id:1,name:"老王",age:null}, false)—>id = 1 and name = '老王'

个别参数说明： filter : 过滤函数,是否允许字段传入比对条件中 params 与 null2IsNull : 同上

2.2 eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)

等于=
例：eq("name", "老王")—>name = '老王'

2.3 ne

ne(R column, Object val)
ne(boolean condition, R column, Object val)

不等于<>
例：ne("name", "老王")—>name <> '老王'

2.4 gt

gt(R column, Object val)
gt(boolean condition, R column, Object val)

大于等于>=
例:lt("age", 18)—>age < 18

2.5 le

le(R column, Object val)
le(boolean condition, R column, Object val)

小于等于<=
例：le("age", 18)—>age <= 18

2.6 between

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

BETWEEN 值1 AND 值2
例：between("age", 18, 30)—>age between 18 and 30

2.7 notBetween

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

NOT BETWEEN 值1 AND 值2
notBetween("age", 18, 30)—>age not between 18 and 30

2.8 like

like(R column, Object val)
like(boolean condition, R column, Object val)

LIKE ‘%值%’
例：like("name", "王")—>name like '%王%'

2.9 notLike

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

NOT LIKE ‘%值%’
例：notLike(“name”, “王”)—>name not like ‘%王%’

2.10 likeLeft

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

LIKE ‘%值’
例: likeLeft("name", "王")—>name like '%王'

2.11 likeRight

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

LIKE ‘值%’
例: likeRight("name", "王")—>name like '王%'

2.12 isNull

isNull(R column)
isNull(boolean condition, R column)

字段 IS NULL
例: isNull("name")—>name is null

2.13 isNotNull

isNotNull(R column)
isNotNull(boolean condition, R column)

字段 IS NOT NULL
例: isNotNull("name")—>name is not null

2.14 in

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)

字段 IN (value.get(0), value.get(1), …)
例: in("age",{1,2,3})—>age in (1,2,3)

in(R column, Object... values)
in(boolean condition, R column, Object... values)

字段 IN (v0, v1, …)
例: in("age", 1, 2, 3)—>age in (1,2,3)

2.15 notIn

notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)

字段 NOT IN (value.get(0), value.get(1), …)
例: notIn("age",{1,2,3})—>age not in (1,2,3)

notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

字段 NOT IN (v0, v1, …)
例: notIn("age", 1, 2, 3)—>age not in (1,2,3)

2.16 inSql

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

字段 IN ( sql语句 )
例: inSql("age", "1,2,3,4,5,6")—>age in (1,2,3,4,5,6)
例: inSql("id", "select id from table where id < 3")—>id in (select id from table where id < 3)

2.17 notInSql

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)

字段 NOT IN ( sql语句 )
例: notInSql("age", "1,2,3,4,5,6")—>age not in (1,2,3,4,5,6)
例: notInSql("id", "select id from table where id < 3")—>id not in (select id from table where id < 3)

2.18 groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)

分组：GROUP BY 字段, …
例: groupBy("id", "name")-–>group by id,name

2.19 orderByAsc

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)

排序：ORDER BY 字段, … ASC
例: orderByAsc("id", "name")—>order by id ASC,name ASC

2.20 orderByDesc

orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)

排序：ORDER BY 字段, … DESC 例: orderByDesc("id", "name")—>order by id DESC,name DESC

2.21 orderBy

orderBy(boolean condition, boolean isAsc, R... columns)

排序：ORDER BY 字段, … 例: orderBy(true, true, "id", "name")—>order by id ASC,name ASC

2.22 having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

HAVING ( sql语句 )
例: having("sum(age) > 10")—>having sum(age) > 10
例: having("sum(age) > {0}", 11)—>having sum(age) > 11

2.23 func

func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)

func 方法(主要方便在出现if…else下调用不同方法能不断链)
例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})

2.24 or

or()
or(boolean condition)

拼接 OR

注意事项: 主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

例: eq("id",1).or().eq("name","老王")—>id = 1 or name = '老王'

or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)

OR 嵌套
例: or(i -> i.eq("name", "李白").ne("status", "活着"))—>or (name = '李白' and status <> '活着')

2.25 and

and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)

AND 嵌套例: and(i -> i.eq("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')

2.26 nested

nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)

正常嵌套不带 AND 或者 OR
例: nested(i -> i.eq("name", "李白").ne("status", "活着"))—>(name = '李白' and status <> '活着')

2.27 apply

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

拼接 sql

注意事项: 该方法可用于数据库函数动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!

例: apply("id = 1")—>id = 1
例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

2.28 last

last(String lastSql)
last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后

注意事项: 只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用

例: last("limit 1")

2.29 exists

exists(String existsSql)
exists(boolean condition, String existsSql)

拼接 EXISTS ( sql语句 )
例: exists("select id from table where age = 1")—>exists (select id from table where age = 1)

2.30 notExists

notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)

拼接 NOT EXISTS ( sql语句 )
例: notExists("select id from table where age = 1")—>not exists (select id from table where age = 1)

3、QuereyWrapper

说明：继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取

3.1 select

select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

设置查询字段说明:

以上方法分为两类. 第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准

例: select("id", "name", "age")
例: select(i -> i.getProperty().startsWith("test"))

4、UpdateWrapper

说明：继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取!

4.1 set

set(String column, Object val)
set(boolean condition, String column, Object val)

SQL SET 字段
例: set("name", "老李头")
例: set("name", "")—>数据库字段值变为空字符串
例: set("name", null)—>数据库字段值变为null

4.2 setSql

setSql(String sql)

设置 SET 部分 SQL
例: setSql("name = '老李头'")

4.3 lambda

获取 LambdaWrapper 在QueryWrapper中是获取LambdaQueryWrapper 在UpdateWrapper中是获取LambdaUpdateWrapper

5、实例

先创建一个SpringBoot工程

5.1 引入依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.baomidou.mybatisplus</groupId>
    <artifactId>mybatisplus01</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatisplus01</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>



        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

5.2 application.yml

# DataSource Config
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mp?&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
# mybatis-plus配置控制台打印sql语句:
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

5.3 Mybatis-plus分页拦截器

@MapperScan注解我加在了SpringBoot的启动类上面，所以这个配置类并没有写

package com.baomidou.mybatisplus.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

5.4 Employee.java

package com.baomidou.mybatisplus.bean;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

/**
 * @TableName
 *  value:指定当前实体类所对应的表名
 */
@Data
@TableName(value = "tbl_employee")
public class Employee {
    /**
     * @TableId:
     *  value:指定表中的主键列的列名，如果实体属性名和列名一致，可以省略不指定
     *  type:指定主键策略
     */
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;         //int

    @TableField(value = "last_name")
    private String lastName;

    @TableField(value = "email")
    private String email;

    @TableField(value = "gender")
    private Integer gender;

    @TableField(value = "age")
    private Integer age;

//    @TableField(exist = false)
//    private Double salary;
}

5.5 条件构造器：查询操作

我们需要分页查询tbl_employee表中，年龄在18~50之间且性别为男且姓名为Tom的所有用户

   Page<Employee> employeePage = employeeMapper.selectPage(new Page<Employee>(1, 2),
                new QueryWrapper<Employee>()
                        .between("age", 18, 50)
                        .eq("gender", 1)
                        .eq("last_name", "Tom")
        );
        List<Employee> employeeList=employeePage.getRecords();
        employeeList.forEach(System.out::println);

查询tbl_employee表中性别为女，且名字中带有“老师”或者邮箱中带有‘a’

 List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>()
                .eq("gender", 0)
                .like("last_name", "老师")
                .or()
                .like("email", "a")
        );
        employeeList.forEach(System.out::println);

查询性别为女的，根据age进行排序(asc/desc)

        List<Employee> employeeList = employeeMapper.selectList(
                new QueryWrapper<Employee>()
                        .eq("gender", 0)
//                        .orderBy(true,true,"age")
//                        .orderByDesc("age")
                        .orderByAsc("age")
        );
        employeeList.forEach(System.out::println);

5.6 条件构造器修改操作

 @Test
    public void testWrapperUpdate(){
        Employee employee=new Employee();
        employee.setLastName("苍老师");
        employee.setEmail("cls@sina.com");
        employee.setGender(0);

        Integer result = employeeMapper.update(employee,
                new UpdateWrapper<Employee>()
                .eq("last_name","Tom")
                .eq("age",45)
        );
        System.out.println("result="+result);
    }

5.7 条件构造器删除操作

 /**
     * 条件构造器  删除操作
     */
    @Test
    public void testWrapperDelete(){

        Integer result = employeeMapper.delete(new QueryWrapper<Employee>()
                .eq("last_name", "Tom")
                .eq("age", 33)
        );
        System.out.println("result="+result);
    }