0
# SQL Database Operations
1

2
Comprehensive database connectivity, query execution, and data manipulation through the Groovy SQL API. Provides simplified database access with automatic resource management, transaction support, and result set navigation.
3

4
## Capabilities
5

6
### Database Connection and Configuration
7

8
Create and manage database connections with automatic resource cleanup.
9

10
```java { .api }
11
/**
12
 * Main class for database operations with automatic connection management
13
 */
14
class Sql {
15
    /**
16
     * Create Sql instance with JDBC URL and credentials
17
     */
18
    static Sql newInstance(String url, String user, String password, String driver) throws SQLException;
19
    
20
    /**
21
     * Create Sql instance with JDBC URL only
22
     */
23
    static Sql newInstance(String url) throws SQLException;
24
    
25
    /**
26
     * Create Sql instance from existing Connection
27
     */
28
    static Sql newInstance(Connection connection);
29
    
30
    /**
31
     * Create Sql instance from DataSource
32
     */
33
    static Sql newInstance(DataSource dataSource);
34
    
35
    /**
36
     * Create Sql instance with Properties configuration
37
     */
38
    static Sql newInstance(Properties properties) throws SQLException;
39
    
40
    /**
41
     * Close the database connection and clean up resources
42
     */
43
    void close() throws SQLException;
44
    
45
    /**
46
     * Get the underlying JDBC Connection
47
     */
48
    Connection getConnection();
49
    
50
    /**
51
     * Check if connection is closed
52
     */
53
    boolean isClosed() throws SQLException;
54
}
55
```
56

57
**Usage Examples:**
58

59
```groovy
60
import groovy.sql.Sql
61

62
// Connect with full credentials
63
def sql = Sql.newInstance("jdbc:h2:mem:testdb", 
64
                         "sa", 
65
                         "", 
66
                         "org.h2.Driver")
67

68
// Connect with DataSource (recommended for production)
69
import javax.sql.DataSource
70
import org.apache.commons.dbcp2.BasicDataSource
71

72
def dataSource = new BasicDataSource()
73
dataSource.url = "jdbc:postgresql://localhost:5432/mydb"
74
dataSource.username = "user"
75
dataSource.password = "password"
76
dataSource.driverClassName = "org.postgresql.Driver"
77

78
def sql = Sql.newInstance(dataSource)
79

80
// Always close when done
81
try {
82
    // database operations
83
} finally {
84
    sql.close()
85
}
86

87
// Or use with try-with-resources pattern
88
Sql.withInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver") { sql ->
89
    // database operations - connection automatically closed
90
}
91
```
92

93
### Query Execution and Result Processing
94

95
Execute SQL queries and process results with various iteration patterns.
96

97
```java { .api }
98
/**
99
 * Query execution methods for retrieving data
100
 */
101
class Sql {
102
    /**
103
     * Execute query and return all rows as List of GroovyRowResult
104
     */
105
    List<GroovyRowResult> rows(String sql) throws SQLException;
106
    List<GroovyRowResult> rows(String sql, List params) throws SQLException;
107
    List<GroovyRowResult> rows(String sql, Object... params) throws SQLException;
108
    List<GroovyRowResult> rows(String sql, Map params) throws SQLException;
109
    
110
    /**
111
     * Execute query and iterate over each row
112
     */
113
    void eachRow(String sql, Closure closure) throws SQLException;
114
    void eachRow(String sql, List params, Closure closure) throws SQLException;
115
    void eachRow(String sql, Map params, Closure closure) throws SQLException;
116
    
117
    /**
118
     * Execute query and return first row only
119
     */
120
    GroovyRowResult firstRow(String sql) throws SQLException;
121
    GroovyRowResult firstRow(String sql, List params) throws SQLException;
122
    GroovyRowResult firstRow(String sql, Map params) throws SQLException;
123
    
124
    /**
125
     * Execute prepared statement with result set processing
126
     */
127
    void query(String sql, Closure closure) throws SQLException;
128
    void query(String sql, List params, Closure closure) throws SQLException;
129
}
130

131
/**
132
 * Row result that provides map-like and object-like access to column data
133
 */
134
interface GroovyRowResult extends Map<String, Object> {
135
    /**
136
     * Get column value by name (case-insensitive)
137
     */
138
    Object getProperty(String columnName);
139
    
140
    /**
141
     * Get column value by index (0-based)
142
     */
143
    Object getAt(int columnIndex);
144
    
145
    /**
146
     * Convert row to Map
147
     */
148
    Map<String, Object> toRowResult();
149
}
150
```
151

