0
# Named Parameter Operations
1

2
Named parameter support in Spring JDBC allows using descriptive parameter names instead of positional placeholders, improving SQL readability and maintainability. The `NamedParameterJdbcTemplate` provides all the functionality of `JdbcTemplate` while supporting named parameters through `:paramName` syntax.
3

4
## Capabilities
5

6
### NamedParameterJdbcTemplate Construction
7

8
Creates template instances that support named parameter binding using Maps or SqlParameterSource implementations.
9

10
```java { .api }
11
/**
12
 * Template class supporting named parameters in SQL statements
13
 * Uses :paramName syntax instead of ? placeholders
14
 */
15
public class NamedParameterJdbcTemplate implements NamedParameterJdbcOperations {
16
    /** Create template with DataSource */
17
    public NamedParameterJdbcTemplate(DataSource dataSource);
18
    
19
    /** Create template wrapping existing JdbcOperations */
20
    public NamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate);
21
    
22
    /** Get underlying JdbcOperations instance */
23
    public JdbcOperations getJdbcOperations();
24
}
25
```
26

27
**Usage Examples:**
28

29
```java
30
// Create from DataSource
31
DataSource dataSource = // ... obtain DataSource
32
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(dataSource);
33

34
// Create from existing JdbcTemplate
35
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
36
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
37
```
38

39
### Query Operations with Named Parameters
40

41
Query methods accepting parameter Maps or SqlParameterSource instances for named parameter binding.
42

43
```java { .api }
44
// Single value queries
45
public <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType);
46
public <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType);
47

48
// Row mapping queries
49
public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper);
50
public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper);
51
public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper);
52
public <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper);
53

54
// Map-based results
55
public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap);
56
public Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource);
57
public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap);
58
public List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource);
59

60
// ResultSet extraction
61
public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse);
62
public <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse);
63
```
64

65
**Usage Examples:**
66

67
```java
68
// Query with Map parameters
69
Map<String, Object> params = Map.of(
70
    "department", "Engineering",
71
    "minSalary", 50000,
72
    "active", true
73
);
74

75
List<User> users = namedTemplate.query(
76
    "SELECT id, name, email FROM users WHERE department = :department " +
77
    "AND salary >= :minSalary AND active = :active",
78
    params,
79
    (rs, rowNum) -> new User(
80
        rs.getLong("id"),
81
        rs.getString("name"),
82
        rs.getString("email")
83
    )
84
);
85

86
// Single value query
87
Integer count = namedTemplate.queryForObject(
88
    "SELECT COUNT(*) FROM orders WHERE user_id = :userId AND status = :status",
89
    Map.of("userId", 123, "status", "COMPLETED"),
90
    Integer.class
91
);
92
```
93

94
### Update Operations with Named Parameters
95

96
Update methods supporting named parameter binding for INSERT, UPDATE, DELETE operations.
97

98
```java { .api }
99
// Single updates
100
public int update(String sql, Map<String, ?> paramMap);
101
public int update(String sql, SqlParameterSource paramSource);
102
public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder);
103
public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, 
104
                 String[] keyColumnNames);
105

106
// Batch updates
107
public int[] batchUpdate(String sql, Map<String, ?>[] batchValues);
108
public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs);
109
```
110

111
**Usage Examples:**
112

113
```java
114
// Update with Map parameters
115
Map<String, Object> params = Map.of(
116
    "name", "John Updated",
117
    "email", "john.updated@example.com",
118
    "userId", 123
119
);
120

121
int rowsUpdated = namedTemplate.update(
122
    "UPDATE users SET name = :name, email = :email WHERE id = :userId",
123
    params
124
);
125

126
// Insert with key generation
127
MapSqlParameterSource paramSource = new MapSqlParameterSource()
128
    .addValue("name", "Jane Doe")
129
    .addValue("email", "jane@example.com")
130
    .addValue("department", "Marketing");
131

132
KeyHolder keyHolder = new GeneratedKeyHolder();
133
namedTemplate.update(
134
    "INSERT INTO users (name, email, department) VALUES (:name, :email, :department)",
135
    paramSource,
136
    keyHolder,
137
    new String[]{"id"}
138
);
139
Long newUserId = keyHolder.getKey().longValue();
140

141
// Batch update
142
List<Map<String, Object>> batchParams = List.of(
143
    Map.of("name", "Alice", "email", "alice@example.com"),
144
    Map.of("name", "Bob", "email", "bob@example.com"),
145
    Map.of("name", "Charlie", "email", "charlie@example.com")
146
);
147

148
int[] updateCounts = namedTemplate.batchUpdate(
149
    "INSERT INTO users (name, email) VALUES (:name, :email)",
150
    batchParams.toArray(new Map[0])
151
);
152
```
153

