0
# Query Building
1

2
MyBatis-Plus provides flexible query wrapper classes for building complex SQL queries with both string-based and lambda-style condition APIs, supporting all common SQL operations with type safety and fluent interfaces.
3

4
## Capabilities
5

6
### QueryWrapper
7

8
String-based query wrapper for building SQL conditions using column names.
9

10
```java { .api }
11
/**
12
 * Query wrapper for building SQL conditions with column names
13
 * @param <T> Entity type
14
 */
15
public class QueryWrapper<T> extends AbstractWrapper<T, String, QueryWrapper<T>>
16
    implements Query<QueryWrapper<T>, T, String> {
17
    
18
    // Equality conditions
19
    public QueryWrapper<T> eq(String column, Object val);
20
    public QueryWrapper<T> ne(String column, Object val);
21
    
22
    // Comparison conditions  
23
    public QueryWrapper<T> gt(String column, Object val);
24
    public QueryWrapper<T> ge(String column, Object val);
25
    public QueryWrapper<T> lt(String column, Object val);
26
    public QueryWrapper<T> le(String column, Object val);
27
    
28
    // Range conditions
29
    public QueryWrapper<T> between(String column, Object val1, Object val2);
30
    public QueryWrapper<T> notBetween(String column, Object val1, Object val2);
31
    
32
    // String matching conditions
33
    public QueryWrapper<T> like(String column, Object val);
34
    public QueryWrapper<T> notLike(String column, Object val);
35
    public QueryWrapper<T> likeLeft(String column, Object val);
36
    public QueryWrapper<T> likeRight(String column, Object val);
37
    
38
    // Null conditions
39
    public QueryWrapper<T> isNull(String column);
40
    public QueryWrapper<T> isNotNull(String column);
41
    
42
    // Collection conditions
43
    public QueryWrapper<T> in(String column, Collection<?> values);
44
    public QueryWrapper<T> notIn(String column, Collection<?> values);
45
    public QueryWrapper<T> in(String column, Object... values);
46
    public QueryWrapper<T> notIn(String column, Object... values);
47
    
48
    // Subquery conditions
49
    public QueryWrapper<T> inSql(String column, String inValue);
50
    public QueryWrapper<T> notInSql(String column, String notInValue);
51
    
52
    // Grouping and ordering
53
    public QueryWrapper<T> groupBy(String... columns);
54
    public QueryWrapper<T> orderByAsc(String... columns);
55
    public QueryWrapper<T> orderByDesc(String... columns);
56
    public QueryWrapper<T> orderBy(boolean condition, boolean isAsc, String... columns);
57
    
58
    // Having clause
59
    public QueryWrapper<T> having(String sqlHaving, Object... params);
60
    
61
    // Logical operators
62
    public QueryWrapper<T> or();
63
    public QueryWrapper<T> or(boolean condition);
64
    public QueryWrapper<T> and(Consumer<QueryWrapper<T>> function);
65
    public QueryWrapper<T> or(Consumer<QueryWrapper<T>> function);
66
    
67
    // Nested conditions
68
    public QueryWrapper<T> nested(Consumer<QueryWrapper<T>> function);
69
    
70
    // Custom SQL
71
    public QueryWrapper<T> apply(String applySql, Object... values);
72
    public QueryWrapper<T> last(String lastSql);
73
    
74
    // Existence conditions
75
    public QueryWrapper<T> exists(String existsSql);
76
    public QueryWrapper<T> notExists(String notExistsSql);
77
    
78
    // Lambda conversion
79
    public LambdaQueryWrapper<T> lambda();
80
    
81
    // Column selection
82
    public QueryWrapper<T> select(String... sqlSelect);
83
    public QueryWrapper<T> select(Class<T> entityClass, Predicate<TableFieldInfo> predicate);
84
}
85
```
86

87
### LambdaQueryWrapper
88

89
Lambda-style query wrapper providing type-safe property references using method references.
90