152
**Usage Examples:**
153

154
```groovy
155
import groovy.sql.Sql
156

157
def sql = Sql.newInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver")
158

159
// Create sample table and data
160
sql.execute("""
161
    CREATE TABLE employees (
162
        id INT PRIMARY KEY,
163
        name VARCHAR(50),
164
        department VARCHAR(30),
165
        salary DECIMAL(10,2)
166
    )
167
""")
168

169
sql.execute("INSERT INTO employees VALUES (1, 'Alice', 'Engineering', 85000)")
170
sql.execute("INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 65000)")
171
sql.execute("INSERT INTO employees VALUES (3, 'Carol', 'Engineering', 90000)")
172

173
// Query all rows
174
def employees = sql.rows("SELECT * FROM employees ORDER BY name")
175
employees.each { row ->
176
    println "${row.name} (${row.department}): \$${row.salary}"
177
}
178

179
// Query with parameters (safe from SQL injection)
180
def engineers = sql.rows("SELECT * FROM employees WHERE department = ?", ["Engineering"])
181
assert engineers.size() == 2
182

183
// Query with named parameters
184
def highEarners = sql.rows("""
185
    SELECT * FROM employees 
186
    WHERE salary > :minSalary 
187
    ORDER BY salary DESC
188
""", [minSalary: 80000])
189

190
// Iterate over large result sets efficiently
191
sql.eachRow("SELECT * FROM employees") { row ->
192
    println "Processing employee: ${row.name}"
193
    // Process one row at a time - memory efficient
194
}
195

196
// Get single row
197
def employee = sql.firstRow("SELECT * FROM employees WHERE id = ?", [1])
198
if (employee) {
199
    println "Found: ${employee.name}"
200
}
201

202
// Access columns by index or name
203
employees.each { row ->
204
    println "ID: ${row[0]}"           // By index
205
    println "Name: ${row.name}"       // By property name  
206
    println "Dept: ${row['department']}" // By map key
207
}
208

209
sql.close()
210
```
211

212
### Data Modification Operations
213

214
Insert, update, and delete operations with parameter binding and batch processing.
215

216
```java { .api }
217
/**
218
 * Data modification methods
219
 */
220
class Sql {
221
    /**
222
     * Execute UPDATE, INSERT, or DELETE statement
223
     */
224
    int executeUpdate(String sql) throws SQLException;
225
    int executeUpdate(String sql, List params) throws SQLException;
226
    int executeUpdate(String sql, Map params) throws SQLException;
227
    
228
    /**
229
     * Execute any SQL statement
230
     */
231
    boolean execute(String sql) throws SQLException;
232
    boolean execute(String sql, List params) throws SQLException;
233
    
234
    /**
235
     * Execute INSERT and return generated keys
236
     */
237
    List<GroovyRowResult> executeInsert(String sql) throws SQLException;
238
    List<GroovyRowResult> executeInsert(String sql, List params) throws SQLException;
239
    
240
    /**
241
     * Batch operations for efficient bulk processing
242
     */
243
    int[] withBatch(String sql, Closure closure) throws SQLException;
244
    int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException;
245
}
246
```
247

248
**Usage Examples:**
249

