0
# DataSet API
1

2
Object-oriented table access providing POJO-style database operations with filtering, sorting, and view creation capabilities.
3

4
## Capabilities
5

6
### DataSet Creation
7

8
Create DataSet instances for table-based operations using either table names or POJO classes.
9

10
```java { .api }
11
public class DataSet extends Sql {
12
    // Constructors
13
    public DataSet(Sql sql, String table);
14
    public DataSet(Sql sql, Class type);
15
    
16
    // Factory methods in Sql class
17
    public DataSet dataSet(String table);
18
    public DataSet dataSet(Class<?> type);
19
}
20
```
21

22
**Example**:
23
```groovy
24
// Create DataSet from table name
25
def usersDataSet = sql.dataSet('users')
26

27
// Create DataSet from POJO class
28
class User {
29
    Long id
30
    String name
31
    String email
32
    Date created
33
}
34

35
def userClassDataSet = sql.dataSet(User)
36

37
// Using factory methods
38
def productsDs = sql.dataSet('products')
39
def ordersDs = sql.dataSet(Order)
40
```
41

42
### Data Insertion
43

44
Add new records to the database table using Map-based data insertion.
45

46
```java { .api }
47
// Data manipulation
48
public void add(Map<String, Object> map) throws SQLException;
49
```
50

51
**Example**:
52
```groovy
53
def usersDs = sql.dataSet('users')
54

55
// Add single record
56
usersDs.add([
57
    name: 'Alice Johnson',
58
    email: 'alice@example.com',
59
    department: 'Engineering',
60
    salary: 75000
61
])
62

63
// Add multiple records
64
def newUsers = [
65
    [name: 'Bob Smith', email: 'bob@example.com', department: 'Sales', salary: 65000],
66
    [name: 'Carol Davis', email: 'carol@example.com', department: 'Marketing', salary: 70000],
67
    [name: 'David Wilson', email: 'david@example.com', department: 'Engineering', salary: 80000]
68
]
69

70
newUsers.each { userData ->
71
    usersDs.add(userData)
72
}
73

74
// Add with automatic timestamp
75
usersDs.add([
76
    name: 'Eve Brown',
77
    email: 'eve@example.com',
78
    department: 'HR',
79
    salary: 60000,
80
    created_date: new Date()
81
])
82
```
83

84
### Filtering and Querying
85

86
Filter table data using closure-based criteria for flexible query construction.
87

88
```java { .api }
89
// Query methods
90
public DataSet findAll(Closure where);
91
public DataSet createView(Closure criteria);
92
```
93

94
**Example**:
95
```groovy
96
def usersDs = sql.dataSet('users')
97

98
// Filter by single condition
99
def engineeringUsers = usersDs.findAll { it.department == 'Engineering' }
100

101
// Filter by multiple conditions
102
def highEarners = usersDs.findAll { 
103
    it.salary > 70000 && it.department in ['Engineering', 'Sales'] 
104
}
105

106
// Filter with date conditions
107
def recentUsers = usersDs.findAll { 
108
    it.created_date > (new Date() - 30) 
109
}
110

111
// Complex filtering with null checks
112
def activeUsers = usersDs.findAll {
113
    it.active != false && it.email?.contains('@') && it.salary != null
114
}
115

116
// Create reusable filtered view
117
def seniorEngineers = usersDs.createView { 
118
    it.department == 'Engineering' && it.salary > 90000 
119
}
120
```
121

122
### Sorting and Ordering
123

124
Sort DataSet results using closure-based sorting criteria.
125

126
```java { .api }
127
// Sorting methods
128
public DataSet sort(Closure sort);
129
public DataSet reverse();
130
```
131

