0
# Core Template Operations
1

2
The `JdbcTemplate` class provides the foundational template-based approach to JDBC operations in Spring. It handles all low-level details of resource management, connection handling, exception translation, and SQL execution while providing multiple convenient methods for queries and updates.
3

4
## Capabilities
5

6
### JdbcTemplate Construction
7

8
Creates JdbcTemplate instances with DataSource dependency and optional configuration.
9

10
```java { .api }
11
/**
12
 * Core template class for JDBC access  
13
 * Handles resource management and exception translation automatically
14
 */
15
public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
16
    /** Create JdbcTemplate with DataSource */
17
    public JdbcTemplate(DataSource dataSource);
18
    
19
    /** Create JdbcTemplate with lazy DataSource lookup */
20
    public JdbcTemplate(DataSource dataSource, boolean lazyInit);
21
    
22
    /** Set maximum number of rows to retrieve */
23
    public void setMaxRows(int maxRows);
24
    
25
    /** Set query timeout in seconds */
26
    public void setQueryTimeout(int queryTimeout);
27
    
28
    /** Set whether to ignore SQLWarnings */
29
    public void setIgnoreWarnings(boolean ignoreWarnings);
30
}
31
```
32

33
**Usage Examples:**
34

35
```java
36
// Basic template creation
37
DataSource dataSource = // ... obtain DataSource
38
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
39

40
// Configuration
41
jdbcTemplate.setMaxRows(1000);
42
jdbcTemplate.setQueryTimeout(30);
43
```
44

45
### Query Operations
46

47
Methods for executing SELECT queries and retrieving results in various formats.
48

49
```java { .api }
50
// Single value queries
51
public <T> T queryForObject(String sql, Class<T> requiredType);
52
public <T> T queryForObject(String sql, Class<T> requiredType, Object... args);
53
public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType);
54

55
// Row mapping queries  
56
public <T> T queryForObject(String sql, RowMapper<T> rowMapper);
57
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args);
58
public <T> List<T> query(String sql, RowMapper<T> rowMapper);
59
public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args);
60

61
// Map-based results
62
public Map<String, Object> queryForMap(String sql);
63
public Map<String, Object> queryForMap(String sql, Object... args);
64
public List<Map<String, Object>> queryForList(String sql);
65
public List<Map<String, Object>> queryForList(String sql, Object... args);
66

67
// ResultSet extraction
68
public <T> T query(String sql, ResultSetExtractor<T> rse);
69
public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse);
70

71
// SqlRowSet (disconnected ResultSet)
72
public SqlRowSet queryForRowSet(String sql);
73
public SqlRowSet queryForRowSet(String sql, Object... args);
74
```
75

76
**Usage Examples:**
77

78
```java
79
// Single value query
80
Integer count = jdbcTemplate.queryForObject(
81
    "SELECT COUNT(*) FROM users WHERE active = ?", 
82
    Integer.class, 
83
    true
84
);
85

86
// Object mapping with RowMapper
87
List<User> activeUsers = jdbcTemplate.query(
88
    "SELECT id, name, email FROM users WHERE active = ?",
89
    (rs, rowNum) -> new User(
90
        rs.getLong("id"),
91
        rs.getString("name"),
92
        rs.getString("email")
93
    ),
94
    true
95
);
96

97
// Map results for dynamic queries
98
List<Map<String, Object>> results = jdbcTemplate.queryForList(
99
    "SELECT * FROM users WHERE department = ?", 
100
    "Engineering"
101
);
102

103
// Custom ResultSetExtractor for complex processing
104
Map<String, List<String>> usersByDept = jdbcTemplate.query(
105
    "SELECT department, name FROM users ORDER BY department",
106
    rs -> {
107
        Map<String, List<String>> map = new HashMap<>();
108
        while (rs.next()) {
109
            String dept = rs.getString("department");
110
            String name = rs.getString("name");
111
            map.computeIfAbsent(dept, k -> new ArrayList<>()).add(name);
112
        }
113
        return map;
114
    }
115
);
116
```
117

118
### Update Operations
119

120
Methods for executing INSERT, UPDATE, DELETE, and DDL statements.
121

122
```java { .api }
123
// Single updates
124
public int update(String sql);
125
public int update(String sql, Object... args);
126
public int update(String sql, Object[] args, int[] argTypes);
127

128
// Updates with PreparedStatementSetter
129
public int update(String sql, PreparedStatementSetter pss);
130
public int update(PreparedStatementCreator psc);
131
public int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder);
132

133
// Batch updates
134
public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss);
135
public int[] batchUpdate(String sql, List<Object[]> batchArgs);
136
public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes);
137
public <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, 
138
                              ParameterizedPreparedStatementSetter<T> pss);
139
```
140

141
**Usage Examples:**
142

143
```java
144
// Simple update
145
int rowsUpdated = jdbcTemplate.update(
146
    "UPDATE users SET last_login = ? WHERE id = ?",
147
    Timestamp.from(Instant.now()),
148
    userId
149
);
150

151
// Insert with key generation
152
KeyHolder keyHolder = new GeneratedKeyHolder();
153
jdbcTemplate.update(connection -> {
154
    PreparedStatement ps = connection.prepareStatement(
155
        "INSERT INTO users (name, email) VALUES (?, ?)",
156
        Statement.RETURN_GENERATED_KEYS
157
    );
158
    ps.setString(1, "John Doe");
159
    ps.setString(2, "john@example.com");
160
    return ps;
161
}, keyHolder);
162
Long newUserId = keyHolder.getKey().longValue();
163

164
// Batch update
165
List<Object[]> batchArgs = Arrays.asList(
166
    new Object[]{"Alice", "alice@example.com"},
167
    new Object[]{"Bob", "bob@example.com"},
168
    new Object[]{"Charlie", "charlie@example.com"}
169
);
170
int[] updateCounts = jdbcTemplate.batchUpdate(
171
    "INSERT INTO users (name, email) VALUES (?, ?)",
172
    batchArgs
173
);
174
```
175

