0
# SQL Service
1

2
Hazelcast provides a distributed SQL engine that allows you to query data across the cluster using standard SQL syntax. The SQL service supports querying maps, accessing streaming data, and performing distributed joins and aggregations.
3

4
## SqlService Interface
5

6
The main interface for executing SQL operations in Hazelcast.
7

8
```java { .api }
9
import com.hazelcast.sql.SqlService;
10
import com.hazelcast.sql.SqlResult;
11
import com.hazelcast.sql.SqlStatement;
12

13
public interface SqlService {
14
    // Execute SQL with inline parameters
15
    SqlResult execute(String sql, Object... arguments);
16
    
17
    // Execute SQL statement object
18
    SqlResult execute(SqlStatement statement);
19
    
20
    // Execute DML statements (INSERT, UPDATE, DELETE)
21
    long executeUpdate(String sql, Object... arguments);
22
}
23
```
24

25
### Getting SqlService
26

27
```java { .api }
28
HazelcastInstance hz = Hazelcast.newHazelcastInstance();
29
SqlService sql = hz.getSql();
30
```
31

32
## SQL Statements
33

34
### SqlStatement Class
35

36
Represents a prepared SQL statement with parameters and execution options.
37

38
```java { .api }
39
import com.hazelcast.sql.SqlStatement;
40
import com.hazelcast.sql.SqlExpectedResultType;
41
import java.util.concurrent.TimeUnit;
42

43
public class SqlStatement {
44
    // Statement creation
45
    public static SqlStatement of(String sql);
46
    public static SqlStatement of(String sql, Object... parameters);
47
    
48
    // Configuration methods
49
    public SqlStatement setSql(String sql);
50
    public String getSql();
51
    
52
    public SqlStatement setParameters(List<?> parameters);
53
    public SqlStatement addParameter(Object parameter);
54
    public List<Object> getParameters();
55
    
56
    public SqlStatement setTimeoutMillis(long timeoutMillis);
57
    public long getTimeoutMillis();
58
    
59
    public SqlStatement setCursorBufferSize(int cursorBufferSize);
60
    public int getCursorBufferSize();
61
    
62
    public SqlStatement setExpectedResultType(SqlExpectedResultType expectedResultType);
63
    public SqlExpectedResultType getExpectedResultType();
64
    
65
    public SqlStatement setSchema(String schema);
66
    public String getSchema();
67
}
68
```
69

70
### Expected Result Types
71

72
```java { .api }
73
import com.hazelcast.sql.SqlExpectedResultType;
74

75
public enum SqlExpectedResultType {
76
    ANY,           // Any result type is acceptable
77
    ROWS,          // Expect query results (SELECT)
78
    UPDATE_COUNT   // Expect update count (INSERT, UPDATE, DELETE)
79
}
80
```
81

82
### Basic SQL Execution
83

84
```java { .api }
85
// Simple query execution
86
SqlResult result = sql.execute("SELECT name, age FROM employees WHERE age > ?", 25);
87

88
// Using SqlStatement
89
SqlStatement statement = SqlStatement.of("SELECT * FROM products WHERE category = ? AND price > ?")
90
    .addParameter("electronics")
91
    .addParameter(100.0)
92
    .setTimeoutMillis(30000);
93

94
SqlResult result = sql.execute(statement);
95

96
// DML operations
97
long updateCount = sql.executeUpdate("UPDATE employees SET salary = salary * 1.1 WHERE department = ?", "Engineering");
98
System.out.println("Updated " + updateCount + " employees");
99
```
100

101
## SQL Results
102

103
### SqlResult Interface
104

105
Represents the result of a SQL query execution.
106

107
```java { .api }
108
import com.hazelcast.sql.SqlResult;
109
import com.hazelcast.sql.SqlRow;
110
import com.hazelcast.sql.SqlRowMetadata;
111
import java.util.Iterator;
112

113
public interface SqlResult extends Iterable<SqlRow>, AutoCloseable {
114
    // Metadata
115
    SqlRowMetadata getRowMetadata();
116
    
117
    // Row iteration
118
    Iterator<SqlRow> iterator();
119
    
120
    // Update count for DML operations
121
    long updateCount();
122
    boolean isUpdateCountValid();
123
    
124
    // Resource management
125
    void close();
126
}
127
```
128