91
```java { .api }
92
/**
93
 * Lambda query wrapper for type-safe property references
94
 * @param <T> Entity type
95
 */
96
public class LambdaQueryWrapper<T> extends AbstractLambdaWrapper<T, LambdaQueryWrapper<T>>
97
    implements Query<LambdaQueryWrapper<T>, T, SFunction<T, ?>> {
98
    
99
    // Equality conditions
100
    public LambdaQueryWrapper<T> eq(SFunction<T, ?> fn, Object val);
101
    public LambdaQueryWrapper<T> ne(SFunction<T, ?> fn, Object val);
102
    
103
    // Comparison conditions
104
    public LambdaQueryWrapper<T> gt(SFunction<T, ?> fn, Object val);
105
    public LambdaQueryWrapper<T> ge(SFunction<T, ?> fn, Object val);
106
    public LambdaQueryWrapper<T> lt(SFunction<T, ?> fn, Object val);
107
    public LambdaQueryWrapper<T> le(SFunction<T, ?> fn, Object val);
108
    
109
    // Range conditions
110
    public LambdaQueryWrapper<T> between(SFunction<T, ?> fn, Object val1, Object val2);
111
    public LambdaQueryWrapper<T> notBetween(SFunction<T, ?> fn, Object val1, Object val2);
112
    
113
    // String matching conditions
114
    public LambdaQueryWrapper<T> like(SFunction<T, ?> fn, Object val);
115
    public LambdaQueryWrapper<T> notLike(SFunction<T, ?> fn, Object val);
116
    public LambdaQueryWrapper<T> likeLeft(SFunction<T, ?> fn, Object val);
117
    public LambdaQueryWrapper<T> likeRight(SFunction<T, ?> fn, Object val);
118
    
119
    // Null conditions
120
    public LambdaQueryWrapper<T> isNull(SFunction<T, ?> fn);
121
    public LambdaQueryWrapper<T> isNotNull(SFunction<T, ?> fn);
122
    
123
    // Collection conditions
124
    public LambdaQueryWrapper<T> in(SFunction<T, ?> fn, Collection<?> values);
125
    public LambdaQueryWrapper<T> notIn(SFunction<T, ?> fn, Collection<?> values);
126
    public LambdaQueryWrapper<T> in(SFunction<T, ?> fn, Object... values);
127
    public LambdaQueryWrapper<T> notIn(SFunction<T, ?> fn, Object... values);
128
    
129
    // Subquery conditions
130
    public LambdaQueryWrapper<T> inSql(SFunction<T, ?> fn, String inValue);
131
    public LambdaQueryWrapper<T> notInSql(SFunction<T, ?> fn, String notInValue);
132
    
133
    // Grouping and ordering
134
    public LambdaQueryWrapper<T> groupBy(SFunction<T, ?>... fns);
135
    public LambdaQueryWrapper<T> orderByAsc(SFunction<T, ?>... fns);
136
    public LambdaQueryWrapper<T> orderByDesc(SFunction<T, ?>... fns);
137
    public LambdaQueryWrapper<T> orderBy(boolean condition, boolean isAsc, SFunction<T, ?>... fns);
138
    
139
    // Having clause
140
    public LambdaQueryWrapper<T> having(String sqlHaving, Object... params);
141
    
142
    // Logical operators
143
    public LambdaQueryWrapper<T> or();
144
    public LambdaQueryWrapper<T> or(boolean condition);
145
    public LambdaQueryWrapper<T> and(Consumer<LambdaQueryWrapper<T>> function);
146
    public LambdaQueryWrapper<T> or(Consumer<LambdaQueryWrapper<T>> function);
147
    
148
    // Nested conditions
149
    public LambdaQueryWrapper<T> nested(Consumer<LambdaQueryWrapper<T>> function);
150
    
151
    // Custom SQL
152
    public LambdaQueryWrapper<T> apply(String applySql, Object... values);
153
    public LambdaQueryWrapper<T> last(String lastSql);
154
    
155
    // Existence conditions
156
    public LambdaQueryWrapper<T> exists(String existsSql);
157
    public LambdaQueryWrapper<T> notExists(String notExistsSql);
158
    
159
    // Column selection
160
    public LambdaQueryWrapper<T> select(SFunction<T, ?>... fns);
161
    public LambdaQueryWrapper<T> select(Class<T> entityClass, Predicate<TableFieldInfo> predicate);
162
}
163
```
164