154
### SqlParameterSource Implementations
155

156
Classes providing named parameter values with additional type information and validation.
157

158
```java { .api }
159
/**
160
 * Abstract base class for SqlParameterSource implementations
161
 */
162
public abstract class AbstractSqlParameterSource implements SqlParameterSource {
163
    public void registerSqlType(String paramName, int sqlType);
164
    public void registerTypeName(String paramName, String typeName);
165
    public int getSqlType(String paramName);
166
    public String getTypeName(String paramName);
167
}
168

169
/**
170
 * SqlParameterSource backed by a Map with fluent API
171
 */
172
public class MapSqlParameterSource extends AbstractSqlParameterSource {
173
    public MapSqlParameterSource();
174
    public MapSqlParameterSource(String paramName, Object value);
175
    
176
    public MapSqlParameterSource addValue(String paramName, Object value);
177
    public MapSqlParameterSource addValue(String paramName, Object value, int sqlType);
178
    public MapSqlParameterSource addValue(String paramName, Object value, int sqlType, String typeName);
179
    public MapSqlParameterSource addValues(Map<String, ?> values);
180
}
181

182
/**
183
 * SqlParameterSource using JavaBean properties
184
 */
185
public class BeanPropertySqlParameterSource extends AbstractSqlParameterSource {
186
    public BeanPropertySqlParameterSource(Object object);
187
    public boolean hasValue(String paramName);
188
    public Object getValue(String paramName);
189
}
190

191
/**
192
 * Empty SqlParameterSource for parameterless queries
193
 */
194
public class EmptySqlParameterSource implements SqlParameterSource {
195
    public static final EmptySqlParameterSource INSTANCE;
196
}
197
```
198

199
**Usage Examples:**
200

201
```java
202
// MapSqlParameterSource with fluent API
203
MapSqlParameterSource params = new MapSqlParameterSource()
204
    .addValue("name", "John Doe")
205
    .addValue("email", "john@example.com")
206
    .addValue("birthDate", LocalDate.of(1990, 1, 15), Types.DATE)
207
    .addValue("salary", new BigDecimal("75000.00"), Types.DECIMAL);
208

209
User user = namedTemplate.queryForObject(
210
    "SELECT * FROM users WHERE name = :name AND email = :email",
211
    params,
212
    new BeanPropertyRowMapper<>(User.class)
213
);
214

215
// BeanPropertySqlParameterSource from object
216
User newUser = new User("Jane Smith", "jane@example.com", "Engineering");
217
BeanPropertySqlParameterSource beanParams = new BeanPropertySqlParameterSource(newUser);
218

219
namedTemplate.update(
220
    "INSERT INTO users (name, email, department) VALUES (:name, :email, :department)",
221
    beanParams
222
);
223

224
// Batch operations with SqlParameterSource
225
List<User> users = Arrays.asList(
226
    new User("Alice", "alice@example.com"),
227
    new User("Bob", "bob@example.com"),
228
    new User("Charlie", "charlie@example.com")
229
);
230

231
SqlParameterSource[] batchParams = users.stream()
232
    .map(BeanPropertySqlParameterSource::new)
233
    .toArray(SqlParameterSource[]::new);
234

235
namedTemplate.batchUpdate(
236
    "INSERT INTO users (name, email) VALUES (:name, :email)",
237
    batchParams
238
);
239
```
240

241
### Parameter Parsing and Utilities
242