129
### SqlRow Interface
130

131
Represents a single row in the result set.
132

133
```java { .api }
134
import com.hazelcast.sql.SqlRow;
135

136
public interface SqlRow {
137
    // Get by column index
138
    <T> T getObject(int columnIndex);
139
    
140
    // Get by column name
141
    <T> T getObject(String columnName);
142
    
143
    // Metadata
144
    SqlRowMetadata getMetadata();
145
}
146
```
147

148
### SqlRowMetadata Interface
149

150
Provides metadata about the result set structure.
151

152
```java { .api }
153
import com.hazelcast.sql.SqlRowMetadata;
154
import com.hazelcast.sql.SqlColumnMetadata;
155
import java.util.List;
156

157
public interface SqlRowMetadata {
158
    // Column information
159
    int getColumnCount();
160
    List<SqlColumnMetadata> getColumns();
161
    SqlColumnMetadata getColumn(int index);
162
    
163
    // Column lookup
164
    int findColumn(String columnName);
165
}
166
```
167

168
### SqlColumnMetadata Interface
169

170
Provides metadata about individual columns.
171

172
```java { .api }
173
import com.hazelcast.sql.SqlColumnMetadata;
174
import com.hazelcast.sql.SqlColumnType;
175

176
public interface SqlColumnMetadata {
177
    String getName();
178
    SqlColumnType getType();
179
    boolean isNullableUnknown();
180
}
181
```
182

183
### SQL Column Types
184

185
```java { .api }
186
import com.hazelcast.sql.SqlColumnType;
187

188
public enum SqlColumnType {
189
    VARCHAR,
190
    BOOLEAN,
191
    TINYINT,
192
    SMALLINT,
193
    INTEGER,
194
    BIGINT,
195
    DECIMAL,
196
    REAL,
197
    DOUBLE,
198
    DATE,
199
    TIME,
200
    TIMESTAMP,
201
    TIMESTAMP_WITH_TIME_ZONE,
202
    OBJECT,
203
    NULL,
204
    JSON
205
}
206
```
207

208
## Query Examples
209

210
### Basic Queries
211

212
```java { .api }
213
// SELECT queries
214
SqlResult result = sql.execute("SELECT * FROM employees");
215

216
// Process results
217
for (SqlRow row : result) {
218
    String name = row.getObject("name");
219
    Integer age = row.getObject("age");
220
    String department = row.getObject("department");
221
    
222
    System.out.println(name + ", " + age + ", " + department);
223
}
224

225
// Always close results
226
result.close();
227

228
// Using try-with-resources
229
try (SqlResult result = sql.execute("SELECT COUNT(*) as employee_count FROM employees")) {
230
    for (SqlRow row : result) {
231
        Long count = row.getObject("employee_count");
232
        System.out.println("Total employees: " + count);
233
    }
234
}
235
```
236

237
### Parameterized Queries
238

239
```java { .api }
240
// Parameterized queries prevent SQL injection
241
String department = "Engineering";
242
int minAge = 25;
243

244
try (SqlResult result = sql.execute(
245
    "SELECT name, age, salary FROM employees WHERE department = ? AND age >= ?",
246
    department, minAge)) {
247
    
248
    for (SqlRow row : result) {
249
        System.out.println(String.format("%s (%d): $%.2f", 
250
            row.getObject("name"),
251
            row.<Integer>getObject("age"),
252
            row.<Double>getObject("salary")));
253
    }
254
}
255
```
256

257
### Aggregation Queries
258

259
```java { .api }
260
// Aggregation with grouping
261
try (SqlResult result = sql.execute(
262
    "SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary " +
263
    "FROM employees " +
264
    "GROUP BY department " +
265
    "HAVING COUNT(*) > ?"
266
    , 5)) {
267
    
268
    System.out.println("Department Statistics:");
269
    for (SqlRow row : result) {
270
        String dept = row.getObject("department");
271
        Long count = row.getObject("emp_count");
272
        Double avgSalary = row.getObject("avg_salary");
273
        
274
        System.out.println(String.format("%s: %d employees, avg salary: $%.2f", 
275
            dept, count, avgSalary));
276
    }
277
}
278
```
279