165
### UpdateWrapper
166

167
String-based update wrapper for building update statements with WHERE conditions.
168

169
```java { .api }
170
/**
171
 * Update wrapper for building update statements with column names
172
 * @param <T> Entity type
173
 */
174
public class UpdateWrapper<T> extends AbstractWrapper<T, String, UpdateWrapper<T>>
175
    implements Update<UpdateWrapper<T>, String> {
176
    
177
    // Set operations
178
    public UpdateWrapper<T> set(String column, Object val);
179
    public UpdateWrapper<T> set(boolean condition, String column, Object val);
180
    public UpdateWrapper<T> setSql(String setSql);
181
    public UpdateWrapper<T> setSql(boolean condition, String setSql);
182
    
183
    // Lambda conversion
184
    public LambdaUpdateWrapper<T> lambda();
185
    
186
    // All QueryWrapper methods are also available for WHERE conditions
187
    // eq, ne, gt, ge, lt, le, between, like, in, etc.
188
}
189
```
190

191
### LambdaUpdateWrapper
192

193
Lambda-style update wrapper with type-safe property references.
194

195
```java { .api }
196
/**
197
 * Lambda update wrapper for type-safe property references
198
 * @param <T> Entity type
199
 */
200
public class LambdaUpdateWrapper<T> extends AbstractLambdaWrapper<T, LambdaUpdateWrapper<T>>
201
    implements Update<LambdaUpdateWrapper<T>, SFunction<T, ?>> {
202
    
203
    // Set operations
204
    public LambdaUpdateWrapper<T> set(SFunction<T, ?> fn, Object val);
205
    public LambdaUpdateWrapper<T> set(boolean condition, SFunction<T, ?> fn, Object val);
206
    public LambdaUpdateWrapper<T> setSql(String setSql);
207
    public LambdaUpdateWrapper<T> setSql(boolean condition, String setSql);
208
    
209
    // All LambdaQueryWrapper methods are also available for WHERE conditions
210
    // eq, ne, gt, ge, lt, le, between, like, in, etc.
211
}
212
```
213

214
### Wrappers Utility
215

216
Factory class for creating wrapper instances.
217

218
```java { .api }
219
/**
220
 * Utility class for creating wrapper instances
221
 */
222
public final class Wrappers {
223
    
224
    /**
225
     * Create an empty QueryWrapper
226
     */
227
    public static <T> QueryWrapper<T> query();
228
    
229
    /**
230
     * Create a QueryWrapper with entity
231
     */
232
    public static <T> QueryWrapper<T> query(T entity);
233
    
234
    /**
235
     * Create an empty LambdaQueryWrapper
236
     */
237
    public static <T> LambdaQueryWrapper<T> lambdaQuery();
238
    
239
    /**
240
     * Create a LambdaQueryWrapper with entity
241
     */
242
    public static <T> LambdaQueryWrapper<T> lambdaQuery(T entity);
243
    
244
    /**
245
     * Create an empty UpdateWrapper
246
     */
247
    public static <T> UpdateWrapper<T> update();
248
    
249
    /**
250
     * Create an UpdateWrapper with entity
251
     */
252
    public static <T> UpdateWrapper<T> update(T entity);
253
    
254
    /**
255
     * Create an empty LambdaUpdateWrapper
256
     */
257
    public static <T> LambdaUpdateWrapper<T> lambdaUpdate();
258
    
259
    /**
260
     * Create a LambdaUpdateWrapper with entity
261
     */
262
    public static <T> LambdaUpdateWrapper<T> lambdaUpdate(T entity);
263
    
264
    /**
265
     * Create an empty wrapper (no conditions)
266
     */
267
    public static <T> QueryWrapper<T> emptyWrapper();
268
}
269
```
270

271
### ChainWrappers Utility (MyBatis-Plus 3.5.7+)
272

