0
# Result Processing and Row Handling
1

2
Advanced result set processing with Groovy-enhanced row access, metadata handling, and collection-style operations on database results.
3

4
## Capabilities
5

6
### Row Iteration
7

8
Process query results row-by-row using closures with automatic resource management and optional paging support.
9

10
```java { .api }
11
// Basic row iteration
12
public void eachRow(String sql, Closure closure) throws SQLException;
13
public void eachRow(String sql, List<?> params, Closure closure) throws SQLException;
14
public void eachRow(String sql, Map params, Closure closure) throws SQLException;
15
public void eachRow(Map params, String sql, Closure closure) throws SQLException;
16
public void eachRow(GString gstring, Closure closure) throws SQLException;
17

18
// With paging
19
public void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException;
20
public void eachRow(String sql, List<?> params, int offset, int maxRows, Closure closure) throws SQLException;
21
public void eachRow(String sql, Map params, int offset, int maxRows, Closure closure) throws SQLException;
22

23
// With metadata processing
24
public void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;
25
public void eachRow(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;
26
public void eachRow(String sql, Map params, Closure metaClosure, Closure rowClosure) throws SQLException;
27
```
28

29
**Example**:
30
```groovy
31
// Basic iteration
32
sql.eachRow('SELECT id, name, email FROM users') { row ->
33
    println "User: ${row.name} (${row.email})"
34
}
35

36
// With parameters
37
sql.eachRow('SELECT * FROM orders WHERE user_id = ?', [userId]) { row ->
38
    println "Order ${row.id}: ${row.total}"
39
}
40

41
// With paging (skip first 10, process next 20)
42
sql.eachRow('SELECT * FROM products ORDER BY name', 10, 20) { row ->
43
    println "${row.name}: \$${row.price}"
44
}
45

46
// With metadata processing
47
sql.eachRow('SELECT * FROM users',
48
    { meta ->
49
        println "Columns: ${(1..meta.columnCount).collect { meta.getColumnName(it) }}"
50
    },
51
    { row ->
52
        println "Row: ${row}"
53
    }
54
)
55

56
// Using GString
57
def category = 'electronics'
58
sql.eachRow("SELECT * FROM products WHERE category = $category") { row ->
59
    println row.name
60
}
61
```
62

63
### GroovyRowResult Access
64

65
Access database row data using property-style syntax and Map interface methods.
66

67
```java { .api }
68
public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
69
    // Property-style access (case-insensitive)
70
    public Object getProperty(String property);
71
    
72
    // Index-based access (supports negative indices)
73
    public Object getAt(int index);
74
    
75
    // Map interface methods
76
    public Object get(Object property);
77
    public Object put(String key, Object value);
78
    public Object remove(Object rawKey);
79
    public boolean containsKey(Object key);
80
    public boolean containsValue(Object value);
81
    public boolean isEmpty();
82
    public int size();
83
    public Set<String> keySet();
84
    public Collection<Object> values();
85
    public Set<Map.Entry<String, Object>> entrySet();
86
    public void clear();
87
    public void putAll(Map<? extends String, ?> t);
88
    
89
    // Standard methods
90
    public boolean equals(Object o);
91
    public int hashCode();
92
    public String toString();
93
}
94
```
95

96
**Example**:
97
```groovy
98
sql.eachRow('SELECT id, first_name, last_name, email FROM users') { row ->
99
    // Property-style access (case-insensitive)
100
    println row.first_name    // or row.FIRST_NAME or row.First_Name
101
    println row.lastName      // matches last_name column
102
    
103
    // Index-based access
104
    println row[0]            // id (first column)
105
    println row[-1]           // email (last column)
106
    
107
    // Map-style access
108
    println row.get('email')
109
    println row['id']
110
    
111
    // Check for columns
112
    if (row.containsKey('phone')) {
113
        println "Phone: ${row.phone}"
114
    }
115
    
116
    // Iterate over columns
117
    row.each { key, value ->
118
        println "$key: $value"
119
    }
120
    
121
    // Get all column names
122
    println "Columns: ${row.keySet()}"
123
    
124
    // Convert to string representation
125
    println row.toString()  // [ID:1, FIRST_NAME:Alice, LAST_NAME:Smith, EMAIL:alice@example.com]
126
}
127
```
128

129
### Enhanced ResultSet Interface
130

131
Work with ResultSet objects enhanced with Groovy-style access methods and additional functionality.
132

133
```java { .api }
134
public interface GroovyResultSet extends GroovyObject, ResultSet {
135
    // Groovy-style column access
136
    Object getAt(int index);
137
    Object getAt(String columnName);
138
    
139
    // Groovy-style column updates
140
    void putAt(int index, Object newValue);
141
    void putAt(String columnName, Object newValue);
142
    
143
    // Row operations
144
    void add(Map values);
145
    void eachRow(Closure closure);
146
}
147
```
148

