0
# Extension APIs
1

2
H2 Database Engine provides comprehensive extension APIs that allow developers to customize and extend database functionality. These include triggers for database events, custom aggregate functions, table engines for alternative storage, event listeners for monitoring, and authentication providers.
3

4
## Trigger System
5

6
### Trigger Interface
7

8
Core interface for implementing database triggers that fire on table operations.
9

10
```java { .api }
11
public interface Trigger {
12
    // Trigger types
13
    int INSERT = 1;
14
    int UPDATE = 2;
15
    int DELETE = 4;
16
    int SELECT = 8;
17
    
18
    // Lifecycle methods
19
    default void init(Connection conn, String schemaName, String triggerName, 
20
                     String tableName, boolean before, int type) throws SQLException {
21
        // Default implementation - override if needed
22
    }
23
    
24
    void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException;
25
    
26
    default void close() throws SQLException {
27
        // Default implementation - override if needed
28
    }
29
    
30
    default void remove() throws SQLException {
31
        // Default implementation - override if needed
32
    }
33
}
34
```
35

36
**Usage Examples:**
37

38
```java
39
// Audit trigger implementation
40
public class AuditTrigger implements Trigger {
41
    private String auditTable;
42
    
43
    @Override
44
    public void init(Connection conn, String schemaName, String triggerName,
45
                    String tableName, boolean before, int type) throws SQLException {
46
        this.auditTable = tableName + "_audit";
47
        
48
        // Create audit table if it doesn't exist
49
        String createAudit = "CREATE TABLE IF NOT EXISTS " + auditTable + " (" +
50
            "audit_id IDENTITY PRIMARY KEY, " +
51
            "operation VARCHAR(10), " +
52
            "table_name VARCHAR(255), " +
53
            "old_values CLOB, " +
54
            "new_values CLOB, " +
55
            "changed_by VARCHAR(255), " +
56
            "changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
57
            ")";
58
        conn.createStatement().execute(createAudit);
59
    }
60
    
61
    @Override
62
    public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
63
        String operation;
64
        if (oldRow == null) {
65
            operation = "INSERT";
66
        } else if (newRow == null) {
67
            operation = "DELETE";
68
        } else {
69
            operation = "UPDATE";
70
        }
71
        
72
        PreparedStatement stmt = conn.prepareStatement(
73
            "INSERT INTO " + auditTable + 
74
            " (operation, table_name, old_values, new_values, changed_by) VALUES (?, ?, ?, ?, ?)");
75
        
76
        stmt.setString(1, operation);
77
        stmt.setString(2, "customers"); // Could be dynamic
78
        stmt.setString(3, oldRow != null ? Arrays.toString(oldRow) : null);
79
        stmt.setString(4, newRow != null ? Arrays.toString(newRow) : null);
80
        stmt.setString(5, System.getProperty("user.name"));
81
        
82
        stmt.executeUpdate();
83
    }
84
}
85

86
// Register trigger
87
Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
88
conn.createStatement().execute(
89
    "CREATE TRIGGER audit_customers AFTER INSERT, UPDATE, DELETE ON customers " +
90
    "FOR EACH ROW CALL \"com.example.AuditTrigger\"");
91
```
92

93
### TriggerAdapter
94

95
Abstract adapter class that simplifies trigger implementation.
96

97
```java { .api }
98
public abstract class TriggerAdapter implements Trigger {
99
    // Template methods for specific operations
100
    protected void fireInsert(Connection conn, Object[] newRow) throws SQLException {
101
        // Override in subclass
102
    }
103
    
104
    protected void fireUpdate(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
105
        // Override in subclass  
106
    }
107
    
108
    protected void fireDelete(Connection conn, Object[] oldRow) throws SQLException {
109
        // Override in subclass
110
    }
111
    
112
    protected void fireSelect(Connection conn, Object[] row) throws SQLException {
113
        // Override in subclass
114
    }
115
    
116
    @Override
117
    public final void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
118
        if (oldRow == null) {
119
            fireInsert(conn, newRow);
120
        } else if (newRow == null) {
121
            fireDelete(conn, oldRow);
122
        } else {
123
            fireUpdate(conn, oldRow, newRow);
124
        }
125
    }
126
}
127
```
128

