0
# Tools and Utilities
1

2
H2 provides a comprehensive set of command-line tools and utilities for database administration, maintenance, and data management operations. These tools can be executed from command line, programmatically, or through the H2 Console web interface.
3

4
## Core Tool Classes
5

6
All tool classes extend the base `Tool` class and can be executed both from command line and programmatically.
7

8
### Server
9

10
Starts H2 database servers (TCP, Web Console, PostgreSQL compatibility).
11

12
```java { .api }
13
public class Server extends Tool implements Runnable, ShutdownHandler {
14
    public Server();
15
    public Server(Service service, String... args);
16
    
17
    // Main entry point
18
    public static void main(String... args);
19
    
20
    // Server management
21
    public Server start() throws SQLException;
22
    public void stop();
23
    public boolean isRunning(boolean traceError);
24
    
25
    // Configuration
26
    public String getURL();
27
    public int getPort();
28
    public String getStatus();
29
}
30
```
31

32
**Usage Examples:**
33

34
```java
35
// Start TCP server programmatically
36
Server server = new Server();
37
server.runTool("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
38

39
// Or using constructor
40
Server tcpServer = Server.createTcpServer("-tcpPort", "9092", "-tcpAllowOthers");
41
tcpServer.start();
42

43
// Start web console
44
Server webServer = Server.createWebServer("-webPort", "8082");
45
webServer.start();
46

47
// Start PostgreSQL compatibility server  
48
Server pgServer = Server.createPgServer("-pgPort", "5435");
49
pgServer.start();
50

51
// Stop servers
52
tcpServer.stop();
53
webServer.stop();
54
pgServer.stop();
55
```
56

57
**Command Line Usage:**
58
```bash
59
# Start TCP server
60
java -cp h2-*.jar org.h2.tools.Server -tcp -tcpAllowOthers -tcpPort 9092
61

62
# Start web console
63
java -cp h2-*.jar org.h2.tools.Server -web -webAllowOthers -webPort 8082
64

65
# Start all servers
66
java -cp h2-*.jar org.h2.tools.Server -tcp -web -pg
67
```
68

69
### Console
70

71
Starts H2 Console with GUI support for desktop environments.
72

73
```java { .api }
74
public class Console extends Tool implements ShutdownHandler {
75
    public static void main(String... args);
76
    
77
    // Console management
78
    public void runTool(String... args) throws SQLException;
79
}
80
```
81

82
**Usage Examples:**
83

84
```java
85
// Start console programmatically
86
Console console = new Console();
87
console.runTool("-web", "-browser");
88

89
// Start without opening browser
90
Console console = new Console();
91
console.runTool("-web");
92
```
93

94
**Command Line Usage:**
95
```bash
96
# Start console and open browser
97
java -cp h2-*.jar org.h2.tools.Console
98

99
# Start console without browser
100
java -cp h2-*.jar org.h2.tools.Console -web
101
```
102

103
### Shell
104

105
Interactive command-line SQL shell for executing queries and commands.
106

107
```java { .api }
108
public class Shell extends Tool implements Runnable {
109
    public static void main(String... args);
110
    
111
    public void runTool(String... args) throws SQLException;
112
    public void setOut(PrintStream out);
113
    public void setErr(PrintStream err);
114
}
115
```
116

117
**Usage Examples:**
118

119
```java
120
// Run shell programmatically
121
Shell shell = new Shell();
122
shell.runTool("-url", "jdbc:h2:~/test", "-user", "sa");
123

124
// Redirect output
125
PrintStream myOut = new PrintStream(new FileOutputStream("output.txt"));
126
shell.setOut(myOut);
127
shell.runTool("-url", "jdbc:h2:~/test", "-user", "sa", "-sql", "SELECT * FROM INFORMATION_SCHEMA.TABLES");
128
```
129

130
**Command Line Usage:**
131
```bash
132
# Interactive shell
133
java -cp h2-*.jar org.h2.tools.Shell -url jdbc:h2:~/test -user sa
134

135
# Execute single command
136
java -cp h2-*.jar org.h2.tools.Shell -url jdbc:h2:~/test -user sa -sql "SELECT COUNT(*) FROM my_table"
137
```
138

