0
# Groovy SQL
1

2
Groovy SQL provides a higher-level abstraction over Java's JDBC technology for relational database operations. It features a fluent facade that greatly simplifies connection management, statement execution, and result set processing while offering Groovy-specific enhancements like GString parameter binding, closure-based iteration, and treating result sets as collections of maps.
3

4
## Package Information
5

6
- **Package Name**: groovy-sql
7
- **Package Type**: Maven (JAR)
8
- **Language**: Java/Groovy
9
- **Installation**: 
10
  ```xml
11
  <dependency>
12
    <groupId>org.apache.groovy</groupId>
13
    <artifactId>groovy-sql</artifactId>
14
    <version>5.0.0</version>
15
  </dependency>
16
  ```
17
- **Gradle**: `implementation 'org.apache.groovy:groovy-sql:5.0.0'`
18

19
## Core Imports
20

21
```java
22
import groovy.sql.Sql;
23
import groovy.sql.DataSet;
24
import groovy.sql.GroovyRowResult;
25
```
26

27
For parameter types:
28
```java
29
import groovy.sql.InParameter;
30
import groovy.sql.OutParameter;
31
import groovy.sql.InOutParameter;
32
```
33

34
## Basic Usage
35

36
```groovy
37
import groovy.sql.Sql
38

39
// Method 1: Manual connection management
40
def sql = Sql.newInstance('jdbc:h2:mem:testdb', 
41
                         'sa', '', 
42
                         'org.h2.Driver')
43

44
// Create table
45
sql.execute '''
46
    CREATE TABLE users (
47
        id INTEGER PRIMARY KEY,
48
        name VARCHAR(50),
49
        email VARCHAR(100)
50
    )
51
'''
52

53
// Insert data using GString syntax
54
def name = 'Alice'
55
def email = 'alice@example.com'
56
sql.execute "INSERT INTO users (id, name, email) VALUES (1, $name, $email)"
57

58
// Query with closure iteration
59
sql.eachRow('SELECT * FROM users') { row ->
60
    println "${row.name}: ${row.email}"
61
}
62

63
// Clean up
64
sql.close()
65

66
// Method 2: Automatic resource management (recommended)
67
Sql.withInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver') { sql ->
68
    // Create table
69
    sql.execute '''
70
        CREATE TABLE products (
71
            id INTEGER PRIMARY KEY,
72
            name VARCHAR(50),
73
            price DECIMAL(10,2)
74
        )
75
    '''
76
    
77
    // Insert with parameters
78
    sql.execute 'INSERT INTO products (id, name, price) VALUES (?, ?, ?)', 
79
                [1, 'Laptop', 999.99]
80
    
81
    // Get all rows as collection
82
    def products = sql.rows('SELECT * FROM products WHERE price > ?', [500])
83
    products.each { product ->
84
        println "${product.name}: \$${product.price}"
85
    }
86
    // Connection automatically closed
87
}
88
```
89

90
## Architecture
91

92
Groovy SQL is built around several key components:
93

94
- **Sql Class**: Main facade providing simplified JDBC interface with automatic resource management
95
- **Connection Management**: Factory methods for DataSource, Connection, or JDBC URL-based connections
96
- **Parameter Binding**: Support for positional (?), named (:name), and GString ($variable) parameters  
97
- **Result Processing**: GroovyRowResult provides Map-like access to database rows with property-style column access
98
- **Transaction Support**: Automatic transaction management with rollback capabilities
99
- **Batch Operations**: Automatic batching with configurable batch sizes for improved performance
100
- **DataSet API**: POJO-style database table access with filtering and sorting capabilities
101

102
## Capabilities
103

104
### Core Database Operations
105

106
Primary database interaction functionality including connection management, query execution, data manipulation, and transaction handling. This forms the foundation of all database operations.
107

