0
# Database Access
1

2
Groovy provides comprehensive database access through the Sql class and DataSet, offering simplified JDBC operations, connection management, and result processing with Groovy's dynamic features.
3

4
## Database Connections
5

6
### Sql
7

8
Main class for database operations providing simplified JDBC access.
9

10
```groovy { .api }
11
class Sql implements Closeable {
12
    // Factory methods for creating connections
13
    static Sql newInstance(String url)
14
    static Sql newInstance(String url, Properties properties)
15
    static Sql newInstance(String url, String user, String password)
16
    static Sql newInstance(String url, String user, String password, String driverClassName)
17
    static Sql newInstance(String url, String user, String password, Properties properties, String driverClassName)
18
    static Sql newInstance(Map<String, Object> args)
19
    static Sql newInstance(DataSource dataSource)
20
    static Sql newInstance(Connection connection)
21
    
22
    // Query methods
23
    List<GroovyRowResult> rows(String sql)
24
    List<GroovyRowResult> rows(String sql, List<Object> params)
25
    List<GroovyRowResult> rows(String sql, Map params)
26
    List<GroovyRowResult> rows(String sql, Object... params)
27
    List<GroovyRowResult> rows(Map params, String sql)
28
    
29
    GroovyRowResult firstRow(String sql)
30
    GroovyRowResult firstRow(String sql, List<Object> params)
31
    GroovyRowResult firstRow(String sql, Map params) 
32
    GroovyRowResult firstRow(String sql, Object... params)
33
    
34
    void eachRow(String sql, Closure closure)
35
    void eachRow(String sql, List<Object> params, Closure closure)
36
    void eachRow(String sql, Map params, Closure closure)
37
    void eachRow(String sql, int offset, int maxRows, Closure closure)
38
    void eachRow(String sql, List<Object> params, int offset, int maxRows, Closure closure)
39
    
40
    // Update methods
41
    int executeUpdate(String sql)
42
    int executeUpdate(String sql, List<Object> params)
43
    int executeUpdate(String sql, Map params)
44
    int executeUpdate(String sql, Object... params)
45
    
46
    boolean execute(String sql)
47
    boolean execute(String sql, List<Object> params)
48
    boolean execute(String sql, Map params)
49
    List<List<Object>> executeInsert(String sql)
50
    List<List<Object>> executeInsert(String sql, List<Object> params)
51
    
52
    // Prepared statement support
53
    void withStatement(Closure closure)
54
    Object call(String sql, List<Object> params, Closure closure)
55
    Object call(String sql, Closure closure)
56
    
57
    // Transaction support
58
    void withTransaction(Closure closure)
59
    void commit()
60
    void rollback()
61
    void setAutoCommit(boolean autoCommit)
62
    boolean getAutoCommit()
63
    
64
    // Batch operations
65
    void withBatch(String sql, Closure closure)
66
    void withBatch(int batchSize, String sql, Closure closure)
67
    int[] executeBatch(String sql)
68
    
69
    // Connection management
70
    Connection getConnection()
71
    DataSource getDataSource()
72
    void close()
73
    boolean isClosed()
74
}
75
```
76

77
Usage examples:
78
```groovy
79
import groovy.sql.Sql
80

81
// Create connection
82
def sql = Sql.newInstance(
83
    'jdbc:h2:mem:testdb',
84
    'sa', 
85
    '', 
86
    'org.h2.Driver'
87
)
88

89
// Create table
90
sql.execute '''
91
    CREATE TABLE person (
92
        id INTEGER PRIMARY KEY,
93
        name VARCHAR(50),
94
        age INTEGER,
95
        email VARCHAR(100)
96
    )
97
'''
98

99
// Insert data
100
sql.executeUpdate '''
101
    INSERT INTO person (id, name, age, email) 
102
    VALUES (?, ?, ?, ?)
103
''', [1, 'John Doe', 30, 'john@example.com']
104

105
// Insert with named parameters
106
sql.executeUpdate '''
107
    INSERT INTO person (id, name, age, email) 
108
    VALUES (:id, :name, :age, :email)
109
''', [id: 2, name: 'Jane Smith', age: 25, email: 'jane@example.com']
110

111
// Query all rows
112
def people = sql.rows('SELECT * FROM person ORDER BY name')
113
people.each { person ->
114
    println "${person.name} (${person.age})"
115
}
116

117
// Query with parameters
118
def adults = sql.rows('SELECT * FROM person WHERE age >= ?', [21])
119
def john = sql.firstRow('SELECT * FROM person WHERE name = ?', ['John Doe'])
120

121
// Iterate through results
122
sql.eachRow('SELECT * FROM person') { row ->
123
    println "ID: ${row.id}, Name: ${row.name}, Email: ${row.email}"
124
}
125

126
// Update records
127
def updated = sql.executeUpdate(
128
    'UPDATE person SET age = ? WHERE name = ?', 
129
    [31, 'John Doe']
130
)
131
println "Updated $updated records"
132

133
// Close connection
134
sql.close()
135
```
136