129
**Usage Examples:**
130

131
```java
132
// Simplified trigger using adapter
133
public class ValidationTrigger extends TriggerAdapter {
134
    
135
    @Override
136
    protected void fireInsert(Connection conn, Object[] newRow) throws SQLException {
137
        validateRow(newRow);
138
    }
139
    
140
    @Override
141
    protected void fireUpdate(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
142
        validateRow(newRow);
143
    }
144
    
145
    private void validateRow(Object[] row) throws SQLException {
146
        // Assume first column is email
147
        if (row.length > 1 && row[1] != null) {
148
            String email = row[1].toString();
149
            if (!email.contains("@")) {
150
                throw new SQLException("Invalid email format: " + email);
151
            }
152
        }
153
    }
154
}
155
```
156

157
## Aggregate Functions
158

159
### AggregateFunction Interface
160

161
Interface for creating custom aggregate functions that work with standard SQL types.
162

163
```java { .api }
164
public interface AggregateFunction {
165
    // Lifecycle
166
    default void init(Connection conn) throws SQLException {
167
        // Default implementation - override if needed
168
    }
169
    
170
    // Type information
171
    int getType(int[] inputTypes) throws SQLException;
172
    
173
    // Aggregation
174
    void add(Object value) throws SQLException;
175
    Object getResult() throws SQLException;
176
}
177
```
178

179
**Usage Examples:**
180

181
```java
182
// String concatenation aggregate
183
public class ConcatAggregate implements AggregateFunction {
184
    private StringBuilder result = new StringBuilder();
185
    private String separator = "";
186
    
187
    @Override
188
    public void init(Connection conn) throws SQLException {
189
        result = new StringBuilder();
190
        separator = "";
191
    }
192
    
193
    @Override
194
    public int getType(int[] inputTypes) throws SQLException {
195
        return Types.VARCHAR;
196
    }
197
    
198
    @Override
199
    public void add(Object value) throws SQLException {
200
        if (value != null) {
201
            result.append(separator).append(value.toString());
202
            separator = ",";
203
        }
204
    }
205
    
206
    @Override
207
    public Object getResult() throws SQLException {
208
        return result.toString();
209
    }
210
}
211

212
// Register and use aggregate
213
Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
214
conn.createStatement().execute(
215
    "CREATE AGGREGATE CONCAT_AGG FOR \"com.example.ConcatAggregate\"");
216

217
// Use in SQL
218
ResultSet rs = conn.createStatement().executeQuery(
219
    "SELECT department, CONCAT_AGG(name) as employees FROM staff GROUP BY department");
220
```
221

222
### Aggregate Interface
223

224
Alternative interface for aggregate functions that work with H2's internal Value types.
225

226
```java { .api }
227
public interface Aggregate {
228
    // Lifecycle
229
    default void init(Connection conn) throws SQLException {
230
        // Default implementation
231
    }
232
    
233
    // Type information (returns H2 internal type)
234
    int getInternalType(int[] inputTypes) throws SQLException;
235
    
236
    // Aggregation
237
    void add(Object value) throws SQLException;
238
    Object getResult() throws SQLException;
239
}
240
```
241

242
**Usage Examples:**
243