139
### Script
140

141
Creates SQL script dumps of database contents and structure.
142

143
```java { .api }
144
public class Script extends Tool {
145
    public static void main(String... args);
146
    
147
    public void runTool(String... args) throws SQLException;
148
}
149
```
150

151
**Usage Examples:**
152

153
```java
154
// Create script programmatically
155
Script script = new Script();
156
script.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-script", "backup.sql");
157

158
// Include data and drop statements
159
script.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-script", "full_backup.sql", 
160
               "-options", "compression", "zip", "drop");
161
```
162

163
**Command Line Usage:**
164
```bash
165
# Create SQL script
166
java -cp h2-*.jar org.h2.tools.Script -url jdbc:h2:~/mydb -user sa -script backup.sql
167

168
# Create compressed script with data
169
java -cp h2-*.jar org.h2.tools.Script -url jdbc:h2:~/mydb -user sa -script backup.sql.gz -options compression gzip
170

171
# Script structure only (no data)
172
java -cp h2-*.jar org.h2.tools.Script -url jdbc:h2:~/mydb -user sa -script structure.sql -options nodata
173
```
174

175
### RunScript
176

177
Executes SQL scripts against a database.
178

179
```java { .api }
180
public class RunScript extends Tool {
181
    public static void main(String... args);
182
    
183
    public void runTool(String... args) throws SQLException;
184
    
185
    // Execute script from string
186
    public static void execute(Connection conn, Reader reader) throws SQLException;
187
    public static void execute(String url, String user, String password, 
188
                              String fileName, String charsetName, 
189
                              boolean continueOnError) throws SQLException;
190
}
191
```
192

193
**Usage Examples:**
194

195
```java
196
// Run script programmatically
197
RunScript runScript = new RunScript();
198
runScript.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-script", "backup.sql");
199

200
// Run script with error handling
201
runScript.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-script", "backup.sql", 
202
                  "-continueOnError");
203

204
// Execute from connection
205
Connection conn = DriverManager.getConnection("jdbc:h2:~/mydb", "sa", "");
206
Reader reader = new FileReader("script.sql");
207
RunScript.execute(conn, reader);
208
```
209

210
**Command Line Usage:**
211
```bash
212
# Execute SQL script
213
java -cp h2-*.jar org.h2.tools.RunScript -url jdbc:h2:~/mydb -user sa -script backup.sql
214

215
# Continue on errors
216
java -cp h2-*.jar org.h2.tools.RunScript -url jdbc:h2:~/mydb -user sa -script backup.sql -continueOnError
217

218
# Execute from URL
219
java -cp h2-*.jar org.h2.tools.RunScript -url jdbc:h2:~/mydb -user sa -script http://example.com/script.sql
220
```
221

222
### Backup
223

224
Creates compressed backup files of databases.
225

226
```java { .api }
227
public class Backup extends Tool {
228
    public static void main(String... args);
229
    
230
    public void runTool(String... args) throws SQLException;
231
}
232
```
233

234
**Usage Examples:**
235

236
```java
237
// Create backup programmatically
238
Backup backup = new Backup();
239
backup.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-file", "mydb_backup.zip");
240

241
// Create backup with quiet mode
242
backup.runTool("-url", "jdbc:h2:~/mydb", "-user", "sa", "-file", "mydb_backup.zip", "-quiet");
243
```
244

245
**Command Line Usage:**
246
```bash
247
# Create database backup
248
java -cp h2-*.jar org.h2.tools.Backup -url jdbc:h2:~/mydb -user sa -file mydb_backup.zip
249

250
# Backup with custom directory
251
java -cp h2-*.jar org.h2.tools.Backup -url jdbc:h2:~/mydb -user sa -file /backups/mydb_backup.zip
252
```
253

254
### Restore
255

256
Restores databases from backup files.
257

258
```java { .api }
259
public class Restore extends Tool {
260
    public static void main(String... args);
261
    
262
    public void runTool(String... args) throws SQLException;
263
}
264
```
265

