0
# Transaction Integration
1

2
Integration between jOOQ and Spring's transaction management system, allowing jOOQ operations to participate seamlessly in Spring-managed transactions.
3

4
## Capabilities
5

6
### SpringTransactionProvider
7

8
Allows Spring transactions to be used with jOOQ by implementing jOOQ's TransactionProvider interface.
9

10
```java { .api }
11
/**
12
 * TransactionProvider implementation that integrates jOOQ with Spring's transaction management
13
 * Automatically configured when PlatformTransactionManager is available
14
 */
15
public class SpringTransactionProvider implements TransactionProvider {
16
    
17
    /**
18
     * Create a new SpringTransactionProvider with the given transaction manager
19
     * @param transactionManager Spring's platform transaction manager
20
     */
21
    public SpringTransactionProvider(PlatformTransactionManager transactionManager);
22
    
23
    /**
24
     * Begin a new transaction context
25
     * Creates a nested transaction with PROPAGATION_NESTED
26
     * @param context the jOOQ transaction context
27
     */
28
    @Override
29
    public void begin(TransactionContext context);
30
    
31
    /**
32
     * Commit the current transaction
33
     * @param ctx the jOOQ transaction context
34
     */
35
    @Override
36
    public void commit(TransactionContext ctx);
37
    
38
    /**
39
     * Rollback the current transaction
40
     * @param ctx the jOOQ transaction context  
41
     */
42
    @Override
43
    public void rollback(TransactionContext ctx);
44
}
45
```
46

47
### SpringTransaction
48

49
Adapter that wraps Spring's TransactionStatus for use with jOOQ's Transaction interface.
50

51
```java { .api }
52
/**
53
 * Adapts a Spring TransactionStatus for jOOQ Transaction interface
54
 * Used internally by SpringTransactionProvider
55
 */
56
class SpringTransaction implements Transaction {
57
    
58
    /**
59
     * Create a new SpringTransaction wrapping the given TransactionStatus
60
     * @param transactionStatus Spring's transaction status
61
     */
62
    SpringTransaction(TransactionStatus transactionStatus);
63
    
64
    /**
65
     * Get the wrapped Spring TransactionStatus
66
     * @return the underlying TransactionStatus
67
     */
68
    TransactionStatus getTxStatus();
69
}
70
```
71

72
**Usage Examples:**
73

74
### Basic Transactional Service
75

76
```java
77
@Service
78
@Transactional
79
public class OrderService {
80
    
81
    @Autowired
82
    private DSLContext dsl;
83
    
84
    public void processOrder(Order order) {
85
        // Insert order - participates in Spring transaction
86
        dsl.insertInto(ORDER)
87
           .set(ORDER.ID, order.getId())
88
           .set(ORDER.CUSTOMER_ID, order.getCustomerId())
89
           .set(ORDER.TOTAL, order.getTotal())
90
           .execute();
91
        
92
        // Insert order items - same transaction
93
        for (OrderItem item : order.getItems()) {
94
            dsl.insertInto(ORDER_ITEM)
95
               .set(ORDER_ITEM.ORDER_ID, order.getId())
96
               .set(ORDER_ITEM.PRODUCT_ID, item.getProductId())
97
               .set(ORDER_ITEM.QUANTITY, item.getQuantity())
98
               .execute();
99
        }
100
        
101
        // Update inventory - still same transaction
102
        updateInventory(order.getItems());
103
    }
104
    
105
    private void updateInventory(List<OrderItem> items) {
106
        for (OrderItem item : items) {
107
            dsl.update(INVENTORY)
108
               .set(INVENTORY.QUANTITY, INVENTORY.QUANTITY.minus(item.getQuantity()))
109
               .where(INVENTORY.PRODUCT_ID.eq(item.getProductId()))
110
               .execute();
111
        }
112
    }
113
}
114
```
115

116
### Manual Transaction Management
117

118
```java
119
@Service
120
public class BatchProcessingService {
121
    
122
    @Autowired
123
    private DSLContext dsl;
124
    
125
    @Autowired 
126
    private PlatformTransactionManager transactionManager;
127
    
128
    public void processBatch(List<Data> dataList) {
129
        TransactionDefinition def = new DefaultTransactionDefinition();
130
        TransactionStatus status = transactionManager.getTransaction(def);
131
        
132
        try {
133
            for (Data data : dataList) {
134
                // jOOQ operations automatically participate in the transaction
135
                dsl.insertInto(BATCH_DATA)
136
                   .set(BATCH_DATA.VALUE, data.getValue())
137
                   .set(BATCH_DATA.PROCESSED_AT, LocalDateTime.now())
138
                   .execute();
139
            }
140
            
141
            transactionManager.commit(status);
142
        } catch (Exception e) {
143
            transactionManager.rollback(status);
144
            throw e;
145
        }
146
    }
147
}
148
```
149