244
```java
245
// Statistical aggregate using H2 Value types
246
public class StatsAggregate implements Aggregate {
247
    private List<Double> values = new ArrayList<>();
248
    
249
    @Override
250
    public void init(Connection conn) throws SQLException {
251
        values.clear();
252
    }
253
    
254
    @Override
255
    public int getInternalType(int[] inputTypes) throws SQLException {
256
        return Value.VARCHAR; // H2 internal type
257
    }
258
    
259
    @Override
260
    public void add(Object value) throws SQLException {
261
        if (value instanceof Number) {
262
            values.add(((Number) value).doubleValue());
263
        }
264
    }
265
    
266
    @Override
267
    public Object getResult() throws SQLException {
268
        if (values.isEmpty()) return "No data";
269
        
270
        double sum = values.stream().mapToDouble(Double::doubleValue).sum();
271
        double mean = sum / values.size();
272
        double variance = values.stream()
273
            .mapToDouble(v -> Math.pow(v - mean, 2))
274
            .sum() / values.size();
275
        double stdDev = Math.sqrt(variance);
276
        
277
        return String.format("Count: %d, Mean: %.2f, StdDev: %.2f", 
278
                           values.size(), mean, stdDev);
279
    }
280
}
281
```
282

283
## Event Listeners
284

285
### DatabaseEventListener
286

287
Interface for monitoring database events, exceptions, and progress.
288

289
```java { .api }
290
public interface DatabaseEventListener extends EventListener {
291
    // Lifecycle events
292
    default void init(String url) {
293
        // Called when listener is registered
294
    }
295
    
296
    default void opened() {
297
        // Called when database is opened
298
    }
299
    
300
    default void closingDatabase() {
301
        // Called before database closes
302
    }
303
    
304
    // Error handling
305
    default void exceptionThrown(SQLException e, String sql) {
306
        // Called when SQL exception occurs
307
    }
308
    
309
    // Progress monitoring
310
    default void setProgress(int state, String name, long x, long max) {
311
        // Called during long-running operations
312
    }
313
    
314
    // State constants
315
    int STATE_SCAN_FILE = 1;
316
    int STATE_CREATE_INDEX = 2;
317
    int STATE_RECOVER = 3;
318
    int STATE_BACKUP_FILE = 4;
319
    int STATE_RECONNECTED = 5;
320
}
321
```
322

323
**Usage Examples:**
324

325
```java
326
// Comprehensive database event logger
327
public class DatabaseEventLogger implements DatabaseEventListener {
328
    private static final Logger logger = LoggerFactory.getLogger(DatabaseEventLogger.class);
329
    
330
    @Override
331
    public void init(String url) {
332
        logger.info("Database event listener initialized for: {}", url);
333
    }
334
    
335
    @Override
336
    public void opened() {
337
        logger.info("Database connection opened");
338
    }
339
    
340
    @Override
341
    public void closingDatabase() {
342
        logger.info("Database is closing");
343
    }
344
    
345
    @Override
346
    public void exceptionThrown(SQLException e, String sql) {
347
        logger.error("SQL Exception occurred. SQL: {}, Error: {}", sql, e.getMessage(), e);
348
        
349
        // Could send alerts, metrics, etc.
350
        if (e.getErrorCode() == ErrorCode.OUT_OF_MEMORY) {
351
            sendCriticalAlert("Database out of memory", e);
352
        }
353
    }
354
    
355
    @Override
356
    public void setProgress(int state, String name, long x, long max) {
357
        String operation = getOperationName(state);
358
        double percentage = max > 0 ? (double) x / max * 100 : 0;
359
        
360
        logger.debug("Progress - {}: {} ({:.1f}%)", operation, name, percentage);
361
        
362
        // Update monitoring dashboards
363
        updateProgressMetrics(operation, percentage);
364
    }
365
    
366
    private String getOperationName(int state) {
367
        switch (state) {
368
            case STATE_SCAN_FILE: return "File Scan";
369
            case STATE_CREATE_INDEX: return "Index Creation";
370
            case STATE_RECOVER: return "Recovery";
371
            case STATE_BACKUP_FILE: return "Backup";
372
            case STATE_RECONNECTED: return "Reconnection";
373
            default: return "Unknown Operation";
374
        }
375
    }
376
    
377
    private void sendCriticalAlert(String message, Exception e) {
378
        // Implementation for alerting system
379
    }
380
    
381
    private void updateProgressMetrics(String operation, double percentage) {
382
        // Implementation for metrics collection
383
    }
384
}
385

386
// Register event listener
387
Connection conn = DriverManager.getConnection(
388
    "jdbc:h2:~/test;DATABASE_EVENT_LISTENER='com.example.DatabaseEventLogger'", "sa", "");
389
```
390

