Skip to content

Conditional Constructor

MyBatis-Plus provides a powerful set of conditional constructors (Wrapper) for building complex database query conditions. The Wrapper classes allow developers to construct query conditions using a chainable method call approach, eliminating the need to write verbose SQL statements. This improves development efficiency and reduces the risk of SQL injection.

In MyBatis-Plus, the Wrapper class is the core tool for building query and update conditions. Here are the main Wrapper classes and their functions:

  • AbstractWrapper: This is an abstract base class that provides methods and properties common to all Wrapper classes. It defines the basic logic for condition construction, including fields (column), values (value), operators (condition), and more. All QueryWrapper, UpdateWrapper, LambdaQueryWrapper, and LambdaUpdateWrapper classes inherit from AbstractWrapper.

  • QueryWrapper: Specifically designed for constructing query conditions, it supports basic operations like equals, not equals, greater than, less than, and various other common operations. It allows you to add multiple query conditions using a chainable approach and supports combining and and or logic.

  • UpdateWrapper: Used for constructing update conditions, allowing you to specify criteria when updating data. Similar to QueryWrapper, it also supports chainable calls and logical combinations. Using UpdateWrapper enables you to directly set update fields and conditions without creating an entity object.

  • LambdaQueryWrapper: This is a Lambda expression-based query condition constructor. It references entity class properties through Lambda expressions, thereby avoiding hard-coded field names. This approach improves code readability and maintainability, especially when field names might change.

  • LambdaUpdateWrapper: Similar to LambdaQueryWrapper, LambdaUpdateWrapper is a Lambda expression-based update condition constructor. It allows you to use Lambda expressions to specify update fields and conditions, similarly avoiding the issue of hard-coded field names.

Feature Details

MyBatis-Plus’s Wrapper class is a key tool for building complex query and update conditions. It allows you to construct SQL WHERE clauses using a chainable method call approach, providing great flexibility and convenience.

The following are tips and considerations for using Wrapper features.

allEq

The allEq method is one of the methods in MyBatis-Plus used to build query conditions. It allows you to set equality conditions for multiple fields using a Map.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets equality conditions for all fields. If a field value is null, the null2IsNull parameter determines whether to set it as IS NULL.
allEq(Map<String, Object> params)
allEq(Map<String, Object> params, boolean null2IsNull)
allEq(boolean condition, Map<String, Object> params, boolean null2IsNull)
// Sets equality conditions for all fields. The filter predicate determines which fields should be included. If a field value is null, the null2IsNull parameter determines whether to set it as IS NULL.
allEq(BiPredicate<String, Object> filter, Map<String, Object> params)
allEq(BiPredicate<String, Object> filter, Map<String, Object> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<String, Object> filter, Map<String, Object> params, boolean null2IsNull)

Parameter Description

  • params: A Map where the key is the database column name and the value is the corresponding field value.
  • null2IsNull: If set to true, when a value in the Map is null, the isNull method is called; if set to false, key-value pairs where the value is null are ignored.
  • filter: A BiPredicate used to filter which fields should be included in the query condition.
  • condition: A boolean value that controls whether to apply these conditions.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(Map.of("id", 1, "name", "老王", "age", null));

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.allEq(Map.of("id", 1, "name", "老王", "age", null));

