0
# Simple Operations
1

2
Spring JDBC's simple operations provide high-level abstractions for common database tasks with minimal configuration. `SimpleJdbcInsert` simplifies INSERT operations with automatic key generation, while `SimpleJdbcCall` streamlines stored procedure and function calls. These classes automatically discover database metadata to reduce boilerplate code.
3

4
## Capabilities
5

6
### SimpleJdbcInsert Operations
7

8
Simplified INSERT operations with automatic metadata discovery and key generation support.
9

10
```java { .api }
11
/**
12
 * Multi-threaded, reusable object for simplified insert operations
13
 * Automatically discovers table metadata to minimize configuration
14
 */
15
public class SimpleJdbcInsert implements SimpleJdbcInsertOperations {
16
    /** Create SimpleJdbcInsert with DataSource */
17
    public SimpleJdbcInsert(DataSource dataSource);
18
    
19
    /** Create SimpleJdbcInsert with JdbcTemplate */
20
    public SimpleJdbcInsert(JdbcTemplate jdbcTemplate);
21
}
22

23
/**
24
 * Configuration and execution methods for insert operations
25
 */
26
public interface SimpleJdbcInsertOperations {
27
    // Table configuration
28
    SimpleJdbcInsertOperations withTableName(String tableName);
29
    SimpleJdbcInsertOperations withSchemaName(String schemaName);
30
    SimpleJdbcInsertOperations withCatalogName(String catalogName);
31
    
32
    // Column configuration
33
    SimpleJdbcInsertOperations usingColumns(String... columnNames);
34
    SimpleJdbcInsertOperations usingGeneratedKeyColumns(String... columnNames);
35
    
36
    // Execution methods
37
    int execute(Map<String, ?> args);
38
    int execute(SqlParameterSource parameterSource);
39
    Number executeAndReturnKey(Map<String, ?> args);
40
    Number executeAndReturnKey(SqlParameterSource parameterSource);
41
    KeyHolder executeAndReturnKeyHolder(Map<String, ?> args);
42
    KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource);
43
    
44
    // Batch operations
45
    int[] executeBatch(Map<String, ?>... batch);
46
    int[] executeBatch(SqlParameterSource... batch);
47
}
48
```
49

50
**Usage Examples:**
51

52
```java
53
// Basic insert operation
54
SimpleJdbcInsert insertUser = new SimpleJdbcInsert(dataSource)
55
    .withTableName("users");
56

57
Map<String, Object> parameters = new HashMap<>();
58
parameters.put("name", "John Doe");
59
parameters.put("email", "john@example.com");
60
parameters.put("department", "Engineering");
61

62
int rowsAffected = insertUser.execute(parameters);
63

64
// Insert with generated key
65
SimpleJdbcInsert insertUserWithKey = new SimpleJdbcInsert(dataSource)
66
    .withTableName("users")
67
    .usingGeneratedKeyColumns("id");
68

69
Number newUserId = insertUserWithKey.executeAndReturnKey(parameters);
70
System.out.println("New user ID: " + newUserId.longValue());
71

72
// Insert with specific columns only
73
SimpleJdbcInsert insertSpecific = new SimpleJdbcInsert(dataSource)
74
    .withTableName("users")
75
    .usingColumns("name", "email", "created_date")
76
    .usingGeneratedKeyColumns("id");
77

78
Map<String, Object> userParams = Map.of(
79
    "name", "Jane Smith",
80
    "email", "jane@example.com",
81
    "created_date", Timestamp.from(Instant.now())
82
);
83

84
KeyHolder keyHolder = insertSpecific.executeAndReturnKeyHolder(userParams);
85
Long userId = keyHolder.getKey().longValue();
86
Map<String, Object> allKeys = keyHolder.getKeys(); // All generated values
87

88
// Using SqlParameterSource
89
BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);
90
Number userId = insertUser.executeAndReturnKey(paramSource);
91

92
// Batch insert
93
Map<String, Object>[] batchParams = {
94
    Map.of("name", "Alice", "email", "alice@example.com"),
95
    Map.of("name", "Bob", "email", "bob@example.com"),
96
    Map.of("name", "Charlie", "email", "charlie@example.com")
97
};
98

99
int[] updateCounts = insertUser.executeBatch(batchParams);
100
```
101