391
## Authentication and Security
392

393
### CredentialsValidator
394

395
Interface for custom credential validation logic.
396

397
```java { .api }
398
public interface CredentialsValidator extends Configurable {
399
    boolean validateCredentials(String userName, String password, String realm) throws Exception;
400
}
401
```
402

403
### UserToRolesMapper
404

405
Interface for mapping authenticated users to database roles.
406

407
```java { .api }
408
public interface UserToRolesMapper extends Configurable {
409
    String[] mapUserToRoles(String userName, String realm) throws Exception;
410
}
411
```
412

413
### Configurable Interface
414

415
Base interface for configurable components.
416

417
```java { .api }
418
public interface Configurable {
419
    void configure(String key, String value);
420
}
421
```
422

423
**Usage Examples:**
424

425
```java
426
// LDAP-based credentials validator
427
public class LdapCredentialsValidator implements CredentialsValidator {
428
    private String ldapUrl;
429
    private String baseDn;
430
    
431
    @Override
432
    public void configure(String key, String value) {
433
        switch (key) {
434
            case "ldapUrl":
435
                this.ldapUrl = value;
436
                break;
437
            case "baseDn":
438
                this.baseDn = value;
439
                break;
440
        }
441
    }
442
    
443
    @Override
444
    public boolean validateCredentials(String userName, String password, String realm) throws Exception {
445
        // LDAP authentication logic
446
        Hashtable<String, String> env = new Hashtable<>();
447
        env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
448
        env.put(Context.PROVIDER_URL, ldapUrl);
449
        env.put(Context.SECURITY_AUTHENTICATION, "simple");
450
        env.put(Context.SECURITY_PRINCIPAL, "uid=" + userName + "," + baseDn);
451
        env.put(Context.SECURITY_CREDENTIALS, password);
452
        
453
        try {
454
            new InitialDirContext(env);
455
            return true; // Authentication successful
456
        } catch (AuthenticationException e) {
457
            return false; // Authentication failed
458
        }
459
    }
460
}
461

462
// Role mapper based on user attributes
463
public class AttributeBasedRoleMapper implements UserToRolesMapper {
464
    private Map<String, String[]> userRoles = new HashMap<>();
465
    
466
    @Override
467
    public void configure(String key, String value) {
468
        // Parse role configuration
469
        // Format: user1=role1,role2;user2=role3
470
        String[] userRolePairs = value.split(";");
471
        for (String pair : userRolePairs) {
472
            String[] parts = pair.split("=");
473
            if (parts.length == 2) {
474
                String user = parts[0].trim();
475
                String[] roles = parts[1].split(",");
476
                for (int i = 0; i < roles.length; i++) {
477
                    roles[i] = roles[i].trim();
478
                }
479
                userRoles.put(user, roles);
480
            }
481
        }
482
    }
483
    
484
    @Override
485
    public String[] mapUserToRoles(String userName, String realm) throws Exception {
486
        return userRoles.getOrDefault(userName, new String[]{"PUBLIC"});
487
    }
488
}
489

490
// Connection with custom authentication
491
String url = "jdbc:h2:~/secure;" +
492
             "CREDENTIALS_VALIDATOR='com.example.LdapCredentialsValidator';" +
493
             "CREDENTIALS_VALIDATOR.ldapUrl='ldap://localhost:389';" +
494
             "CREDENTIALS_VALIDATOR.baseDn='ou=users,dc=example,dc=com';" +
495
             "USER_TO_ROLES_MAPPER='com.example.AttributeBasedRoleMapper';" +
496
             "USER_TO_ROLES_MAPPER=admin=DBA,ADMIN;user1=SELECT_ROLE,INSERT_ROLE";
497

498
Connection conn = DriverManager.getConnection(url, "admin", "password");
499
```
500

