0
# Core Database Operations
1

2
The Sql class provides the primary interface for database operations with automatic resource management and support for multiple query patterns including String SQL, parameterized queries, and GString syntax.
3

4
## Connection Creation
5

6
### Factory Methods
7

8
Create Sql instances with automatic connection management:
9

10
```groovy { .api }
11
// Basic connection
12
static Sql newInstance(String url) throws SQLException
13

14
// With authentication
15
static Sql newInstance(String url, String user, String password) throws SQLException
16

17
// With driver specification
18
static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException
19

20
// With properties
21
static Sql newInstance(String url, Properties properties) throws SQLException
22
static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException
23

24
// With named parameters
25
static Sql newInstance(Map<String,Object> args) throws SQLException
26
```
27

28
### Closure-based Factory Methods
29

30
Automatically handle connection cleanup:
31

32
```groovy { .api }
33
static void withInstance(String url, Closure c) throws SQLException
34
static void withInstance(String url, String user, String password, Closure c) throws SQLException
35
static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException
36
static void withInstance(String url, Properties properties, Closure c) throws SQLException
37
static void withInstance(Map<String,Object> args, Closure c) throws SQLException
38
```
39

40
### Constructors
41

42
Create from existing connections or data sources:
43

44
```groovy { .api }
45
Sql(DataSource dataSource)
46
Sql(Connection connection)
47
Sql(Sql parent)
48
```
49

50
## Query Execution
51

52
### Result Set Iteration
53

54
Execute queries and iterate over results using closures:
55

56
```groovy { .api }
57
// Basic iteration with GroovyResultSet
58
void eachRow(String sql, Closure closure) throws SQLException
59

60
// With parameters
61
void eachRow(String sql, List<Object> params, Closure closure) throws SQLException
62
void eachRow(String sql, Map params, Closure closure) throws SQLException
63

64
// With pagination
65
void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException
66
void eachRow(String sql, List<Object> params, int offset, int maxRows, Closure closure) throws SQLException
67

68
// With metadata handling
69
void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException
70
void eachRow(String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
71

72
// GString support
73
void eachRow(GString gstring, Closure closure) throws SQLException
74
void eachRow(GString gstring, int offset, int maxRows, Closure closure) throws SQLException
75
```
76

77
### Raw ResultSet Access
78

79
Execute queries with direct ResultSet access:
80

81
```groovy { .api }
82
// Direct ResultSet handling
83
void query(String sql, Closure closure) throws SQLException
84
void query(String sql, List<Object> params, Closure closure) throws SQLException
85
void query(String sql, Map map, Closure closure) throws SQLException
86
void query(GString gstring, Closure closure) throws SQLException
87
```
88

89
## Row Collection Methods
90

91
### All Rows
92

93
Retrieve complete result sets as lists:
94

95
```groovy { .api }
96
// Get all rows as List<GroovyRowResult>
97
List<GroovyRowResult> rows(String sql) throws SQLException
98

99
// With parameters
100
List<GroovyRowResult> rows(String sql, List<Object> params) throws SQLException
101
List<GroovyRowResult> rows(String sql, Map params, Closure closure) throws SQLException
102
List<GroovyRowResult> rows(String sql, Object[] params) throws SQLException
103

104
// With pagination
105
List<GroovyRowResult> rows(String sql, int offset, int maxRows) throws SQLException
106
List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows) throws SQLException
107

108
// With metadata handling
109
List<GroovyRowResult> rows(String sql, Closure metaClosure) throws SQLException
110
List<GroovyRowResult> rows(String sql, int offset, int maxRows, Closure metaClosure) throws SQLException
111

112
// GString support  
113
List<GroovyRowResult> rows(GString gstring) throws SQLException
114
List<GroovyRowResult> rows(GString gstring, int offset, int maxRows) throws SQLException
115
```
116

117
### Single Row
118

119
Retrieve the first row only:
120

121
```groovy { .api }
122
// Get first row as GroovyRowResult
123
GroovyRowResult firstRow(String sql) throws SQLException
124
GroovyRowResult firstRow(String sql, List<Object> params) throws SQLException
125
GroovyRowResult firstRow(String sql, Map params) throws SQLException
126
GroovyRowResult firstRow(String sql, Object[] params) throws SQLException
127
GroovyRowResult firstRow(GString gstring) throws SQLException
128
```
129

130
## Statement Execution
131

132
### Execute Statements
133

134
Execute SQL statements with optional result processing:
135

136
```groovy { .api }
137
// Execute statement, returns true if ResultSet available
138
boolean execute(String sql) throws SQLException
139

140
// With result processing closure
141
void execute(String sql, Closure processResults) throws SQLException
142

143
// With parameters
144
boolean execute(String sql, List<Object> params) throws SQLException
145
boolean execute(String sql, Map params) throws SQLException
146
boolean execute(String sql, Object[] params) throws SQLException
147

148
// With parameters and result processing  
149
void execute(String sql, List<Object> params, Closure processResults) throws SQLException
150
void execute(String sql, Map params, Closure processResults) throws SQLException
151

152
// GString support
153
boolean execute(GString gstring) throws SQLException
154
void execute(GString gstring, Closure processResults) throws SQLException
155
```
156

157
### Update Operations
158

159
Execute UPDATE, DELETE statements:
160

