0
# Statement Execution
1

2
Comprehensive statement execution support including prepared statements, callable statements, batch operations, and streaming result sets.
3

4
## Capabilities
5

6
### Basic Statement Execution
7

8
Standard JDBC Statement implementation for executing SQL queries.
9

10
```java { .api }
11
/**
12
 * MariaDB Statement implementation
13
 */
14
public class Statement implements java.sql.Statement {
15
  // Query execution
16
  public ResultSet executeQuery(String sql) throws SQLException;
17
  public int executeUpdate(String sql) throws SQLException;
18
  public boolean execute(String sql) throws SQLException;
19
  
20
  // Batch operations
21
  public void addBatch(String sql) throws SQLException;
22
  public int[] executeBatch() throws SQLException;
23
  public void clearBatch() throws SQLException;
24
  
25
  // Result set management
26
  public ResultSet getResultSet() throws SQLException;
27
  public int getUpdateCount() throws SQLException;
28
  public boolean getMoreResults() throws SQLException;
29
  public boolean getMoreResults(int current) throws SQLException;
30
  
31
  // Configuration
32
  public void setMaxRows(int max) throws SQLException;
33
  public int getMaxRows() throws SQLException;
34
  public void setQueryTimeout(int seconds) throws SQLException;
35
  public int getQueryTimeout() throws SQLException;
36
  public void setFetchSize(int rows) throws SQLException;
37
  public int getFetchSize() throws SQLException;
38
  public void setFetchDirection(int direction) throws SQLException;
39
  public int getFetchDirection() throws SQLException;
40
  
41
  // Cursor and result set types
42
  public void setCursorName(String name) throws SQLException;
43
  public int getResultSetType() throws SQLException;
44
  public int getResultSetConcurrency() throws SQLException;
45
  public int getResultSetHoldability() throws SQLException;
46
  
47
  // Warnings and metadata
48
  public SQLWarning getWarnings() throws SQLException;
49
  public void clearWarnings() throws SQLException;
50
  public Connection getConnection() throws SQLException;
51
  
52
  // Generated keys
53
  public ResultSet getGeneratedKeys() throws SQLException;
54
  public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException;
55
  public int executeUpdate(String sql, int[] columnIndexes) throws SQLException;
56
  public int executeUpdate(String sql, String[] columnNames) throws SQLException;
57
  
58
  // Cleanup
59
  public void close() throws SQLException;
60
  public boolean isClosed() throws SQLException;
61
}
62
```
63

64
**Usage Examples:**
65

66
```java
67
// Basic statement execution
68
Connection conn = DriverManager.getConnection(url, user, password);
69
Statement stmt = conn.createStatement();
70

71
// Execute query
72
ResultSet rs = stmt.executeQuery("SELECT id, name, email FROM users");
73
while (rs.next()) {
74
    int id = rs.getInt("id");
75
    String name = rs.getString("name");
76
    String email = rs.getString("email");
77
    System.out.println(id + ": " + name + " (" + email + ")");
78
}
79

80
// Execute update
81
int affectedRows = stmt.executeUpdate("UPDATE users SET active = 1 WHERE last_login > '2023-01-01'");
82
System.out.println("Updated " + affectedRows + " users");
83

84
// Execute with generated keys
85
int newRows = stmt.executeUpdate(
86
    "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')",
87
    Statement.RETURN_GENERATED_KEYS
88
);
89
ResultSet generatedKeys = stmt.getGeneratedKeys();
90
if (generatedKeys.next()) {
91
    long newId = generatedKeys.getLong(1);
92
    System.out.println("Created user with ID: " + newId);
93
}
94

95
// Batch operations
96
stmt.addBatch("INSERT INTO logs (message) VALUES ('Event 1')");
97
stmt.addBatch("INSERT INTO logs (message) VALUES ('Event 2')");
98
stmt.addBatch("INSERT INTO logs (message) VALUES ('Event 3')");
99
int[] batchResults = stmt.executeBatch();
100
```
101

102
### Prepared Statements
103

104
Prepared statement implementations for secure and efficient parameterized queries.
105