149
**Example**:
150
```groovy
151
sql.query('SELECT id, name, email FROM users') { resultSet ->
152
    // Cast to GroovyResultSet for enhanced functionality
153
    def grs = resultSet as GroovyResultSet
154
    
155
    grs.eachRow { 
156
        // Access columns with Groovy syntax
157
        println grs['name']      // by name
158
        println grs[1]           // by index
159
        
160
        // Update columns (if ResultSet is updatable)
161
        if (grs['email'] == null) {
162
            grs['email'] = 'no-email@example.com'
163
            grs.updateRow()
164
        }
165
    }
166
}
167
```
168

169
### Metadata Handling
170

171
Access and process result set metadata for dynamic query handling and schema introspection.
172

173
```java { .api }
174
public class ResultSetMetaDataWrapper extends GroovyObjectSupport {
175
    public ResultSetMetaDataWrapper(ResultSetMetaData target, int index);
176
    
177
    // Property access delegates to ResultSetMetaData methods for specific column
178
    public Object getProperty(String property);
179
    public void setProperty(String property, Object newValue);
180
    public Object invokeMethod(String name, Object args);
181
}
182
```
183

184
**Example**:
185
```groovy
186
sql.eachRow('SELECT * FROM users', 
187
    { meta ->
188
        // Access metadata
189
        def columnCount = meta.columnCount
190
        println "Query returned $columnCount columns:"
191
        
192
        (1..columnCount).each { i ->
193
            def wrapper = new ResultSetMetaDataWrapper(meta, i)
194
            println "Column $i: ${wrapper.columnName} (${wrapper.columnTypeName})"
195
            
196
            // Or access directly
197
            println "  - Type: ${meta.getColumnType(i)}"
198
            println "  - Nullable: ${meta.isNullable(i)}"
199
            println "  - Precision: ${meta.getPrecision(i)}"
200
        }
201
    },
202
    { row ->
203
        // Process row data
204
        row.each { key, value ->
205
            println "$key = $value (${value?.class?.simpleName})"
206
        }
207
    }
208
)
209

210
// Using metadata in dynamic queries
211
def buildSelectQuery(tableName) {
212
    def columns = []
213
    sql.eachRow("SELECT * FROM $tableName LIMIT 0", 
214
        { meta ->
215
            (1..meta.columnCount).each { i ->
216
                columns << meta.getColumnName(i)
217
            }
218
        },
219
        { /* no rows to process */ }
220
    )
221
    return "SELECT ${columns.join(', ')} FROM $tableName"
222
}
223
```
224

225
### Collection Integration
226

227
Use standard Groovy collection methods on query results for powerful data processing.
228

229
**Example**:
230
```groovy
231
// Get results as collection and apply collection methods
232
def users = sql.rows('SELECT * FROM users')
233

234
// Filter active users
235
def activeUsers = users.findAll { it.active }
236

237
// Extract email addresses
238
def emails = users.collect { it.email }
239

240
// Group by department
241
def byDepartment = users.groupBy { it.department }
242

243
// Find specific user
244
def admin = users.find { it.role == 'admin' }
245

246
// Sort by name
247
def sortedUsers = users.sort { it.last_name }
248

249
// Calculate statistics
250
def totalSalary = users.sum { it.salary ?: 0 }
251
def avgAge = users.sum { it.age ?: 0 } / users.size()
252

253
// Complex processing
254
def summary = users
255
    .findAll { it.active && it.salary > 50000 }
256
    .groupBy { it.department }
257
    .collectEntries { dept, people ->
258
        [dept, [
259
            count: people.size(),
260
            avgSalary: people.sum { it.salary } / people.size(),
261
            names: people.collect { "${it.first_name} ${it.last_name}" }
262
        ]]
263
    }
264

265
println summary
266
```
267

268
### Error Handling
269

270
Handle database exceptions and result processing errors with proper resource cleanup.
271

272
**Example**:
273
```groovy
274
try {
275
    sql.eachRow('SELECT * FROM non_existent_table') { row ->
276
        println row
277
    }
278
} catch (SQLException e) {
279
    println "Database error: ${e.message}"
280
    // Resources automatically cleaned up
281
}
282

283
// Validate row data during processing
284
sql.eachRow('SELECT id, email FROM users') { row ->
285
    if (!row.email?.contains('@')) {
286
        println "Warning: Invalid email for user ${row.id}: ${row.email}"
287
    }
288
    
289
    // Defensive null checking
290
    def name = row.name ?: 'Unknown'
291
    println "Processing user: $name"
292
}
293
```