108
```java { .api }
109
// Connection factory methods
110
public static Sql newInstance(String url) throws SQLException;
111
public static Sql newInstance(String url, Properties properties) throws SQLException;
112
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException;
113
public static Sql newInstance(String url, String user, String password) throws SQLException;
114
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException;
115
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException;
116
public static Sql newInstance(Map<String, Object> args) throws SQLException, ClassNotFoundException;
117

118
// Resource management factory methods (automatic cleanup)
119
public static void withInstance(String url, Closure c) throws SQLException;
120
public static void withInstance(String url, Properties properties, Closure c) throws SQLException;
121
public static void withInstance(String url, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
122
public static void withInstance(String url, String user, String password, Closure c) throws SQLException;
123
public static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
124
public static void withInstance(String url, Properties properties, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
125
public static void withInstance(Map<String, Object> args, Closure c) throws SQLException, ClassNotFoundException;
126

127
// Constructors  
128
public Sql(DataSource dataSource);
129
public Sql(Connection connection);
130
public Sql(Sql parent);
131

132
// Query execution
133
public void query(String sql, Closure closure) throws SQLException;
134
public void query(String sql, List<?> params, Closure closure) throws SQLException;
135
public void query(String sql, Map params, Closure closure) throws SQLException;
136
public void query(GString gstring, Closure closure) throws SQLException;
137
public List<GroovyRowResult> rows(String sql) throws SQLException;
138
public List<GroovyRowResult> rows(String sql, List<?> params) throws SQLException;
139
public List<GroovyRowResult> rows(String sql, Map params) throws SQLException;
140
public List<GroovyRowResult> rows(GString gstring) throws SQLException;
141
public GroovyRowResult firstRow(String sql) throws SQLException;
142
public GroovyRowResult firstRow(String sql, List<?> params) throws SQLException;
143
public GroovyRowResult firstRow(GString gstring) throws SQLException;
144

145
// Data manipulation
146
public boolean execute(String sql) throws SQLException;
147
public boolean execute(String sql, List<?> params) throws SQLException;
148
public boolean execute(String sql, Map params) throws SQLException;
149
public boolean execute(GString gstring) throws SQLException;
150
public int executeUpdate(String sql) throws SQLException;
151
public int executeUpdate(String sql, List<?> params) throws SQLException;
152
public int executeUpdate(String sql, Map params) throws SQLException;
153
public int executeUpdate(GString gstring) throws SQLException;
154
public List<List<Object>> executeInsert(String sql) throws SQLException;
155
public List<List<Object>> executeInsert(String sql, List<?> params) throws SQLException;
156
public List<List<Object>> executeInsert(String sql, Map params) throws SQLException;
157
public List<List<Object>> executeInsert(GString gstring) throws SQLException;
158

159
// Utility methods
160
public static void loadDriver(String driverClassName) throws ClassNotFoundException;
161
public void close();
162
```
163

164
[Core Operations](./core-operations.md)
165

166
### Result Processing and Row Handling
167

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

170
```java { .api }
171
public void eachRow(String sql, Closure closure) throws SQLException;
172
public void eachRow(String sql, List<?> params, Closure closure) throws SQLException;
173
public void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException;
174
public void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;
175

176
// GroovyRowResult - represents a database row
177
public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
178
    public Object getProperty(String property);
179
    public Object getAt(int index);
180
    public Object get(Object property);
181
    public int size();
182
    public Set<String> keySet();
183
    public Collection<Object> values();
184
}
185
```
186

187
[Result Processing](./result-processing.md)
188

189
### Transaction Management
190

191
Comprehensive transaction support with automatic resource management, rollback capabilities, and connection caching for improved performance.
192

193
```java { .api }
194
public void withTransaction(Closure closure) throws SQLException;
195
public void commit() throws SQLException;
196
public void rollback() throws SQLException;
197
public void cacheConnection(Closure closure) throws SQLException;
198

199
// Transaction properties
200
public int getResultSetType();
201
public void setResultSetType(int resultSetType);
202
public int getResultSetConcurrency();
203
public void setResultSetConcurrency(int resultSetConcurrency);
204
public int getResultSetHoldability();
205
public void setResultSetHoldability(int resultSetHoldability);
206
```
207

208
[Transaction Management](./transactions.md)
209

210
### Parameter Binding and Types
211

212
Advanced parameter binding with typed parameters for stored procedures, GString expression support, and named parameter processing.
213

214
```java { .api }
215
// Parameter interfaces
216
public interface InParameter {
217
    int getType();
218
    Object getValue();
219
}
220

221
public interface OutParameter {
222
    int getType();
223
}
224

225
public interface InOutParameter extends InParameter, OutParameter {}
226

227
// Parameter factory methods
228
public static InParameter in(int type, Object value);
229
public static OutParameter out(int type);
230
public static InOutParameter inout(InParameter in);
231
public static ResultSetOutParameter resultSet(int type);
232

233
// Type constants
234
public static final OutParameter INTEGER;
235
public static final OutParameter VARCHAR;
236
public static final OutParameter TIMESTAMP;
237
// ... all JDBC types available
238
```
239

240
[Parameters and Types](./parameters.md)
241

242
### Stored Procedures
243

244
Complete stored procedure support with input/output parameters, result set processing, and CallableStatement wrapper functionality.
245