106
```java { .api }
107
/**
108
 * Base class for prepared statements
109
 */
110
public abstract class BasePreparedStatement implements PreparedStatement {
111
  // Parameter setting methods
112
  public void setNull(int parameterIndex, int sqlType) throws SQLException;
113
  public void setBoolean(int parameterIndex, boolean x) throws SQLException;
114
  public void setByte(int parameterIndex, byte x) throws SQLException;
115
  public void setShort(int parameterIndex, short x) throws SQLException;
116
  public void setInt(int parameterIndex, int x) throws SQLException;
117
  public void setLong(int parameterIndex, long x) throws SQLException;
118
  public void setFloat(int parameterIndex, float x) throws SQLException;
119
  public void setDouble(int parameterIndex, double x) throws SQLException;
120
  public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException;
121
  public void setString(int parameterIndex, String x) throws SQLException;
122
  public void setBytes(int parameterIndex, byte[] x) throws SQLException;
123
  public void setDate(int parameterIndex, Date x) throws SQLException;
124
  public void setTime(int parameterIndex, Time x) throws SQLException;
125
  public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException;
126
  public void setObject(int parameterIndex, Object x) throws SQLException;
127
  
128
  // LOB methods
129
  public void setBinaryStream(int parameterIndex, InputStream x) throws SQLException;
130
  public void setCharacterStream(int parameterIndex, Reader x) throws SQLException;
131
  public void setBlob(int parameterIndex, Blob x) throws SQLException;
132
  public void setClob(int parameterIndex, Clob x) throws SQLException;
133
  
134
  // Execution methods
135
  public ResultSet executeQuery() throws SQLException;
136
  public int executeUpdate() throws SQLException;
137
  public boolean execute() throws SQLException;
138
  
139
  // Batch operations
140
  public void addBatch() throws SQLException;
141
  public int[] executeBatch() throws SQLException;
142
  
143
  // Metadata
144
  public ParameterMetaData getParameterMetaData() throws SQLException;
145
  
146
  // Parameter management
147
  public void clearParameters() throws SQLException;
148
}
149

150
/**
151
 * Client-side prepared statement implementation
152
 */
153
public class ClientPreparedStatement extends BasePreparedStatement {
154
  // Client-side parameter processing and SQL generation
155
  // Parameters are processed on the client and SQL is generated locally
156
}
157

158
/**
159
 * Server-side prepared statement implementation  
160
 */
161
public class ServerPreparedStatement extends BasePreparedStatement {
162
  // Server-side statement preparation and execution
163
  // Statements are prepared on the database server for better performance
164
}
165
```
166

167
**Usage Examples:**
168

169
```java
170
// Basic prepared statement
171
String sql = "SELECT * FROM users WHERE age > ? AND city = ?";
172
PreparedStatement pstmt = conn.prepareStatement(sql);
173
pstmt.setInt(1, 21);
174
pstmt.setString(2, "New York");
175
ResultSet rs = pstmt.executeQuery();
176

177
// Insert with prepared statement
178
String insertSql = "INSERT INTO users (name, email, age, created_at) VALUES (?, ?, ?, ?)";
179
PreparedStatement insertStmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
180
insertStmt.setString(1, "Alice Smith");
181
insertStmt.setString(2, "alice@example.com");
182
insertStmt.setInt(3, 28);
183
insertStmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
184
int rows = insertStmt.executeUpdate();
185

186
// Get generated keys
187
ResultSet keys = insertStmt.getGeneratedKeys();
188
if (keys.next()) {
189
    long userId = keys.getLong(1);
190
    System.out.println("Created user ID: " + userId);
191
}
192

193
// Batch prepared statements
194
PreparedStatement batchStmt = conn.prepareStatement("INSERT INTO products (name, price) VALUES (?, ?)");
195
batchStmt.setString(1, "Product A");
196
batchStmt.setBigDecimal(2, new BigDecimal("19.99"));
197
batchStmt.addBatch();
198

199
batchStmt.setString(1, "Product B");  
200
batchStmt.setBigDecimal(2, new BigDecimal("29.99"));
201
batchStmt.addBatch();
202

203
int[] batchResults = batchStmt.executeBatch();
204

205
// Working with LOBs
206
PreparedStatement lobStmt = conn.prepareStatement("INSERT INTO documents (name, content) VALUES (?, ?)");
207
lobStmt.setString(1, "document.pdf");
208

209
// Set binary content
210
FileInputStream fileInput = new FileInputStream("document.pdf");
211
lobStmt.setBinaryStream(2, fileInput);
212
lobStmt.executeUpdate();
213

214
// Set text content
215
String textContent = "This is a large text document...";
216
lobStmt.setString(1, "text_doc.txt");
217
lobStmt.setCharacterStream(2, new StringReader(textContent));
218
lobStmt.executeUpdate();
219
```
220

221
### Callable Statements
222

223
Support for stored procedures and database functions.
224

