0
# Result Handling
1

2
Groovy SQL provides specialized result handling classes that offer Groovy-friendly access to database results. These classes bridge the gap between JDBC's ResultSet interface and Groovy's expressive syntax patterns.
3

4
## GroovyRowResult
5

6
GroovyRowResult is a Map-like wrapper for SQL result rows that provides case-insensitive column access and integrates seamlessly with Groovy's property access syntax.
7

8
### Constructor
9

10
```groovy { .api }
11
GroovyRowResult(Map<String, Object> result)
12
```
13

14
### Property Access
15

16
Access column values using Groovy property syntax with case-insensitive column names:
17

18
```groovy { .api }
19
Object getProperty(String property)
20
```
21

22
### Index Access
23

24
Access columns by their position in the result set:
25

26
```groovy { .api }
27
Object getAt(int index)  // Supports negative indices for reverse access
28
```
29

30
### Map Interface
31

32
GroovyRowResult implements the full Map interface with case-insensitive key handling:
33

34
```groovy { .api }
35
// Map interface methods
36
void clear()
37
boolean containsKey(Object key)  // Case-insensitive
38
boolean containsValue(Object value)
39
Set<Map.Entry<String, Object>> entrySet()
40
boolean equals(Object o)
41
Object get(Object property)  // Case-insensitive
42
int hashCode()
43
boolean isEmpty()
44
Set<String> keySet()
45
Object put(String key, Object value)  // Handles case-insensitive replacement
46
void putAll(Map<? extends String, ?> t)
47
Object remove(Object rawKey)  // Case-insensitive
48
int size()
49
Collection<Object> values()
50
```
51

52
## GroovyResultSet
53

54
GroovyResultSet extends the standard JDBC ResultSet interface with Groovy-specific enhancements for more expressive data access patterns.
55

56
### Interface Definition
57

58
```groovy { .api }
59
interface GroovyResultSet extends ResultSet, GroovyObject
60
```
61

62
### Enhanced Column Access
63

64
Access columns using Groovy's array-like syntax:
65

66
```groovy { .api }
67
Object getAt(int index)  // Supports negative indices
68
Object getAt(String columnName)  // Case-insensitive column access
69
```
70

71
### Column Updates
72

73
Update column values using Groovy's assignment syntax:
74

75
```groovy { .api }
76
void putAt(int index, Object newValue)
77
void putAt(String columnName, Object newValue)
78
```
79

80
### Row Operations
81

82
Add new rows and iterate using closures:
83

84
```groovy { .api }
85
void add(Map values)  // Add new row from map
86
void eachRow(Closure closure)  // Iterate with closure
87
```
88

89
## GroovyResultSetExtension
90

91
Implementation class that provides the actual functionality for GroovyResultSet:
92

93
### Constructor
94

95
```groovy { .api }
96
GroovyResultSetExtension(ResultSet set)
97
```
98

99
### Property Access
100

101
```groovy { .api }
102
Object getProperty(String columnName)
103
void setProperty(String columnName, Object newValue)
104
```
105

106
### Index Access
107

108
```groovy { .api }
109
Object getAt(int index)  // Supports negative indices
110
void putAt(int index, Object newValue)
111
```
112

113
### Navigation
114

115
```groovy { .api }
116
boolean next()
117
boolean previous()
118
```
119

120
### Row Operations
121

122
```groovy { .api }
123
void add(Map values)
124
void eachRow(Closure closure)
125
```
126

127
### Method Delegation
128

129
```groovy { .api }
130
Object invokeMethod(String name, Object args)  // Delegates to underlying ResultSet
131
```
132

133
## GroovyResultSetProxy
134

135
Proxy class for creating GroovyResultSet instances:
136

137
### Constructors
138

139
```groovy { .api }
140
GroovyResultSetProxy(ResultSet set)
141
GroovyResultSetProxy(GroovyResultSetExtension ext)
142
```
143

144
### Proxy Methods
145

146
```groovy { .api }
147
GroovyResultSet getImpl()  // Get proxy instance
148
Object invoke(Object proxy, Method method, Object[] args)  // InvocationHandler method
149
```
150

151
## ResultSetMetaDataWrapper
152

153
Wrapper for accessing ResultSetMetaData columns with Groovy syntax:
154

155
### Constructor
156

157
```groovy { .api }
158
ResultSetMetaDataWrapper(ResultSetMetaData target, int index)
159
```
160

161
### Metadata Access
162

163
```groovy { .api }
164
Object invokeMethod(String name, Object args)  // Invoke metadata methods with column index
165
Object getProperty(String property)  // Get metadata properties for column
166
void setProperty(String property, Object newValue)  // Read-only (throws exception)
167
```
168