501
## Table Engines
502

503
### TableEngine Interface
504

505
Interface for implementing custom table storage engines.
506

507
```java { .api }
508
public interface TableEngine {
509
    TableBase createTable(CreateTableData data);
510
}
511
```
512

513
This is an advanced extension point typically used for integrating external storage systems.
514

515
**Usage Examples:**
516

517
```java
518
// Simple in-memory table engine
519
public class MemoryTableEngine implements TableEngine {
520
    
521
    @Override
522
    public TableBase createTable(CreateTableData data) {
523
        return new MemoryTable(data);
524
    }
525
    
526
    private static class MemoryTable extends TableBase {
527
        private final List<Row> rows = new ArrayList<>();
528
        
529
        public MemoryTable(CreateTableData data) {
530
            // Initialize table structure
531
        }
532
        
533
        // Implement required table operations
534
        // This is a simplified example - full implementation would be extensive
535
    }
536
}
537

538
// Register table engine
539
conn.createStatement().execute(
540
    "CREATE TABLE memory_table (...) ENGINE \"com.example.MemoryTableEngine\"");
541
```
542

543
## Java Object Serialization
544

545
### JavaObjectSerializer
546

547
Interface for custom serialization of Java objects stored in the database.
548

549
```java { .api }
550
public interface JavaObjectSerializer {
551
    byte[] serialize(Object obj) throws Exception;
552
    Object deserialize(byte[] bytes) throws Exception;
553
}
554
```
555

556
**Usage Examples:**
557

558
```java
559
// JSON-based object serializer
560
public class JsonObjectSerializer implements JavaObjectSerializer {
561
    private final ObjectMapper objectMapper = new ObjectMapper();
562
    
563
    @Override
564
    public byte[] serialize(Object obj) throws Exception {
565
        return objectMapper.writeValueAsBytes(obj);
566
    }
567
    
568
    @Override
569
    public Object deserialize(byte[] bytes) throws Exception {
570
        return objectMapper.readValue(bytes, Object.class);
571
    }
572
}
573

574
// Use custom serializer
575
String url = "jdbc:h2:~/test;JAVA_OBJECT_SERIALIZER='com.example.JsonObjectSerializer'";
576
Connection conn = DriverManager.getConnection(url, "sa", "");
577

578
// Store objects that will use custom serialization
579
PreparedStatement stmt = conn.prepareStatement("INSERT INTO objects (data) VALUES (?)");
580
stmt.setObject(1, new MyCustomObject());
581
stmt.executeUpdate();
582
```
583

584
## Extension Registration Patterns
585

586
### Programmatic Registration
587

588
```java
589
public class ExtensionManager {
590
    
591
    public static void registerExtensions(Connection conn) throws SQLException {
592
        // Register triggers
593
        conn.createStatement().execute(
594
            "CREATE TRIGGER audit_users AFTER INSERT, UPDATE, DELETE ON users " +
595
            "FOR EACH ROW CALL \"com.example.AuditTrigger\"");
596
        
597
        // Register aggregates
598
        conn.createStatement().execute(
599
            "CREATE AGGREGATE CONCAT_AGG FOR \"com.example.ConcatAggregate\"");
600
        
601
        // Register functions (if using function interface)
602
        conn.createStatement().execute(
603
            "CREATE ALIAS CUSTOM_HASH FOR \"com.example.CustomHashFunction.hash\"");
604
    }
605
    
606
    public static void unregisterExtensions(Connection conn) throws SQLException {
607
        conn.createStatement().execute("DROP TRIGGER IF EXISTS audit_users");
608
        conn.createStatement().execute("DROP AGGREGATE IF EXISTS CONCAT_AGG");
609
        conn.createStatement().execute("DROP ALIAS IF EXISTS CUSTOM_HASH");
610
    }
611
}
612
```
613

