0
# SQL Database Access
1

2
Database operations with enhanced result sets, fluent query building, and comprehensive JDBC integration for seamless database interactions.
3

4
## Capabilities
5

6
### Main SQL Class
7

8
Primary class for database operations providing connection management and query execution.
9

10
```groovy { .api }
11
/**
12
 * Main class for database operations
13
 */
14
class Sql {
15
    /** Create Sql instance from DataSource */
16
    static Sql newInstance(DataSource dataSource)
17
    
18
    /** Create Sql instance with connection parameters */
19
    static Sql newInstance(String url, String user, String password, String driver)
20
    
21
    /** Create Sql instance with Properties */
22
    static Sql newInstance(String url, Properties properties, String driver)
23
    
24
    /** Create Sql instance from existing Connection */
25
    static Sql newInstance(Connection connection)
26
    
27
    /** Execute query and process each row with closure */
28
    void eachRow(String sql, Closure closure)
29
    
30
    /** Execute query with parameters and process each row */
31
    void eachRow(String sql, List<Object> params, Closure closure)
32
    
33
    /** Execute query with named parameters and process each row */
34
    void eachRow(String sql, Map<String, Object> params, Closure closure)
35
    
36
    /** Execute query and return list of GroovyRowResult */
37
    List<GroovyRowResult> rows(String sql)
38
    
39
    /** Execute query with parameters and return rows */
40
    List<GroovyRowResult> rows(String sql, List<Object> params)
41
    
42
    /** Execute query with named parameters and return rows */
43
    List<GroovyRowResult> rows(String sql, Map<String, Object> params)
44
    
45
    /** Execute query and return first row */
46
    GroovyRowResult firstRow(String sql)
47
    
48
    /** Execute query with parameters and return first row */
49
    GroovyRowResult firstRow(String sql, List<Object> params)
50
    
51
    /** Execute update/insert/delete statement */
52
    int executeUpdate(String sql)
53
    
54
    /** Execute update with parameters */
55
    int executeUpdate(String sql, List<Object> params)
56
    
57
    /** Execute any SQL statement */
58
    boolean execute(String sql)
59
    
60
    /** Execute stored procedure */
61
    void call(String sql, Closure closure)
62
    
63
    /** Execute in transaction */
64
    Object withTransaction(Closure closure)
65
    
66
    /** Execute in batch */
67
    int[] withBatch(String sql, Closure closure)
68
    
69
    /** Get underlying Connection */
70
    Connection getConnection()
71
    
72
    /** Close the connection */
73
    void close()
74
}
75
```
76

77
**Usage Examples:**
78

79
```groovy
80
import groovy.sql.Sql
81

82
// Connect to database
83
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", 
84
                         "user", "password", "com.mysql.jdbc.Driver")
85

86
// Query with eachRow
87
sql.eachRow("SELECT * FROM users WHERE age > ?", [18]) { row ->
88
    println "User: ${row.name}, Age: ${row.age}, Email: ${row.email}"
89
}
90

91
// Query returning all rows
92
def users = sql.rows("SELECT * FROM users ORDER BY name")
93
users.each { user ->
94
    println "${user.name} (${user.age})"
95
}
96

97
// Query with named parameters
98
def activeUsers = sql.rows("""
99
    SELECT * FROM users 
100
    WHERE active = :active AND department = :dept
101
""", [active: true, dept: 'Engineering'])
102

103
// Get single row
104
def user = sql.firstRow("SELECT * FROM users WHERE id = ?", [123])
105
if (user) {
106
    println "Found user: ${user.name}"
107
}
108

109
// Execute updates
110
def rowsAffected = sql.executeUpdate("""
111
    UPDATE users SET last_login = ? WHERE id = ?
112
""", [new Date(), 123])
113

114
// Insert data
115
sql.executeUpdate("""
116
    INSERT INTO users (name, email, age) VALUES (?, ?, ?)
117
""", ["John Doe", "john@example.com", 30])
118

119
// Transaction handling
120
sql.withTransaction {
121
    sql.executeUpdate("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, 1])
122
    sql.executeUpdate("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, 2])
123
}
124

125
// Batch operations
126
sql.withBatch("INSERT INTO logs (message, timestamp) VALUES (?, ?)") { stmt ->
127
    logMessages.each { message ->
128
        stmt.addBatch([message, new Date()])
129
    }
130
}
131

132
// Always close connection
133
sql.close()
134
```
135