137
### GroovyRowResult
138

139
Enhanced result row that provides map-like and object-like access to column values.
140

141
```groovy { .api }
142
class GroovyRowResult implements Map<String, Object> {
143
    Object getAt(int index)
144
    Object getAt(String columnName)
145
    void putAt(String columnName, Object value)
146
    void putAt(int index, Object value)
147
    
148
    Object getProperty(String property)
149
    void setProperty(String property, Object value)
150
    
151
    // Map interface methods
152
    int size()
153
    boolean isEmpty()
154
    boolean containsKey(Object key)
155
    boolean containsValue(Object value)
156
    Object get(Object key)
157
    Object put(String key, Object value)
158
    Object remove(Object key)
159
    void putAll(Map<? extends String, ?> m)
160
    void clear()
161
    Set<String> keySet()
162
    Collection<Object> values()
163
    Set<Map.Entry<String, Object>> entrySet()
164
    
165
    // Utility methods
166
    String toString()
167
}
168
```
169

170
Usage example:
171
```groovy
172
def person = sql.firstRow('SELECT * FROM person WHERE id = ?', [1])
173

174
// Access columns by name
175
assert person.name == 'John Doe'
176
assert person['email'] == 'john@example.com'
177

178
// Access columns by index
179
assert person[1] == 'John Doe'  // Assuming name is second column
180

181
// Use as Map
182
person.each { key, value ->
183
    println "$key: $value"
184
}
185

186
// Modify values (for update operations)
187
person.age = 32
188
sql.executeUpdate(
189
    'UPDATE person SET age = ? WHERE id = ?',
190
    [person.age, person.id]
191
)
192
```
193

194
## Dataset Operations
195

196
### DataSet
197

198
Higher-level abstraction for table operations with automatic SQL generation.
199

200
```groovy { .api }
201
class DataSet {
202
    DataSet(Sql sql, String table)
203
    DataSet(DataSet parent, String table)
204
    DataSet(DataSet parent, Closure where)
205
    
206
    // Query operations
207
    void each(Closure closure)
208
    void eachWithIndex(Closure closure) 
209
    List findAll(Closure where)
210
    Object find(Closure where)
211
    GroovyRowResult firstRow()
212
    List<GroovyRowResult> rows()
213
    int size()
214
    
215
    // Data modification
216
    void add(Map<String, Object> values)
217
    DataSet findAll(Closure where)
218
    int update(Map<String, Object> values)
219
    int update(Closure where, Map<String, Object> values)
220
    
221
    // Dataset operations
222
    DataSet createView(Closure where)
223
    DataSet reverse()
224
    DataSet sort(Closure sort)
225
    
226
    String getTable()
227
    Sql getSql()
228
}
229
```
230

231
Usage examples:
232
```groovy
233
import groovy.sql.DataSet
234

235
def personTable = sql.dataSet('person')
236

237
// Add records
238
personTable.add(name: 'Alice Brown', age: 28, email: 'alice@example.com')
239
personTable.add(name: 'Bob Wilson', age: 35, email: 'bob@example.com')
240

241
// Query with closures
242
def adults = personTable.findAll { it.age >= 21 }
243
def alice = personTable.find { it.name == 'Alice Brown' }
244

245
// Iterate through records
246
personTable.each { person ->
247
    println "${person.name}: ${person.email}"
248
}
249

250
// Update records
251
personTable.update(age: 29) { it.name == 'Alice Brown' }
252

253
// Create filtered views
254
def seniors = personTable.createView { it.age >= 65 }
255
def youngAdults = personTable.createView { it.age >= 18 && it.age < 30 }
256

257
// Sort data
258
def sortedByAge = personTable.sort { it.age }
259
def sortedByName = personTable.sort { it.name }
260
```
261