132
**Example**:
133
```groovy
134
def usersDs = sql.dataSet('users')
135

136
// Sort by single field
137
def usersByName = usersDs.sort { it.name }
138

139
// Sort by multiple fields
140
def usersBySalaryThenName = usersDs.sort { a, b ->
141
    def salaryCompare = b.salary <=> a.salary  // Descending salary
142
    salaryCompare != 0 ? salaryCompare : a.name <=> b.name  // Then ascending name
143
}
144

145
// Sort with null handling
146
def usersSorted = usersDs.sort { a, b ->
147
    if (a.salary == null && b.salary == null) return 0
148
    if (a.salary == null) return 1
149
    if (b.salary == null) return -1
150
    return a.salary <=> b.salary
151
}
152

153
// Reverse sort order
154
def usersReversed = usersDs.sort { it.name }.reverse()
155

156
// Complex sorting example
157
def prioritizedUsers = usersDs.sort { a, b ->
158
    // Priority: active status, then department, then salary (desc), then name
159
    def activeCompare = (b.active ? 1 : 0) <=> (a.active ? 1 : 0)
160
    if (activeCompare != 0) return activeCompare
161
    
162
    def deptCompare = a.department <=> b.department
163
    if (deptCompare != 0) return deptCompare
164
    
165
    def salaryCompare = (b.salary ?: 0) <=> (a.salary ?: 0)
166
    if (salaryCompare != 0) return salaryCompare
167
    
168
    return a.name <=> b.name
169
}
170
```
171

172
### Result Processing
173

174
Process DataSet results using various iteration and collection methods.
175

176
```java { .api }
177
// Row processing methods
178
public void each(Closure closure) throws SQLException;
179
public void each(int offset, int maxRows, Closure closure) throws SQLException;
180

181
// Collection methods
182
public List<GroovyRowResult> rows() throws SQLException;
183
public List<GroovyRowResult> rows(int offset, int maxRows) throws SQLException;
184
public GroovyRowResult firstRow() throws SQLException;
185
```
186

187
**Example**:
188
```groovy
189
def usersDs = sql.dataSet('users')
190

191
// Process all rows
192
usersDs.findAll { it.active }.each { user ->
193
    println "Active user: ${user.name} (${user.email})"
194
}
195

196
// Process with paging
197
usersDs.sort { it.name }.each(10, 5) { user ->
198
    // Skip first 10, process next 5
199
    println "User: ${user.name}"
200
}
201

202
// Get results as collection
203
def allUsers = usersDs.rows()
204
def userNames = allUsers.collect { it.name }
205
def totalSalary = allUsers.sum { it.salary ?: 0 }
206

207
// Get paged results
208
def page2Users = usersDs.sort { it.name }.rows(20, 10)  // Skip 20, take 10
209

210
// Get first matching record
211
def firstAdmin = usersDs.findAll { it.role == 'admin' }.firstRow()
212
if (firstAdmin) {
213
    println "First admin: ${firstAdmin.name}"
214
}
215

216
// Combine filtering, sorting, and collection operations
217
def summary = usersDs
218
    .findAll { it.department == 'Engineering' }
219
    .sort { it.salary }
220
    .rows()
221
    .groupBy { it.level }
222
    .collectEntries { level, engineers ->
223
        [level, [
224
            count: engineers.size(),
225
            avgSalary: engineers.sum { it.salary } / engineers.size(),
226
            names: engineers.collect { it.name }
227
        ]]
228
    }
229
```
230

231
### SQL Generation and Inspection
232

233
Access the generated SQL and parameters for debugging and optimization purposes.
234

235
```java { .api }
236
// SQL inspection methods
237
public String getSql();
238
public List<Object> getParameters();
239
```
240

241
**Example**:
242
```groovy
243
def usersDs = sql.dataSet('users')
244

245
// Create a filtered dataset
246
def filteredDs = usersDs
247
    .findAll { it.department == 'Engineering' && it.salary > 70000 }
248
    .sort { it.name }
249

250
// Inspect generated SQL
251
println "Generated SQL: ${filteredDs.sql}"
252
println "Parameters: ${filteredDs.parameters}"
253

254
// This helps with debugging and query optimization
255
def complexFilter = usersDs.findAll { 
256
    it.active && 
257
    it.department in ['Engineering', 'Sales'] && 
258
    it.created_date > (new Date() - 90) &&
259
    it.salary > 50000 
260
}
261

262
println "Complex query SQL:"
263
println complexFilter.sql
264
println "Parameters: ${complexFilter.parameters}"
265

266
// Use for query performance analysis
267
def measureQueryPerformance(dataSet) {
268
    println "Executing query: ${dataSet.sql}"
269
    def start = System.currentTimeMillis()
270
    
271
    def results = dataSet.rows()
272
    
273
    def end = System.currentTimeMillis()
274
    println "Query executed in ${end - start}ms, returned ${results.size()} rows"
275
    return results
276
}
277

278
measureQueryPerformance(filteredDs)
279
```
280

