0
# DataSet Operations
1

2
DataSet provides an enhanced interface for database operations using POGO (Plain Old Groovy Object) field-based queries. It extends the Sql class with fluent API methods for filtering, sorting, and data manipulation using closure-based syntax.
3

4
## DataSet Creation
5

6
### From Table Name
7

8
Create a DataSet for a specific database table:
9

10
```groovy { .api }
11
DataSet(Sql sql, String table)
12
```
13

14
### From POGO Class
15

16
Create a DataSet based on a Groovy class structure:
17

18
```groovy { .api }
19
DataSet(Sql sql, Class type)
20
```
21

22
### Factory Methods from Sql
23

24
Create DataSet instances from existing Sql connections:
25

26
```groovy { .api }
27
// From Sql instance
28
DataSet dataSet(String table)
29
DataSet dataSet(Class<?> type)
30
```
31

32
## Data Manipulation
33

34
### Adding Records
35

36
Insert new records using map-based syntax:
37

38
```groovy { .api }
39
void add(Map<String, Object> map) throws SQLException
40
```
41

42
## Query Operations
43

44
### Filtering
45

46
Create filtered views using closure-based WHERE conditions:
47

48
```groovy { .api }
49
DataSet findAll(Closure where)
50
```
51

52
### Sorting
53

54
Sort results using closure-based ORDER BY conditions:
55

56
```groovy { .api }
57
DataSet sort(Closure sort)
58
```
59

60
### Reverse Order
61

62
Reverse the order of results (only valid after sort):
63

64
```groovy { .api }
65
DataSet reverse()
66
```
67

68
### Create Views
69

70
Create filtered views with closure criteria:
71

72
```groovy { .api }
73
DataSet createView(Closure criteria)
74
```
75

76
## Result Iteration
77

78
### Row Iteration
79

80
Iterate over all rows in the DataSet:
81

82
```groovy { .api }
83
void each(Closure closure) throws SQLException
84
void each(int offset, int maxRows, Closure closure) throws SQLException
85
```
86

87
### Collect All Rows
88

89
Retrieve all rows as a list:
90

91
```groovy { .api }
92
List rows() throws SQLException
93
List rows(int offset, int maxRows) throws SQLException
94
```
95

96
### First Row
97

98
Get the first row only:
99

100
```groovy { .api }
101
Object firstRow() throws SQLException
102
```
103

104
## Query Information
105

106
### Generated SQL
107

108
Access the underlying SQL query:
109

110
```groovy { .api }
111
String getSql()
112
```
113

114
### Query Parameters
115

116
Access the parameters for the generated query:
117

118
```groovy { .api }
119
List<Object> getParameters()
120
```
121

122
## Inherited Methods
123

124
DataSet inherits all methods from the Sql class, including:
125

126
### Batch Operations
127

128
```groovy { .api }
129
int[] withBatch(Closure closure) throws SQLException
130
int[] withBatch(int batchSize, Closure closure) throws SQLException
131
```
132

133
### Transaction Management
134

135
```groovy { .api }
136
void withTransaction(Closure closure) throws SQLException
137
void commit() throws SQLException
138
void rollback() throws SQLException
139
```
140

141
### Connection Management
142

143
```groovy { .api }
144
void cacheConnection(Closure closure) throws SQLException
145
void close()
146
```
147

148
## Usage Examples
149

150
### Basic DataSet Operations
151

152
```groovy
153
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
154

155
// Create DataSet for users table
156
def users = sql.dataSet("users")
157

158
// Add new user
159
users.add(name: "John Doe", email: "john@example.com", age: 30)
160
users.add(name: "Jane Smith", email: "jane@example.com", age: 25)
161

162
// Query all users
163
users.each { user ->
164
    println "User: ${user.name}, Age: ${user.age}"
165
}
166
```
167

168
### Filtering and Sorting
169

170
```groovy
171
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
172
def users = sql.dataSet("users")
173

174
// Filter users by age
175
def adults = users.findAll { it.age >= 18 }
176
adults.each { user ->
177
    println "Adult: ${user.name}"
178
}
179

180
// Sort users by name
181
def sortedUsers = users.sort { it.name }
182
sortedUsers.each { user ->
183
    println "User: ${user.name}"
184
}
185

186
// Reverse sort by age
187
def youngestFirst = users.sort { it.age }.reverse()
188
youngestFirst.each { user ->
189
    println "${user.name} is ${user.age}"
190
}
191
```
192

193
### POGO-based DataSet
194

195
```groovy
196
// Define a POGO class
197
class Person {
198
    String name
199
    String email
200
    int age
201
    boolean active
202
}
203

204
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
205

206
// Create DataSet based on POGO
207
def people = sql.dataSet(Person)
208

209
// Add using map syntax
210
people.add(name: "Alice", email: "alice@example.com", age: 28, active: true)
211

212
// Filter active people over 25
213
def activeSeniors = people.findAll { it.active && it.age > 25 }
214
activeSeniors.each { person ->
215
    println "Active senior: ${person.name}"
216
}
217
```
218

219
### Complex Filtering
220

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

225
// Complex filter with multiple conditions
226
def recentLargeOrders = orders.findAll { 
227
    it.amount > 1000 && it.status == 'completed' && it.order_date > new Date() - 30
228
}
229

230
// Get count of matching records
231
def count = recentLargeOrders.rows().size()
232
println "Found ${count} recent large orders"
233

234
// Get first matching record
235
def firstOrder = recentLargeOrders.firstRow()
236
if (firstOrder) {
237
    println "Largest recent order: ${firstOrder.amount}"
238
}
239
```
240

241
### Pagination with DataSet
242

243
```groovy
244
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
245
def products = sql.dataSet("products")
246

247
// Get paginated results
248
def pageSize = 10
249
def offset = 0
250

251
products.sort { it.name }.each(offset, pageSize) { product ->
252
    println "Product: ${product.name} - \$${product.price}"
253
}
254

255
// Or get as list
256
def page1 = products.sort { it.name }.rows(0, 10)
257
def page2 = products.sort { it.name }.rows(10, 10)
258
```
259

260
### View Creation
261

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

266
// Create a reusable view
267
def managers = employees.createView { it.role == 'manager' && it.active == true }
268

269
// Use the view
270
managers.each { manager ->
271
    println "Manager: ${manager.name} in ${manager.department}"
272
}
273

274
// Views can be further filtered
275
def seniorManagers = managers.findAll { it.years_experience > 5 }
276
```
277

278
### Query Introspection
279

280
```groovy
281
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
282
def users = sql.dataSet("users")
283

284
// Create a filtered dataset
285
def activeUsers = users.findAll { it.active == true && it.age > 21 }
286

287
// Inspect the generated SQL
288
println "Generated SQL: ${activeUsers.getSql()}"
289
println "Parameters: ${activeUsers.getParameters()}"
290
```