0
# Groovy SQL
1

2
Groovy SQL is a facade over Java's JDBC APIs that provides greatly simplified resource management and result set handling. It features closures for result set iteration, GString syntax for prepared statements, and treats result sets like collections of maps with normal Groovy collection methods available.
3

4
## Package Information
5

6
- **Package Name**: groovy-sql
7
- **Package Type**: maven
8
- **Language**: Java/Groovy
9
- **Installation**: `implementation 'org.codehaus.groovy:groovy-sql:3.0.25'` (Gradle) or `<dependency><groupId>org.codehaus.groovy</groupId><artifactId>groovy-sql</artifactId><version>3.0.25</version></dependency>` (Maven)
10
- **Group ID**: org.codehaus.groovy
11
- **Artifact ID**: groovy-sql
12

13
## Core Imports
14

15
```groovy
16
import groovy.sql.Sql
17
import groovy.sql.DataSet
18
import groovy.sql.GroovyRowResult
19
import static org.apache.groovy.sql.extensions.SqlExtensions.*
20
```
21

22
For Java usage:
23

24
```java
25
import groovy.sql.Sql;
26
import groovy.sql.DataSet;
27
import groovy.sql.GroovyRowResult;
28
import static org.apache.groovy.sql.extensions.SqlExtensions.*;
29
```
30

31
## Basic Usage
32

33
```groovy
34
// Create connection
35
def sql = Sql.newInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver")
36

37
// Create table
38
sql.execute '''
39
    CREATE TABLE person (
40
        id INTEGER PRIMARY KEY,
41
        name VARCHAR(50),
42
        age INTEGER
43
    )
44
'''
45

46
// Insert data
47
sql.execute "INSERT INTO person (id, name, age) VALUES (?, ?, ?)", [1, "Alice", 30]
48

49
// Query with closure iteration
50
sql.eachRow("SELECT * FROM person WHERE age > ?", [25]) { row ->
51
    println "Found: ${row.name}, age ${row.age}"
52
}
53

54
// Get all rows as list
55
def people = sql.rows("SELECT * FROM person")
56
people.each { person ->
57
    println "${person.name} is ${person.age} years old"
58
}
59

60
// Close connection
61
sql.close()
62
```
63

64
## Architecture
65

66
Groovy SQL is built around several key components:
67

68
- **Sql Class**: Main facade providing simplified database operations with automatic resource management
69
- **DataSet**: Enhanced query interface supporting POGO field-based filtering and sorting
70
- **GroovyRowResult**: Map-like wrapper for result rows with case-insensitive column access
71
- **Parameter Types**: Typed parameter system for stored procedures (InParameter, OutParameter, InOutParameter)
72
- **Batch Processing**: Automatic statement batching with configurable batch sizes
73
- **Transaction Support**: Declarative transaction management using closures
74

75
## Capabilities
76

77
### Core Database Operations
78

79
Primary database operations including queries, updates, inserts, and stored procedure calls. Provides multiple execution patterns from simple SQL strings to parameterized GString queries.
80

81
```groovy { .api }
82
class Sql implements AutoCloseable {
83
    // Factory methods
84
    static Sql newInstance(String url, String user, String password, String driverClassName)
85
    static void withInstance(String url, String user, String password, String driverClassName, Closure c)
86
    
87
    // Core query methods
88
    void eachRow(String sql, Closure closure)
89
    void eachRow(String sql, List<Object> params, Closure closure)
90
    List<GroovyRowResult> rows(String sql)
91
    List<GroovyRowResult> rows(String sql, List<Object> params)
92
    GroovyRowResult firstRow(String sql)
93
    
94
    // Execution methods
95
    boolean execute(String sql)
96
    boolean execute(String sql, List<Object> params)
97
    int executeUpdate(String sql, List<Object> params)
98
    List<List<Object>> executeInsert(String sql, List<Object> params)
99
}
100
```
101

102
[Core Database Operations](./core-operations.md)
103

104
### DataSet Operations
105

106
Enhanced database operations using POGO (Plain Old Groovy Object) field-based queries. Provides fluent API for filtering, sorting, and data manipulation with closure-based syntax.
107