280
### Window Functions
281

282
```java { .api }
283
// Window functions for ranking
284
try (SqlResult result = sql.execute(
285
    "SELECT name, department, salary, " +
286
    "       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank, " +
287
    "       ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank " +
288
    "FROM employees")) {
289
    
290
    for (SqlRow row : result) {
291
        System.out.println(String.format("%s (%s): Salary $%.2f, Dept Rank: %d, Overall Rank: %d",
292
            row.<String>getObject("name"),
293
            row.<String>getObject("department"),
294
            row.<Double>getObject("salary"),
295
            row.<Long>getObject("dept_rank"),
296
            row.<Long>getObject("overall_rank")));
297
    }
298
}
299
```
300

301
## Data Manipulation
302

303
### INSERT Operations
304

305
```java { .api }
306
// Insert single record
307
long insertCount = sql.executeUpdate(
308
    "INSERT INTO employees (id, name, age, department, salary) VALUES (?, ?, ?, ?, ?)",
309
    1001, "Alice Johnson", 28, "Engineering", 75000.0);
310

311
System.out.println("Inserted " + insertCount + " record(s)");
312

313
// Insert multiple records with batch
314
SqlStatement insertStatement = SqlStatement.of(
315
    "INSERT INTO employees (id, name, age, department, salary) VALUES (?, ?, ?, ?, ?)");
316

317
// Note: Batch operations would typically be done in a loop or with bulk insert
318
long count1 = sql.executeUpdate(insertStatement.getSql(), 1002, "Bob Smith", 32, "Marketing", 68000.0);
319
long count2 = sql.executeUpdate(insertStatement.getSql(), 1003, "Carol Davis", 29, "Engineering", 72000.0);
320
```
321

322
### UPDATE Operations
323

324
```java { .api }
325
// Update with conditions
326
long updateCount = sql.executeUpdate(
327
    "UPDATE employees SET salary = salary * ? WHERE department = ? AND age < ?",
328
    1.15, "Engineering", 30);
329

330
System.out.println("Updated " + updateCount + " employees");
331

332
// Conditional update with CASE
333
sql.executeUpdate(
334
    "UPDATE employees SET bonus = " +
335
    "CASE " +
336
    "  WHEN salary > 80000 THEN salary * 0.15 " +
337
    "  WHEN salary > 60000 THEN salary * 0.10 " +
338
    "  ELSE salary * 0.05 " +
339
    "END " +
340
    "WHERE department = ?", "Sales");
341
```
342

343
### DELETE Operations
344

345
```java { .api }
346
// Delete with conditions
347
long deleteCount = sql.executeUpdate(
348
    "DELETE FROM employees WHERE age > ? AND department = ?",
349
    65, "Retired");
350

351
System.out.println("Deleted " + deleteCount + " employees");
352

353
// Delete with subquery
354
sql.executeUpdate(
355
    "DELETE FROM employees WHERE id IN " +
356
    "(SELECT id FROM employees WHERE salary < (SELECT AVG(salary) * 0.5 FROM employees))");
357
```
358

359
## Advanced SQL Features
360

361
### Joins Across Maps
362

363
```java { .api }
364
// Setup: Assume we have employees and departments maps
365
// employees: {id, name, dept_id, salary}
366
// departments: {id, name, budget}
367

368
// Inner join
369
try (SqlResult result = sql.execute(
370
    "SELECT e.name as employee_name, d.name as department_name, e.salary " +
371
    "FROM employees e " +
372
    "INNER JOIN departments d ON e.dept_id = d.id " +
373
    "WHERE e.salary > ?", 70000)) {
374
    
375
    for (SqlRow row : result) {
376
        System.out.println(String.format("%s works in %s, earns $%.2f",
377
            row.<String>getObject("employee_name"),
378
            row.<String>getObject("department_name"),
379
            row.<Double>getObject("salary")));
380
    }
381
}
382

383
// Left join with aggregation
384
try (SqlResult result = sql.execute(
385
    "SELECT d.name as department, COUNT(e.id) as employee_count, " +
386
    "       COALESCE(AVG(e.salary), 0) as avg_salary " +
387
    "FROM departments d " +
388
    "LEFT JOIN employees e ON d.id = e.dept_id " +
389
    "GROUP BY d.name " +
390
    "ORDER BY employee_count DESC")) {
391
    
392
    for (SqlRow row : result) {
393
        System.out.println(String.format("%s: %d employees, avg salary: $%.2f",
394
            row.<String>getObject("department"),
395
            row.<Long>getObject("employee_count"),
396
            row.<Double>getObject("avg_salary")));
397
    }
398
}
399
```
400