102
### SimpleJdbcCall Operations
103

104
Simplified stored procedure and function calls with automatic parameter discovery.
105

106
```java { .api }
107
/**
108
 * Multi-threaded, reusable object for stored procedure calls
109
 * Automatically discovers procedure metadata to minimize configuration
110
 */
111
public class SimpleJdbcCall implements SimpleJdbcCallOperations {
112
    /** Create SimpleJdbcCall with DataSource */
113
    public SimpleJdbcCall(DataSource dataSource);
114
    
115
    /** Create SimpleJdbcCall with JdbcTemplate */
116
    public SimpleJdbcCall(JdbcTemplate jdbcTemplate);
117
}
118

119
/**
120
 * Configuration and execution methods for procedure calls
121
 */
122
public interface SimpleJdbcCallOperations {
123
    // Procedure configuration
124
    SimpleJdbcCallOperations withProcedureName(String procedureName);
125
    SimpleJdbcCallOperations withFunctionName(String functionName);
126
    SimpleJdbcCallOperations withSchemaName(String schemaName);
127
    SimpleJdbcCallOperations withCatalogName(String catalogName);
128
    
129
    // Parameter configuration
130
    SimpleJdbcCallOperations declareParameters(SqlParameter... sqlParameters);
131
    SimpleJdbcCallOperations withoutProcedureColumnMetaDataAccess();
132
    SimpleJdbcCallOperations useInParameterNames(String... inParameterNames);
133
    SimpleJdbcCallOperations returningResultSet(String parameterName, RowMapper<?> rowMapper);
134
    
135
    // Execution methods
136
    Map<String, Object> execute(Map<String, ?> args);
137
    Map<String, Object> execute(SqlParameterSource parameterSource);
138
    <T> T executeFunction(Class<T> returnType, Map<String, ?> args);
139
    <T> T executeFunction(Class<T> returnType, SqlParameterSource parameterSource);
140
    <T> T executeObject(Class<T> returnType, Map<String, ?> args);
141
    <T> T executeObject(Class<T> returnType, SqlParameterSource parameterSource);
142
}
143
```
144

145
**Usage Examples:**
146

147
```java
148
// Basic stored procedure call
149
SimpleJdbcCall procedureCall = new SimpleJdbcCall(dataSource)
150
    .withProcedureName("update_user_status");
151

152
Map<String, Object> inParams = Map.of(
153
    "user_id", 123,
154
    "new_status", "ACTIVE",
155
    "updated_by", "admin"
156
);
157

158
Map<String, Object> result = procedureCall.execute(inParams);
159
Integer rowsAffected = (Integer) result.get("rows_affected");
160

161
// Function call with return value
162
SimpleJdbcCall functionCall = new SimpleJdbcCall(dataSource)
163
    .withFunctionName("calculate_discount");
164

165
BigDecimal discount = functionCall.executeFunction(
166
    BigDecimal.class,
167
    Map.of("customer_id", 456, "order_total", new BigDecimal("150.00"))
168
);
169

170
// Procedure with output parameters
171
SimpleJdbcCall procWithOutput = new SimpleJdbcCall(dataSource)
172
    .withProcedureName("get_user_stats")
173
    .declareParameters(
174
        new SqlParameter("user_id", Types.INTEGER),
175
        new SqlOutParameter("total_orders", Types.INTEGER),
176
        new SqlOutParameter("total_spent", Types.DECIMAL)
177
    );
178

179
Map<String, Object> results = procWithOutput.execute(Map.of("user_id", 123));
180
Integer totalOrders = (Integer) results.get("total_orders");
181
BigDecimal totalSpent = (BigDecimal) results.get("total_spent");
182

183
// Procedure returning result set
184
SimpleJdbcCall procWithResultSet = new SimpleJdbcCall(dataSource)
185
    .withProcedureName("get_department_users")
186
    .returningResultSet("users", (rs, rowNum) -> new User(
187
        rs.getLong("id"),
188
        rs.getString("name"),
189
        rs.getString("email")
190
    ));
191

192
Map<String, Object> results = procWithResultSet.execute(
193
    Map.of("department", "Engineering")
194
);
195
@SuppressWarnings("unchecked")
196
List<User> users = (List<User>) results.get("users");
197

198
// Using SqlParameterSource
199
MapSqlParameterSource paramSource = new MapSqlParameterSource()
200
    .addValue("start_date", LocalDate.of(2024, 1, 1))
201
    .addValue("end_date", LocalDate.of(2024, 12, 31))
202
    .addValue("department", "Sales");
203

204
Map<String, Object> salesReport = procedureCall.execute(paramSource);
205
```
206

