Where Builder

Hint

This article is generated by AI translation.

Use the where builder with query, update, and delete to produce the WHERE clause. This page covers:

Condition samples
Condition groups
Relations: and, or, not

Example
LambdaTemplate lambda = ...;
List<User> result = lambda.query(User.class)
                          .eq(User::getAge, 32)
                          .queryForList();

Generated SQL
SELECT * FROM users WHERE age = ?

Available methods:

Method	SQL	Description
eq	`=`	Column equals value
ne	`<>`	Column not equal to value
gt	`>`	Column greater than value
ge	`>=`	Column greater than or equal to value
lt	`<`	Column less than value
le	`<=`	Column less than or equal to value
like	`LIKE concat('%', ? ,'%')`	Column fuzzy match
likeLeft	`LIKE concat('%', ?)`	Column left-side fuzzy match
likeRight	`LIKE concat(?, '%')`	Column right-side fuzzy match
notLike	`NOT LIKE concat('%', ? ,'%')`	Column fuzzy match (negated)
notLikeLeft	`NOT LIKE concat('%', ?)`	Column left-side fuzzy match (negated)
notLikeRight	`NOT LIKE concat(?, '%')`	Column right-side fuzzy match (negated)
isNull	`IS NULL`	Column is null
isNotNull	`IS NOT NULL`	Column is not null
in	`IN (? ,?)`	Column value is in the set
notIn	`NOT IN (? ,?)`	Column value is not in the set
rangeBetween	`BETWEEN ? AND ?`	Range query using BETWEEN ... AND ...
rangeOpenOpen	`? < col AND col < ?`	Value is inside an open interval `(a,b)`
rangeClosedClosed	`? <= col AND col <= ?`	Value is inside a closed interval `[a,b]`
rangeOpenClosed	`? < col AND col <= ?`	Value is inside a left-open, right-closed interval `(a,b]`
rangeClosedOpen	`? <= col AND col < ?`	Value is inside a left-closed, right-open interval `[a,b)`
rangeNotBetween	`NOT BETWEEN ? AND ?`	Range query using NOT BETWEEN ... AND ...
rangeNotOpenOpen	`not (? < col AND col < ?)`	Value is outside the open interval `(a,b)`
rangeNotClosedClosed	`not (? <= col AND col <= ?)`	Value is outside the closed interval `[a,b]`
rangeNotOpenClosed	`not (? < col AND col <= ?)`	Value is outside the left-open, right-closed interval `(a,b]`
rangeNotClosedOpen	`not (? <= col AND col < ?)`	Value is outside the left-closed, right-open interval `[a,b)`
eqBySample, eqBySampleMap	See condition sample	Build conditions from a sample.
nested(...)	See condition group	Add parentheses to define a condition group.
and, and(...)	See and	Define an and relation between conditions or groups.
or, or(...)	See or	Define an or relation between conditions or groups.
not, not(...)	See not	Negate the following condition or group.

Sample

Non-null properties of the sample are joined with and and treated as one group, e.g. ('col1 = ?' and 'col2 = ?' and col3 = ?).

For multiple equality checks, repeated eq calls work but samples simplify it. Example requirement: set name, mail, uid as conditions only when values exist. The two approaches are equivalent:

Chaining eq conditions
LambdaTemplate lambda = ...;
User c = ...;

List<User> result = null;
result = lambda.query(User.class)
               // add condition when name is not blank
               .eq(StringUtils.isNotBlank(c.getName()), User::getName, c.getName())
               // add condition when email is not blank
               .eq(StringUtils.isNotBlank(c.getEmail()), User::getEmail, c.getEmail())
               // add condition when uid is not blank
               .eq(StringUtils.isNotBlank(c.getUID()), User::getUID, c.getUID())
               .queryForList();

Use a Bean sample
LambdaTemplate lambda = ...;
User u = ...;

List<User> result = null;
result = lambda.query(User.class)
               .eqBySample(u)
               .queryForList();

Use a Map sample
LambdaTemplate lambda = ...;
Map<String,Object> u = ...;

List<User> result = null;
result = lambda.query(User.class)
               .eqBySampleMap(u)
               .queryForList();

Usage notes

eqBySample and eqBySampleMap only use non-null sample properties:
- To match null, explicitly call isNull.
- Avoid primitive properties (byte, short, int, long, float, double, char) in samples.
If eqBySample is called multiple times, later samples override overlapping fields.

Nested

Use nested groups (parentheses) like this:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .nested(qc -> {
                   qc.eq(User::getName, "log1");
                   qc.and();
                   qc.eq(User::getEmail, "log1@xx.com");
               })
               .or()
               .nested(qc -> {
                   qc.eq(User::getName, "log2");
                   qc.and();
                   qc.eq(User::getEmail, "log2@xx.com");
               })
               .queryForList();

Generated SQL
SELECT * FROM users WHERE ( name = ? AND email = ? ) OR ( name = ? AND email = ? )

Tips

nested(...).or().nested(...) can be shortened to nested(...).or(...)
nested(...).and().nested(...) can be shortened to nested(...).nested(...) or nested(...).and(...)
nested(...).not().nested(...) can be shortened to nested(...).not(...)
Parentheses can nest multiple levels.

And

and is the default relation between expressions, so you can omit it:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .eq(User::getName, "123")
             //.and() // default relation, optional
               .eq(User::getAge, 12)
               .queryForList();

Generated SQL
SELECT * FROM users WHERE name = ? AND age = ?

To connect groups with and:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .nested(qc -> {
                   qc.eq(User::getId, 1)
                     .or()
                     .eq(User::getId, 2);
               }).and(qc -> {
                   qc.eq(User::getName, "user-1")
                     .or()
                     .eq(User::getName, "user-2");
               }).queryForList();

Generated SQL
SELECT * FROM users WHERE (id = ? OR id = ?) AND (name = ? OR name = ?);

Or

Use or between conditions:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .eq(User::getName, "123") // condition 1
               .or()                     // or relation
               .eq(User::getAge, 12)     // condition 2
               .queryForList();

Generated SQL
SELECT * FROM users WHERE name = ? OR age = ?

Connect groups with or:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .nested(qc -> {
                   qc.eq(User::getName, "user-1")
                     .eq(User::getStatus, 2);
               }).or(qc -> {
                   qc.eq(User::getName, "user-2")
                     .eq(User::getStatus, 2);
               }).queryForList();

Generated SQL
SELECT * FROM users WHERE ( name = ? AND status = ? ) OR ( name = ? AND status = ? )

Not

Negate a condition like this:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .not()   // invert the next equality check
               .eq(User::getName, "123")
               .queryForList();

Generated SQL
SELECT * FROM users WHERE not name = ?

Tip

Most conditions have a paired negation method; prefer those when available, e.g. eq vs ne, isNull vs isNotNull.

Negation is also useful for entire groups:

LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .not(qc -> {
                   qc.eq(User::getId, 1)
                     .or()
                     .eq(User::getId, 2);
               }).queryForList();

Generated SQL
SELECT * FROM users WHERE NOT (id = ? OR id = ?);

Sample​

Nested​

And​

Or​

Not​

Sample

Nested

And

Or

Not