401
### Streaming SQL
402

403
```java { .api }
404
// Query streaming data from map journals or topics
405
// Note: This requires appropriate configuration for streaming
406

407
try (SqlResult result = sql.execute(
408
    "SELECT * FROM TABLE(IMPOSE_ORDER(" +
409
    "  TABLE(stream_from_map_journal('events', 'event_timestamp')), " +
410
    "  DESCRIPTOR(event_timestamp), " +
411
    "  INTERVAL '1' SECOND" +
412
    "))")) {
413
    
414
    for (SqlRow row : result) {
415
        // Process streaming data
416
        System.out.println("Event: " + row.getObject("event_data"));
417
    }
418
}
419
```
420

421
### JSON Support
422

423
```java { .api }
424
// Working with JSON data
425
try (SqlResult result = sql.execute(
426
    "SELECT " +
427
    "  JSON_EXTRACT(user_data, '$.name') as name, " +
428
    "  JSON_EXTRACT(user_data, '$.preferences.theme') as theme " +
429
    "FROM user_profiles " +
430
    "WHERE JSON_EXTRACT(user_data, '$.active') = true")) {
431
    
432
    for (SqlRow row : result) {
433
        String name = row.getObject("name");
434
        String theme = row.getObject("theme");
435
        System.out.println("User " + name + " prefers " + theme + " theme");
436
    }
437
}
438
```
439

440
## Exception Handling
441

442
### HazelcastSqlException
443

444
```java { .api }
445
import com.hazelcast.sql.HazelcastSqlException;
446

447
public class HazelcastSqlException extends HazelcastException {
448
    public int getCode();
449
    public String getMessage();
450
    public String getSuggestion();
451
    public Member getOriginatingMember();
452
}
453
```
454

455
### Error Handling Examples
456

457
```java { .api }
458
try {
459
    SqlResult result = sql.execute("SELECT * FROM non_existent_table");
460
    // Process results...
461
    
462
} catch (HazelcastSqlException e) {
463
    System.err.println("SQL Error " + e.getCode() + ": " + e.getMessage());
464
    if (e.getSuggestion() != null) {
465
        System.err.println("Suggestion: " + e.getSuggestion());
466
    }
467
    
468
    // Handle specific error codes
469
    switch (e.getCode()) {
470
        case 1000: // Generic error
471
            // Handle generic error
472
            break;
473
        case 2000: // Parsing error
474
            System.err.println("SQL syntax error");
475
            break;
476
        case 3000: // Data conversion error
477
            System.err.println("Data type conversion failed");
478
            break;
479
        default:
480
            System.err.println("Unhandled SQL error");
481
    }
482
    
483
} catch (Exception e) {
484
    System.err.println("Unexpected error: " + e.getMessage());
485
}
486
```
487

488
## Performance and Best Practices
489

490
### Query Optimization
491

492
```java { .api }
493
// Use indexes for better performance
494
// This should be done during map configuration, not via SQL
495
// But you can check index usage in execution plans
496

497
// Parameterized queries for prepared statement benefits
498
SqlStatement statement = SqlStatement.of("SELECT * FROM employees WHERE department = ?")
499
    .setCursorBufferSize(1000)  // Optimize for large result sets
500
    .setTimeoutMillis(30000);   // Set appropriate timeout
501

502
// Process in batches for large results
503
try (SqlResult result = sql.execute(statement.addParameter("Engineering"))) {
504
    int batchSize = 0;
505
    for (SqlRow row : result) {
506
        // Process row
507
        batchSize++;
508
        
509
        // Process in batches
510
        if (batchSize % 1000 == 0) {
511
            System.out.println("Processed " + batchSize + " rows");
512
        }
513
    }
514
}
515
```
516