266
**Usage Examples:**
267

268
```java
269
// Restore backup programmatically
270
Restore restore = new Restore();
271
restore.runTool("-file", "mydb_backup.zip", "-dir", "~/restored", "-db", "mydb_restored");
272

273
// Restore with quiet mode
274
restore.runTool("-file", "mydb_backup.zip", "-dir", "~/restored", "-db", "mydb_restored", "-quiet");
275
```
276

277
**Command Line Usage:**
278
```bash
279
# Restore database backup
280
java -cp h2-*.jar org.h2.tools.Restore -file mydb_backup.zip -dir ~/restored -db mydb_restored
281

282
# Restore to specific directory
283
java -cp h2-*.jar org.h2.tools.Restore -file /backups/mydb_backup.zip -dir /data -db production
284
```
285

286
### Recover
287

288
Recovers data from corrupted database files.
289

290
```java { .api }
291
public class Recover extends Tool implements DataHandler {
292
    public static void main(String... args);
293
    
294
    public void runTool(String... args) throws SQLException;
295
}
296
```
297

298
**Usage Examples:**
299

300
```java
301
// Recover corrupted database
302
Recover recover = new Recover();
303
recover.runTool("-dir", "~/corrupted_db", "-db", "mydb");
304

305
// Recover with trace information
306
recover.runTool("-dir", "~/corrupted_db", "-db", "mydb", "-trace");
307
```
308

309
**Command Line Usage:**
310
```bash
311
# Recover corrupted database
312
java -cp h2-*.jar org.h2.tools.Recover -dir ~/corrupted_db -db mydb
313

314
# Recover with detailed output
315
java -cp h2-*.jar org.h2.tools.Recover -dir ~/corrupted_db -db mydb -trace
316
```
317

318
### DeleteDbFiles
319

320
Utility to delete database files safely.
321

322
```java { .api }
323
public class DeleteDbFiles extends Tool {
324
    public static void main(String... args);
325
    
326
    public void runTool(String... args) throws SQLException;
327
    public static void execute(String dir, String db, boolean quiet) throws SQLException;
328
}
329
```
330

331
**Usage Examples:**
332

333
```java
334
// Delete database files programmatically
335
DeleteDbFiles deleteFiles = new DeleteDbFiles();
336
deleteFiles.runTool("-dir", "~/mydb", "-db", "test", "-quiet");
337

338
// Using static method
339
DeleteDbFiles.execute("~/mydb", "test", false);
340
```
341

342
**Command Line Usage:**
343
```bash
344
# Delete database files
345
java -cp h2-*.jar org.h2.tools.DeleteDbFiles -dir ~/mydb -db test
346

347
# Delete quietly (no confirmation)
348
java -cp h2-*.jar org.h2.tools.DeleteDbFiles -dir ~/mydb -db test -quiet
349
```
350

351
### ChangeFileEncryption
352

353
Changes file encryption settings for existing databases.
354

355
```java { .api }
356
public class ChangeFileEncryption extends Tool {
357
    public static void main(String... args);
358
    
359
    public void runTool(String... args) throws SQLException;
360
}
361
```
362

363
**Usage Examples:**
364

365
```java
366
// Change encryption programmatically
367
ChangeFileEncryption encrypt = new ChangeFileEncryption();
368
encrypt.runTool("-dir", "~/mydb", "-db", "test", "-cipher", "AES", "-decrypt", "oldpass", "-encrypt", "newpass");
369
```
370

371
**Command Line Usage:**
372
```bash
373
# Add encryption to existing database
374
java -cp h2-*.jar org.h2.tools.ChangeFileEncryption -dir ~/mydb -db test -cipher AES -encrypt newpassword
375

376
# Change encryption password
377
java -cp h2-*.jar org.h2.tools.ChangeFileEncryption -dir ~/mydb -db test -cipher AES -decrypt oldpass -encrypt newpass
378

379
# Remove encryption
380
java -cp h2-*.jar org.h2.tools.ChangeFileEncryption -dir ~/mydb -db test -cipher AES -decrypt password
381
```
382

383
## Data Import/Export Tools
384