243
Utility classes for named parameter processing and SQL parsing.
244

245
```java { .api }
246
/**
247
 * Utility methods for named parameter processing
248
 */
249
public abstract class NamedParameterUtils {
250
    public static ParsedSql parseSqlStatement(String sql);
251
    public static String substituteNamedParameters(String sql, SqlParameterSource paramSource);
252
    public static Object[] buildValueArray(String sql, SqlParameterSource paramSource);
253
}
254

255
/**
256
 * Utility methods for SqlParameterSource operations  
257
 */
258
public abstract class SqlParameterSourceUtils {
259
    public static SqlParameterSource[] createBatch(Object... objects);
260
    public static SqlParameterSource[] createBatch(Collection<?> objects);
261
}
262

263
/**
264
 * Holds information for parsed SQL statement
265
 */
266
public class ParsedSql {
267
    public String getOriginalSql();
268
    public List<String> getParameterNames();
269
    public int[] getParameterIndexes();
270
    public int getTotalParameterCount();
271
    public int getNamedParameterCount();
272
}
273
```
274

275
**Usage Examples:**
276

277
```java
278
// Parse SQL to understand parameter structure
279
ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(
280
    "SELECT * FROM users WHERE name = :name AND department = :dept"
281
);
282
List<String> paramNames = parsedSql.getParameterNames(); // ["name", "dept"]
283

284
// Create batch from object collection
285
List<User> users = getUsersToInsert();
286
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users);
287

288
namedTemplate.batchUpdate(
289
    "INSERT INTO users (name, email, department) VALUES (:name, :email, :department)",
290
    batch
291
);
292
```
293

294
### DAO Support
295

296
Convenient base class for DAOs using NamedParameterJdbcTemplate.
297

298
```java { .api }
299
/**
300
 * Convenient base class for DAOs using NamedParameterJdbcTemplate
301
 */
302
public abstract class NamedParameterJdbcDaoSupport extends JdbcDaoSupport {
303
    public final NamedParameterJdbcTemplate getNamedParameterJdbcTemplate();
304
    protected final void checkDaoConfig();
305
}
306
```
307

308
**Usage Examples:**
309

310
```java
311
@Repository
312
public class UserDao extends NamedParameterJdbcDaoSupport {
313
    
314
    @Autowired
315
    public void setDataSource(DataSource dataSource) {
316
        super.setDataSource(dataSource);
317
    }
318
    
319
    public List<User> findUsersByDepartment(String department) {
320
        return getNamedParameterJdbcTemplate().query(
321
            "SELECT id, name, email FROM users WHERE department = :department",
322
            Map.of("department", department),
323
            new BeanPropertyRowMapper<>(User.class)
324
        );
325
    }
326
    
327
    public void saveUser(User user) {
328
        MapSqlParameterSource params = new MapSqlParameterSource()
329
            .addValue("name", user.getName())
330
            .addValue("email", user.getEmail())
331
            .addValue("department", user.getDepartment());
332
            
333
        getNamedParameterJdbcTemplate().update(
334
            "INSERT INTO users (name, email, department) VALUES (:name, :email, :department)",
335
            params
336
        );
337
    }
338
}
339
```
340

341
## Core Interfaces
342

343
```java { .api }
344
/**
345
 * Interface specifying named parameter JDBC operations
346
 */
347
public interface NamedParameterJdbcOperations {
348
    JdbcOperations getJdbcOperations();
349
    
350
    // Query operations
351
    <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType);
352
    <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper);
353
    <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper);
354
    
355
    // Update operations
356
    int update(String sql, Map<String, ?> paramMap);
357
    int update(String sql, SqlParameterSource paramSource);
358
    int[] batchUpdate(String sql, SqlParameterSource[] batchArgs);
359
}
360

361
/**
362
 * Interface for named SQL parameter sources
363
 */
364
public interface SqlParameterSource {
365
    boolean hasValue(String paramName);
366
    Object getValue(String paramName) throws IllegalArgumentException;
367
    int getSqlType(String paramName);
368
    String getTypeName(String paramName);
369
}
370
```