136
### Enhanced Result Sets
137

138
Enhanced result set classes providing convenient access to query results.
139

140
```groovy { .api }
141
/**
142
 * Row result from SQL queries implementing Map interface
143
 */
144
class GroovyRowResult implements Map<String, Object> {
145
    /** Get column value by index (0-based) */
146
    Object getAt(int index)
147
    
148
    /** Get column value by name (case-insensitive) */
149
    Object getAt(String columnName)
150
    
151
    /** Get column value as specific type */
152
    Object asType(Class type)
153
    
154
    /** Get all column names */
155
    Set<String> keySet()
156
    
157
    /** Get all column values */
158
    Collection<Object> values()
159
    
160
    /** Convert to regular Map */
161
    Map<String, Object> toRowMap()
162
}
163

164
/**
165
 * Enhanced ResultSet with Groovy features
166
 */
167
class GroovyResultSet {
168
    /** Get column value by index */
169
    Object getAt(int index)
170
    
171
    /** Get column value by name */
172
    Object getAt(String columnName)
173
    
174
    /** Process each row with closure */
175
    void eachRow(Closure closure)
176
    
177
    /** Get all rows as list */
178
    List<GroovyRowResult> toRowList()
179
}
180
```
181

182
**Usage Examples:**
183

184
```groovy
185
// Working with GroovyRowResult
186
def user = sql.firstRow("SELECT id, name, email, created_at FROM users WHERE id = ?", [123])
187

188
// Access by column name
189
println user.name           // Column name access
190
println user['email']       // Map-style access
191
println user.created_at     // Timestamp column
192

193
// Access by index
194
println user[0]             // id (first column)
195
println user[1]             // name (second column)
196

197
// Type conversion
198
def userId = user.id as Long
199
def createdDate = user.created_at as java.time.LocalDateTime
200

201
// Map operations
202
user.each { column, value ->
203
    println "$column: $value"
204
}
205

206
println "User has ${user.size()} columns"
207
println "Column names: ${user.keySet()}"
208
```
209

210
### DataSet Abstraction
211

212
High-level abstraction for working with database tables as datasets.
213

214
```groovy { .api }
215
/**
216
 * Dataset abstraction for database tables
217
 */
218
class DataSet {
219
    /** Create DataSet for specified table */
220
    DataSet(Sql sql, String tableName)
221
    
222
    /** Iterate over all rows */
223
    void each(Closure closure)
224
    
225
    /** Find rows matching closure condition */
226
    DataSet findAll(Closure closure)
227
    
228
    /** Add new row to the table */
229
    void add(Map<String, Object> values)
230
    
231
    /** Get first row matching condition */
232
    GroovyRowResult find(Closure closure)
233
    
234
    /** Count rows matching condition */
235
    int count(Closure closure)
236
    
237
    /** Remove rows matching condition */
238
    void remove(Closure closure)
239
    
240
    /** Get underlying SQL instance */
241
    Sql getSql()
242
    
243
    /** Get table name */
244
    String getTableName()
245
}
246
```
247

248
**Usage Examples:**
249

250
```groovy
251
// Create DataSet for a table
252
def users = sql.dataSet("users")
253

254
// Add new records
255
users.add([
256
    name: "Alice Johnson",
257
    email: "alice@company.com",
258
    age: 28,
259
    department: "Engineering"
260
])
261

262
// Find records
263
def engineers = users.findAll { it.department == "Engineering" }
264
engineers.each { user ->
265
    println "${user.name} - ${user.email}"
266
}
267

268
// Count records
269
def engineerCount = users.count { it.department == "Engineering" }
270
println "Engineers: $engineerCount"
271

272
// Find specific record
273
def alice = users.find { it.name == "Alice Johnson" }
274
if (alice) {
275
    println "Found Alice: ${alice.email}"
276
}
277

278
// Remove records
279
users.remove { it.age < 18 }  // Remove minors
280
```
281

282
### Parameter Handling
283

284
Support for various parameter binding approaches.
285

286
```groovy { .api }
287
/**
288
 * In parameter for stored procedures
289
 */
290
class InParameter {
291
    InParameter(Object value)
292
    InParameter(Object value, int sqlType)
293
}
294

295
/**
296
 * Out parameter for stored procedures
297
 */
298
class OutParameter {
299
    OutParameter(int sqlType)
300
}
301

302
/**
303
 * In-Out parameter for stored procedures
304
 */
305
class InOutParameter {
306
    InOutParameter(Object value, int sqlType)
307
}
308
```
309