273
Factory class for creating chain wrapper instances, providing fluent chainable query and update operations.
274

275
```java { .api }
276
/**
277
 * Utility class for creating chain wrapper instances
278
 */
279
public final class ChainWrappers {
280
    
281
    /**
282
     * Create a LambdaQueryChainWrapper for fluent querying
283
     * @param mapper BaseMapper instance
284
     * @return LambdaQueryChainWrapper instance
285
     */
286
    public static <T> LambdaQueryChainWrapper<T> lambdaQueryChain(BaseMapper<T> mapper);
287
    
288
    /**
289
     * Create a QueryChainWrapper for fluent querying
290
     * @param mapper BaseMapper instance
291
     * @return QueryChainWrapper instance
292
     */
293
    public static <T> QueryChainWrapper<T> queryChain(BaseMapper<T> mapper);
294
    
295
    /**
296
     * Create a LambdaUpdateChainWrapper for fluent updating
297
     * @param mapper BaseMapper instance
298
     * @return LambdaUpdateChainWrapper instance
299
     */
300
    public static <T> LambdaUpdateChainWrapper<T> lambdaUpdateChain(BaseMapper<T> mapper);
301
    
302
    /**
303
     * Create an UpdateChainWrapper for fluent updating
304
     * @param mapper BaseMapper instance
305
     * @return UpdateChainWrapper instance
306
     */
307
    public static <T> UpdateChainWrapper<T> updateChain(BaseMapper<T> mapper);
308
}
309
```
310

311
## Usage Examples
312

313
**Basic Query Conditions:**
314

315
```java
316
// String-based QueryWrapper
317
QueryWrapper<User> wrapper = new QueryWrapper<>();
318
wrapper.eq("name", "John")
319
       .gt("age", 18)
320
       .like("email", "@gmail.com")
321
       .isNotNull("phone");
322

323
List<User> users = userMapper.selectList(wrapper);
324

325
// Lambda-based LambdaQueryWrapper (type-safe)
326
LambdaQueryWrapper<User> lambdaWrapper = new LambdaQueryWrapper<>();
327
lambdaWrapper.eq(User::getName, "John")
328
             .gt(User::getAge, 18)
329
             .like(User::getEmail, "@gmail.com")
330
             .isNotNull(User::getPhone);
331

332
List<User> users = userMapper.selectList(lambdaWrapper);
333
```
334

335
**Complex Logical Conditions:**
336

337
```java
338
// AND and OR combinations
339
QueryWrapper<User> wrapper = new QueryWrapper<>();
340
wrapper.eq("status", 1)
341
       .and(w -> w.eq("type", "VIP").or().gt("score", 1000))
342
       .or(w -> w.eq("level", "ADMIN"));
343

344
// Generates: WHERE status = 1 AND (type = 'VIP' OR score > 1000) OR level = 'ADMIN'
345

346
// Nested conditions
347
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
348
wrapper.eq(User::getStatus, 1)
349
       .nested(w -> w.eq(User::getType, "VIP")
350
                     .or()
351
                     .gt(User::getScore, 1000));
352

353
// Generates: WHERE status = 1 AND (type = 'VIP' OR score > 1000)
354
```
355

356
**Range and Collection Conditions:**
357

358
```java
359
// Range conditions
360
wrapper.between("age", 18, 65)
361
       .notBetween("score", 0, 60);
362

363
// Collection conditions
364
List<String> cities = Arrays.asList("Beijing", "Shanghai", "Guangzhou");
365
wrapper.in("city", cities)
366
       .notIn("status", 0, -1);
367

368
// Subquery conditions
369
wrapper.inSql("dept_id", "SELECT id FROM department WHERE name = 'IT'")
370
       .notInSql("role_id", "SELECT id FROM role WHERE name = 'GUEST'");
371
```
372

373
**String Matching:**
374

