0
# Parameter Binding and Types
1

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

4
## Capabilities
5

6
### Parameter Interfaces
7

8
Define typed parameters for stored procedure input/output operations.
9

10
```java { .api }
11
// Input parameter interface
12
public interface InParameter {
13
    int getType();    // JDBC type constant
14
    Object getValue(); // Parameter value
15
}
16

17
// Output parameter interface
18
public interface OutParameter {
19
    int getType();    // JDBC type constant
20
}
21

22
// Bidirectional parameter interface
23
public interface InOutParameter extends InParameter, OutParameter {}
24

25
// ResultSet output parameter interface
26
public interface ResultSetOutParameter extends OutParameter {}
27
```
28

29
### Parameter Factory Methods
30

31
Create typed parameters using static factory methods for all JDBC types.
32

33
```java { .api }
34
// Generic parameter factories
35
public static InParameter in(int type, Object value);
36
public static OutParameter out(int type);
37
public static InOutParameter inout(InParameter in);
38
public static ResultSetOutParameter resultSet(int type);
39

40
// Type-specific InParameter factories
41
public static InParameter ARRAY(Object value);
42
public static InParameter BIGINT(Object value);
43
public static InParameter BINARY(Object value);
44
public static InParameter BIT(Object value);
45
public static InParameter BLOB(Object value);
46
public static InParameter BOOLEAN(Object value);
47
public static InParameter CHAR(Object value);
48
public static InParameter CLOB(Object value);
49
public static InParameter DATE(Object value);
50
public static InParameter DECIMAL(Object value);
51
public static InParameter DOUBLE(Object value);
52
public static InParameter FLOAT(Object value);
53
public static InParameter INTEGER(Object value);
54
public static InParameter LONGVARBINARY(Object value);
55
public static InParameter LONGVARCHAR(Object value);
56
public static InParameter NUMERIC(Object value);
57
public static InParameter OTHER(Object value);
58
public static InParameter REAL(Object value);
59
public static InParameter SMALLINT(Object value);
60
public static InParameter TIME(Object value);
61
public static InParameter TIMESTAMP(Object value);
62
public static InParameter TINYINT(Object value);
63
public static InParameter VARBINARY(Object value);
64
public static InParameter VARCHAR(Object value);
65
```
66

67
### Parameter Type Constants
68

69
Pre-defined OutParameter constants for all JDBC types.
70

71
```java { .api }
72
// OutParameter constants
73
public static final OutParameter ARRAY;
74
public static final OutParameter BIGINT;
75
public static final OutParameter BINARY;
76
public static final OutParameter BIT;
77
public static final OutParameter BLOB;
78
public static final OutParameter BOOLEAN;
79
public static final OutParameter CHAR;
80
public static final OutParameter CLOB;
81
public static final OutParameter DATALINK;
82
public static final OutParameter DATE;
83
public static final OutParameter DECIMAL;
84
public static final OutParameter DISTINCT;
85
public static final OutParameter DOUBLE;
86
public static final OutParameter FLOAT;
87
public static final OutParameter INTEGER;
88
public static final OutParameter JAVA_OBJECT;
89
public static final OutParameter LONGVARBINARY;
90
public static final OutParameter LONGVARCHAR;
91
public static final OutParameter NULL;
92
public static final OutParameter NUMERIC;
93
public static final OutParameter OTHER;
94
public static final OutParameter REAL;
95
public static final OutParameter REF;
96
public static final OutParameter SMALLINT;
97
public static final OutParameter STRUCT;
98
public static final OutParameter TIME;
99
public static final OutParameter TIMESTAMP;
100
public static final OutParameter TINYINT;
101
public static final OutParameter VARBINARY;
102
public static final OutParameter VARCHAR;
103
```
104

105
**Example**:
106
```groovy
107
import static groovy.sql.Sql.*
108

109
// Using type constants for output parameters
110
def result = sql.call('CALL get_user_stats(?, ?)', [userId, INTEGER])
111
println "User count: ${result[0][1]}"
112

113
// Using factory methods for input parameters
114
def params = [
115
    VARCHAR('John'),
116
    INTEGER(25),
117
    TIMESTAMP(new Date()),
118
    BOOLEAN(true)
119
]
120
sql.call('CALL create_user(?, ?, ?, ?)', params)
121

122
// Mixed input/output parameters
123
def mixedParams = [
124
    VARCHAR('admin'),           // input
125
    out(INTEGER),              // output
126
    inout(VARCHAR('pending'))  // input/output
127
]
128
def results = sql.call('CALL process_request(?, ?, ?)', mixedParams)
129
```
130

131
### GString Parameter Binding
132

133
Use Groovy GString expressions for dynamic parameter binding with automatic type inference.
134

135
**Example**:
136
```groovy
137
// Simple GString binding
138
def userId = 123
139
def status = 'active'
140
sql.eachRow("SELECT * FROM users WHERE id = $userId AND status = $status") { row ->
141
    println row.name
142
}
143

144
// Complex expressions
145
def minDate = new Date() - 30
146
def categories = ['electronics', 'books', 'clothing']
147
sql.eachRow("SELECT * FROM products WHERE created_date > $minDate AND category IN (${categories.join(',')})") { row ->
148
    println "${row.name}: ${row.category}"
149
}
150

151
// Null handling
152
def optionalFilter = condition ? someValue : null
153
sql.eachRow("SELECT * FROM items WHERE ${optionalFilter ? "category = $optionalFilter" : '1=1'}") { row ->
154
    println row
155
}
156
```
157

158
### Named Parameter Processing
159

160
Use named parameters with :name syntax for improved readability and maintainability.
161