161
```groovy { .api }
162
// Returns number of affected rows
163
int executeUpdate(String sql) throws SQLException
164
int executeUpdate(String sql, List<Object> params) throws SQLException
165
int executeUpdate(String sql, Map params) throws SQLException
166
int executeUpdate(String sql, Object[] params) throws SQLException
167
int executeUpdate(GString gstring) throws SQLException
168
```
169

170
### Insert Operations
171

172
Execute INSERT statements with generated key support:
173

174
```groovy { .api }
175
// Insert with generated keys
176
List<List<Object>> executeInsert(String sql) throws SQLException
177
List<List<Object>> executeInsert(String sql, List<Object> params) throws SQLException
178
List<List<Object>> executeInsert(String sql, Map params) throws SQLException
179
List<List<Object>> executeInsert(String sql, Object[] params) throws SQLException
180

181
// Insert with specific key columns
182
List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames) throws SQLException
183
List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException
184
List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames) throws SQLException
185

186
// GString support
187
List<List<Object>> executeInsert(GString gstring) throws SQLException
188
List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException
189
```
190

191
## Usage Examples
192

193
### Basic Query Execution
194

195
```groovy
196
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
197

198
// Simple query
199
sql.eachRow("SELECT * FROM users") { row ->
200
    println "User: ${row.name}, Email: ${row.email}"
201
}
202

203
// Parameterized query
204
sql.eachRow("SELECT * FROM users WHERE age > ?", [21]) { row ->
205
    println "Adult user: ${row.name}"
206
}
207

208
// GString query
209
def minAge = 18
210
sql.eachRow("SELECT * FROM users WHERE age > ${minAge}") { row ->
211
    println "User: ${row.name}"
212
}
213
```
214

215
### Insert with Generated Keys
216

217
```groovy
218
// Insert and get generated keys
219
def keys = sql.executeInsert("INSERT INTO users (name, email) VALUES (?, ?)", 
220
                            ["John Doe", "john@example.com"])
221
println "Generated ID: ${keys[0][0]}"
222

223
// Insert with specific key columns
224
def result = sql.executeInsert("INSERT INTO users (name, email) VALUES (?, ?)", 
225
                              ["Jane Doe", "jane@example.com"], 
226
                              ["id"])
227
println "New user ID: ${result[0].id}"
228
```
229

230
### Closure-based Resource Management
231

232
```groovy
233
// Automatic cleanup
234
Sql.withInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver") { sql ->
235
    sql.eachRow("SELECT COUNT(*) as total FROM users") { row ->
236
        println "Total users: ${row.total}"
237
    }
238
    // Connection automatically closed
239
}
240
```
241

242
### Result Processing
243

244
```groovy
245
// Process different result types
246
sql.execute("CALL complex_procedure()") { isResultSet, result ->
247
    if (isResultSet) {
248
        result.each { row ->
249
            println "Result row: ${row}"
250
        }
251
    } else {
252
        println "Update count: ${result}"
253
    }
254
}
255
```
256

257
## Connection and Resource Management
258

259
### Connection Access
260

261
Access the underlying connection and data source:
262

263
```groovy { .api }
264
// Get the underlying Connection
265
Connection getConnection()
266

267
// Get the underlying DataSource (if available)
268
DataSource getDataSource()
269

270
// Get the update count from the last operation
271
int getUpdateCount()
272
```
273

274
### Driver Management
275

276
Utility methods for JDBC driver management:
277

278
```groovy { .api }
279
// Load a JDBC driver class
280
static void loadDriver(String driverClassName) throws ClassNotFoundException
281
```
282

283
## ResultSet Configuration
284

285
Configure ResultSet behavior for queries:
286

287
```groovy { .api }
288
// ResultSet type configuration
289
int getResultSetType()
290
void setResultSetType(int resultSetType)
291

292
// ResultSet concurrency configuration  
293
int getResultSetConcurrency()
294
void setResultSetConcurrency(int resultSetConcurrency)
295

296
// ResultSet holdability configuration
297
int getResultSetHoldability()
298
void setResultSetHoldability(int resultSetHoldability)
299
```
300

301
## Statement Configuration
302

303
Configure statement behavior before execution:
304

305
```groovy { .api }
306
// Configure statement settings like timeout, fetch size, etc.
307
void withStatement(Closure configureStatement)
308
```
309

310
## Query Configuration
311

312
Configure query caching and named query support:
313

314
```groovy { .api }
315
// Named query caching configuration
316
boolean isCacheNamedQueries()
317
void setCacheNamedQueries(boolean cacheNamedQueries)
318

319
// Named query support configuration
320
boolean isEnableNamedQueries()
321
void setEnableNamedQueries(boolean enableNamedQueries)
322
```
323

324
### Usage Example: Connection and Configuration
325

326
```groovy
327
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
328

329
// Configure ResultSet behavior
330
sql.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)
331
sql.setResultSetConcurrency(ResultSet.CONCUR_READ_ONLY)
332

333
// Configure statement behavior
334
sql.withStatement { stmt ->
335
    stmt.setQueryTimeout(30)
336
    stmt.setFetchSize(100)
337
}
338

339
// Access underlying connection
340
Connection conn = sql.getConnection()
341
DatabaseMetaData metadata = conn.getMetaData()
342
println "Database: ${metadata.getDatabaseProductName()}"
343

344
// Load additional drivers if needed
345
Sql.loadDriver("com.mysql.cj.jdbc.Driver")
346
```