375
```java
376
// Different LIKE patterns
377
wrapper.like("name", "John")           // name LIKE '%John%'
378
       .likeLeft("email", "gmail.com") // email LIKE '%gmail.com'
379
       .likeRight("phone", "138")      // phone LIKE '138%'
380
       .notLike("address", "temp");    // address NOT LIKE '%temp%'
381
```
382

383
**Ordering and Grouping:**
384

385
```java
386
// Ordering
387
wrapper.orderByAsc("create_time")
388
       .orderByDesc("update_time", "id");
389

390
// Dynamic ordering
391
boolean sortByAge = true;
392
wrapper.orderBy(sortByAge, true, "age"); // Conditional ascending sort
393

394
// Grouping with HAVING
395
wrapper.select("dept_id", "COUNT(*) as count")
396
       .groupBy("dept_id")
397
       .having("COUNT(*) > 5");
398
```
399

400
**Column Selection:**
401

402
```java
403
// Select specific columns
404
wrapper.select("id", "name", "email");
405

406
// Lambda column selection
407
lambdaWrapper.select(User::getId, User::getName, User::getEmail);
408

409
// Conditional column selection
410
wrapper.select(User.class, info -> !info.getColumn().equals("password"));
411
```
412

413
**Update Operations:**
414

415
```java
416
// UpdateWrapper for setting values
417
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
418
updateWrapper.set("status", 1)
419
             .set("update_time", LocalDateTime.now())
420
             .eq("id", 123);
421

422
userMapper.update(null, updateWrapper);
423

424
// LambdaUpdateWrapper (type-safe)
425
LambdaUpdateWrapper<User> lambdaUpdate = new LambdaUpdateWrapper<>();
426
lambdaUpdate.set(User::getStatus, 1)
427
            .set(User::getUpdateTime, LocalDateTime.now())
428
            .eq(User::getId, 123);
429

430
userMapper.update(null, lambdaUpdate);
431

432
// Update with entity and conditions
433
User updateUser = new User();
434
updateUser.setStatus(1);
435

436
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
437
wrapper.eq("dept_id", 10).gt("age", 25);
438

439
userMapper.update(updateUser, wrapper);
440
```
441

442
**Using Wrappers Utility:**
443

444
```java
445
// Factory methods for cleaner code
446
import static com.baomidou.mybatisplus.core.conditions.Wrappers.*;
447

448
// Query wrappers
449
List<User> users = userMapper.selectList(
450
    query(User.class).eq("status", 1).gt("age", 18)
451
);
452

453
List<User> activeUsers = userMapper.selectList(
454
    lambdaQuery(User.class).eq(User::getStatus, 1)
455
                           .gt(User::getAge, 18)
456
);
457

458
// Update wrappers
459
userMapper.update(null, 
460
    update(User.class).set("status", 0).eq("id", 123)
461
);
462

463
userMapper.update(null,
464
    lambdaUpdate(User.class).set(User::getStatus, 0)
465
                            .eq(User::getId, 123)
466
);
467
```
468

469
**Custom SQL and Advanced Features:**
470

471
```java
472
// Custom SQL fragments
473
wrapper.apply("DATE_FORMAT(create_time, '%Y-%m') = {0}", "2023-11");
474

475
// Raw SQL at the end (use with caution)
476
wrapper.eq("status", 1).last("LIMIT 10");
477

478
// Existence conditions
479
wrapper.exists("SELECT 1 FROM user_role WHERE user_id = user.id AND role_id = 1")
480
       .notExists("SELECT 1 FROM user_ban WHERE user_id = user.id");
481

482
// Conditional building
483
String name = getNameParam(); // Could be null
484
Integer minAge = getMinAgeParam(); // Could be null
485

486
wrapper.eq(StringUtils.isNotBlank(name), "name", name)
487
       .ge(minAge != null, "age", minAge);
488
```
489

490
**Performance Tips:**
491

492
- Use `LambdaQueryWrapper` for type safety and refactoring support
493
- Prefer method chaining for readability
494
- Use conditional building to avoid null parameter issues
495
- Select only needed columns with `select()` methods
496
- Use `last()` sparingly and never with user input (SQL injection risk)
497
- Consider database indexes for frequently used query conditions