0
# DML Operations
1

2
QueryDSL SQL provides type-safe Data Manipulation Language operations for INSERT, UPDATE, DELETE, and MERGE operations with support for batch processing, automatic key generation, and object mapping.
3

4
## Capabilities
5

6
### Insert Operations
7

8
Type-safe INSERT operations with support for value binding, column selection, and automatic key generation.
9

10
```java { .api }
11
/**
12
 * Creates an INSERT clause for the specified table
13
 * @param path Table to insert into
14
 * @return SQLInsertClause for building INSERT statement
15
 */
16
public SQLInsertClause insert(RelationalPath<?> path);
17

18
/**
19
 * Sets values for specific columns
20
 * @param path Column path
21
 * @param value Value to insert
22
 * @return Insert clause for method chaining
23
 */
24
public <T> SQLInsertClause set(Path<T> path, T value);
25

26
/**
27
 * Sets multiple column values from a map
28
 * @param values Map of column paths to values
29
 * @return Insert clause for method chaining
30
 */
31
public SQLInsertClause set(Map<Path<?>, ?> values);
32

33
/**
34
 * Executes the insert and returns number of affected rows
35
 * @return Number of inserted rows
36
 */
37
public long execute();
38

39
/**
40
 * Executes insert and returns generated key
41
 * @param path Path to the key column
42
 * @return Generated key value
43
 */
44
public <T> T executeWithKey(Path<T> path);
45
```
46

47
**Usage Examples:**
48

49
```java
50
// Simple insert with individual values
51
long rowsInserted = queryFactory
52
    .insert(qUser)
53
    .set(qUser.name, "John Doe")
54
    .set(qUser.email, "john@example.com")
55
    .set(qUser.age, 30)
56
    .set(qUser.active, true)
57
    .execute();
58

59
// Insert with generated key
60
Long userId = queryFactory
61
    .insert(qUser)
62
    .set(qUser.name, "Jane Smith")
63
    .set(qUser.email, "jane@example.com")
64
    .executeWithKey(qUser.id);
65

66
// Insert from map
67
Map<Path<?>, Object> values = new HashMap<>();
68
values.put(qUser.name, "Bob Johnson");
69
values.put(qUser.email, "bob@example.com");
70
values.put(qUser.age, 25);
71

72
queryFactory
73
    .insert(qUser)
74
    .set(values)
75
    .execute();
76
```
77

78
### Batch Insert Operations
79

80
Efficient batch processing for inserting multiple records in a single database operation.
81

82
```java { .api }
83
/**
84
 * Creates a batch insert operation
85
 * @param path Table to insert into
86
 * @return SQLInsertBatch for batch operations
87
 */
88
public SQLInsertBatch insert(RelationalPath<?> path);
89

90
/**
91
 * Adds a batch entry with specified values
92
 * @param values Map of column paths to values for this batch entry
93
 * @return Batch insert for method chaining
94
 */
95
public SQLInsertBatch addBatch(Map<Path<?>, ?> values);
96

97
/**
98
 * Executes all batched inserts
99
 * @return Array of affected row counts for each batch
100
 */
101
public long[] execute();
102
```
103

104
**Usage Examples:**
105

106
```java
107
// Batch insert multiple users
108
SQLInsertBatch batch = queryFactory.insert(qUser);
109

110
for (UserDto user : users) {
111
    Map<Path<?>, Object> values = new HashMap<>();
112
    values.put(qUser.name, user.getName());
113
    values.put(qUser.email, user.getEmail());
114
    values.put(qUser.age, user.getAge());
115
    batch.addBatch(values);
116
}
117

118
long[] results = batch.execute();
119
```
120

121
### Update Operations
122

123
Type-safe UPDATE operations with WHERE conditions and support for conditional updates.
124

125
```java { .api }
126
/**
127
 * Creates an UPDATE clause for the specified table
128
 * @param path Table to update
129
 * @return SQLUpdateClause for building UPDATE statement
130
 */
131
public SQLUpdateClause update(RelationalPath<?> path);
132

133
/**
134
 * Sets a column to a new value
135
 * @param path Column path
136
 * @param value New value
137
 * @return Update clause for method chaining
138
 */
139
public <T> SQLUpdateClause set(Path<T> path, T value);
140

141
/**
142
 * Sets a column using an expression
143
 * @param path Column path
144
 * @param expression Expression to evaluate for new value
145
 * @return Update clause for method chaining
146
 */
147
public <T> SQLUpdateClause set(Path<T> path, Expression<? extends T> expression);
148

149
/**
150
 * Adds WHERE conditions to limit which rows are updated
151
 * @param conditions Boolean expressions for filtering
152
 * @return Update clause for method chaining
153
 */
154
public SQLUpdateClause where(Predicate... conditions);
155

156
/**
157
 * Executes the update and returns number of affected rows
158
 * @return Number of updated rows
159
 */
160
public long execute();
161
```
162