281
### Batch Operations with DataSet
282

283
Combine DataSet functionality with batch operations for improved performance.
284

285
```java { .api }
286
// Batch operations
287
public Object withBatch(Closure closure) throws SQLException;
288
public Object withBatch(int batchSize, Closure closure) throws SQLException;
289
```
290

291
**Example**:
292
```groovy
293
def usersDs = sql.dataSet('users')
294
def logsDs = sql.dataSet('user_logs')
295

296
// Batch insert multiple users
297
def newUsers = [
298
    [name: 'User1', email: 'user1@example.com', department: 'IT'],
299
    [name: 'User2', email: 'user2@example.com', department: 'Finance'],
300
    [name: 'User3', email: 'user3@example.com', department: 'HR']
301
]
302

303
usersDs.withBatch(10) {
304
    newUsers.each { userData ->
305
        usersDs.add(userData)
306
    }
307
}
308

309
// Batch operations across multiple DataSets
310
sql.withTransaction {
311
    usersDs.withBatch(50) {
312
        csvData.each { row ->
313
            // Add user
314
            usersDs.add([
315
                name: row.name,
316
                email: row.email,
317
                department: row.department
318
            ])
319
            
320
            // Add corresponding log entry
321
            logsDs.add([
322
                user_email: row.email,
323
                action: 'user_created',
324
                timestamp: new Date()
325
            ])
326
        }
327
    }
328
}
329
```
330

331
### Advanced DataSet Patterns
332

333
Implement advanced patterns using DataSet for complex database operations.
334

335
**Example**:
336
```groovy
337
// DataSet inheritance for domain-specific operations
338
class UserDataSet extends DataSet {
339
    UserDataSet(Sql sql) {
340
        super(sql, 'users')
341
    }
342
    
343
    def getActiveUsers() {
344
        return findAll { it.active == true }
345
    }
346
    
347
    def getUsersByDepartment(department) {
348
        return findAll { it.department == department }
349
    }
350
    
351
    def getHighEarners(threshold = 100000) {
352
        return findAll { it.salary > threshold }
353
    }
354
}
355

356
def userDs = new UserDataSet(sql)
357

358
// Use domain-specific methods
359
def activeEngineers = userDs.getActiveUsers()
360
    .findAll { it.department == 'Engineering' }
361

362
def topEarners = userDs.getHighEarners(120000)
363
    .sort { -it.salary }  // Descending order
364

365
// DataSet composition for complex queries
366
def buildUserReport(department, minSalary, includeInactive = false) {
367
    def baseQuery = sql.dataSet('users')
368
    
369
    if (!includeInactive) {
370
        baseQuery = baseQuery.findAll { it.active }
371
    }
372
    
373
    if (department) {
374
        baseQuery = baseQuery.findAll { it.department == department }
375
    }
376
    
377
    if (minSalary) {
378
        baseQuery = baseQuery.findAll { it.salary >= minSalary }
379
    }
380
    
381
    return baseQuery.sort { it.name }
382
}
383

384
// Generate different reports
385
def engineeringReport = buildUserReport('Engineering', 70000)
386
def allHighEarnersReport = buildUserReport(null, 100000, true)
387

388
// DataSet with joins (simulated through multiple queries)
389
def getUsersWithOrderCounts() {
390
    def users = sql.dataSet('users').rows()
391
    def orders = sql.dataSet('orders').rows()
392
    
393
    def orderCounts = orders.groupBy { it.user_id }
394
        .collectEntries { userId, userOrders -> 
395
            [userId, userOrders.size()] 
396
        }
397
    
398
    return users.collect { user ->
399
        user + [order_count: orderCounts[user.id] ?: 0]
400
    }
401
}
402
```