385
### Csv
386

387
CSV import/export functionality for data exchange.
388

389
```java { .api }
390
public class Csv implements SimpleRowSource {
391
    // Reading CSV
392
    public static Csv getInstance(String fileName, String[] colNames, String charset);
393
    public static ResultSet read(String fileName, String[] colNames, String charset) throws SQLException;
394
    
395
    // Writing CSV  
396
    public static int write(String fileName, ResultSet rs, String charset) throws SQLException;
397
    public static void write(String fileName, ResultSet rs, String options) throws SQLException;
398
    
399
    // Row source interface
400
    public Object[] readRow() throws SQLException;
401
    public void close();
402
    public void reset() throws SQLException;
403
}
404
```
405

406
**Usage Examples:**
407

408
```java
409
// Import CSV data
410
ResultSet rs = Csv.read("data.csv", null, "UTF-8");
411
// Process ResultSet...
412

413
// Export to CSV
414
Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
415
Statement stmt = conn.createStatement();
416
ResultSet rs = stmt.executeQuery("SELECT * FROM customers");
417
Csv.write("customers_export.csv", rs, "UTF-8");
418

419
// Custom CSV reading with column names
420
String[] columns = {"id", "name", "email", "created_date"};
421
Csv csv = Csv.getInstance("customers.csv", columns, "UTF-8");
422
Object[] row;
423
while ((row = csv.readRow()) != null) {
424
    // Process row data
425
    System.out.println("ID: " + row[0] + ", Name: " + row[1]);
426
}
427
csv.close();
428
```
429

430
### SimpleResultSet
431

432
In-memory ResultSet implementation for tools and utilities.
433

434
```java { .api }
435
public class SimpleResultSet implements ResultSet, ResultSetMetaData {
436
    public SimpleResultSet();
437
    public SimpleResultSet(SimpleRowSource source);
438
    
439
    // Column definition
440
    public void addColumn(String name, int sqlType, int precision, int scale);
441
    public void addColumn(String name);
442
    
443
    // Row manipulation
444
    public void addRow(Object... values);
445
    public Object[] readRow() throws SQLException;
446
    
447
    // Standard ResultSet methods
448
    public boolean next() throws SQLException;
449
    public String getString(int columnIndex) throws SQLException;
450
    public String getString(String columnLabel) throws SQLException;
451
    public int getInt(int columnIndex) throws SQLException;
452
    // ... other standard ResultSet methods
453
}
454
```
455

456
**Usage Examples:**
457

458
```java
459
// Create in-memory result set
460
SimpleResultSet rs = new SimpleResultSet();
461
rs.addColumn("id", Types.INTEGER, 10, 0);
462
rs.addColumn("name", Types.VARCHAR, 255, 0);
463
rs.addColumn("active", Types.BOOLEAN, 1, 0);
464

465
// Add data rows
466
rs.addRow(1, "Alice", true);
467
rs.addRow(2, "Bob", false);  
468
rs.addRow(3, "Charlie", true);
469

470
// Process like normal ResultSet
471
while (rs.next()) {
472
    int id = rs.getInt("id");
473
    String name = rs.getString("name");
474
    boolean active = rs.getBoolean("active");
475
    System.out.println(id + ": " + name + " (active: " + active + ")");
476
}
477
```
478

479
### CompressTool
480

481
File compression utilities for database files and backups.
482

483
```java { .api }
484
public class CompressTool {
485
    // Compression methods
486
    public static void compress(String inFileName, String outFileName, String algorithm) throws SQLException;
487
    public static void expand(String inFileName, String outFileName) throws SQLException;
488
    
489
    // Available algorithms
490
    public static final String GZIP = "GZIP";
491
    public static final String LZF = "LZF";
492
    public static final String DEFLATE = "DEFLATE";
493
}
494
```
495

496
**Usage Examples:**
497