163
**Usage Examples:**
164

165
```java
166
// Simple update with WHERE condition
167
long updatedRows = queryFactory
168
    .update(qUser)
169
    .set(qUser.lastLogin, LocalDateTime.now())
170
    .where(qUser.id.eq(userId))
171
    .execute();
172

173
// Update with expression
174
queryFactory
175
    .update(qProduct)
176
    .set(qProduct.price, qProduct.price.multiply(1.1))
177
    .where(qProduct.category.eq("electronics"))
178
    .execute();
179

180
// Conditional update
181
queryFactory
182
    .update(qUser)
183
    .set(qUser.status, "INACTIVE")
184
    .set(qUser.deactivatedAt, LocalDateTime.now())
185
    .where(qUser.lastLogin.lt(LocalDateTime.now().minusDays(90))
186
        .and(qUser.status.eq("ACTIVE")))
187
    .execute();
188
```
189

190
### Batch Update Operations
191

192
Batch processing for multiple UPDATE operations with different conditions and values.
193

194
```java { .api }
195
/**
196
 * Creates a batch update operation
197
 * @param path Table to update
198
 * @return SQLUpdateBatch for batch operations
199
 */
200
public SQLUpdateBatch update(RelationalPath<?> path);
201

202
/**
203
 * Adds a batch entry with specified values and conditions
204
 * @param values Map of column paths to new values
205
 * @param conditions WHERE conditions for this batch entry
206
 * @return Batch update for method chaining
207
 */
208
public SQLUpdateBatch addBatch(Map<Path<?>, ?> values, Predicate... conditions);
209

210
/**
211
 * Executes all batched updates
212
 * @return Array of affected row counts for each batch
213
 */
214
public long[] execute();
215
```
216

217
### Delete Operations
218

219
Type-safe DELETE operations with WHERE conditions for selective row removal.
220

221
```java { .api }
222
/**
223
 * Creates a DELETE clause for the specified table
224
 * @param path Table to delete from
225
 * @return SQLDeleteClause for building DELETE statement
226
 */
227
public SQLDeleteClause delete(RelationalPath<?> path);
228

229
/**
230
 * Adds WHERE conditions to limit which rows are deleted
231
 * @param conditions Boolean expressions for filtering
232
 * @return Delete clause for method chaining
233
 */
234
public SQLDeleteClause where(Predicate... conditions);
235

236
/**
237
 * Executes the delete and returns number of affected rows
238
 * @return Number of deleted rows
239
 */
240
public long execute();
241
```
242

243
**Usage Examples:**
244

245
```java
246
// Delete with simple condition
247
long deletedRows = queryFactory
248
    .delete(qUser)
249
    .where(qUser.active.isFalse()
250
        .and(qUser.lastLogin.lt(LocalDateTime.now().minusYears(1))))
251
    .execute();
252

253
// Delete with subquery
254
queryFactory
255
    .delete(qOrder)
256
    .where(qOrder.userId.in(
257
        queryFactory.select(qUser.id)
258
            .from(qUser)
259
            .where(qUser.status.eq("DELETED"))
260
    ))
261
    .execute();
262
```
263

264
### Merge Operations
265

266
MERGE (UPSERT) operations for inserting new records or updating existing ones based on key matching.
267

268
```java { .api }
269
/**
270
 * Creates a MERGE clause for the specified table
271
 * @param path Table to merge into
272
 * @return SQLMergeClause for building MERGE statement
273
 */
274
public SQLMergeClause merge(RelationalPath<?> path);
275

276
/**
277
 * Specifies the key columns for matching existing records
278
 * @param paths Key column paths
279
 * @return Merge clause for method chaining
280
 */
281
public SQLMergeClause key(Path<?>... paths);
282

283
/**
284
 * Sets values for the merge operation
285
 * @param path Column path
286
 * @param value Value to insert or update
287
 * @return Merge clause for method chaining
288
 */
289
public <T> SQLMergeClause set(Path<T> path, T value);
290

291
/**
292
 * Executes the merge and returns number of affected rows
293
 * @return Number of merged rows
294
 */
295
public long execute();
296
```
297