250
```groovy
251
import groovy.sql.Sql
252

253
def sql = Sql.newInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver")
254

255
// Insert single record
256
def rowsAffected = sql.executeUpdate("""
257
    INSERT INTO employees (id, name, department, salary) 
258
    VALUES (?, ?, ?, ?)
259
""", [4, "Dave", "Sales", 55000])
260

261
assert rowsAffected == 1
262

263
// Insert with named parameters
264
sql.executeUpdate("""
265
    INSERT INTO employees (id, name, department, salary) 
266
    VALUES (:id, :name, :dept, :salary)
267
""", [id: 5, name: "Eve", dept: "HR", salary: 70000])
268

269
// Update records
270
def updated = sql.executeUpdate("""
271
    UPDATE employees 
272
    SET salary = salary * 1.1 
273
    WHERE department = ?
274
""", ["Engineering"])
275

276
println "Updated $updated engineering salaries"
277

278
// Delete records  
279
def deleted = sql.executeUpdate("DELETE FROM employees WHERE salary < ?", [60000])
280

281
// Insert and get generated keys
282
def keys = sql.executeInsert("""
283
    INSERT INTO employees (name, department, salary) 
284
    VALUES (?, ?, ?)
285
""", ["Frank", "IT", 75000])
286

287
keys.each { key ->
288
    println "Generated ID: ${key[0]}"
289
}
290

291
// Batch processing for bulk operations
292
def newEmployees = [
293
    [name: "Grace", dept: "Finance", salary: 68000],
294
    [name: "Henry", dept: "Operations", salary: 62000],
295
    [name: "Iris", dept: "Legal", salary: 95000]
296
]
297

298
sql.withBatch("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)") { stmt ->
299
    newEmployees.each { emp ->
300
        stmt.addBatch([emp.name, emp.dept, emp.salary])
301
    }
302
}
303

304
sql.close()
305
```
306

307
### Transaction Management
308

309
Handle database transactions with automatic rollback on exceptions.
310

311
```java { .api }
312
/**
313
 * Transaction management methods
314
 */
315
class Sql {
316
    /**
317
     * Execute closure within a database transaction
318
     */
319
    void withTransaction(Closure closure) throws SQLException;
320
    
321
    /**
322
     * Begin a new transaction
323
     */
324
    void begin() throws SQLException;
325
    
326
    /**
327
     * Commit current transaction
328
     */
329
    void commit() throws SQLException;
330
    
331
    /**
332
     * Rollback current transaction
333
     */
334
    void rollback() throws SQLException;
335
    
336
    /**
337
     * Check if currently in a transaction
338
     */
339
    boolean isInTransaction();
340
    
341
    /**
342
     * Set auto-commit mode
343
     */
344
    void setAutoCommit(boolean autoCommit) throws SQLException;
345
}
346
```
347

348
**Usage Examples:**
349

350
```groovy
351
import groovy.sql.Sql
352

353
def sql = Sql.newInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver")
354

355
// Automatic transaction management
356
sql.withTransaction { 
357
    sql.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = ?", [1])
358
    sql.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = ?", [2])
359
    
360
    // If any exception occurs, transaction is automatically rolled back
361
    def balance = sql.firstRow("SELECT balance FROM accounts WHERE id = ?", [1])
362
    if (balance.balance < 0) {
363
        throw new RuntimeException("Insufficient funds")
364
    }
365
    // Transaction commits automatically if no exception
366
}
367

368
// Manual transaction control
369
try {
370
    sql.begin()
371
    
372
    sql.executeUpdate("INSERT INTO audit_log VALUES (?, ?)", [new Date(), "Operation started"])
373
    sql.executeUpdate("UPDATE critical_data SET status = 'processing'")
374
    
375
    // Simulate complex operation
376
    performComplexOperation()
377
    
378
    sql.executeUpdate("UPDATE critical_data SET status = 'completed'")
379
    sql.commit()
380
    
381
} catch (Exception e) {
382
    sql.rollback()
383
    println "Transaction rolled back: ${e.message}"
384
}
385

386
sql.close()
387
```
388

389
### DataSet Operations
390

391
Object-relational mapping-like operations for working with database tables.
392

393
```java { .api }
394
/**
395
 * DataSet provides table-like operations on database tables
396
 */
397
class DataSet {
398
    /**
399
     * Create DataSet for specific table
400
     */
401
    DataSet(Sql sql, String tableName);
402
    
403
    /**
404
     * Add new row to the dataset/table
405
     */
406
    void add(Map<String, Object> values);
407
    
408
    /**
409
     * Find all rows matching criteria
410
     */
411
    DataSet findAll(Closure criteria);
412
    
413
    /**
414
     * Iterate over all rows in dataset
415
     */
416
    void each(Closure closure);
417
    
418
    /**
419
     * Get first row matching criteria
420
     */
421
    GroovyRowResult firstRow();
422
    
423
    /**
424
     * Get all rows as List
425
     */
426
    List<GroovyRowResult> rows();
427
}
428
```
429