225
```java { .api }
226
/**
227
 * Base callable statement for stored procedures and functions
228
 */
229
public class BaseCallableStatement extends BasePreparedStatement implements CallableStatement {
230
  // Output parameter registration
231
  public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException;
232
  public void registerOutParameter(int parameterIndex, int sqlType, int scale) throws SQLException;
233
  public void registerOutParameter(int parameterIndex, int sqlType, String typeName) throws SQLException;
234
  public void registerOutParameter(String parameterName, int sqlType) throws SQLException;
235
  
236
  // Named parameter support
237
  public void setString(String parameterName, String x) throws SQLException;
238
  public void setInt(String parameterName, int x) throws SQLException;
239
  public void setNull(String parameterName, int sqlType) throws SQLException;
240
  
241
  // Output parameter retrieval
242
  public String getString(int parameterIndex) throws SQLException;
243
  public int getInt(int parameterIndex) throws SQLException;
244
  public boolean getBoolean(int parameterIndex) throws SQLException;
245
  public byte getByte(int parameterIndex) throws SQLException;
246
  public short getShort(int parameterIndex) throws SQLException;
247
  public long getLong(int parameterIndex) throws SQLException;
248
  public float getFloat(int parameterIndex) throws SQLException;
249
  public double getDouble(int parameterIndex) throws SQLException;
250
  public BigDecimal getBigDecimal(int parameterIndex) throws SQLException;
251
  public Date getDate(int parameterIndex) throws SQLException;
252
  public Time getTime(int parameterIndex) throws SQLException;
253
  public Timestamp getTimestamp(int parameterIndex) throws SQLException;
254
  public Object getObject(int parameterIndex) throws SQLException;
255
  
256
  // Named output parameter retrieval
257
  public String getString(String parameterName) throws SQLException;
258
  public int getInt(String parameterName) throws SQLException;
259
  public boolean getBoolean(String parameterName) throws SQLException;
260
  
261
  // Null checking
262
  public boolean wasNull() throws SQLException;
263
}
264

265
/**
266
 * Callable statement for stored procedures
267
 */
268
public class ProcedureStatement extends BaseCallableStatement {
269
  // Specialized for stored procedure calls
270
}
271

272
/**
273
 * Callable statement for database functions
274
 */
275
public class FunctionStatement extends BaseCallableStatement {
276
  // Specialized for function calls
277
}
278
```
279

280
**Usage Examples:**
281

282
```java
283
// Call stored procedure with IN and OUT parameters
284
String procedureCall = "{call getUserStats(?, ?)}";
285
CallableStatement cstmt = conn.prepareCall(procedureCall);
286

287
// Set input parameter
288
cstmt.setInt(1, 100);  // user_id
289

290
// Register output parameter
291
cstmt.registerOutParameter(2, Types.INTEGER);  // user_count
292

293
// Execute procedure
294
cstmt.execute();
295

296
// Get output parameter value
297
int userCount = cstmt.getInt(2);
298
System.out.println("User count: " + userCount);
299

300
// Call function
301
String functionCall = "{? = call calculateTax(?, ?)}";
302
CallableStatement funcStmt = conn.prepareCall(functionCall);
303

304
// Register return value
305
funcStmt.registerOutParameter(1, Types.DECIMAL);
306

307
// Set input parameters  
308
funcStmt.setBigDecimal(2, new BigDecimal("1000.00"));  // amount
309
funcStmt.setBigDecimal(3, new BigDecimal("0.08"));     // tax_rate
310

311
// Execute function
312
funcStmt.execute();
313

314
// Get return value
315
BigDecimal tax = funcStmt.getBigDecimal(1);
316
System.out.println("Tax: " + tax);
317

318
// Named parameters (if supported by procedure)
319
String namedCall = "{call createUser(?, ?, ?)}";
320
CallableStatement namedStmt = conn.prepareCall(namedCall);
321
namedStmt.setString("user_name", "John Doe");
322
namedStmt.setString("user_email", "john@example.com");
323
namedStmt.registerOutParameter("user_id", Types.BIGINT);
324
namedStmt.execute();
325
long newUserId = namedStmt.getLong("user_id");
326
```
327

328
### Result Set Processing
329

330
Advanced result set handling and navigation.
331