298
**Usage Examples:**
299

300
```java
301
// MERGE operation with key matching
302
long mergedRows = queryFactory
303
    .merge(qUser)
304
    .key(qUser.email) // Use email as the key for matching
305
    .set(qUser.name, "John Updated")
306
    .set(qUser.age, 31)
307
    .set(qUser.lastModified, LocalDateTime.now())
308
    .execute();
309

310
// MERGE with composite key
311
queryFactory
312
    .merge(qUserRole)
313
    .key(qUserRole.userId, qUserRole.roleId)
314
    .set(qUserRole.assignedAt, LocalDateTime.now())
315
    .set(qUserRole.assignedBy, currentUserId)
316
    .execute();
317
```
318

319
### Object Mapping
320

321
Automatic mapping between Java objects and database columns using various mapping strategies.
322

323
```java { .api }
324
/**
325
 * Interface for mapping objects to column/value pairs
326
 * @param <T> Type of object to map
327
 */
328
public interface Mapper<T> {
329
    /**
330
     * Creates a map of column paths to values from an object
331
     * @param relationalPath Table path containing column definitions
332
     * @param object Object to map
333
     * @return Map of column paths to values
334
     */
335
    Map<Path<?>, Object> createMap(RelationalPath<?> relationalPath, T object);
336
}
337

338
/**
339
 * Default mapper using reflection and naming conventions
340
 */
341
public class DefaultMapper<T> implements Mapper<T>;
342

343
/**
344
 * Bean mapper using getter/setter methods
345
 */
346
public class BeanMapper<T> implements Mapper<T>;
347

348
/**
349
 * Annotation-based mapper using @Column annotations
350
 */
351
public class AnnotationMapper<T> implements Mapper<T>;
352
```
353

354
**Usage Examples:**
355

356
```java
357
// Using BeanMapper for automatic object mapping
358
BeanMapper<User> userMapper = new BeanMapper<>(User.class);
359

360
User user = new User();
361
user.setName("Alice Smith");
362
user.setEmail("alice@example.com");
363
user.setAge(28);
364

365
// Insert using object mapping
366
Map<Path<?>, Object> values = userMapper.createMap(qUser, user);
367
queryFactory
368
    .insert(qUser)
369
    .set(values)
370
    .execute();
371

372
// Update using object mapping
373
user.setAge(29);
374
user.setLastModified(LocalDateTime.now());
375

376
Map<Path<?>, Object> updateValues = userMapper.createMap(qUser, user);
377
queryFactory
378
    .update(qUser)
379
    .set(updateValues)
380
    .where(qUser.id.eq(user.getId()))
381
    .execute();
382
```
383

384
### Result Handling
385

386
Utilities for handling DML operation results and managing database state.
387

388
```java { .api }
389
/**
390
 * Options for controlling prepared statement behavior
391
 */
392
public class StatementOptions {
393
    public StatementOptions fetchSize(int fetchSize);
394
    public StatementOptions queryTimeout(int seconds);
395
    public StatementOptions maxRows(int maxRows);
396
}
397

398
/**
399
 * Contains SQL statement and parameter bindings for logging/debugging
400
 */
401
public class SQLBindings {
402
    /**
403
     * Gets the SQL statement with placeholders
404
     * @return SQL string
405
     */
406
    public String getSQL();
407
    
408
    /**
409
     * Gets the parameter bindings in order
410
     * @return List of parameter values
411
     */
412
    public List<Object> getBindings();
413
}
414
```
415

416
**Usage Examples:**
417

418
```java
419
// Configure statement options
420
StatementOptions options = new StatementOptions()
421
    .fetchSize(1000)
422
    .queryTimeout(30);
423

424
// Apply options to query
425
queryFactory
426
    .selectFrom(qUser)
427
    .configure(options)
428
    .fetch();
429

430
// Get SQL bindings for logging
431
SQLDeleteClause deleteClause = queryFactory
432
    .delete(qUser)
433
    .where(qUser.active.isFalse());
434

435
SQLBindings bindings = deleteClause.getSQL();
436
logger.info("Executing SQL: {} with bindings: {}", 
437
    bindings.getSQL(), bindings.getBindings());
438

439
long deleted = deleteClause.execute();
440
```