517
### Resource Management
518

519
```java { .api }
520
// Always close SqlResult to free resources
521
public List<Employee> getEmployees(String department) {
522
    List<Employee> employees = new ArrayList<>();
523
    
524
    try (SqlResult result = sql.execute(
525
        "SELECT id, name, salary FROM employees WHERE department = ?", department)) {
526
        
527
        for (SqlRow row : result) {
528
            employees.add(new Employee(
529
                row.<Integer>getObject("id"),
530
                row.<String>getObject("name"),
531
                row.<Double>getObject("salary")
532
            ));
533
        }
534
    }
535
    
536
    return employees;
537
}
538

539
// For streaming queries, handle properly
540
public void processStreamingData() {
541
    SqlStatement streamingQuery = SqlStatement.of(
542
        "SELECT * FROM events_stream WHERE event_type = ?")
543
        .addParameter("user_action")
544
        .setCursorBufferSize(100); // Smaller buffer for streaming
545
    
546
    try (SqlResult result = sql.execute(streamingQuery)) {
547
        for (SqlRow row : result) {
548
            // Process streaming event
549
            processEvent(row);
550
            
551
            // Add break condition for long-running streams
552
            if (shouldStop()) {
553
                break;
554
            }
555
        }
556
    }
557
}
558
```
559

560
### Mapping Configuration for SQL
561

562
```java { .api }
563
// Configure maps for optimal SQL performance
564
Config config = new Config();
565

566
MapConfig mapConfig = new MapConfig("employees");
567
// Add indexes for commonly queried fields
568
mapConfig.addIndexConfig(new IndexConfig(IndexType.SORTED, "age"));
569
mapConfig.addIndexConfig(new IndexConfig(IndexType.HASH, "department"));
570
mapConfig.addIndexConfig(new IndexConfig(IndexType.BITMAP, "active"));
571

572
config.addMapConfig(mapConfig);
573

574
HazelcastInstance hz = Hazelcast.newHazelcastInstance(config);
575
```
576

577
## Common SQL Patterns
578

579
### Pagination
580

581
```java { .api }
582
public List<Employee> getEmployeesPage(int offset, int limit) {
583
    List<Employee> employees = new ArrayList<>();
584
    
585
    try (SqlResult result = sql.execute(
586
        "SELECT * FROM employees ORDER BY name LIMIT ? OFFSET ?", 
587
        limit, offset)) {
588
        
589
        for (SqlRow row : result) {
590
            employees.add(mapRowToEmployee(row));
591
        }
592
    }
593
    
594
    return employees;
595
}
596
```
597

598
### Existence Check
599

600
```java { .api }
601
public boolean employeeExists(int employeeId) {
602
    try (SqlResult result = sql.execute(
603
        "SELECT 1 FROM employees WHERE id = ? LIMIT 1", employeeId)) {
604
        
605
        return result.iterator().hasNext();
606
    }
607
}
608
```
609

610
### Conditional Aggregation
611

612
```java { .api }
613
public DepartmentStats getDepartmentStats(String department) {
614
    try (SqlResult result = sql.execute(
615
        "SELECT " +
616
        "  COUNT(*) as total_count, " +
617
        "  COUNT(CASE WHEN age < 30 THEN 1 END) as young_count, " +
618
        "  COUNT(CASE WHEN salary > 80000 THEN 1 END) as high_earners, " +
619
        "  AVG(salary) as avg_salary, " +
620
        "  MIN(salary) as min_salary, " +
621
        "  MAX(salary) as max_salary " +
622
        "FROM employees WHERE department = ?", department)) {
623
        
624
        if (result.iterator().hasNext()) {
625
            SqlRow row = result.iterator().next();
626
            return new DepartmentStats(
627
                row.<Long>getObject("total_count"),
628
                row.<Long>getObject("young_count"),
629
                row.<Long>getObject("high_earners"),
630
                row.<Double>getObject("avg_salary"),
631
                row.<Double>getObject("min_salary"),
632
                row.<Double>getObject("max_salary")
633
            );
634
        }
635
    }
636
    
637
    return null;
638
}
639
```