207
### Advanced Configuration
208

209
Advanced configuration options for both SimpleJdbcInsert and SimpleJdbcCall.
210

211
```java { .api }
212
// Schema and catalog specification
213
SimpleJdbcInsert insertWithSchema = new SimpleJdbcInsert(dataSource)
214
    .withSchemaName("hr")
215
    .withCatalogName("company_db")
216
    .withTableName("employees");
217

218
SimpleJdbcCall callWithSchema = new SimpleJdbcCall(dataSource)
219
    .withSchemaName("reporting")
220
    .withCatalogName("analytics_db")  
221
    .withProcedureName("generate_monthly_report");
222

223
// Disabling metadata access for performance
224
SimpleJdbcCall manualConfig = new SimpleJdbcCall(dataSource)
225
    .withProcedureName("fast_procedure")
226
    .withoutProcedureColumnMetaDataAccess()
227
    .declareParameters(
228
        new SqlParameter("param1", Types.VARCHAR),
229
        new SqlParameter("param2", Types.INTEGER),
230
        new SqlOutParameter("result", Types.VARCHAR)
231
    );
232

233
// Specific IN parameter names
234
SimpleJdbcCall specificParams = new SimpleJdbcCall(dataSource)
235
    .withProcedureName("complex_procedure")
236
    .useInParameterNames("user_id", "action_type", "timestamp");
237
```
238

239
**Usage Examples:**
240

241
```java
242
// Complete workflow example
243
@Service
244
@Transactional
245
public class UserService {
246
    private final SimpleJdbcInsert insertUser;
247
    private final SimpleJdbcCall updateUserStats;
248
    private final SimpleJdbcCall calculateRewards;
249
    
250
    public UserService(DataSource dataSource) {
251
        this.insertUser = new SimpleJdbcInsert(dataSource)
252
            .withTableName("users")
253
            .usingGeneratedKeyColumns("id")
254
            .usingColumns("name", "email", "department", "salary");
255
            
256
        this.updateUserStats = new SimpleJdbcCall(dataSource)
257
            .withProcedureName("update_user_statistics");
258
            
259
        this.calculateRewards = new SimpleJdbcCall(dataSource)
260
            .withFunctionName("calculate_user_rewards");
261
    }
262
    
263
    public User createUser(User user) {
264
        // Insert new user
265
        BeanPropertySqlParameterSource paramSource = 
266
            new BeanPropertySqlParameterSource(user);
267
        Number newId = insertUser.executeAndReturnKey(paramSource);
268
        user.setId(newId.longValue());
269
        
270
        // Update statistics
271
        updateUserStats.execute(Map.of(
272
            "user_id", user.getId(),
273
            "action", "USER_CREATED"
274
        ));
275
        
276
        return user;
277
    }
278
    
279
    public BigDecimal getUserRewards(Long userId) {
280
        return calculateRewards.executeFunction(
281
            BigDecimal.class,
282
            Map.of("user_id", userId)
283
        );
284
    }
285
}
286

287
// Batch operations example
288
@Repository
289
public class BatchUserRepository {
290
    private final SimpleJdbcInsert batchInsert;
291
    
292
    public BatchUserRepository(DataSource dataSource) {
293
        this.batchInsert = new SimpleJdbcInsert(dataSource)
294
            .withTableName("user_audit")
295
            .usingColumns("user_id", "action", "timestamp", "details");
296
    }
297
    
298
    public void logUserActions(List<UserAction> actions) {
299
        SqlParameterSource[] batchParams = actions.stream()
300
            .map(BeanPropertySqlParameterSource::new)
301
            .toArray(SqlParameterSource[]::new);
302
            
303
        int[] results = batchInsert.executeBatch(batchParams);
304
        
305
        log.info("Inserted {} audit records", 
306
                 Arrays.stream(results).sum());
307
    }
308
}
309
```
310