108
```groovy { .api }
109
class DataSet extends Sql {
110
    DataSet(Sql sql, String table)
111
    DataSet(Sql sql, Class type)
112
    
113
    void add(Map<String, Object> map)
114
    DataSet findAll(Closure where)
115
    DataSet sort(Closure sort)
116
    void each(Closure closure)
117
    List rows()
118
    Object firstRow()
119
}
120
```
121

122
[DataSet Operations](./dataset-operations.md)
123

124
### Result Handling
125

126
Specialized result handling classes that provide Groovy-friendly access to database results. Includes map-like row access and enhanced ResultSet functionality.
127

128
```groovy { .api }
129
class GroovyRowResult implements Map<String, Object> {
130
    Object getProperty(String property)
131
    Object getAt(int index)
132
    // Standard Map interface methods
133
}
134

135
interface GroovyResultSet extends ResultSet, GroovyObject {
136
    Object getAt(int index)
137
    Object getAt(String columnName)
138
    void putAt(int index, Object newValue)
139
    void eachRow(Closure closure)
140
}
141
```
142

143
[Result Handling](./result-handling.md)
144

145
### Parameter Types and Stored Procedures
146

147
Type-safe parameter system for stored procedure calls supporting input, output, and input/output parameters with proper JDBC type mapping.
148

149
```groovy { .api }
150
// Static parameter factory methods
151
static InParameter in(int type, Object value)
152
static OutParameter out(int type)
153
static InOutParameter inout(InParameter in)
154
static ResultSetOutParameter resultSet(int type)
155

156
// Typed parameter constants
157
static final OutParameter INTEGER, VARCHAR, TIMESTAMP, BLOB // ... and others
158

159
// Stored procedure calls
160
int call(String sql, List<Object> params)
161
void call(String sql, List<Object> params, Closure closure)
162
List<GroovyRowResult> callWithRows(String sql, List<Object> params, Closure closure)
163
```
164

165
[Parameter Types and Stored Procedures](./parameters-procedures.md)
166

167
### Batch Processing and Transactions
168

169
Advanced features for batch processing multiple statements and declarative transaction management with automatic rollback on exceptions.
170

171
```groovy { .api }
172
// Batch processing
173
int[] withBatch(Closure closure)
174
int[] withBatch(int batchSize, Closure closure)
175
int[] withBatch(String sql, Closure closure)
176

177
// Transaction management
178
void withTransaction(Closure closure)
179
void commit()
180
void rollback()
181

182
// Connection caching
183
void cacheConnection(Closure closure)
184
void cacheStatements(Closure closure)
185
```
186

187
[Batch Processing and Transactions](./batch-transactions.md)
188

189
## Types
190

191
```groovy { .api }
192
interface InParameter {
193
    int getType()
194
    Object getValue()
195
}
196

197
interface OutParameter {
198
    int getType()
199
}
200

201
interface InOutParameter extends InParameter, OutParameter {
202
}
203

204
interface ResultSetOutParameter extends OutParameter {
205
}
206

207
interface ExpandedVariable {
208
    Object getObject()
209
}
210

211
class SqlWithParams {
212
    SqlWithParams(String sql, List<Object> params)
213
    String getSql()
214
    List<Object> getParams()
215
}
216

217
class BatchingStatementWrapper implements AutoCloseable {
218
    void addBatch(String sql)
219
    void clearBatch()
220
    int[] executeBatch()
221
    void close()
222
}
223

224
class BatchingPreparedStatementWrapper extends BatchingStatementWrapper {
225
    void addBatch(Object[] parameters)
226
    void addBatch(List<Object> parameters)
227
}
228

229
// Extension methods from org.apache.groovy.sql.extensions.SqlExtensions
230
static GroovyRowResult toRowResult(ResultSet rs) throws SQLException
231
static Timestamp toTimestamp(Date d)
232
static boolean asBoolean(GroovyResultSet grs)
233
static Iterator<ResultSetMetaDataWrapper> iterator(ResultSetMetaData resultSetMetaData)
234
```