498
```java
499
// Compress file
500
CompressTool.compress("large_backup.sql", "large_backup.sql.gz", CompressTool.GZIP);
501

502
// Decompress file
503
CompressTool.expand("large_backup.sql.gz", "restored_backup.sql");
504

505
// Use with different algorithms
506
CompressTool.compress("data.sql", "data.sql.lzf", CompressTool.LZF);
507
CompressTool.compress("data.sql", "data.sql.deflate", CompressTool.DEFLATE);
508
```
509

510
## Utility Interfaces
511

512
### SimpleRowSource
513

514
Interface for providing row data to import/export tools.
515

516
```java { .api }
517
public interface SimpleRowSource {
518
    Object[] readRow() throws SQLException;
519
    void close();
520
    void reset() throws SQLException;
521
}
522
```
523

524
**Implementation Example:**
525

526
```java
527
public class CustomDataSource implements SimpleRowSource {
528
    private List<Object[]> data;
529
    private int currentIndex = 0;
530
    
531
    public CustomDataSource(List<Object[]> data) {
532
        this.data = data;
533
    }
534
    
535
    @Override
536
    public Object[] readRow() throws SQLException {
537
        if (currentIndex >= data.size()) {
538
            return null; // End of data
539
        }
540
        return data.get(currentIndex++);
541
    }
542
    
543
    @Override
544
    public void close() {
545
        // Cleanup resources if needed
546
    }
547
    
548
    @Override
549
    public void reset() throws SQLException {
550
        currentIndex = 0;
551
    }
552
}
553

554
// Usage
555
List<Object[]> myData = Arrays.asList(
556
    new Object[]{1, "Alice", "alice@example.com"},
557
    new Object[]{2, "Bob", "bob@example.com"}
558
);
559

560
SimpleResultSet rs = new SimpleResultSet(new CustomDataSource(myData));
561
```
562

563
## Tool Execution Patterns
564

565
### Programmatic Execution
566

567
```java
568
public class DatabaseMaintenance {
569
    
570
    public void performBackup(String dbUrl, String user, String password, String backupFile) {
571
        try {
572
            Backup backup = new Backup();
573
            backup.runTool("-url", dbUrl, "-user", user, "-password", password, "-file", backupFile);
574
            System.out.println("Backup completed: " + backupFile);
575
        } catch (SQLException e) {
576
            System.err.println("Backup failed: " + e.getMessage());
577
        }
578
    }
579
    
580
    public void performRestore(String backupFile, String targetDir, String dbName) {
581
        try {
582
            Restore restore = new Restore();
583
            restore.runTool("-file", backupFile, "-dir", targetDir, "-db", dbName);
584
            System.out.println("Restore completed: " + dbName);
585
        } catch (SQLException e) {
586
            System.err.println("Restore failed: " + e.getMessage());
587
        }
588
    }
589
    
590
    public void exportToScript(String dbUrl, String user, String password, String scriptFile) {
591
        try {
592
            Script script = new Script();
593
            script.runTool("-url", dbUrl, "-user", user, "-password", password, "-script", scriptFile);
594
            System.out.println("Script export completed: " + scriptFile);
595
        } catch (SQLException e) {
596
            System.err.println("Script export failed: " + e.getMessage());
597
        }
598
    }
599
}
600
```
601

602
### Command Line Execution
603

604
```bash
605
#!/bin/bash
606
# Database maintenance script
607

608
DB_URL="jdbc:h2:~/production"
609
DB_USER="sa"
610
DB_PASSWORD="securepassword"
611
BACKUP_DIR="/var/backups/h2"
612
DATE=$(date +%Y%m%d_%H%M%S)
613

614
# Create backup
615
java -cp h2-*.jar org.h2.tools.Backup \
616
    -url "$DB_URL" \
617
    -user "$DB_USER" \
618
    -password "$DB_PASSWORD" \
619
    -file "$BACKUP_DIR/backup_$DATE.zip" \
620
    -quiet
621

622
# Create SQL script
623
java -cp h2-*.jar org.h2.tools.Script \
624
    -url "$DB_URL" \
625
    -user "$DB_USER" \
626
    -password "$DB_PASSWORD" \
627
    -script "$BACKUP_DIR/script_$DATE.sql" \
628
    -options compression gzip
629

630
echo "Maintenance completed at $(date)"
631
```