614
### Configuration-Based Registration
615

616
Extensions can also be registered via connection parameters:
617

618
```java
619
// Multiple extensions via connection URL
620
String url = "jdbc:h2:~/test;" +
621
             "DATABASE_EVENT_LISTENER='com.example.DatabaseEventLogger';" +
622
             "CREDENTIALS_VALIDATOR='com.example.LdapCredentialsValidator';" +
623
             "USER_TO_ROLES_MAPPER='com.example.AttributeBasedRoleMapper';" +
624
             "JAVA_OBJECT_SERIALIZER='com.example.JsonObjectSerializer'";
625

626
Connection conn = DriverManager.getConnection(url, "sa", "");
627
```
628

629
## Best Practices
630

631
### Error Handling in Extensions
632

633
```java
634
public class RobustTrigger implements Trigger {
635
    private static final Logger logger = LoggerFactory.getLogger(RobustTrigger.class);
636
    
637
    @Override
638
    public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
639
        try {
640
            performTriggerLogic(conn, oldRow, newRow);
641
        } catch (SQLException e) {
642
            logger.error("Trigger execution failed", e);
643
            throw e; // Re-throw to fail the transaction
644
        } catch (Exception e) {
645
            logger.error("Unexpected error in trigger", e);
646
            throw new SQLException("Trigger failed due to unexpected error", e);
647
        }
648
    }
649
    
650
    private void performTriggerLogic(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
651
        // Actual trigger implementation
652
    }
653
}
654
```
655

656
### Performance Considerations
657

658
```java
659
public class PerformantAggregate implements AggregateFunction {
660
    private final List<Object> values = new ArrayList<>();
661
    private Object cachedResult;
662
    private boolean resultValid = false;
663
    
664
    @Override
665
    public void add(Object value) throws SQLException {
666
        values.add(value);
667
        resultValid = false; // Invalidate cache
668
    }
669
    
670
    @Override
671
    public Object getResult() throws SQLException {
672
        if (!resultValid) {
673
            cachedResult = computeResult();
674
            resultValid = true;
675
        }
676
        return cachedResult;
677
    }
678
    
679
    private Object computeResult() {
680
        // Expensive computation only when needed
681
        return values.stream()
682
                    .filter(Objects::nonNull)
683
                    .collect(Collectors.toList())
684
                    .toString();
685
    }
686
}
687
```
688

689
### Thread Safety
690

691
```java
692
public class ThreadSafeEventListener implements DatabaseEventListener {
693
    private final AtomicLong eventCount = new AtomicLong(0);
694
    private final ConcurrentHashMap<String, AtomicLong> errorCounts = new ConcurrentHashMap<>();
695
    
696
    @Override
697
    public void exceptionThrown(SQLException e, String sql) {
698
        eventCount.incrementAndGet();
699
        
700
        String errorType = e.getClass().getSimpleName();
701
        errorCounts.computeIfAbsent(errorType, k -> new AtomicLong(0)).incrementAndGet();
702
        
703
        // Thread-safe logging and monitoring
704
        logError(e, sql);
705
    }
706
    
707
    private void logError(SQLException e, String sql) {
708
        // Thread-safe logging implementation
709
    }
710
    
711
    public long getEventCount() {
712
        return eventCount.get();
713
    }
714
    
715
    public Map<String, Long> getErrorCounts() {
716
        return errorCounts.entrySet().stream()
717
                         .collect(Collectors.toMap(
718
                             Map.Entry::getKey,
719
                             entry -> entry.getValue().get()));
720
    }
721
}
722
```