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
andor
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
: AMap
where thekey
is the database column name and thevalue
is the corresponding field value.null2IsNull
: If set totrue
, when avalue
in theMap
isnull
, theisNull
method is called; if set tofalse
, key-value pairs where the value isnull
are ignored.filter
: ABiPredicate
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 SQLSELECT * FROM user WHERE id = 1 AND name = '老王' AND age IS NULL
-- Regular Wrapper with Filter and Lambda Wrapper with Filter generate the same SQLSELECT * 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 fieldeq(R column, Object val)
// Sets an equality condition for the specified field based on a conditioneq(boolean condition, R column, Object val)
Parameter Description
column
: The database column name or the field name using aLambda
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 identicalSELECT * 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 columnne(R column, Object val)
// Sets a not-equal condition for the specified column based on a conditionne(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 identicalSELECT * 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 fieldgt(R column, Object val)
// Sets the greater than condition for the specified field based on a conditiongt(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 identicalSELECT * 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 columnge(R column, Object val)
// Sets the greater-than-or-equal-to condition for the specified column based on a conditionge(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 identicalSELECT * 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 columnlt(R column, Object val)
// Sets a "less than" condition for the specified column based on a conditionlt(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 identicalSELECT * 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 fieldle(R column, Object val)
// Sets a less-than-or-equal-to condition for the specified field based on a conditionle(boolean condition, R column, Object val)
Parameter Description
column
: The database column name or the field name using aLambda
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 sameSELECT * 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 fieldbetween(R column, Object val1, Object val2)
// Sets the BETWEEN condition for the specified field based on a conditionbetween(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 identicalSELECT * 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 fieldnotBetween(R column, Object val1, Object val2)
// Sets a NOT BETWEEN condition for the specified field based on a conditionnotBetween(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 SQLSELECT * 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 columnlike(R column, Object val)
// Sets the LIKE condition for the specified column based on a conditionlike(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 identicalSELECT * 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 fieldnotLike(R column, Object val)
// Sets a NOT LIKE condition for the specified field based on a conditionnotLike(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 SQLSELECT * 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 columnlikeLeft(R column, Object val)
// Sets the right fuzzy match condition for the specified column based on a conditionlikeLeft(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 sameSELECT * 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 columnlikeRight(R column, Object val)
// Sets the left fuzzy match condition for the specified column based on a conditionlikeRight(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 SQLSELECT * 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 fieldnotLikeLeft(R column, Object val)
// Sets the non-right-fuzzy match condition for the specified field based on a conditionnotLikeLeft(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 sameSELECT * 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 fieldnotLikeRight(R column, Object val)
// Sets a non-left fuzzy match condition for the specified field based on a conditionnotLikeRight(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 SQLSELECT * 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 fieldisNull(R column)
// Sets the IS NULL condition for the specified field based on a conditionisNull(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 SQLSELECT * 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 Collectionin(R column, Collection<?> value)in(boolean condition, R column, Collection<?> value)
// Sets the IN condition for the specified column using varargsin(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 identicalSELECT * 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 collectionnotIn(R column, Collection<?> value)notIn(boolean condition, R column, Collection<?> value)
// Sets a NOT IN condition for the specified field using varargsnotIn(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 identicalSELECT * 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 statementinSql(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 sameSELECT * 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 statementnotInSql(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 sameSELECT * 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 statementeqSql(R column, String inValue)
// Sets the specified column equal to the result of an SQL statement when the condition is meteqSql(boolean condition, R column, String inValue)
Parameters
column
: The database column name or field name using aLambda
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 SQLSELECT * 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 statementgtSql(R column, String inValue)
// Sets the specified column to be greater than the result of an SQL statement if the condition is metgtSql(boolean condition, R column, String inValue)
Parameters
column
: The database column name or the column name using aLambda
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 identicalSELECT * 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 statementgeSql(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 metgeSql(boolean condition, R column, String inValue)
Parameters
column
: The database column name or a field name using aLambda
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 identicalSELECT * 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 statementltSql(R column, String inValue)
// Sets the specified column to be less than the result of an SQL statement when the condition is metltSql(boolean condition, R column, String inValue)
Parameters
column
: The database column name or the field name using aLambda
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 SQLSELECT * 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 statementleSql(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 metleSql(boolean condition, R column, String inValue)
Parameters
column
: The database column name or a field name using aLambda
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 SQLSELECT * 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 namesgroupBy(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 sameSELECT * 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 namesorderByAsc(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 sameSELECT * 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 namesorderByDesc(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 sameSELECT * 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 directionorderBy(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 sameSELECT * 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 parametershaving(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 SQLSELECT * 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 conditionsfunc(Consumer<Children> consumer)func(boolean condition, Consumer<Children> consumer)
Parameter Description
consumer
: AConsumer
functional interface that accepts a parameter of typeChildren
and can call methods on theChildren
object to build query conditions.condition
: A boolean value that controls whether to apply thisConsumer
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 ORor()or(boolean condition)
// Adds OR nested conditionsor(Consumer<Param> consumer)or(boolean condition, Consumer<Param> consumer)
Parameter Description
condition
: A boolean value that controls whether to apply this OR logic.consumer
: AConsumer
functional interface that accepts a parameter of typeParam
and can call methods on theParam
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 SQLSELECT * FROM user WHERE id = 1 OR name = '老王'
OR Nesting Example:
// Regular WrapperQueryWrapper<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 WrapperLambdaQueryWrapper<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 SQLSELECT * 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 conditionand(Consumer<Param> consumer)and(boolean condition, Consumer<Param> consumer)
Parameter Description
consumer
: AConsumer
functional interface that accepts a parameter of typeParam
. You can call methods on thisParam
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 sameSELECT * 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 blocknested(Consumer<Param> consumer)nested(boolean condition, Consumer<Param> consumer)
Parameter Description
consumer
: AConsumer
functional interface that accepts a parameter of typeParam
. You can call methods on thisParam
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 sameSELECT * 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 fragmentapply(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 WrapperSELECT * FROM user WHERE id = 1
-- SQL generated by Lambda WrapperSELECT * FROM user WHERE date_format(dateColumn, '%Y-%m-%d') = '2008-08-08'
-- SQL generated using parameter placeholdersSELECT * 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 theapplySql
, 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 theapplySql
parameter is a valid SQL fragment and that theparams
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 querylast(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 WrapperSELECT * 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 subqueryexists(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 identicalSELECT * 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 subquerynotExists(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 identicalSELECT * 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 columnsselect(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
: APredicate
functional interface used to filter query columns. It accepts a parameter of typeTableFieldInfo
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 sameSELECT id, name, age FROM user
-- SQL generated using Predicate to filter fieldsSELECT 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 statementset(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 sameUPDATE user SET name = 'Old Man Li'
Example using conditional control:
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();updateWrapper.set(true, "name", "");
Generated SQL
-- SQL using conditional controlUPDATE 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 statementsetSql(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 valuesetIncrBy(SFunction<T, ?> column, Number val)
// Increment field by specified value when condition is metsetIncrBy(boolean condition, SFunction<T, ?> column, Number val)
Parameter Description
column
: AnSFunction
object representing the field to increment.val
: ANumber
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 SQLUPDATE 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 valuesetDecrBy(SFunction<T, ?> column, Number val)
// Decreases a field by the specified value when the condition is metsetDecrBy(boolean condition, SFunction<T, ?> column, Number val)
Parameter Description
column
: AnSFunction
object representing the field to decrement.val
: ANumber
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 SQLUPDATE 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 Wrapperlambda();
Examples
Get LambdaQueryWrapper from QueryWrapper:
QueryWrapper<User> queryWrapper = new QueryWrapper<>();LambdaQueryWrapper<User> lambdaQueryWrapper = queryWrapper.lambda();// Use Lambda expressions to build query conditionslambdaQueryWrapper.eq(User::getName, "张三");
Get LambdaUpdateWrapper from UpdateWrapper:
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();LambdaUpdateWrapper<User> lambdaUpdateWrapper = updateWrapper.lambda();// Use Lambda expressions to build update conditionslambdaUpdateWrapper.set(User::getName, "李四");
Explanation
- The
lambda
method returns aLambdaWrapper
object. The specific type depends on the type ofWrapper
that calls it. - Calling the
lambda
method on aQueryWrapper
returns aLambdaQueryWrapper
. - Calling the
lambda
method on anUpdateWrapper
returns aLambdaUpdateWrapper
. - 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
inwrapper
, special handling is required using theformatSqlMaybeWithParam
method
// QueryqueryWrapper.apply("type={0,typeHandler="+ MyTypeHandler.class.getCanonicalName()+ "}", "string to process");
// UpdateupdateWrapper.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
andor
. - 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
andapply
, 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 QueryWrapperQueryWrapper<User> queryWrapper = Wrappers.query();queryWrapper.eq("name", "张三");
// Create LambdaQueryWrapperLambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();lambdaQueryWrapper.eq(User::getName, "张三");
// Create UpdateWrapperUpdateWrapper<User> updateWrapper = Wrappers.update();updateWrapper.set("name", "李四");
// Create LambdaUpdateWrapperLambdaUpdateWrapper<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 requestpublic 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 least3.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 = 2updateRecord.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 callUpdateChainWrapper<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.