262
## Stored Procedures
263

264
### Parameter Types
265

266
Support for stored procedure input, output, and input/output parameters.
267

268
```groovy { .api }
269
class OutParameter {
270
    OutParameter(int sqlType)
271
    OutParameter(int sqlType, int scale)
272
    OutParameter(int sqlType, String typeName)
273
    
274
    int getType()
275
    int getScale()
276
    String getTypeName()
277
}
278

279
class InOutParameter extends OutParameter {
280
    InOutParameter(Object value, int sqlType)
281
    InOutParameter(Object value, int sqlType, int scale)
282
    InOutParameter(Object value, int sqlType, String typeName)
283
    
284
    Object getValue()
285
}
286

287
class ResultSetOutParameter extends OutParameter {
288
    ResultSetOutParameter(int sqlType)
289
}
290
```
291

292
Usage examples:
293
```groovy
294
import groovy.sql.OutParameter
295
import groovy.sql.InOutParameter
296
import java.sql.Types
297

298
// Call stored procedure with output parameters
299
def outParam = new OutParameter(Types.VARCHAR)
300
def inOutParam = new InOutParameter('initial', Types.VARCHAR)
301

302
def result = sql.call(
303
    '{call GetPersonInfo(?, ?, ?)}',
304
    [1, outParam, inOutParam]
305
)
306

307
println "Output parameter: ${result[1]}"
308
println "InOut parameter: ${result[2]}"
309

310
// Call function that returns result set
311
def resultSetParam = new ResultSetOutParameter(Types.REF_CURSOR)
312
sql.call('{? = call GetAllPersons()}', [resultSetParam]) { cursor ->
313
    cursor.eachRow { row ->
314
        println "${row.name}: ${row.email}"
315
    }
316
}
317
```
318

319
## Transaction Management
320

321
### Transaction Control
322

323
```groovy
324
// Automatic transaction management
325
sql.withTransaction { connection ->
326
    sql.executeUpdate('UPDATE person SET age = age + 1 WHERE id = ?', [1])
327
    sql.executeUpdate('INSERT INTO audit_log (action, timestamp) VALUES (?, ?)', 
328
                     ['age_increment', new Date()])
329
    // Transaction automatically committed on success, rolled back on exception
330
}
331

332
// Manual transaction control
333
try {
334
    sql.autoCommit = false
335
    
336
    sql.executeUpdate('DELETE FROM person WHERE age < ?', [18])
337
    def deletedCount = sql.updateCount
338
    
339
    if (deletedCount > 10) {
340
        throw new RuntimeException('Too many records would be deleted')
341
    }
342
    
343
    sql.commit()
344
    println "Successfully deleted $deletedCount records"
345
    
346
} catch (Exception e) {
347
    sql.rollback()
348
    println "Transaction rolled back: ${e.message}"
349
} finally {
350
    sql.autoCommit = true
351
}
352
```
353

354
## Batch Operations
355

356
### Batch Processing
357

358
```groovy
359
// Simple batch execution
360
def batchData = [
361
    [3, 'Charlie Brown', 22, 'charlie@example.com'],
362
    [4, 'Diana Prince', 27, 'diana@example.com'],
363
    [5, 'Edward Norton', 35, 'edward@example.com']
364
]
365

366
sql.withBatch('INSERT INTO person (id, name, age, email) VALUES (?, ?, ?, ?)') { stmt ->
367
    batchData.each { person ->
368
        stmt.addBatch(person)
369
    }
370
}
371

372
// Batch with size limit
373
sql.withBatch(100, 'INSERT INTO large_table (data) VALUES (?)') { stmt ->
374
    (1..1000).each { i ->
375
        stmt.addBatch(["Data item $i"])
376
    }
377
}
378

379
// Manual batch control
380
def stmt = sql.connection.prepareStatement('UPDATE person SET last_login = ? WHERE id = ?')
381
try {
382
    [1, 2, 3, 4, 5].each { id ->
383
        stmt.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()))
384
        stmt.setInt(2, id)
385
        stmt.addBatch()
386
    }
387
    int[] results = stmt.executeBatch()
388
    println "Batch update results: $results"
389
} finally {
390
    stmt.close()
391
}
392
```
393

