0
# Core Database Operations
1

2
Primary database interaction functionality providing simplified JDBC interface with automatic resource management, connection handling, and basic CRUD operations.
3

4
## Capabilities
5

6
### Connection Management
7

8
Create and manage database connections through various factory methods supporting different connection sources.
9

10
```java { .api }
11
// JDBC URL connections
12
public static Sql newInstance(String url) throws SQLException;
13
public static Sql newInstance(String url, Properties properties) throws SQLException;
14
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException;
15
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException;
16

17
// Credential-based connections
18
public static Sql newInstance(String url, String user, String password) throws SQLException;
19
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException;
20

21
// Map-based configuration
22
public static Sql newInstance(Map<String, Object> args) throws SQLException, ClassNotFoundException;
23

24
// Resource management versions (automatic cleanup)
25
public static void withInstance(String url, Closure c) throws SQLException;
26
public static void withInstance(String url, Properties properties, Closure c) throws SQLException;
27
public static void withInstance(String url, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
28
public static void withInstance(String url, String user, String password, Closure c) throws SQLException;
29
public static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
30
public static void withInstance(String url, Properties properties, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
31
public static void withInstance(Map<String, Object> args, Closure c) throws SQLException, ClassNotFoundException;
32

33
// Direct construction
34
public Sql(DataSource dataSource);
35
public Sql(Connection connection);
36
public Sql(Sql parent);
37

38
// Utility methods
39
public static void loadDriver(String driverClassName) throws ClassNotFoundException;
40
public void close();
41
```
42

43
**Example**:
44
```groovy
45
// Using JDBC URL
46
def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')
47

48
// Using DataSource
49
def dataSource = new BasicDataSource()
50
dataSource.url = 'jdbc:mysql://localhost:3306/mydb'
51
dataSource.username = 'user'
52
dataSource.password = 'pass'
53
def sql = new Sql(dataSource)
54

55
// Using withInstance for automatic cleanup
56
Sql.withInstance('jdbc:h2:mem:testdb', 'sa', '') { sql ->
57
    // Operations here - automatically closed
58
    sql.execute('CREATE TABLE test (id INT)')
59
}
60
```
61

62
### Query Execution
63

64
Execute SELECT queries with various parameter binding options and result processing approaches.
65

66
```java { .api }
67
// Basic query execution with closure processing
68
public void query(String sql, Closure closure) throws SQLException;
69
public void query(String sql, List<?> params, Closure closure) throws SQLException;
70
public void query(String sql, Map params, Closure closure) throws SQLException;
71
public void query(Map params, String sql, Closure closure) throws SQLException;
72
public void query(GString gstring, Closure closure) throws SQLException;
73

74
// Query with metadata callback
75
public void query(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;
76
public void query(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;
77
```
78

79
**Example**:
80
```groovy
81
// Basic query with closure
82
sql.query('SELECT * FROM users') { resultSet ->
83
    while (resultSet.next()) {
84
        println resultSet.getString('name')
85
    }
86
}
87

88
// With parameters
89
sql.query('SELECT * FROM users WHERE age > ?', [25]) { resultSet ->
90
    // Process results
91
}
92

93
// With GString
94
def minAge = 25
95
sql.query("SELECT * FROM users WHERE age > $minAge") { resultSet ->
96
    // Process results  
97
}
98

99
// With metadata
100
sql.query('SELECT * FROM users', 
101
    { meta -> println "Columns: ${meta.columnCount}" },
102
    { resultSet -> /* process rows */ }
103
)
104
```
105

106
### Collection-Style Queries
107

108
Retrieve query results as collections for easier processing with Groovy's collection methods.
109

110
```java { .api }
111
// Get all rows as List<GroovyRowResult>
112
public List<GroovyRowResult> rows(String sql) throws SQLException;
113
public List<GroovyRowResult> rows(String sql, List<?> params) throws SQLException;
114
public List<GroovyRowResult> rows(String sql, Map params) throws SQLException;
115
public List<GroovyRowResult> rows(Map params, String sql) throws SQLException;
116
public List<GroovyRowResult> rows(GString gstring) throws SQLException;
117

118
// With paging
119
public List<GroovyRowResult> rows(String sql, int offset, int maxRows) throws SQLException;
120
public List<GroovyRowResult> rows(String sql, List<?> params, int offset, int maxRows) throws SQLException;
121

122
// With metadata callback
123
public List<GroovyRowResult> rows(String sql, Closure metaClosure) throws SQLException;
124
public List<GroovyRowResult> rows(String sql, List<?> params, Closure metaClosure) throws SQLException;
125

126
// Get single row
127
public GroovyRowResult firstRow(String sql) throws SQLException;
128
public GroovyRowResult firstRow(String sql, List<?> params) throws SQLException;
129
public GroovyRowResult firstRow(String sql, Map params) throws SQLException;
130
public GroovyRowResult firstRow(Map params, String sql) throws SQLException;
131
public GroovyRowResult firstRow(GString gstring) throws SQLException;
132
```
133

134
**Example**:
135
```groovy
136
// Get all rows
137
def users = sql.rows('SELECT * FROM users')
138
users.each { user ->
139
    println "${user.name}: ${user.email}"
140
}
141

142
// With parameters
143
def activeUsers = sql.rows('SELECT * FROM users WHERE active = ?', [true])
144

145
// With paging (offset=10, maxRows=5)
146
def page = sql.rows('SELECT * FROM users ORDER BY name', 10, 5)
147

148
// Single row
149
def user = sql.firstRow('SELECT * FROM users WHERE id = ?', [1])
150
if (user) {
151
    println "Found: ${user.name}"
152
}
153

154
// Using collection methods
155
def emails = sql.rows('SELECT * FROM users')
156
                .findAll { it.active }
157
                .collect { it.email }
158
```
159

