0
# X DevAPI SQL Operations
1

2
Direct SQL execution and result handling within the X DevAPI context. These interfaces allow executing SQL statements and processing results while using the X DevAPI session.
3

4
## Capabilities
5

6
### SQL Statement Execution
7

8
Execute SQL statements within X DevAPI sessions.
9

10
```java { .api }
11
package com.mysql.cj.xdevapi;
12

13
public interface SqlStatement extends Statement<SqlStatement, SqlResult> {
14
    // Parameter binding
15
    SqlStatement bind(Object... values);
16
    SqlStatement bind(List<Object> values);
17
    SqlStatement bind(Map<String, Object> values);
18

19
    // Execution
20
    SqlResult execute();
21
}
22

23
public class SqlStatementImpl implements SqlStatement {
24
    public SqlStatementImpl(Session session, String sql);
25
    
26
    public SqlStatement bind(Object... values);
27
    public SqlStatement bind(List<Object> values);
28
    public SqlStatement bind(Map<String, Object> values);
29
    public SqlResult execute();
30
}
31
```
32

33
Usage:
34

35
```java
36
Session session = factory.getSession("mysqlx://root:password@localhost:33060/mydb");
37

38
// Execute simple SQL
39
SqlResult result = session.sql("SELECT * FROM users").execute();
40

41
// Execute SQL with positional parameters
42
SqlResult result2 = session.sql("SELECT * FROM users WHERE age > ?")
43
    .bind(25)
44
    .execute();
45

46
// Execute SQL with named parameters
47
SqlResult result3 = session.sql("SELECT * FROM users WHERE age > :minAge AND city = :city")
48
    .bind(Map.of("minAge", 25, "city", "New York"))
49
    .execute();
50

51
// Execute DML
52
SqlResult insertResult = session.sql("INSERT INTO users (name, age) VALUES (?, ?)")
53
    .bind("Alice", 30)
54
    .execute();
55
System.out.println("Affected rows: " + insertResult.getAffectedItemsCount());
56

57
// Execute DDL
58
session.sql("CREATE TABLE temp (id INT, name VARCHAR(50))").execute();
59
session.sql("DROP TABLE temp").execute();
60
```
61

62
### SQL Result Handling
63

64
Handle results from SQL statement execution.
65

66
```java { .api }
67
package com.mysql.cj.xdevapi;
68

69
public interface SqlResult extends Result, RowResult, Iterator<Row> {
70
    // Check if result has data
71
    boolean hasData();
72

73
    // Move to next result set
74
    SqlResult nextResult();
75

76
    // Get auto-increment value
77
    long getAutoIncrementValue();
78

79
    // Result information
80
    long getAffectedItemsCount();
81
    int getWarningsCount();
82
    Iterator<Warning> getWarnings();
83

84
    // Row iteration (from RowResult)
85
    boolean hasNext();
86
    Row next();
87
    Row fetchOne();
88
    List<Row> fetchAll();
89
    int count();
90

91
    // Column metadata (from RowResult)
92
    int getColumnCount();
93
    List<Column> getColumns();
94
    List<String> getColumnNames();
95
}
96

97
public class SqlSingleResult implements SqlResult {
98
    // Single result set implementation
99
    public boolean hasData();
100
    public SqlResult nextResult();
101
    public long getAutoIncrementValue();
102
    public long getAffectedItemsCount();
103
    public int getWarningsCount();
104
    public Iterator<Warning> getWarnings();
105
    public boolean hasNext();
106
    public Row next();
107
    public Row fetchOne();
108
    public List<Row> fetchAll();
109
    public int count();
110
    public int getColumnCount();
111
    public List<Column> getColumns();
112
    public List<String> getColumnNames();
113
}
114

115
public class SqlMultiResult implements SqlResult {
116
    // Multiple result sets implementation
117
    public boolean hasData();
118
    public SqlResult nextResult();
119
    public long getAutoIncrementValue();
120
    public long getAffectedItemsCount();
121
    public int getWarningsCount();
122
    public Iterator<Warning> getWarnings();
123
    public boolean hasNext();
124
    public Row next();
125
    public Row fetchOne();
126
    public List<Row> fetchAll();
127
    public int count();
128
    public int getColumnCount();
129
    public List<Column> getColumns();
130
    public List<String> getColumnNames();
131
}
132

133
public class SqlUpdateResult implements SqlResult {
134
    // DML/DDL result implementation (no data rows)
135
    public boolean hasData();
136
    public SqlResult nextResult();
137
    public long getAutoIncrementValue();
138
    public long getAffectedItemsCount();
139
    public int getWarningsCount();
140
    public Iterator<Warning> getWarnings();
141
}
142
```
143

144
Usage:
145