430
**Usage Examples:**
431

432
```groovy
433
import groovy.sql.Sql
434
import groovy.sql.DataSet
435

436
def sql = Sql.newInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver")
437

438
// Create DataSet for employees table
439
def employees = new DataSet(sql, "employees")
440

441
// Add new employees
442
employees.add(name: "John", department: "IT", salary: 72000)
443
employees.add(name: "Jane", department: "IT", salary: 78000)
444

445
// Find IT employees
446
def itEmployees = employees.findAll { it.department == "IT" }
447
itEmployees.each { emp ->
448
    println "${emp.name}: \$${emp.salary}"
449
}
450

451
// Iterate over all employees
452
employees.each { employee ->
453
    if (employee.salary > 75000) {
454
        println "High earner: ${employee.name}"
455
    }
456
}
457

458
sql.close()
459
```
460

461
### Connection Pooling and Advanced Configuration
462

463
Configure connection pooling and advanced database settings.
464

465
**Usage Examples:**
466

467
```groovy
468
import groovy.sql.Sql
469
import org.apache.commons.dbcp2.BasicDataSource
470

471
// Configure connection pool
472
def setupDataSource() {
473
    def dataSource = new BasicDataSource()
474
    dataSource.url = "jdbc:postgresql://localhost:5432/production"
475
    dataSource.username = "app_user"
476
    dataSource.password = "secure_password"
477
    dataSource.driverClassName = "org.postgresql.Driver"
478
    
479
    // Connection pool settings
480
    dataSource.initialSize = 5
481
    dataSource.maxTotal = 20
482
    dataSource.maxIdle = 10
483
    dataSource.minIdle = 5
484
    dataSource.maxWaitMillis = 30000
485
    
486
    // Connection validation
487
    dataSource.validationQuery = "SELECT 1"
488
    dataSource.testOnBorrow = true
489
    dataSource.testWhileIdle = true
490
    
491
    return dataSource
492
}
493

494
def dataSource = setupDataSource()
495

496
// Use pooled connections
497
Sql.withInstance(dataSource) { sql ->
498
    // Database operations using pooled connection
499
    def results = sql.rows("SELECT COUNT(*) as total FROM orders WHERE status = 'completed'")
500
    println "Completed orders: ${results[0].total}"
501
}
502

503
// Connection is automatically returned to pool
504
```
505

506
## Types
507

508
### Core SQL Types
509

510
```java { .api }
511
/**
512
 * Row result interface providing multiple access patterns
513
 */
514
interface GroovyRowResult extends Map<String, Object> {
515
    /**
516
     * Get column value by property-style access
517
     */
518
    Object getProperty(String columnName);
519
    
520
    /**
521
     * Get column value by array-style access  
522
     */
523
    Object getAt(int columnIndex);
524
    Object getAt(String columnName);
525
    
526
    /**
527
     * Convert to standard Map
528
     */
529
    Map<String, Object> toRowResult();
530
    
531
    /**
532
     * Get column metadata
533
     */
534
    ResultSetMetaData getMetaData();
535
}
536

537
/**
538
 * Exception thrown for SQL-related errors
539
 */
540
class SQLException extends Exception {
541
    String getSQLState();
542
    int getErrorCode();
543
    SQLException getNextException();
544
}
545
```
546

547
### DataSet Types
548

549
```java { .api }
550
/**
551
 * Table-like abstraction over SQL operations
552
 */
553
class DataSet {
554
    /**
555
     * The underlying Sql instance
556
     */
557
    Sql getSql();
558
    
559
    /**
560
     * The table name this DataSet represents
561
     */
562
    String getTableName();
563
    
564
    /**
565
     * Add row to dataset
566
     */
567
    void add(Map<String, Object> values);
568
    
569
    /**
570
     * Find rows matching criteria
571
     */
572
    DataSet findAll(Closure criteria);
573
    
574
    /**
575
     * Iterate over rows
576
     */
577
    void each(Closure closure);
578
    
579
    /**
580
     * Get first matching row
581
     */
582
    GroovyRowResult firstRow();
583
    
584
    /**
585
     * Get all rows
586
     */
587
    List<GroovyRowResult> rows();
588
}
589
```