332
```java
333
// Scrollable result sets
334
Statement scrollStmt = conn.createStatement(
335
    ResultSet.TYPE_SCROLL_SENSITIVE,
336
    ResultSet.CONCUR_READ_ONLY
337
);
338
ResultSet scrollRs = scrollStmt.executeQuery("SELECT * FROM large_table");
339

340
// Navigate result set
341
scrollRs.first();           // Go to first row
342
scrollRs.last();            // Go to last row  
343
scrollRs.absolute(100);     // Go to row 100
344
scrollRs.relative(-10);     // Move back 10 rows
345
scrollRs.previous();        // Previous row
346

347
// Updatable result sets
348
Statement updateStmt = conn.createStatement(
349
    ResultSet.TYPE_SCROLL_INSENSITIVE,
350
    ResultSet.CONCUR_UPDATABLE
351
);
352
ResultSet updateRs = updateStmt.executeQuery("SELECT id, name, salary FROM employees");
353

354
while (updateRs.next()) {
355
    double salary = updateRs.getDouble("salary");
356
    if (salary < 50000) {
357
        // Update salary in place
358
        updateRs.updateDouble("salary", salary * 1.1);
359
        updateRs.updateRow();
360
    }
361
}
362

363
// Insert new row
364
updateRs.moveToInsertRow();
365
updateRs.updateString("name", "New Employee");
366
updateRs.updateDouble("salary", 60000);
367
updateRs.insertRow();
368
updateRs.moveToCurrentRow();
369

370
// Large result set streaming
371
Statement streamStmt = conn.createStatement();
372
streamStmt.setFetchSize(1000);  // Process in chunks of 1000 rows
373
ResultSet streamRs = streamStmt.executeQuery("SELECT * FROM huge_table");
374

375
// Process results in chunks to manage memory
376
while (streamRs.next()) {
377
    // Process row without loading entire result set into memory
378
    processRow(streamRs);
379
}
380
```
381

382
### Statement Configuration
383

384
Advanced statement configuration options.
385

386
```java
387
// Statement timeouts and limits
388
Statement configStmt = conn.createStatement();
389
configStmt.setQueryTimeout(30);        // 30 second timeout
390
configStmt.setMaxRows(10000);          // Limit to 10,000 rows
391
configStmt.setFetchSize(500);          // Fetch 500 rows at a time
392
configStmt.setFetchDirection(ResultSet.FETCH_FORWARD);
393

394
// Prepared statement configuration
395
PreparedStatement configPstmt = conn.prepareStatement(
396
    "SELECT * FROM users WHERE created_at > ?",
397
    ResultSet.TYPE_FORWARD_ONLY,
398
    ResultSet.CONCUR_READ_ONLY,
399
    ResultSet.HOLD_CURSORS_OVER_COMMIT
400
);
401

402
// Enable statement pooling (server-side prepared statements)
403
String poolingUrl = "jdbc:mariadb://localhost:3306/mydb?" +
404
    "useServerPrepStmts=true&" +
405
    "cachePrepStmts=true&" +
406
    "prepStmtCacheSize=250&" +
407
    "prepStmtCacheSqlLimit=2048";
408

409
Connection poolConn = DriverManager.getConnection(poolingUrl, user, password);
410
PreparedStatement pooledStmt = poolConn.prepareStatement("SELECT * FROM users WHERE id = ?");
411
// This statement will be cached on the server for reuse
412
```
413

414
## Performance Optimization
415

416
### Bulk Operations
417

418
```java
419
// Use bulk statements for better performance
420
String bulkUrl = "jdbc:mariadb://localhost:3306/mydb?useBulkStmts=true";
421
Connection bulkConn = DriverManager.getConnection(bulkUrl, user, password);
422

423
// Bulk insert
424
PreparedStatement bulkInsert = bulkConn.prepareStatement(
425
    "INSERT INTO log_entries (timestamp, level, message) VALUES (?, ?, ?)"
426
);
427

428
// Add multiple rows to batch
429
for (LogEntry entry : logEntries) {
430
    bulkInsert.setTimestamp(1, entry.getTimestamp());
431
    bulkInsert.setString(2, entry.getLevel());
432
    bulkInsert.setString(3, entry.getMessage());
433
    bulkInsert.addBatch();
434
}
435

436
// Execute all at once with bulk protocol
437
int[] results = bulkInsert.executeBatch();
438
```
439

440
### Statement Caching
441

442
```java
443
// Client-side statement caching
444
String cacheUrl = "jdbc:mariadb://localhost:3306/mydb?" +
445
    "cachePrepStmts=true&" +           // Enable caching
446
    "prepStmtCacheSize=250&" +         // Cache up to 250 statements
447
    "prepStmtCacheSqlLimit=2048";      // Cache statements up to 2KB
448

449
// Server-side prepared statements
450
String serverPrepUrl = "jdbc:mariadb://localhost:3306/mydb?" +
451
    "useServerPrepStmts=true&" +       // Use server-side preparation
452
    "cachePrepStmts=true";             // Cache prepared statements
453

454
// Statements will be automatically cached and reused
455
Connection cacheConn = DriverManager.getConnection(cacheUrl, user, password);
456
PreparedStatement cachedStmt = cacheConn.prepareStatement("SELECT * FROM users WHERE id = ?");
457
// Subsequent prepareStatement calls with same SQL will reuse cached statement
458
```