310
**Usage Examples:**
311

312
```groovy
313
import groovy.sql.*
314
import java.sql.Types
315

316
// Stored procedure with parameters
317
sql.call("{ call getUserStats(?, ?, ?) }") { stmt ->
318
    stmt.setInt(1, userId)
319
    stmt.registerOutParameter(2, Types.INTEGER)  // total_orders
320
    stmt.registerOutParameter(3, Types.DECIMAL)  // total_spent
321
    
322
    stmt.execute()
323
    
324
    def totalOrders = stmt.getInt(2)
325
    def totalSpent = stmt.getBigDecimal(3)
326
    
327
    println "User has $totalOrders orders totaling $totalSpent"
328
}
329

330
// Using parameter objects
331
def params = [
332
    new InParameter(userId),
333
    new OutParameter(Types.INTEGER),
334
    new OutParameter(Types.DECIMAL)
335
]
336

337
sql.call("{ call getUserStats(?, ?, ?) }", params) { result ->
338
    println "Orders: ${result[1]}, Spent: ${result[2]}"
339
}
340
```
341

342
### Batch Operations
343

344
Efficient batch processing for bulk operations.
345

346
```groovy { .api }
347
/**
348
 * Wrapper for batch prepared statements
349
 */
350
class BatchingPreparedStatementWrapper {
351
    /** Add parameters to batch */
352
    void addBatch(List params)
353
    
354
    /** Execute the batch */
355
    int[] executeBatch()
356
    
357
    /** Clear the batch */
358
    void clearBatch()
359
}
360

361
/**
362
 * Wrapper for batch statements
363
 */
364
class BatchingStatementWrapper {
365
    /** Add SQL to batch */
366
    void addBatch(String sql)
367
    
368
    /** Execute the batch */
369
    int[] executeBatch()
370
    
371
    /** Clear the batch */
372
    void clearBatch()
373
}
374
```
375

376
**Usage Examples:**
377

378
```groovy
379
// Batch insert with prepared statement
380
sql.withBatch("INSERT INTO products (name, price, category) VALUES (?, ?, ?)") { stmt ->
381
    products.each { product ->
382
        stmt.addBatch([product.name, product.price, product.category])
383
    }
384
}
385

386
// Batch multiple different statements
387
sql.withBatch { stmt ->
388
    stmt.addBatch("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1")
389
    stmt.addBatch("INSERT INTO sales_log (product_id, quantity) VALUES (1, 1)")
390
    stmt.addBatch("UPDATE customer_stats SET total_purchases = total_purchases + 1 WHERE customer_id = 123")
391
}
392

393
// Custom batch processing
394
sql.withBatch("""
395
    INSERT INTO order_items (order_id, product_id, quantity, price) 
396
    VALUES (?, ?, ?, ?)
397
""") { batchStmt ->
398
    order.items.each { item ->
399
        batchStmt.addBatch([
400
            order.id,
401
            item.productId,
402
            item.quantity,
403
            item.unitPrice
404
        ])
405
    }
406
}
407
```
408

409
## Connection Management
410

411
```groovy { .api }
412
/**
413
 * Connection utilities and management
414
 */
415
class Sql {
416
    /** Create connection pool */
417
    static Sql newInstance(String url, Properties props, String driver, int maxConnections)
418
    
419
    /** Test connection */
420
    boolean isConnected()
421
    
422
    /** Get connection metadata */
423
    DatabaseMetaData getMetaData()
424
    
425
    /** Set auto-commit mode */
426
    void setAutoCommit(boolean autoCommit)
427
    
428
    /** Commit transaction */
429
    void commit()
430
    
431
    /** Rollback transaction */
432
    void rollback()
433
}
434
```
435

436
**Usage Examples:**
437

438
```groovy
439
// Connection management
440
def sql = Sql.newInstance(jdbcUrl, props, driver)
441

442
try {
443
    // Disable auto-commit for transaction
444
    sql.setAutoCommit(false)
445
    
446
    // Perform operations
447
    sql.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
448
    sql.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
449
    
450
    // Commit if all succeeded
451
    sql.commit()
452
    
453
} catch (Exception e) {
454
    // Rollback on error
455
    sql.rollback()
456
    throw e
457
} finally {
458
    sql.close()
459
}
460

461
// Check connection status
462
if (sql.isConnected()) {
463
    println "Database connection is active"
464
}
465
```