160
### Data Manipulation
161

162
Execute INSERT, UPDATE, DELETE operations with various parameter binding options.
163

164
```java { .api }
165
// General execution
166
public boolean execute(String sql) throws SQLException;
167
public boolean execute(String sql, List<?> params) throws SQLException;
168
public boolean execute(String sql, Object[] params) throws SQLException;
169
public boolean execute(String sql, Map params) throws SQLException;
170
public boolean execute(Map params, String sql) throws SQLException;
171
public boolean execute(GString gstring) throws SQLException;
172

173
// Execute with result processing
174
public void execute(String sql, Closure resultClosure) throws SQLException;
175
public void execute(String sql, List<?> params, Closure resultClosure) throws SQLException;
176
public void execute(String sql, List<?> params, Closure metaClosure, Closure resultClosure) throws SQLException;
177
public void execute(String sql, Map params, Closure resultClosure) throws SQLException;
178
public void execute(Map params, String sql, Closure resultClosure) throws SQLException;
179
public void execute(String sql, Map params, Closure metaClosure, Closure resultClosure) throws SQLException;
180
public void execute(Map params, String sql, Closure metaClosure, Closure resultClosure) throws SQLException;
181

182
// Update operations (returns row count)
183
public int executeUpdate(String sql) throws SQLException;
184
public int executeUpdate(String sql, List<?> params) throws SQLException;
185
public int executeUpdate(String sql, Object[] params) throws SQLException;
186
public int executeUpdate(String sql, Map params) throws SQLException;
187
public int executeUpdate(Map params, String sql) throws SQLException;
188
public int executeUpdate(GString gstring) throws SQLException;
189

190
// Insert with generated keys
191
public List<List<Object>> executeInsert(String sql) throws SQLException;
192
public List<List<Object>> executeInsert(String sql, List<?> params) throws SQLException;
193
public List<List<Object>> executeInsert(String sql, Object[] params) throws SQLException;
194
public List<GroovyRowResult> executeInsert(String sql, List<?> params, List<String> keyColumnNames) throws SQLException;
195
public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames) throws SQLException;
196
public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException;
197
public List<List<Object>> executeInsert(String sql, Map params) throws SQLException;
198
public List<List<Object>> executeInsert(Map params, String sql) throws SQLException;
199
public List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames) throws SQLException;
200
public List<List<Object>> executeInsert(GString gstring) throws SQLException;
201
public List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException;
202
```
203

204
**Example**:
205
```groovy
206
// DDL execution
207
sql.execute '''
208
    CREATE TABLE products (
209
        id INTEGER PRIMARY KEY,
210
        name VARCHAR(100),
211
        price DECIMAL(10,2)
212
    )
213
'''
214

215
// Insert with parameters
216
def rowsAffected = sql.executeUpdate(
217
    'INSERT INTO products (name, price) VALUES (?, ?)', 
218
    ['Laptop', 999.99]
219
)
220

221
// Insert with GString
222
def name = 'Phone'
223
def price = 699.99
224
sql.executeUpdate "INSERT INTO products (name, price) VALUES ($name, $price)"
225

226
// Insert with generated keys
227
def keys = sql.executeInsert(
228
    'INSERT INTO products (name, price) VALUES (?, ?)',
229
    ['Tablet', 499.99]
230
)
231
println "Generated key: ${keys[0][0]}"
232

233
// Update
234
def updated = sql.executeUpdate(
235
    'UPDATE products SET price = ? WHERE name = ?',
236
    [899.99, 'Laptop']
237
)
238
println "Updated $updated rows"
239

240
// Delete
241
def deleted = sql.executeUpdate(
242
    'DELETE FROM products WHERE price < ?',
243
    [500.00]
244
)
245
```
246

247
### Resource Management
248

249
Manage database resources with automatic cleanup and connection caching.
250

251
```java { .api }
252
// Resource cleanup
253
public void close() throws SQLException;
254

255
// Connection caching
256
public Object cacheConnection(Closure closure) throws SQLException;
257

258
// Statement caching and configuration
259
public Object cacheStatements(Closure closure) throws SQLException;
260
public Object withStatement(Closure configureStatement) throws SQLException;
261
public Object withCleanupStatement(Closure cleanupStatement) throws SQLException;
262

263
// Configuration
264
public boolean isCacheStatements();
265
public void setCacheStatements(boolean cacheStatements);
266
public boolean isCacheNamedQueries();
267
public void setCacheNamedQueries(boolean cacheNamedQueries);
268
public boolean isEnableNamedQueries();
269
public void setEnableNamedQueries(boolean enableNamedQueries);
270

271
// Access underlying resources
272
public Connection getConnection() throws SQLException;
273
public DataSource getDataSource();
274
public int getUpdateCount();
275
```
276

277
**Example**:
278
```groovy
279
// Manual resource management
280
def sql = Sql.newInstance(url, user, pass, driver)
281
try {
282
    // Database operations
283
    sql.execute('CREATE TABLE temp (id INT)')
284
} finally {
285
    sql.close()
286
}
287

288
// Automatic resource management with caching
289
sql.cacheConnection { 
290
    // Multiple operations share same connection
291
    sql.execute('INSERT INTO users VALUES (1, "Alice")')
292
    sql.execute('INSERT INTO users VALUES (2, "Bob")')
293
    def users = sql.rows('SELECT * FROM users')
294
    // Connection automatically closed after block
295
}
296

297
// Statement configuration
298
sql.withStatement { stmt ->
299
    stmt.queryTimeout = 30
300
    stmt.fetchSize = 1000
301
} {
302
    // Operations use configured statement
303
    def results = sql.rows('SELECT * FROM large_table')
304
}
305
```