311
### Metadata Discovery
312

313
Understanding how SimpleJdbc classes discover and use database metadata.
314

315
**Key Features:**
316

317
```java
318
// Automatic column discovery
319
SimpleJdbcInsert autoInsert = new SimpleJdbcInsert(dataSource)
320
    .withTableName("products");
321
// Automatically discovers all table columns
322

323
// Selective column usage
324
SimpleJdbcInsert selectiveInsert = new SimpleJdbcInsert(dataSource)  
325
    .withTableName("products")
326
    .usingColumns("name", "price", "category"); // Only use these columns
327

328
// Generated key discovery  
329
SimpleJdbcInsert keyInsert = new SimpleJdbcInsert(dataSource)
330
    .withTableName("orders")
331
    .usingGeneratedKeyColumns("id", "created_timestamp"); // Multiple generated columns
332

333
// Procedure parameter discovery
334
SimpleJdbcCall autoCall = new SimpleJdbcCall(dataSource)
335
    .withProcedureName("calculate_tax");
336
// Automatically discovers IN/OUT/INOUT parameters
337

338
// Manual parameter declaration (for performance or compatibility)
339
SimpleJdbcCall manualCall = new SimpleJdbcCall(dataSource)
340
    .withProcedureName("calculate_tax")
341
    .withoutProcedureColumnMetaDataAccess()
342
    .declareParameters(
343
        new SqlParameter("amount", Types.DECIMAL),
344
        new SqlParameter("tax_rate", Types.DECIMAL),
345
        new SqlOutParameter("tax_amount", Types.DECIMAL)
346
    );
347
```
348

349
## Core Interfaces
350

351
```java { .api }
352
/**
353
 * Interface for simplified insert operations
354
 */
355
public interface SimpleJdbcInsertOperations {
356
    SimpleJdbcInsertOperations withTableName(String tableName);
357
    SimpleJdbcInsertOperations usingGeneratedKeyColumns(String... columnNames);
358
    SimpleJdbcInsertOperations usingColumns(String... columnNames);
359
    
360
    int execute(Map<String, ?> args);
361
    Number executeAndReturnKey(Map<String, ?> args);
362
    KeyHolder executeAndReturnKeyHolder(Map<String, ?> args);
363
    int[] executeBatch(Map<String, ?>... batch);
364
}
365

366
/**
367
 * Interface for simplified stored procedure calls
368
 */
369
public interface SimpleJdbcCallOperations {
370
    SimpleJdbcCallOperations withProcedureName(String procedureName);
371
    SimpleJdbcCallOperations withFunctionName(String functionName);
372
    SimpleJdbcCallOperations declareParameters(SqlParameter... sqlParameters);
373
    
374
    Map<String, Object> execute(Map<String, ?> args);
375
    <T> T executeFunction(Class<T> returnType, Map<String, ?> args);
376
    <T> T executeObject(Class<T> returnType, Map<String, ?> args);
377
}
378
```