176
### Callback-Based Operations
177

178
Low-level operations using callback interfaces for complete control over JDBC operations.
179

180
```java { .api }
181
// Connection-level callbacks
182
public <T> T execute(ConnectionCallback<T> action);
183

184
// Statement-level callbacks  
185
public <T> T execute(StatementCallback<T> action);
186

187
// PreparedStatement callbacks
188
public <T> T execute(String sql, PreparedStatementCallback<T> action);
189

190
// CallableStatement callbacks
191
public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action);
192
public <T> T execute(String callString, CallableStatementCallback<T> action);
193
```
194

195
**Usage Examples:**
196

197
```java
198
// Connection callback for multiple operations
199
jdbcTemplate.execute((Connection con) -> {
200
    // Perform multiple operations with same connection
201
    try (PreparedStatement ps1 = con.prepareStatement("UPDATE table1 SET col1 = ?")) {
202
        ps1.setString(1, "value1");
203
        ps1.executeUpdate();
204
    }
205
    try (PreparedStatement ps2 = con.prepareStatement("UPDATE table2 SET col2 = ?")) {
206
        ps2.setString(1, "value2");
207
        ps2.executeUpdate();
208
    }
209
    return null;
210
});
211

212
// PreparedStatement callback for complex parameter handling
213
List<User> users = jdbcTemplate.execute(
214
    "SELECT * FROM users WHERE created_date BETWEEN ? AND ?",
215
    (PreparedStatement ps) -> {
216
        ps.setDate(1, Date.valueOf(startDate));
217
        ps.setDate(2, Date.valueOf(endDate));
218
        try (ResultSet rs = ps.executeQuery()) {
219
            List<User> results = new ArrayList<>();
220
            while (rs.next()) {
221
                results.add(mapUser(rs));
222
            }
223
            return results;
224
        }
225
    }
226
);
227
```
228

229
### Row Mapping Support
230

231
Built-in RowMapper implementations for common mapping scenarios.
232

233
```java { .api }
234
/**
235
 * Maps ResultSet rows to objects using JavaBean conventions
236
 */
237
public class BeanPropertyRowMapper<T> implements RowMapper<T> {
238
    public static <T> BeanPropertyRowMapper<T> newInstance(Class<T> mappedClass);
239
    public T mapRow(ResultSet rs, int rowNumber) throws SQLException;
240
}
241

242
/**
243
 * Maps single column results to specified type
244
 */  
245
public class SingleColumnRowMapper<T> implements RowMapper<T> {
246
    public SingleColumnRowMapper(Class<T> requiredType);
247
    public T mapRow(ResultSet rs, int rowNumber) throws SQLException;
248
}
249

250
/**
251
 * Maps each row to a Map with column names as keys
252
 */
253
public class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {
254
    public Map<String, Object> mapRow(ResultSet rs, int rowNumber) throws SQLException;
255
}
256
```
257

258
**Usage Examples:**
259

260
```java
261
// Bean property mapping (requires matching properties)
262
List<User> users = jdbcTemplate.query(
263
    "SELECT id, first_name, last_name, email FROM users",
264
    BeanPropertyRowMapper.newInstance(User.class)
265
);
266

267
// Single column mapping
268
List<String> names = jdbcTemplate.query(
269
    "SELECT name FROM users WHERE active = ?",
270
    new SingleColumnRowMapper<>(String.class),
271
    true
272
);
273

274
// Map-based results
275
List<Map<String, Object>> userMaps = jdbcTemplate.query(
276
    "SELECT * FROM users LIMIT 10",
277
    new ColumnMapRowMapper()
278
);
279
```
280

281
## Core Interfaces
282

283
```java { .api }
284
/**
285
 * Central interface defining JDBC operations
286
 */
287
public interface JdbcOperations {
288
    // Query methods
289
    <T> T queryForObject(String sql, Class<T> requiredType, Object... args);
290
    <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args);
291
    List<Map<String, Object>> queryForList(String sql, Object... args);
292
    
293
    // Update methods
294
    int update(String sql, Object... args);
295
    int[] batchUpdate(String sql, List<Object[]> batchArgs);
296
    
297
    // Callback methods
298
    <T> T execute(StatementCallback<T> action);
299
    <T> T execute(String sql, PreparedStatementCallback<T> action);
300
}
301

302
/**
303
 * Callback for mapping ResultSet rows to objects
304
 */
305
@FunctionalInterface
306
public interface RowMapper<T> {
307
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
308
}
309

310
/**
311
 * Callback for processing entire ResultSet
312
 */
313
@FunctionalInterface
314
public interface ResultSetExtractor<T> {
315
    T extractData(ResultSet rs) throws SQLException, DataAccessException;
316
}
317

318
/**
319
 * Callback for setting PreparedStatement parameters
320
 */
321
@FunctionalInterface  
322
public interface PreparedStatementSetter {
323
    void setValues(PreparedStatement ps) throws SQLException;
324
}
325

326
/**
327
 * Factory for creating PreparedStatement instances
328
 */
329
@FunctionalInterface
330
public interface PreparedStatementCreator {
331
    PreparedStatement createPreparedStatement(Connection con) throws SQLException;
332
}
333
```