150
### Transaction Propagation
151

152
```java
153
@Service
154
public class UserService {
155
    
156
    @Autowired
157
    private DSLContext dsl;
158
    
159
    @Autowired
160
    private AuditService auditService;
161
    
162
    @Transactional
163
    public void createUser(User user) {
164
        // Main transaction
165
        dsl.insertInto(USER)
166
           .set(USER.NAME, user.getName())
167
           .set(USER.EMAIL, user.getEmail())
168
           .execute();
169
        
170
        // Audit in same transaction by default
171
        auditService.logUserCreation(user.getId());
172
    }
173
    
174
    @Transactional(propagation = Propagation.REQUIRES_NEW)
175
    public void createUserWithSeparateAudit(User user) {
176
        // Main transaction
177
        dsl.insertInto(USER)
178
           .set(USER.NAME, user.getName()) 
179
           .set(USER.EMAIL, user.getEmail())
180
           .execute();
181
        
182
        // Audit in separate transaction - will commit even if main fails
183
        auditService.logUserCreationSeparate(user.getId());
184
    }
185
}
186

187
@Service
188
public class AuditService {
189
    
190
    @Autowired
191
    private DSLContext dsl;
192
    
193
    @Transactional(propagation = Propagation.REQUIRED)
194
    public void logUserCreation(Long userId) {
195
        // Participates in existing transaction
196
        dsl.insertInto(AUDIT_LOG)
197
           .set(AUDIT_LOG.USER_ID, userId)
198
           .set(AUDIT_LOG.ACTION, "CREATE")
199
           .execute();
200
    }
201
    
202
    @Transactional(propagation = Propagation.REQUIRES_NEW)
203
    public void logUserCreationSeparate(Long userId) {
204
        // Always runs in new transaction
205
        dsl.insertInto(AUDIT_LOG)
206
           .set(AUDIT_LOG.USER_ID, userId)
207
           .set(AUDIT_LOG.ACTION, "CREATE")
208
           .execute();
209
    }
210
}
211
```
212

213
### Exception Handling with Transactions
214

215
```java
216
@Service
217
public class PaymentService {
218
    
219
    @Autowired
220
    private DSLContext dsl;
221
    
222
    @Transactional(rollbackFor = Exception.class)
223
    public void processPayment(Payment payment) throws PaymentException {
224
        try {
225
            // Validate payment
226
            validatePayment(payment);
227
            
228
            // Record payment attempt
229
            dsl.insertInto(PAYMENT_ATTEMPT)
230
               .set(PAYMENT_ATTEMPT.AMOUNT, payment.getAmount())
231
               .set(PAYMENT_ATTEMPT.STATUS, "PROCESSING")
232
               .execute();
233
            
234
            // Process with external service
235
            processWithGateway(payment);
236
            
237
            // Update status on success
238
            dsl.update(PAYMENT_ATTEMPT)
239
               .set(PAYMENT_ATTEMPT.STATUS, "SUCCESS")
240
               .where(PAYMENT_ATTEMPT.ID.eq(payment.getId()))
241
               .execute();
242
               
243
        } catch (PaymentException e) {
244
            // Transaction will be rolled back automatically
245
            // Both INSERT and UPDATE will be undone
246
            throw e;
247
        }
248
    }
249
    
250
    @Transactional(noRollbackFor = BusinessException.class)
251
    public void processPaymentWithBusinessExceptionHandling(Payment payment) {
252
        // Business exceptions won't trigger rollback
253
        // Only runtime exceptions and errors will
254
    }
255
}
256
```
257

258
## Key Integration Points
259

260
### Automatic Transaction Participation
261

262
- jOOQ operations automatically participate in Spring-managed transactions
263
- No additional configuration needed - works out of the box
264
- Supports all Spring transaction propagation levels
265
- Works with `@Transactional` annotations and programmatic transaction management
266

267
### Connection Management
268

269
- Uses `TransactionAwareDataSourceProxy` to ensure connection reuse within transactions
270
- Properly releases connections when transactions complete
271
- Handles connection pooling integration seamlessly
272

273
### Nested Transaction Support
274

275
- Supports Spring's nested transaction capability
276
- Uses `PROPAGATION_NESTED` by default for jOOQ-initiated transactions
277
- Integrates with savepoint management where supported by the database
278

279
### Rollback Behavior
280

281
- Automatic rollback on runtime exceptions
282
- Configurable rollback rules via `@Transactional` attributes
283
- Proper cleanup of jOOQ resources on rollback
284
- Maintains consistency between Spring and jOOQ transaction states