Regular Wrapper with Filter (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq((field, value) -> field.contains("a"), Map.of("id", 1, "name", "老王", "age", null));

Lambda Wrapper with Filter (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.allEq((field, value) -> field.contains("a"), Map.of("id", 1, "name", "老王", "age", null));

Generated SQL

-- Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE id = 1 AND name = '老王' AND age IS NULL
-- Regular Wrapper with Filter and Lambda Wrapper with Filter generate the same SQL
SELECT * FROM user WHERE name = '老王' AND age IS NULL

eq

The eq method is one of the fundamental methods in MyBatis-Plus for building query conditions. You use it to set an equality condition for a single field.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets an equality condition for the specified field
eq(R column, Object val)
// Sets an equality condition for the specified field based on a condition
eq(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the field name.
  • condition: A boolean value that controls whether to apply this equality condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "老王");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(User::getName, "老王");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE name = '老王'

ne

The ne method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a not-equal condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a not-equal condition for the specified column
ne(R column, Object val)
// Sets a not-equal condition for the specified column based on a condition
ne(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value to compare against the column.
  • condition: A boolean value that controls whether to apply this not-equal condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ne("name", "老王");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.ne(User::getName, "老王");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE name <> '老王'

gt

The gt method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a “greater than” condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the greater than condition for the specified field
gt(R column, Object val)
// Sets the greater than condition for the specified field based on a condition
gt(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the field name.
  • condition: A boolean value that controls whether to apply this greater than condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 18);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.gt(User::getAge, 18);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age > 18

ge

The ge method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a greater-than-or-equal-to condition for a single field.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the greater-than-or-equal-to condition for the specified column
ge(R column, Object val)
// Sets the greater-than-or-equal-to condition for the specified column based on a condition
ge(boolean condition, R column, Object val)

Parameters

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the column.
  • condition: A boolean value that controls whether to apply this greater-than-or-equal-to condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 18);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.ge(User::getAge, 18);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age >= 18

lt

The lt method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a “less than” condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a "less than" condition for the specified column
lt(R column, Object val)
// Sets a "less than" condition for the specified column based on a condition
lt(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value to compare against the column.
  • condition: A boolean value that controls whether to apply this “less than” condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age", 18);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.lt(User::getAge, 18);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age < 18

le

The le method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a less-than-or-equal-to condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a less-than-or-equal-to condition for the specified field
le(R column, Object val)
// Sets a less-than-or-equal-to condition for the specified field based on a condition
le(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the field name.
  • condition: A boolean value that controls whether to apply this less-than-or-equal-to condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.le("age", 18);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.le(User::getAge, 18);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE age <= 18

between

The between method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to set a BETWEEN condition for a single field.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the BETWEEN condition for the specified field
between(R column, Object val1, Object val2)
// Sets the BETWEEN condition for the specified field based on a condition
between(boolean condition, R column, Object val1, Object val2)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val1: The first value corresponding to the field name, representing the start value of the BETWEEN condition.
  • val2: The second value corresponding to the field name, representing the end value of the BETWEEN condition.
  • condition: A boolean value that controls whether to apply this BETWEEN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 18, 30);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.between(User::getAge, 18, 30);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age BETWEEN 18 AND 30

notBetween

The notBetween method is another fundamental method in MyBatis-Plus for building query conditions. It sets a NOT BETWEEN condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a NOT BETWEEN condition for the specified field
notBetween(R column, Object val1, Object val2)
// Sets a NOT BETWEEN condition for the specified field based on a condition
notBetween(boolean condition, R column, Object val1, Object val2)

Parameter Description

  • column: The database column name or field name using a Lambda expression.
  • val1: The first value corresponding to the field name, representing the start value of the NOT BETWEEN condition.
  • val2: The second value corresponding to the field name, representing the end value of the NOT BETWEEN condition.
  • condition: A boolean value that controls whether to apply this NOT BETWEEN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notBetween("age", 18, 30);

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notBetween(User::getAge, 18, 30);

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE age NOT BETWEEN 18 AND 30

like

The like method is one of the fundamental methods in MyBatis-Plus for building fuzzy query conditions. You use it to set a LIKE condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the LIKE condition for the specified column
like(R column, Object val)
// Sets the LIKE condition for the specified column based on a condition
like(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the column name, representing the search value for the LIKE condition.
  • condition: A boolean value that controls whether to apply this LIKE condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.like(User::getName, "");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE name LIKE '%王%'

notLike

The notLike method is another fundamental method in MyBatis-Plus for building fuzzy query conditions. It sets a NOT LIKE condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a NOT LIKE condition for the specified field
notLike(R column, Object val)
// Sets a NOT LIKE condition for the specified field based on a condition
notLike(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or field name using a Lambda expression.
  • val: The value corresponding to the field name, representing the search value in the NOT LIKE condition.
  • condition: A boolean value that controls whether to apply this NOT LIKE condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notLike("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notLike(User::getName, "");

Generated SQL

-- Both regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE name NOT LIKE '%王%'

likeLeft

The likeLeft method is one of the fundamental methods in MyBatis-Plus for building fuzzy query conditions. It is used to set a right fuzzy match condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the right fuzzy match condition for the specified column
likeLeft(R column, Object val)
// Sets the right fuzzy match condition for the specified column based on a condition
likeLeft(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the column name, representing the search value for the right fuzzy match condition.
  • condition: A boolean value that controls whether to apply this right fuzzy match condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeLeft("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeLeft(User::getName, "");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE name LIKE '%王'

Through the above optimization, the documentation for the likeLeft method more clearly demonstrates its usage, parameter descriptions, examples, and notes, making it easier for developers to understand and correctly use this method.

likeRight

The likeRight method is one of the fundamental methods in MyBatis-Plus for building fuzzy query conditions. It sets a left fuzzy match condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the left fuzzy match condition for the specified column
likeRight(R column, Object val)
// Sets the left fuzzy match condition for the specified column based on a condition
likeRight(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the column name, representing the search value for the left fuzzy match condition.
  • condition: A boolean value that controls whether to apply this left fuzzy match condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "");

Generated SQL

-- Both the regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE name LIKE '王%'

notLikeLeft

The notLikeLeft method is another fundamental method in MyBatis-Plus for building fuzzy query conditions. It sets a non-right-fuzzy match condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the non-right-fuzzy match condition for the specified field
notLikeLeft(R column, Object val)
// Sets the non-right-fuzzy match condition for the specified field based on a condition
notLikeLeft(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the field name, representing the search value in the non-right-fuzzy match condition.
  • condition: A boolean value that controls whether to apply this non-right-fuzzy match condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notLikeLeft("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notLikeLeft(User::getName, "");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE name NOT LIKE '%王'

notLikeRight

The notLikeRight method is another fundamental method in MyBatis-Plus for building fuzzy query conditions. It sets a non-left fuzzy match condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a non-left fuzzy match condition for the specified field
notLikeRight(R column, Object val)
// Sets a non-left fuzzy match condition for the specified field based on a condition
notLikeRight(boolean condition, R column, Object val)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • val: The value corresponding to the field name, representing the search value in the non-left fuzzy match condition.
  • condition: A boolean value that controls whether to apply this non-left fuzzy match condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notLikeRight("name", "");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notLikeRight(User::getName, "");

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE name NOT LIKE '王%'

Through the above optimization, the documentation for the notLikeRight method more clearly demonstrates its usage, parameter descriptions, examples, and important notes, making it easier for developers to understand and correctly use this method.

isNull

The isNull method is one of the fundamental methods in MyBatis-Plus for building query conditions. It sets an IS NULL condition for a single field.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the IS NULL condition for the specified field
isNull(R column)
// Sets the IS NULL condition for the specified field based on a condition
isNull(boolean condition, R column)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • condition: A boolean value that controls whether to apply this IS NULL condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("name");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.isNull(User::getName);

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE name IS NULL

in

The in method is one of the fundamental methods in MyBatis-Plus for building query conditions. It sets an IN condition for a single field, meaning the field’s value must be within a given collection.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the IN condition for the specified column using a Collection
in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
// Sets the IN condition for the specified column using varargs
in(R column, Object... values)
in(boolean condition, R column, Object... values)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • value: A collection containing the possible values for the field in the IN condition.
  • values: A variable arguments list containing the possible values for the field in the IN condition.
  • condition: A boolean value that controls whether to apply this IN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(1, 2, 3));

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.in(User::getAge, Arrays.asList(1, 2, 3));

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age IN (1, 2, 3)

notIn

The notIn method is one of the fundamental methods in MyBatis-Plus for building query conditions. It sets a NOT IN condition for a single field, meaning the field’s value is not in the given collection.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets a NOT IN condition for the specified field using a collection
notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
// Sets a NOT IN condition for the specified field using varargs
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • value: A collection containing the possible values for the field in the NOT IN condition.
  • values: A varargs list containing the possible values for the field in the NOT IN condition.
  • condition: A boolean value that controls whether to apply this NOT IN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notIn("age", Arrays.asList(1, 2, 3));

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notIn(User::getAge, Arrays.asList(1, 2, 3));

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE age NOT IN (1, 2, 3)

inSql

The inSql method is one of the advanced methods in MyBatis-Plus for building query conditions. It sets an IN condition for a single field, but unlike the in method, inSql allows you to use a SQL statement directly to generate the set of values for the IN clause.

Applicable Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the IN condition for the specified column using a SQL statement
inSql(R column, String sqlValue)
inSql(boolean condition, R column, String sqlValue)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • sqlValue: A string containing the SQL statement used to generate the set of values for the IN clause.
  • condition: A boolean value that controls whether to apply this IN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("age", "1,2,3,4,5,6");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.inSql(User::getAge, "1,2,3,4,5,6");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE age IN (1, 2, 3, 4, 5, 6)

Example Using a Subquery:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id", "select id from other_table where id < 3");

Generated SQL

SELECT * FROM user WHERE id IN (select id from other_table where id < 3)

notInSql

The notInSql method is one of the advanced methods in MyBatis-Plus for building query conditions. It sets a NOT IN condition for a single field. Unlike the notIn method, notInSql allows you to use a SQL statement directly to generate the set of values for the NOT IN clause.

Applicable Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the NOT IN condition for the specified column using a SQL statement
notInSql(R column, String sqlValue)
notInSql(boolean condition, R column, String sqlValue)

Parameter Description

  • column: The database column name or the field name using a Lambda expression.
  • sqlValue: A string containing the SQL statement used to generate the set of values for the NOT IN clause.
  • condition: A boolean value that controls whether to apply this NOT IN condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notInSql("age", "1,2,3,4,5,6");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notInSql(User::getAge, "1,2,3,4,5,6");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE age NOT IN (1, 2, 3, 4, 5, 6)

Example Using a Subquery:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notInSql("id", "select id from other_table where id < 3");

Generated SQL

SELECT * FROM user WHERE id NOT IN (select id from other_table where id < 3)

eqSql Since 3.5.6

The eqSql method is one of the advanced methods in MyBatis-Plus for building query conditions. It allows you to set a field equal to (EQ) the result of an SQL statement. This method is particularly useful for scenarios where you need to compare a field value with the result of a subquery.

Method Signature

// Sets the specified column equal to the result of an SQL statement
eqSql(R column, String inValue)
// Sets the specified column equal to the result of an SQL statement when the condition is met
eqSql(boolean condition, R column, String inValue)

Parameters

  • column: The database column name or field name using a Lambda expression.
  • inValue: A string containing the SQL statement used to generate the equal condition.
  • condition: A boolean value that controls whether to apply this equal condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eqSql("id", "select MAX(id) from table");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eqSql(User::getId, "select MAX(id) from table");

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE id = (select MAX(id) from table)

gtSql Since 3.4.3.2

The gtSql method is one of the advanced methods in MyBatis-Plus for building query conditions, allowing you to set a field as greater than (GT) the result of an SQL statement.

Method Signature

// Sets the specified column to be greater than the result of an SQL statement
gtSql(R column, String inValue)
// Sets the specified column to be greater than the result of an SQL statement if the condition is met
gtSql(boolean condition, R column, String inValue)

Parameters

  • column: The database column name or the column name using a Lambda expression.
  • inValue: A string containing the SQL statement used to generate the greater-than condition.
  • condition: A boolean value that controls whether to apply this greater-than condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gtSql("id", "select id from table where name = 'xx'");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.gtSql(User::getId, "select id from table where name = 'xx'");

Generated SQL

-- The SQL generated by both the Regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE id > (select id from table where name = 'xx')

geSql Since 3.4.3.2

The geSql method is one of the advanced methods in MyBatis-Plus for building query conditions. It allows you to set a field to be greater than or equal to (GE) the result of an SQL statement.

Method Signature

// Sets the specified column to be greater than or equal to the result of an SQL statement
geSql(R column, String inValue)
// Sets the specified column to be greater than or equal to the result of an SQL statement when the condition is met
geSql(boolean condition, R column, String inValue)

Parameters

  • column: The database column name or a field name using a Lambda expression.
  • inValue: A string containing the SQL statement used to generate the greater-than-or-equal condition.
  • condition: A boolean value that controls whether to apply this greater-than-or-equal condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.geSql("id", "select id from table where name = 'xx'");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.geSql(User::getId, "select id from table where name = 'xx'");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE id >= (select id from table where name = 'xx')

ltSql Since 3.4.3.2

The ltSql method is one of the advanced methods in MyBatis-Plus for building query conditions. It allows you to set a field to be less than (LT) the result of an SQL statement.

Method Signature

// Sets the specified column to be less than the result of an SQL statement
ltSql(R column, String inValue)
// Sets the specified column to be less than the result of an SQL statement when the condition is met
ltSql(boolean condition, R column, String inValue)

Parameters

  • column: The database column name or the field name using a Lambda expression.
  • inValue: A string containing the SQL statement used to generate the less-than condition.
  • condition: A boolean value that controls whether to apply this less-than condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ltSql("id", "select id from table where name = 'xx'");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.ltSql(User::getId, "select id from table where name = 'xx'");

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE id < (select id from table where name = 'xx')

leSql Since 3.4.3.2

The leSql method is one of the advanced query-building methods in MyBatis-Plus that allows you to specify that a field should be less than or equal to (LE) the result of an SQL statement.

Method Signature

// Sets the specified column to be less than or equal to the result of an SQL statement
leSql(R column, String inValue)
// Sets the specified column to be less than or equal to the result of an SQL statement when the condition is met
leSql(boolean condition, R column, String inValue)

Parameters

  • column: The database column name or a field name using a Lambda expression.
  • inValue: A string containing the SQL statement used to generate the less-than-or-equal-to condition.
  • condition: A boolean value that controls whether to apply this less-than-or-equal-to condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.leSql("id", "select id from table where name = 'xx'");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.leSql(User::getId, "select id from table where name = 'xx'");

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE id <= (select id from table where name = 'xx')

groupBy

The groupBy method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to set grouping conditions for query results. By specifying one or more fields, the groupBy method generates the GROUP BY clause in the SQL statement.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets grouping conditions using column names
groupBy(R... columns)
groupBy(boolean condition, R... columns)

Parameter Description

  • columns: A variable argument list containing the column names used for grouping.
  • condition: A boolean value that controls whether to apply this grouping condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.groupBy("id", "name");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.groupBy(User::getId, User::getName);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user GROUP BY id, name

orderByAsc

The orderByAsc method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to set ascending order conditions for query results. By specifying one or more columns, the orderByAsc method generates the ORDER BY clause in the SQL statement, specifying ascending order.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets ascending order conditions using column names
orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)

Parameter Description

  • columns: A variable argument list containing the column names used for sorting.
  • condition: A boolean value that controls whether to apply this sorting condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByAsc("id", "name");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderByAsc(User::getId, User::getName);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user ORDER BY id ASC, name ASC

orderByDesc

The orderByDesc method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to set descending order criteria for query results. By specifying one or more columns, the orderByDesc method generates the ORDER BY clause in the SQL statement, specifying descending order.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets descending order conditions using column names
orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)

Parameter Description

  • columns: A variable argument list containing the column names used for sorting.
  • condition: A boolean value that controls whether to apply this sorting condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id", "name");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderByDesc(User::getId, User::getName);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user ORDER BY id DESC, name DESC

orderBy

The orderBy method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to set the sorting criteria for query results. By specifying one or more fields along with the sort direction (ascending or descending), the orderBy method generates the ORDER BY clause in the SQL statement.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the sorting conditions using field names and sort direction
orderBy(boolean condition, boolean isAsc, R... columns)

Parameter Description

  • condition: A boolean value that controls whether to apply this sorting condition.
  • isAsc: A boolean value indicating the sort direction. true means ascending (ASC), false means descending (DESC).
  • columns: A variable argument list containing the field names used for sorting.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true, "id", "name");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.orderBy(true, true, User::getId, User::getName);

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user ORDER BY id ASC, name ASC

having

The having method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to set the HAVING clause, typically used with GROUP BY to filter grouped data.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the HAVING clause using an SQL statement and parameters
having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

Parameter Description

  • sqlHaving: A string containing the SQL statement used to generate the HAVING clause.
  • params: A variable argument list containing replacement values for placeholders in the SQL statement.
  • condition: A boolean value that controls whether to apply this HAVING condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.groupBy("age").having("sum(age) > 10");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.groupBy(User::getAge).having("sum(age) > {0}", 10);

Generated SQL

-- Both the regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user GROUP BY age HAVING sum(age) > 10

func

The func method is one of the advanced methods in MyBatis-Plus for building query conditions. It provides a mechanism to execute different query operations within a chain call based on conditions. By passing a Consumer functional interface, the func method allows you to execute different query-building logic based on conditions without breaking the chain call.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Executes different query-building logic based on conditions
func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)

Parameter Description

  • consumer: A Consumer functional interface that accepts a parameter of type Children and can call methods on the Children object to build query conditions.
  • condition: A boolean value that controls whether to apply this Consumer logic.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.func(i -> {
if (true) {
i.eq("id", 1);
} else {
i.ne("id", 1);
}
});

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.func(i -> {
if (true) {
i.eq(User::getId, 1);
} else {
i.ne(User::getId, 1);
}
});

Generated SQL

-- The generated SQL will differ based on the condition
-- If the condition is true, the generated SQL is:
SELECT * FROM user WHERE id = 1
-- If the condition is false, the generated SQL is:
SELECT * FROM user WHERE id != 1

or

The or method is one of the fundamental methods in MyBatis-Plus for building query conditions. It is used to add OR logic to your query conditions. By calling the or method, you can change the connection method for subsequent query conditions from the default AND connection to an OR connection.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Changes the connection method for subsequent query conditions to OR
or()
or(boolean condition)
// Adds OR nested conditions
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)

Parameter Description

  • condition: A boolean value that controls whether to apply this OR logic.
  • consumer: A Consumer functional interface that accepts a parameter of type Param and can call methods on the Param object to build OR nested conditions.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("id", 1).or().eq("name", "老王");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(User::getId, 1).or().eq(User::getName, "老王");

Generated SQL

-- Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE id = 1 OR name = '老王'

OR Nesting Example:

// Regular Wrapper
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.or(i -> i.and(j -> j.eq("name", "李白").eq("status", "alive"))
.or(j -> j.eq("name", "杜甫").eq("status", "alive")));
// Lambda Wrapper
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.or(i -> i.and(j -> j.eq(User::getName, "李白").eq(User::getStatus, "alive"))
.or(j -> j.eq(User::getName, "杜甫").eq(User::getStatus, "alive")));

Generated SQL

-- Regular Wrapper and Lambda Wrapper generate the same SQL
SELECT * FROM user WHERE (name = '李白' AND status = 'alive') OR (name = '杜甫' AND status = 'alive')

and

The and method is one of the fundamental methods in MyBatis-Plus for building query conditions. It adds AND logic to your query conditions. By calling the and method, you can create nested AND conditions, meaning you can group multiple query conditions within a single AND logic block.

Usage Scope

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Add AND nested condition
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)

Parameter Description

  • consumer: A Consumer functional interface that accepts a parameter of type Param. You can call methods on this Param object to build the nested AND conditions.
  • condition: A boolean value that controls whether to apply this AND nested logic.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.and(i -> i.and(j -> j.eq("name", "李白").eq("status", "alive"))
.and(j -> j.eq("name", "杜甫").eq("status", "alive")));

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.and(i -> i.and(j -> j.eq(User::getName, "李白").eq(User::getStatus, "alive"))
.and(j -> j.eq(User::getName, "杜甫").eq(User::getStatus, "alive")));

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE ((name = '李白' AND status = 'alive') AND (name = '杜甫' AND status = 'alive'))

nested

The nested method is one of the advanced methods in MyBatis-Plus for building query conditions. It creates an independent query condition block without a default AND or OR logic. By calling the nested method, you can add a nested clause to your query conditions. This clause can contain multiple conditions and can be connected to the outer query conditions via AND or OR.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Adds an independent query condition block
nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)

Parameter Description

  • consumer: A Consumer functional interface that accepts a parameter of type Param. You can call methods on this Param object to build the nested query conditions.
  • condition: A boolean value that controls whether to apply this nested logic.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(i -> i.eq("name", "李白").ne("status", "活着"));

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.nested(i -> i.eq(User::getName, "李白").ne(User::getStatus, "活着"));

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT * FROM user WHERE (name = '李白' AND status <> '活着')

apply

The apply method is one of the advanced methods in MyBatis-Plus for building query conditions, allowing you to directly concatenate SQL fragments into the query. This method is particularly useful for scenarios requiring database functions or other complex SQL constructs.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Concatenate SQL fragment
apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

Parameter Description

  • applySql: A string containing the SQL fragment to be concatenated.
  • params: A variable argument list containing replacement values for the placeholders in the SQL fragment.
  • condition: A boolean value that controls whether to apply this SQL fragment.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("id = 1");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.apply("date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'");

Example using parameter placeholders:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(dateColumn, '%Y-%m-%d') = {0}", "2008-08-08");

Generated SQL

-- SQL generated by regular Wrapper
SELECT * FROM user WHERE id = 1
-- SQL generated by Lambda Wrapper
SELECT * FROM user WHERE date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'
-- SQL generated using parameter placeholders
SELECT * FROM user WHERE date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'

Important Notes

  • The apply method can be used to concatenate SQL fragments containing database functions.
  • The dynamic input parameters in params correspond to the {index} parts inside the applySql, which eliminates SQL injection risk. If you directly concatenate parameters into the SQL, there is a risk of SQL injection. Therefore, you should avoid having SQL dynamically passed from frontend parameters and directly referenced.
  • When using the apply method, ensure the applySql parameter is a valid SQL fragment and that the params parameter correctly replaces the placeholders.
  • The condition parameter controls whether to apply this SQL fragment, allowing you to dynamically add query conditions based on certain criteria.

last

The last method is one of the advanced methods in MyBatis-Plus for building query conditions. It allows you to directly append an SQL fragment to the end of a query, bypassing MyBatis-Plus’s query optimization rules. Use this method with caution, as it may circumvent MyBatis-Plus’s query optimization.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Appends an SQL fragment to the end of the query
last(String lastSql)
last(boolean condition, String lastSql)

Parameter Description

  • lastSql: A string containing the SQL fragment to append to the end of the query.
  • condition: A boolean value that controls whether to apply this SQL fragment.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.last("limit 1");

Generated SQL

-- The generated SQL is the same for both the regular Wrapper and Lambda Wrapper
SELECT * FROM user LIMIT 1

exists

The exists method is one of the advanced methods in MyBatis-Plus for building query conditions. It is used to add an EXISTS subquery to your query. By calling the exists method, you can add a complete SQL subquery as an EXISTS condition to your main query.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Add an EXISTS subquery
exists(String existsSql)
exists(boolean condition, String existsSql)

Parameter Description

  • existsSql: A string containing the SQL subquery to be used as the EXISTS condition.
  • condition: A boolean value that controls whether to apply this EXISTS condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.exists("select id from table where age = 1");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.exists("select id from table where age = 1");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE EXISTS (select id from table where age = 1)

notExists

The notExists method is one of the advanced methods in MyBatis-Plus for building query conditions. It adds a NOT EXISTS subquery to your query. By calling the notExists method, you can add a complete SQL subquery as a NOT EXISTS condition to the main query.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper
  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Add a NOT EXISTS subquery
notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)

Parameter Description

  • notExistsSql: A string containing the SQL subquery to be used as the NOT EXISTS condition.
  • condition: A boolean value that controls whether to apply this NOT EXISTS condition.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notExists("select id from table where age = 1");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.notExists("select id from table where age = 1");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is identical
SELECT * FROM user WHERE NOT EXISTS (select id from table where age = 1)

select

The select method is one of the advanced methods in MyBatis-Plus for building query conditions, used to specify which fields to include in the query results. By calling the select method, you can define the specific columns to be returned, enabling field-level customization of your queries.

Scope of Use

  • QueryWrapper
  • LambdaQueryWrapper

Method Signature

// Sets the query columns
select(String... sqlSelect)
// Filters query columns (excluding the primary key)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

Parameter Description

  • sqlSelect: A string array containing the names of the columns to query.
  • predicate: A Predicate functional interface used to filter query columns. It accepts a parameter of type TableFieldInfo and returns a boolean value indicating whether to select the field.
  • entityClass: The type of the entity class, used to obtain field information.

Examples

Regular Wrapper (QueryWrapper):

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name", "age");

Lambda Wrapper (LambdaQueryWrapper):

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.select(User::getId, User::getName, User::getAge);

Example using Predicate to filter fields:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(i -> i.getProperty().startsWith("test"));

Generated SQL

-- SQL generated by both the regular Wrapper and Lambda Wrapper is the same
SELECT id, name, age FROM user
-- SQL generated using Predicate to filter fields
SELECT testField1, testField2 FROM user

set

The set method is one of the advanced methods in MyBatis-Plus for building update operations. It is used to specify the SET fields in an update statement. By calling the set method, you can define which fields to modify and their new values during an update operation.

Scope of Use

  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the SET fields in the update statement
set(R column, Object val)
set(R column, Object val, String mapping)
set(boolean condition, R column, Object val)
set(boolean condition, R column, Object val, String mapping)

Parameter Description

  • column: The database column name or a field name using a Lambda expression.
  • condition: A boolean value that controls whether to apply this SET field.
  • val: An object representing the new value to update the field to.
  • mapping: Additional specifications, for example: javaType=int,jdbcType=NUMERIC,typeHandler=xxx.xxx.MyTypeHandler

Examples

Regular Wrapper (UpdateWrapper):

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.set("name", "Old Man Li");

Lambda Wrapper (LambdaUpdateWrapper):

LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.set(User::getName, "Old Man Li");

Generated SQL

-- The SQL generated by both the regular Wrapper and Lambda Wrapper is the same
UPDATE user SET name = 'Old Man Li'

Example using conditional control:

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.set(true, "name", "");

Generated SQL

-- SQL using conditional control
UPDATE user SET name = ''

setSql

The setSql method is one of the advanced methods in MyBatis-Plus for building update operations. It allows you to directly set the SQL for the SET clause in the update statement. By calling the setSql method, you can add a custom SQL fragment as the SET clause to the update statement.

Scope of Use

  • UpdateWrapper
  • LambdaUpdateWrapper

Method Signature

// Sets the SQL for the SET clause in the update statement
setSql(String setSql, Object... params)
setSql(boolean condition, String setSql, Object... params)

Parameter Description

  • setSql: A string containing the SQL fragment to be used as the SET clause.
  • condition: A boolean value that controls whether to apply this SET field.
  • params: A variable argument list containing replacement values for placeholders in the SQL fragment.

Examples

setSql("name = '老李头'")
setSql("dateColumn={0}", LocalDate.now())
setSql("type={0,javaType=int,jdbcType=NUMERIC,typeHandler=xxx.xxx.MyTypeHandler}", "待处理字符串");

setIncrBy Since 3.5.6

The setIncrBy method is one of the advanced update operations in MyBatis-Plus that allows you to specify a field and increment its value in the database by a specified amount. This method is particularly useful for scenarios where you need to perform incremental operations on numeric fields.

Scope of Use

  • LambdaUpdateWrapper

Method Signature

// Increment field by specified value
setIncrBy(SFunction<T, ?> column, Number val)
// Increment field by specified value when condition is met
setIncrBy(boolean condition, SFunction<T, ?> column, Number val)

Parameter Description

  • column: An SFunction object representing the field to increment.
  • val: A Number object representing the value to add.
  • condition (optional): A boolean value indicating whether to perform the increment operation when the condition is met.

Examples

Regular Wrapper (UpdateWrapper):

UpdateWrapper<Product> updateWrapper = new UpdateWrapper<>();
updateWrapper.setIncrBy(Product::getNum, 1);

Lambda Wrapper (LambdaUpdateWrapper):

LambdaUpdateWrapper<Product> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.setIncrBy(Product::getNum, 1);

Generated SQL

-- Both Regular Wrapper and Lambda Wrapper generate the same SQL
UPDATE product SET num = num + 1

setDecrBy Since 3.5.6

The setDecrBy method is one of the advanced update operations in MyBatis-Plus that allows you to specify a field and decrease its value in the database by a specified amount. This method is particularly useful for scenarios where you need to perform decrement operations on numeric fields.

Scope of Use

  • LambdaUpdateWrapper

Method Signature

// Decreases a field by the specified value
setDecrBy(SFunction<T, ?> column, Number val)
// Decreases a field by the specified value when the condition is met
setDecrBy(boolean condition, SFunction<T, ?> column, Number val)

Parameter Description

  • column: An SFunction object representing the field to decrement.
  • val: A Number object representing the value to subtract.
  • condition (optional): A boolean value indicating whether to perform the decrement operation when the condition is met.

Examples

Regular Wrapper (UpdateWrapper):

UpdateWrapper<Product> updateWrapper = new UpdateWrapper<>();
updateWrapper.setDecrBy("num", 1);

Lambda Wrapper (LambdaUpdateWrapper):

LambdaUpdateWrapper<Product> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.setDecrBy(Product::getNum, 1);

Generated SQL

-- Both regular Wrapper and Lambda Wrapper generate the same SQL
UPDATE product SET num = num - 1

lambda

The lambda method is a convenient way to get the corresponding LambdaQueryWrapper or LambdaUpdateWrapper object from a QueryWrapper or UpdateWrapper object. This allows you to use Lambda expressions to build query or update conditions, making your code more concise and type-safe.

Scope of Use

  • QueryWrapper
  • UpdateWrapper

Method Signature

// Get Lamdba Wrapper
lambda();

Examples

Get LambdaQueryWrapper from QueryWrapper:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
LambdaQueryWrapper<User> lambdaQueryWrapper = queryWrapper.lambda();
// Use Lambda expressions to build query conditions
lambdaQueryWrapper.eq(User::getName, "张三");

Get LambdaUpdateWrapper from UpdateWrapper:

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
LambdaUpdateWrapper<User> lambdaUpdateWrapper = updateWrapper.lambda();
// Use Lambda expressions to build update conditions
lambdaUpdateWrapper.set(User::getName, "李四");

Explanation

  • The lambda method returns a LambdaWrapper object. The specific type depends on the type of Wrapper that calls it.
  • Calling the lambda method on a QueryWrapper returns a LambdaQueryWrapper.
  • Calling the lambda method on an UpdateWrapper returns a LambdaUpdateWrapper.
  • Using Lambda expressions helps you avoid using strings directly to specify field names, which reduces errors and improves code readability.

Using TypeHandler

When using typeHandler in wrapper, special handling is required using the formatSqlMaybeWithParam method

// Query
queryWrapper.apply("type={0,typeHandler="+ MyTypeHandler.class.getCanonicalName()+ "}", "string to process");
// Update
updateWrapper.setSql("type={0,javaType=string,jdbcType=VARCHAR,typeHandler=xxx.xxx.MyTypeHandler}", "string to process");

Usage Tips

By using MyBatis-Plus’s Wrapper condition builder, you can build complex database query conditions more efficiently while maintaining code simplicity and security. Here are some considerations and recommended practices:

  • When using Wrapper, prefer Lambda expressions to avoid hard-coding field names. This improves code readability and maintainability.
  • Wrapper supports chain calls, allowing you to combine multiple conditions using logical operators like and and or.
  • When using UpdateWrapper or LambdaUpdateWrapper for update operations, you can omit the entity object and set the update fields directly in the Wrapper.
  • Be aware of Wrapper’s thread safety; typically, you should create a new Wrapper instance for each use.
  • When using MyBatis-Plus’s Wrapper, avoid directly concatenating front-end dynamic parameters into SQL fragments to prevent SQL injection attacks. MyBatis-Plus provides safe parameter binding methods, such as eq and apply, which automatically handle parameter binding to mitigate SQL injection risks.

Wrappers

MyBatis-Plus provides the Wrappers class, which is a static factory class for quickly creating instances of QueryWrapper, UpdateWrapper, LambdaQueryWrapper, and LambdaUpdateWrapper. Using Wrappers can reduce code volume and improve development efficiency.

Example:

// Create QueryWrapper
QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper.eq("name", "张三");
// Create LambdaQueryWrapper
LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
lambdaQueryWrapper.eq(User::getName, "张三");
// Create UpdateWrapper
UpdateWrapper<User> updateWrapper = Wrappers.update();
updateWrapper.set("name", "李四");
// Create LambdaUpdateWrapper
LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
lambdaUpdateWrapper.set(User::getName, "李四");

Thread Safety

Wrapper instances are not thread-safe, so we recommend creating new Wrapper instances each time you use them. This helps avoid data races and potential errors in multi-threaded environments.

Example:

// Create new Wrapper instances in each method or request
public List<User> getUsersByName(String name) {
QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper.eq("name", name);
return userMapper.selectList(queryWrapper);
}

By following these best practices, you can use MyBatis-Plus’s Wrapper condition constructor more safely and efficiently, building database operation code that is both secure and easy to maintain.

Using Wrapper for Custom SQL

MyBatis-Plus provides a powerful Wrapper conditional constructor that allows developers to customize SQL statements to meet more complex database query requirements. To use this feature, ensure your mybatis-plus version is 3.0.7 or higher.

Notes

  • Version Requirement: Ensure the mybatis-plus version used in your project is at least 3.0.7 to support custom SQL functionality.
  • Parameter Naming: When passing a Wrapper object as a parameter in custom SQL, the parameter name must be ew, or you must explicitly specify the parameter as a Wrapper object using the @Param(Constants.WRAPPER) annotation.
  • Using ${ew.customSqlSegment}: In your SQL statement, use ${ew.customSqlSegment} to reference the SQL fragment generated by the Wrapper object.
  • Entity-based WHERE Clauses Not Supported: When using custom SQL, the Wrapper object does not automatically generate WHERE clauses based on the entity class. You need to manually write the complete SQL statement.

Example

Here is an example of using Wrapper for custom SQL:

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
public interface UserMapper extends BaseMapper<User> {
@Select("SELECT * FROM user ${ew.customSqlSegment}")
List<User> selectByCustomSql(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}

In the example above, we defined a selectByCustomSql method that uses a custom SQL statement and incorporates the SQL fragment generated by the Wrapper object via ${ew.customSqlSegment}.

Usage

To use custom SQL, simply call the method above and pass in a Wrapper object:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "张三");
List<User> userList = userMapper.selectByCustomSql(queryWrapper);

In this example, the selectByCustomSql method executes a query with a WHERE condition generated by the passed queryWrapper object.

This approach allows you to flexibly combine MyBatis-Plus’s Wrapper functionality with custom SQL to meet various complex database operation requirements.

Kotlin Persistence Object Definition Best Practices

When defining persistence objects in Kotlin, you should follow some best practices to ensure code clarity and maintainability. Here’s an example using MyBatis-Plus that demonstrates how to define a persistence object:

@TableName("sys_user")
class User {
@TableId(type = IdType.AUTO)
var id: Int? = null
@TableField("username")
var name: String? = null
var roleId: Int? = null
}

Note: The @TableId and @TableField annotations in the code above are used to demonstrate MyBatis-Plus usage and are not mandatory. All member variables should be defined as nullable types with initial values of null to support scenarios like Java’s updateSelective operations.

Avoid using data class or full-parameter constructors, as they may require you to provide unnecessary null values when creating empty objects.

Using Annotations for Queries

@Select("select * from mysql_data ${ew.customSqlSegment}")
List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);