162
```java { .api }
163
// Named parameter processing (internal)
164
public SqlWithParams checkForNamedParams(String sql, List<?> params);
165
protected SqlWithParams buildSqlWithIndexedProps(String sql);
166

167
// Configuration
168
public boolean isEnableNamedQueries();
169
public void setEnableNamedQueries(boolean enableNamedQueries);
170
```
171

172
**Example**:
173
```groovy
174
// Enable named parameters
175
sql.enableNamedQueries = true
176

177
// Named parameter syntax
178
def params = [
179
    userId: 123,
180
    status: 'active',
181
    minAge: 18,
182
    department: 'Engineering'
183
]
184

185
// Use named parameters in queries
186
sql.eachRow('SELECT * FROM users WHERE id = :userId AND status = :status', params) { row ->
187
    println row.name
188
}
189

190
// Complex named query
191
def searchCriteria = [
192
    firstName: 'John',
193
    lastName: 'Smith',
194
    minSalary: 50000,
195
    departments: ['IT', 'Engineering', 'Sales']
196
]
197

198
sql.eachRow('''
199
    SELECT * FROM employees 
200
    WHERE first_name LIKE :firstName 
201
    AND last_name LIKE :lastName
202
    AND salary >= :minSalary
203
    AND department IN (:departments)
204
''', searchCriteria) { row ->
205
    println "${row.first_name} ${row.last_name} - ${row.department}"
206
}
207

208
// Property-based named parameters (e.g., :user.id, :user.name)
209
def user = [id: 123, name: 'Alice', email: 'alice@example.com']
210
sql.executeUpdate('INSERT INTO users (id, name, email) VALUES (:user.id, :user.name, :user.email)', [user: user])
211
```
212

213
### Variable Expansion
214

215
Control how variables are expanded into SQL strings versus parameter placeholders.
216

217
```java { .api }
218
// Variable expansion interface
219
public interface ExpandedVariable {
220
    Object getObject();
221
}
222

223
// Factory method
224
public static ExpandedVariable expand(Object object);
225
```
226

227
**Example**:
228
```groovy
229
import static groovy.sql.Sql.expand
230

231
// Normal parameter binding (safe from SQL injection)
232
def tableName = 'users'
233
def userId = 123
234
// This won't work - table names can't be parameterized
235
// sql.rows('SELECT * FROM ? WHERE id = ?', [tableName, userId])
236

237
// Use expand() for dynamic SQL parts (be careful with user input!)
238
def safeSql = "SELECT * FROM ${expand(tableName)} WHERE id = ?"
239
sql.eachRow(safeSql, [userId]) { row ->
240
    println row.name
241
}
242

243
// Dynamic ORDER BY clause
244
def sortColumn = 'name'  // validated against allowed columns
245
def sortOrder = 'ASC'    // validated against ASC/DESC
246
def orderBy = "${expand(sortColumn)} ${expand(sortOrder)}"
247
sql.eachRow("SELECT * FROM users ORDER BY $orderBy") { row ->
248
    println row.name
249
}
250

251
// Building dynamic WHERE clauses
252
def buildWhereClause(filters) {
253
    def conditions = []
254
    def params = []
255
    
256
    if (filters.name) {
257
        conditions << 'name LIKE ?'
258
        params << "%${filters.name}%"
259
    }
260
    if (filters.minAge) {
261
        conditions << 'age >= ?'
262
        params << filters.minAge
263
    }
264
    if (filters.department) {
265
        conditions << 'department = ?'
266
        params << filters.department
267
    }
268
    
269
    def whereClause = conditions ? "WHERE ${conditions.join(' AND ')}" : ''
270
    return [whereClause: expand(whereClause), params: params]
271
}
272

273
def filters = [name: 'John', minAge: 25]
274
def query = buildWhereClause(filters)
275
sql.eachRow("SELECT * FROM users ${query.whereClause}", query.params) { row ->
276
    println row
277
}
278
```
279

280
### Type Conversion and Validation
281

282
Handle type conversion and parameter validation for database operations.
283

284
**Example**:
285
```groovy
286
import java.sql.Types
287
import java.sql.Timestamp
288
import java.text.SimpleDateFormat
289

290
// Automatic type conversion examples
291
def params = [
292
    // String to appropriate SQL type
293
    stringParam: 'Hello World',
294
    
295
    // Number conversion
296
    intParam: 42,
297
    longParam: 123456789L,
298
    doubleParam: 3.14159,
299
    bigDecimalParam: new BigDecimal('999.99'),
300
    
301
    // Date/time conversion
302
    dateParam: new Date(),
303
    timestampParam: new Timestamp(System.currentTimeMillis()),
304
    
305
    // Boolean conversion
306
    boolParam: true,
307
    
308
    // Null handling
309
    nullParam: null,
310
    
311
    // Binary data
312
    blobParam: 'binary data'.bytes
313
]
314

315
sql.executeUpdate('''
316
    INSERT INTO test_table (
317
        string_col, int_col, long_col, double_col, decimal_col,
318
        date_col, timestamp_col, bool_col, null_col, blob_col
319
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
320
''', params.values() as List)
321

322
// Custom type handling
323
def customDateFormat = new SimpleDateFormat('yyyy-MM-dd')
324
def dateString = '2024-01-15'
325
def parsedDate = customDateFormat.parse(dateString)
326
sql.executeUpdate('INSERT INTO events (event_date) VALUES (?)', [TIMESTAMP(parsedDate)])
327

328
// Validate parameters before use
329
def validateAndExecute(sql, query, params) {
330
    params.each { param ->
331
        if (param instanceof String && param.contains(';')) {
332
            throw new IllegalArgumentException('Potentially unsafe parameter detected')
333
        }
334
    }
335
    return sql.executeUpdate(query, params)
336
}
337
```