394
## Connection Pooling
395

396
### DataSource Integration
397

398
```groovy
399
import org.apache.commons.dbcp2.BasicDataSource
400

401
// Create connection pool
402
def dataSource = new BasicDataSource()
403
dataSource.driverClassName = 'org.h2.Driver'
404
dataSource.url = 'jdbc:h2:mem:testdb'
405
dataSource.username = 'sa'
406
dataSource.password = ''
407
dataSource.initialSize = 5
408
dataSource.maxTotal = 20
409

410
// Use with Groovy SQL
411
def sql = new Sql(dataSource)
412

413
// Perform operations
414
sql.eachRow('SELECT * FROM person') { row ->
415
    println row.name
416
}
417

418
// Connection automatically returned to pool when Sql instance is closed
419
sql.close()
420

421
// Shutdown pool when application ends
422
dataSource.close()
423
```
424

425
## Error Handling
426

427
### Exception Handling
428

429
```groovy
430
import java.sql.SQLException
431
import java.sql.SQLIntegrityConstraintViolationException
432

433
try {
434
    sql.executeUpdate('INSERT INTO person (id, name) VALUES (?, ?)', [1, 'Duplicate'])
435
} catch (SQLIntegrityConstraintViolationException e) {
436
    println "Constraint violation: ${e.message}"
437
} catch (SQLException e) {
438
    println "SQL error (${e.SQLState}): ${e.message}"
439
} catch (Exception e) {
440
    println "General error: ${e.message}"
441
}
442

443
// Check for warnings
444
def warnings = sql.connection.warnings
445
while (warnings) {
446
    println "Warning: ${warnings.message}"
447
    warnings = warnings.nextWarning
448
}
449
```
450

451
## Advanced Features
452

453
### Metadata Access
454

455
```groovy
456
// Database metadata
457
def metaData = sql.connection.metaData
458
println "Database: ${metaData.databaseProductName} ${metaData.databaseProductVersion}"
459
println "Driver: ${metaData.driverName} ${metaData.driverVersion}"
460

461
// Table information
462
def tables = sql.rows("""
463
    SELECT table_name, table_type 
464
    FROM information_schema.tables 
465
    WHERE table_schema = 'PUBLIC'
466
""")
467

468
tables.each { table ->
469
    println "Table: ${table.table_name} (${table.table_type})"
470
}
471

472
// Column information
473
def columns = sql.rows("""
474
    SELECT column_name, data_type, is_nullable
475
    FROM information_schema.columns
476
    WHERE table_name = 'PERSON'
477
""")
478

479
columns.each { col ->
480
    println "Column: ${col.column_name} - ${col.data_type} (nullable: ${col.is_nullable})"
481
}
482
```
483

484
### Custom Type Handling
485

486
```groovy
487
import java.sql.Types
488

489
// Custom type conversion
490
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_UPDATABLE
491

492
// Handle special data types
493
sql.eachRow('SELECT id, data, created_date FROM special_table') { row ->
494
    def id = row.id
495
    def jsonData = row.data // Assuming CLOB containing JSON
496
    def timestamp = row.created_date
497
    
498
    // Process custom types
499
    if (jsonData) {
500
        def parsed = new groovy.json.JsonSlurper().parseText(jsonData.toString())
501
        println "JSON data: $parsed"
502
    }
503
}
504

505
// Insert custom types
506
def jsonData = groovy.json.JsonOutput.toJson([key: 'value', number: 42])
507
sql.executeUpdate(
508
    'INSERT INTO special_table (data, created_date) VALUES (?, ?)',
509
    [jsonData, new java.sql.Timestamp(System.currentTimeMillis())]
510
)
511
```