246
```java { .api }
247
public int call(String sql) throws SQLException;
248
public int call(String sql, List<?> params) throws SQLException;
249
public int call(GString gstring) throws SQLException;
250
public void call(String sql, List<?> params, Closure closure) throws SQLException;
251
public void call(GString gstring, Closure closure) throws SQLException;
252
public List<GroovyRowResult> callWithRows(String sql, List<?> params, Closure closure) throws SQLException;
253
public List<GroovyRowResult> callWithRows(GString gstring, Closure closure) throws SQLException;
254
public List<List<GroovyRowResult>> callWithAllRows(String sql, List<?> params, Closure closure) throws SQLException;
255
public List<List<GroovyRowResult>> callWithAllRows(GString gstring, Closure closure) throws SQLException;
256
```
257

258
[Stored Procedures](./stored-procedures.md)
259

260
### Batch Operations
261

262
High-performance batch processing with automatic batching, configurable batch sizes, and support for both Statement and PreparedStatement batching.
263

264
```java { .api }
265
public int[] withBatch(Closure closure) throws SQLException;
266
public int[] withBatch(int batchSize, Closure closure) throws SQLException;
267
public int[] withBatch(String sql, Closure closure) throws SQLException;
268
public int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException;
269
public boolean isWithinBatch();
270

271
// Batch wrapper classes
272
public class BatchingStatementWrapper extends GroovyObjectSupport implements AutoCloseable {
273
    public void addBatch(String sql) throws SQLException;
274
    public int[] executeBatch() throws SQLException;
275
    public void clearBatch() throws SQLException;
276
}
277

278
public class BatchingPreparedStatementWrapper extends BatchingStatementWrapper {
279
    public void addBatch(Object[] parameters) throws SQLException;
280
    public void addBatch(List<Object> parameters) throws SQLException;
281
}
282
```
283

284
[Batch Operations](./batch-operations.md)
285

286
### DataSet API
287

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

290
```java { .api }
291
public class DataSet extends Sql {
292
    public DataSet(Sql sql, String table);
293
    public DataSet(Sql sql, Class type);
294
    
295
    public void add(Map<String, Object> map) throws SQLException;
296
    public DataSet findAll(Closure where);
297
    public DataSet sort(Closure sort);
298
    public DataSet reverse();
299
    public DataSet createView(Closure criteria);
300
    
301
    public void each(Closure closure) throws SQLException;
302
    public void each(int offset, int maxRows, Closure closure) throws SQLException;
303
    public List<GroovyRowResult> rows() throws SQLException;
304
    public List<GroovyRowResult> rows(int offset, int maxRows) throws SQLException;
305
    public GroovyRowResult firstRow() throws SQLException;
306
}
307

308
// DataSet factory methods in Sql class
309
public DataSet dataSet(String table);
310
public DataSet dataSet(Class<?> type);
311
```
312

313
[DataSet API](./dataset.md)
314

315
## Types
316

317
```java { .api }
318
// Core result type
319
public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
320
    public Object getProperty(String property);
321
    public Object getAt(int index);
322
    public Object get(Object property);
323
    public Object put(String key, Object value);
324
    public boolean containsKey(Object key);
325
    public boolean containsValue(Object value);
326
    public int size();
327
    public boolean isEmpty();
328
    public Set<String> keySet();
329
    public Collection<Object> values();
330
    public Set<Map.Entry<String, Object>> entrySet();
331
}
332

333
// Enhanced ResultSet interface
334
public interface GroovyResultSet extends GroovyObject, ResultSet {
335
    Object getAt(int index);
336
    Object getAt(String columnName);
337
    void putAt(int index, Object newValue);
338
    void putAt(String columnName, Object newValue);
339
    void add(Map values);
340
    void eachRow(Closure closure);
341
}
342

343
// Internal parameter holder
344
public class SqlWithParams {
345
    public String getSql();
346
    public List<Object> getParams();
347
}
348

349
// Variable expansion interface
350
public interface ExpandedVariable {
351
    Object getObject();
352
}
353

354
// Additional important properties and configuration methods
355
public DataSource getDataSource();
356
public Connection getConnection();
357
public int getUpdateCount();
358
public boolean isCacheStatements();
359
public void setCacheStatements(boolean cacheStatements);
360
public boolean isCacheNamedQueries();
361
public void setCacheNamedQueries(boolean cacheNamedQueries);
362
public boolean isEnableNamedQueries();
363
public void setEnableNamedQueries(boolean enableNamedQueries);
364

365
// Statement configuration
366
public void withStatement(Closure configureStatement);
367
public void withCleanupStatement(Closure cleanupStatement);
368
public void cacheStatements(Closure closure) throws SQLException;
369

370
// Static parameter factory methods
371
public static InParameter in(int type, Object value);
372
public static OutParameter out(int type);
373
public static InOutParameter inout(InParameter in);
374
public static ResultSetOutParameter resultSet(int type);
375
public static ExpandedVariable expand(Object object);
376
```