Using XML to Configure Queries

List<MysqlData> getAll(Wrapper ew);
<select id="getAll" resultType="MysqlData">
SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>

Using Wrapper in Kotlin

Kotlin supports QueryWrapper and UpdateWrapper, but does not support LambdaQueryWrapper and LambdaUpdateWrapper. If you need to use Lambda-style Wrappers, you can use KtQueryWrapper and KtUpdateWrapper.

Reference example:

val queryWrapper = KtQueryWrapper(User()).eq(User::name, "sss").eq(User::roleId, "sss2")
userMapper!!.selectList(queryWrapper)
val updateConditionWrapper = KtUpdateWrapper(User()).eq(User::name, "sss").eq(User::roleId, "sss2")
val updateRecord = User()
updateRecord.name = "newName"
userMapper!!.update(updateRecord, updateConditionWrapper)
val updateRecord = User()
updateRecord.id = 2
updateRecord.name = "haha"
userMapper.updateById(updateRecord)

Chained Calls and Lambda Calls

MyBatis-Plus provides two styles of chained calls: regular chained calls and lambda chained calls. Note that lambda chained calls do not support Kotlin.

// Regular chained call
UpdateChainWrapper<T> update();
// Lambda chained call (does not support Kotlin)
LambdaUpdateChainWrapper<T> lambdaUpdate();
// Equivalent examples:
query().eq("id", value).one();
lambdaQuery().eq(Entity::getId, value).one();
// Equivalent examples:
update().eq("id", value).remove();
lambdaUpdate().eq(Entity::getId, value).remove();

By following these best practices, you can ensure that your persistence object definitions in Kotlin are both clear and maintainable, while fully leveraging the capabilities provided by MyBatis-Plus.

Baomidou

© 2016-2025 Baomidou™. All Rights Reserved.

Power by Astro Starlight | Sponsored by JetBrains

渝ICP备2021000141号-1 | 渝公网安备50011302222097