169
## Usage Examples
170

171
### GroovyRowResult Access Patterns
172

173
```groovy
174
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
175

176
// Different ways to access column values
177
sql.eachRow("SELECT user_id, first_name, last_name, email FROM users") { row ->
178
    // Property access (case-insensitive)
179
    println "User ID: ${row.user_id}"
180
    println "Name: ${row.first_name} ${row.last_name}"
181
    
182
    // Alternative case variations work
183
    println "Email: ${row.EMAIL}"  // Works even if column is lowercase
184
    println "Name: ${row.First_Name}"  // Works with different casing
185
    
186
    // Index access
187
    println "First column: ${row[0]}"  // user_id
188
    println "Last column: ${row[-1]}"  // email (negative index)
189
    
190
    // Map-style access
191
    println "User: ${row.get('first_name')}"
192
    println "Contains email: ${row.containsKey('email')}"
193
}
194
```
195

196
### GroovyResultSet Operations
197

198
```groovy
199
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
200

201
// Using GroovyResultSet in query closure
202
sql.query("SELECT * FROM users WHERE active = ?", [true]) { rs ->
203
    while (rs.next()) {
204
        // Groovy-style column access
205
        def userName = rs['user_name']
206
        def userEmail = rs['email']
207
        
208
        // Update values
209
        rs['last_login'] = new Date()
210
        
211
        // Access by index (supports negative)
212
        def firstCol = rs[0]  // First column
213
        def lastCol = rs[-1]  // Last column
214
        
215
        println "User: ${userName}, Email: ${userEmail}"
216
    }
217
}
218
```
219

220
### Case-Insensitive Column Access
221

222
```groovy
223
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
224

225
sql.eachRow("SELECT USER_NAME, USER_EMAIL, CREATED_DATE FROM users") { row ->
226
    // All these work regardless of actual column case
227
    println row.user_name     // Works
228
    println row.USER_NAME     // Works  
229
    println row.User_Name     // Works
230
    println row.userName      // Works
231
    
232
    // Map access is also case-insensitive
233
    println row['user_email']   // Works
234
    println row['USER_EMAIL']   // Works
235
    println row['userEmail']    // Works
236
}
237
```
238

239
### Working with Complex Data Types
240

241
```groovy
242
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
243

244
sql.eachRow("SELECT id, data, created_at FROM complex_table") { row ->
245
    // Handle different data types
246
    def id = row.id as Integer
247
    def jsonData = row.data as String
248
    def timestamp = row.created_at as Timestamp
249
    
250
    // Check for null values
251
    if (row.data != null) {
252
        println "Data: ${jsonData}"
253
    }
254
    
255
    // Convert to appropriate types
256
    def date = timestamp?.toLocalDateTime()
257
    println "Created: ${date}"
258
}
259
```
260

261
### Metadata Access
262

263
```groovy
264
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
265

266
sql.eachRow("SELECT * FROM users", { meta ->
267
    // Access metadata using wrapper
268
    def columnCount = meta.columnCount
269
    println "Result has ${columnCount} columns:"
270
    
271
    (1..columnCount).each { i ->
272
        def column = meta[i-1]  // 0-based index
273
        println "Column ${i}: ${column.columnName} (${column.columnTypeName})"
274
    }
275
}) { row ->
276
    // Process rows
277
    println "Row: ${row}"
278
}
279
```
280

281
### Row Manipulation
282

283
```groovy
284
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
285

286
// Collect results with transformations
287
def results = []
288
sql.eachRow("SELECT name, age, salary FROM employees") { row ->
289
    // Create modified result
290
    def employee = [:]
291
    employee.name = row.name?.toUpperCase()
292
    employee.age = row.age as Integer
293
    employee.salaryCategory = row.salary > 50000 ? 'high' : 'standard'
294
    
295
    results << employee
296
}
297

298
results.each { emp ->
299
    println "${emp.name}: ${emp.salaryCategory} salary"
300
}
301
```
302

303
### Integration with Groovy Collections
304

305
```groovy
306
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
307

308
// Get all rows and use Groovy collection methods
309
def users = sql.rows("SELECT name, age, department FROM users")
310

311
// Filter using Groovy methods
312
def youngUsers = users.findAll { it.age < 30 }
313
def departments = users.collect { it.department }.unique()
314
def avgAge = users.sum { it.age } / users.size()
315

316
println "Young users: ${youngUsers.size()}"
317
println "Departments: ${departments}"
318
println "Average age: ${avgAge}"
319

320
// Group by department
321
def byDept = users.groupBy { it.department }
322
byDept.each { dept, empList ->
323
    println "${dept}: ${empList.size()} employees"
324
}
325
```