146
```java
147
Session session = factory.getSession("mysqlx://root:password@localhost:33060/mydb");
148

149
// Query with result set
150
SqlResult result = session.sql("SELECT * FROM users WHERE age > ?")
151
    .bind(25)
152
    .execute();
153

154
if (result.hasData()) {
155
    while (result.hasNext()) {
156
        Row row = result.next();
157
        System.out.println(row.getString("name") + " - " + row.getInt("age"));
158
    }
159
}
160

161
// Get column metadata
162
List<Column> columns = result.getColumns();
163
for (Column col : columns) {
164
    System.out.println(col.getColumnName() + " (" + col.getType() + ")");
165
}
166

167
// INSERT with auto-increment
168
SqlResult insertResult = session.sql("INSERT INTO users (name, age) VALUES (?, ?)")
169
    .bind("Bob", 28)
170
    .execute();
171
System.out.println("Auto-increment ID: " + insertResult.getAutoIncrementValue());
172
System.out.println("Affected rows: " + insertResult.getAffectedItemsCount());
173

174
// Multiple result sets (from stored procedure)
175
SqlResult multiResult = session.sql("CALL get_user_stats()").execute();
176
do {
177
    if (multiResult.hasData()) {
178
        System.out.println("Result set:");
179
        while (multiResult.hasNext()) {
180
            Row row = multiResult.next();
181
            // Process row
182
        }
183
    }
184
} while ((multiResult = multiResult.nextResult()) != null);
185

186
// Check warnings
187
if (result.getWarningsCount() > 0) {
188
    Iterator<Warning> warnings = result.getWarnings();
189
    while (warnings.hasNext()) {
190
        Warning warning = warnings.next();
191
        System.out.println("Warning: " + warning.getMessage());
192
    }
193
}
194

195
// Fetch all rows at once
196
List<Row> allRows = result.fetchAll();
197
```
198

199
### Result Builders
200

201
Internal result builders for constructing result objects.
202

203
```java { .api }
204
package com.mysql.cj.xdevapi;
205

206
public class SqlResultBuilder implements com.mysql.cj.protocol.ResultBuilder<SqlResult> {
207
    // Build SQL result from protocol messages
208
    public SqlResultBuilder(Session session);
209
    public boolean addProtocolEntity(com.mysql.cj.protocol.ProtocolEntity entity);
210
    public SqlResult build();
211
}
212

213
public class StreamingSqlResultBuilder implements com.mysql.cj.protocol.ResultBuilder<SqlResult> {
214
    // Build streaming SQL result
215
    public StreamingSqlResultBuilder(Session session);
216
    public boolean addProtocolEntity(com.mysql.cj.protocol.ProtocolEntity entity);
217
    public SqlResult build();
218
}
219
```
220

221
### Transaction Support in SQL
222

223
SQL statements respect session transaction boundaries.
224

225
Usage:
226

227
```java
228
Session session = factory.getSession("mysqlx://root:password@localhost:33060/mydb");
229

230
// Begin transaction
231
session.startTransaction();
232

233
try {
234
    // Execute multiple SQL statements
235
    session.sql("INSERT INTO accounts (name, balance) VALUES (?, ?)")
236
        .bind("Alice", 1000)
237
        .execute();
238
    
239
    session.sql("UPDATE accounts SET balance = balance - ? WHERE name = ?")
240
        .bind(100, "Alice")
241
        .execute();
242
    
243
    session.sql("INSERT INTO transactions (account, amount) VALUES (?, ?)")
244
        .bind("Alice", -100)
245
        .execute();
246
    
247
    // Commit transaction
248
    session.commit();
249
} catch (Exception e) {
250
    // Rollback on error
251
    session.rollback();
252
    throw e;
253
}
254

255
session.close();
256
```
257

258
### SQL with Collections
259

260
Execute SQL on collections (treated as tables).
261

262
Usage:
263

264
```java
265
Session session = factory.getSession("mysqlx://root:password@localhost:33060/mydb");
266

267
// Query collection as table
268
SqlResult result = session.sql(
269
    "SELECT doc->>'$.name' as name, doc->>'$.age' as age " +
270
    "FROM mydb.users " +
271
    "WHERE doc->>'$.age' > ?"
272
).bind(25).execute();
273

274
while (result.hasNext()) {
275
    Row row = result.next();
276
    System.out.println(row.getString("name") + " - " + row.getString("age"));
277
}
278

279
// Update collection document via SQL
280
session.sql(
281
    "UPDATE mydb.users " +
282
    "SET doc = JSON_SET(doc, '$.status', 'active') " +
283
    "WHERE doc->>'$.age' > ?"
284
).bind(18).execute();
285

286
session.close();
287
```
288