/
features.html
1843 lines (1745 loc) · 79 KB
/
features.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2022 H2 Group. Multiple-Licensed under the MPL 2.0,
and the EPL 1.0 (https://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>
Features
</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<!-- } -->
<h1>Features</h1>
<a href="#feature_list">
Feature List</a><br />
<a href="#products_work_with">
H2 in Use</a><br />
<a href="#connection_modes">
Connection Modes</a><br />
<a href="#database_url">
Database URL Overview</a><br />
<a href="#embedded_databases">
Connecting to an Embedded (Local) Database</a><br />
<a href="#in_memory_databases">
In-Memory Databases</a><br />
<a href="#file_encryption">
Database Files Encryption</a><br />
<a href="#database_file_locking">
Database File Locking</a><br />
<a href="#database_only_if_exists">
Opening a Database Only if it Already Exists</a><br />
<a href="#closing_a_database">
Closing a Database</a><br />
<a href="#ignore_unknown_settings">
Ignore Unknown Settings</a><br />
<a href="#other_settings">
Changing Other Settings when Opening a Connection</a><br />
<a href="#custom_access_mode">
Custom File Access Mode</a><br />
<a href="#multiple_connections">
Multiple Connections</a><br />
<a href="#database_file_layout">
Database File Layout</a><br />
<a href="#logging_recovery">
Logging and Recovery</a><br />
<a href="#compatibility">
Compatibility</a><br />
<a href="#auto_reconnect">
Auto-Reconnect</a><br />
<a href="#auto_mixed_mode">
Automatic Mixed Mode</a><br />
<a href="#page_size">
Page Size</a><br />
<a href="#trace_options">
Using the Trace Options</a><br />
<a href="#other_logging">
Using Other Logging APIs</a><br />
<a href="#read_only">
Read Only Databases</a><br />
<a href="#database_in_zip">
Read Only Databases in Zip or Jar File</a><br />
<a href="#generated_columns">
Generated Columns (Computed Columns) / Function Based Index</a><br />
<a href="#multi_dimensional">
Multi-Dimensional Indexes</a><br />
<a href="#user_defined_functions">
User-Defined Functions and Stored Procedures</a><br />
<a href="#pluggable_tables">
Pluggable or User-Defined Tables</a><br />
<a href="#triggers">
Triggers</a><br />
<a href="#compacting">
Compacting a Database</a><br />
<a href="#cache_settings">
Cache Settings</a><br />
<a href="#external_authentication">
External Authentication (Experimental)</a><br />
<h2 id="feature_list">Feature List</h2>
<h3>Main Features</h3>
<ul>
<li>Very fast database engine
</li><li>Open source
</li><li>Written in Java
</li><li>Supports standard SQL, JDBC API
</li><li>Embedded and Server mode, Clustering support
</li><li>Strong security features
</li><li>The PostgreSQL ODBC driver can be used
</li><li>Multi version concurrency
</li></ul>
<h3>Additional Features</h3>
<ul>
<li>Disk based or in-memory databases and tables, read-only database support, temporary tables
</li><li>Transaction support (read uncommitted, read committed, repeatable read, snapshot), 2-phase-commit
</li><li>Multiple connections, row-level locking
</li><li>Cost based optimizer, using a genetic algorithm for complex queries, zero-administration
</li><li>Scrollable and updatable result set support, large result set, external result sorting,
functions can return a result set
</li><li>Encrypted database (AES), SHA-256 password encryption, encryption functions, SSL
</li></ul>
<h3>SQL Support</h3>
<ul>
<li>Support for multiple schemas, information schema
</li><li>Referential integrity / foreign key constraints with cascade, check constraints
</li><li>Inner and outer joins, subqueries, read only views and inline views
</li><li>Triggers and Java functions / stored procedures
</li><li>Many built-in functions, including XML and lossless data compression
</li><li>Wide range of data types including large objects (BLOB/CLOB) and arrays
</li><li>Sequences and identity columns, generated columns (can be used for function based indexes)
</li><li>ORDER BY, GROUP BY, HAVING, UNION, OFFSET / FETCH (including PERCENT and WITH TIES), LIMIT, TOP,
DISTINCT / DISTINCT ON (...)
</li><li>Window functions
</li><li>Collation support, including support for the ICU4J library
</li><li>Support for users and roles
</li><li>Compatibility modes for IBM DB2, Apache Derby, HSQLDB,
MS SQL Server, MySQL, Oracle, and PostgreSQL.
</li></ul>
<h3>Security Features</h3>
<ul>
<li>Includes a solution for the SQL injection problem
</li><li>User password authentication uses SHA-256 and salt
</li><li>For server mode connections, user passwords are never transmitted in plain text over the network
(even when using insecure connections; this only applies to the TCP server and not to the H2 Console however;
it also doesn't apply if you set the password in the database URL)
</li><li>All database files (including script files that can be used to backup data) can be
encrypted using the AES-128 encryption algorithm
</li><li>The remote JDBC driver supports TCP/IP connections over TLS
</li><li>The built-in web server supports connections over TLS
</li><li>Passwords can be sent to the database using char arrays instead of Strings
</li></ul>
<h3>Other Features and Tools</h3>
<ul>
<li>Small footprint (around 2.5 MB), low memory requirements
</li><li>Multiple index types (b-tree, tree, hash)
</li><li>Support for multi-dimensional indexes
</li><li>CSV (comma separated values) file support
</li><li>Support for linked tables, and a built-in virtual 'range' table
</li><li>Supports the <code>EXPLAIN PLAN</code> statement; sophisticated trace options
</li><li>Database closing can be delayed or disabled to improve the performance
</li><li>Web-based Console application (translated to many languages) with autocomplete
</li><li>The database can generate SQL script files
</li><li>Contains a recovery tool that can dump the contents of the database
</li><li>Support for variables (for example to calculate running totals)
</li><li>Automatic re-compilation of prepared statements
</li><li>Uses a small number of database files
</li><li>Uses a checksum for each record and log entry for data integrity
</li><li>Well tested (high code coverage, randomized stress tests)
</li></ul>
<h2 id="products_work_with">H2 in Use</h2>
<p>
For a list of applications that work with or use H2, see:
<a href="https://h2database.com/html/links.html">Links</a>.
</p>
<h2 id="connection_modes">Connection Modes</h2>
<p>
The following connection modes are supported:
</p>
<ul>
<li>Embedded mode (local connections using JDBC)
</li><li>Server mode (remote connections using JDBC or ODBC over TCP/IP)
</li><li>Mixed mode (local and remote connections at the same time)
</li></ul>
<h3>Embedded Mode</h3>
<p>
In embedded mode, an application opens a database from within the same JVM using JDBC.
This is the fastest and easiest connection mode.
The disadvantage is that a database may only be open in one virtual machine (and class loader) at any time.
As in all modes, both persistent and in-memory databases are supported.
There is no limit on the number of database open concurrently,
or on the number of open connections.
</p>
<p>
In embedded mode I/O operations can be performed by application's threads that execute a SQL command.
The application may not interrupt these threads, it can lead to database corruption,
because JVM closes I/O handle during thread interruption.
Consider other ways to control execution of your application.
When interrupts are possible the <a href="advanced.html#file_system"><code>async:</code></a>
file system can be used as a workaround, but full safety is not guaranteed.
It's recommended to use the client-server model instead, the client side may interrupt own threads.
</p>
<img src="images/connection-mode-embedded-2.png"
width="208" height="259"
alt="The database is embedded in the application" />
<h3>Server Mode</h3>
<p>
When using the server mode (sometimes called remote mode or client/server mode),
an application opens a database remotely using the JDBC or ODBC API.
A server needs to be started within the same or another virtual machine, or on another computer.
Many applications can connect to the same database at the same time, by connecting to this server.
Internally, the server process opens the database(s) in embedded mode.
</p>
<p>
The server mode is slower than the embedded mode, because all data is transferred over TCP/IP.
As in all modes, both persistent and in-memory databases are supported.
There is no limit on the number of database open concurrently per server,
or on the number of open connections.
</p>
<img src="images/connection-mode-remote-2.png"
width="376" height="218"
alt="The database is running in a server; the application connects to the server" />
<h3>Mixed Mode</h3>
<p>
The mixed mode is a combination of the embedded and the server mode.
The first application that connects to a database does that in embedded mode, but also starts
a server so that other applications (running in different processes or virtual machines) can
concurrently access the same data. The local connections are as fast as if
the database is used in just the embedded mode, while the remote
connections are a bit slower.
</p><p>
The server can be started and stopped from within the application (using the server API),
or automatically (automatic mixed mode). When using the <a href="#auto_mixed_mode">automatic mixed mode</a>,
all clients that want to connect to the database (no matter if
it's an local or remote connection) can do so using the exact same database URL.
</p>
<img src="images/connection-mode-mixed-2.png"
width="403" height="240"
alt="Database, server, and application run in one JVM; an application connects" />
<h2 id="database_url">Database URL Overview</h2>
<p>
This database supports multiple connection modes and connection settings.
This is achieved using different database URLs. Settings in the URLs are not case sensitive.
</p>
<table class="main">
<tr><th>Topic</th><th>URL Format and Examples</th></tr>
<tr>
<td><a href="#embedded_databases">Embedded (local) connection</a></td>
<td class="notranslate">
jdbc:h2:[file:][<path>]<databaseName><br />
jdbc:h2:~/test<br />
jdbc:h2:file:/data/sample<br />
jdbc:h2:file:C:/data/sample (Windows only)<br />
</td>
</tr>
<tr>
<td><a href="#in_memory_databases">In-memory (private)</a></td>
<td class="notranslate">jdbc:h2:mem:</td>
</tr>
<tr>
<td><a href="#in_memory_databases">In-memory (named)</a></td>
<td class="notranslate">
jdbc:h2:mem:<databaseName><br />
jdbc:h2:mem:test_mem
</td>
</tr>
<tr>
<td><a href="tutorial.html#using_server">Server mode (remote connections)<br /> using TCP/IP</a></td>
<td class="notranslate">
jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName><br />
jdbc:h2:tcp://localhost/~/test<br />
jdbc:h2:tcp://dbserv:8084/~/sample<br />
jdbc:h2:tcp://localhost/mem:test<br />
</td>
</tr>
<tr>
<td><a href="advanced.html#tls_connections">Server mode (remote connections)<br /> using TLS</a></td>
<td class="notranslate">
jdbc:h2:ssl://<server>[:<port>]/[<path>]<databaseName><br />
jdbc:h2:ssl://localhost:8085/~/sample;
</td>
</tr>
<tr>
<td><a href="#file_encryption">Using encrypted files</a></td>
<td class="notranslate">
jdbc:h2:<url>;CIPHER=AES<br />
jdbc:h2:ssl://localhost/~/test;CIPHER=AES<br />
jdbc:h2:file:~/secure;CIPHER=AES<br />
</td>
</tr>
<tr>
<td><a href="#database_file_locking">File locking methods</a></td>
<td class="notranslate">
jdbc:h2:<url>;FILE_LOCK={FILE|SOCKET|FS|NO}<br />
jdbc:h2:file:~/private;CIPHER=AES;FILE_LOCK=SOCKET<br />
</td>
</tr>
<tr>
<td><a href="#database_only_if_exists">Only open if it already exists</a></td>
<td class="notranslate">
jdbc:h2:<url>;IFEXISTS=TRUE<br />
jdbc:h2:file:~/sample;IFEXISTS=TRUE<br />
</td>
</tr>
<tr>
<td><a href="#do_not_close_on_exit">Don't close the database when the VM exits</a></td>
<td class="notranslate">
jdbc:h2:<url>;DB_CLOSE_ON_EXIT=FALSE
</td>
</tr>
<tr>
<td><a href="#execute_sql_on_connection">Execute SQL on connection</a></td>
<td class="notranslate">
jdbc:h2:<url>;INIT=RUNSCRIPT FROM '~/create.sql'<br />
jdbc:h2:file:~/sample;INIT=RUNSCRIPT FROM '~/create.sql'\;RUNSCRIPT FROM '~/populate.sql'<br />
</td>
</tr>
<tr>
<td><a href="advanced.html#passwords">User name and/or password</a></td>
<td class="notranslate">
jdbc:h2:<url>[;USER=<username>][;PASSWORD=<value>]<br />
jdbc:h2:file:~/sample;USER=sa;PASSWORD=123<br />
</td>
</tr>
<tr>
<td><a href="#trace_options">Debug trace settings</a></td>
<td class="notranslate">
jdbc:h2:<url>;TRACE_LEVEL_FILE=<level 0..3><br />
jdbc:h2:file:~/sample;TRACE_LEVEL_FILE=3<br />
</td>
</tr>
<tr>
<td><a href="#ignore_unknown_settings">Ignore unknown settings</a></td>
<td class="notranslate">
jdbc:h2:<url>;IGNORE_UNKNOWN_SETTINGS=TRUE<br />
</td>
</tr>
<tr>
<td><a href="#custom_access_mode">Custom file access mode</a></td>
<td class="notranslate">
jdbc:h2:<url>;ACCESS_MODE_DATA=rws<br />
</td>
</tr>
<tr>
<td><a href="#database_in_zip">Database in a zip file</a></td>
<td class="notranslate">
jdbc:h2:zip:<zipFileName>!/<databaseName><br />
jdbc:h2:zip:~/db.zip!/test
</td>
</tr>
<tr>
<td><a href="#compatibility">Compatibility mode</a></td>
<td class="notranslate">
jdbc:h2:<url>;MODE=<databaseType><br />
jdbc:h2:~/test;MODE=MYSQL;DATABASE_TO_LOWER=TRUE
</td>
</tr>
<tr>
<td><a href="#auto_reconnect">Auto-reconnect</a></td>
<td class="notranslate">
jdbc:h2:<url>;AUTO_RECONNECT=TRUE<br />
jdbc:h2:tcp://localhost/~/test;AUTO_RECONNECT=TRUE
</td>
</tr>
<tr>
<td><a href="#auto_mixed_mode">Automatic mixed mode</a></td>
<td class="notranslate">
jdbc:h2:<url>;AUTO_SERVER=TRUE<br />
jdbc:h2:~/test;AUTO_SERVER=TRUE
</td>
</tr>
<tr>
<td><a href="#page_size">Page size</a></td>
<td class="notranslate">
jdbc:h2:<url>;PAGE_SIZE=512<br />
</td>
</tr>
<tr>
<td><a href="#other_settings">Changing other settings</a></td>
<td class="notranslate">
jdbc:h2:<url>;<setting>=<value>[;<setting>=<value>...]<br />
jdbc:h2:file:~/sample;TRACE_LEVEL_SYSTEM_OUT=3<br />
</td>
</tr>
</table>
<h2 id="embedded_databases">Connecting to an Embedded (Local) Database</h2>
<p>
The database URL for connecting to a local database is
<code>jdbc:h2:[file:][<path>]<databaseName></code>.
The prefix <code>file:</code> is optional. If no or only a relative path is used, then the current working
directory is used as a starting point. The case sensitivity of the path and database name depend on the
operating system, however it is recommended to use lowercase letters only.
The database name must be at least three characters long
(a limitation of <code>File.createTempFile</code>).
The database name must not contain a semicolon.
To point to the user home directory, use <code>~/</code>, as in: <code>jdbc:h2:~/test</code>.
</p>
<h2 id="in_memory_databases">In-Memory Databases</h2>
<p>
For certain use cases (for example: rapid prototyping, testing, high performance
operations, read-only databases), it may not be required to persist data, or persist changes to the data.
This database supports the in-memory mode, where the data is not persisted.
</p><p>
In some cases, only one connection to a in-memory database is required.
This means the database to be opened is private. In this case, the database URL is
<code>jdbc:h2:mem:</code> Opening two connections within the same virtual machine
means opening two different (private) databases.
</p><p>
Sometimes multiple connections to the same in-memory database are required.
In this case, the database URL must include a name. Example: <code>jdbc:h2:mem:db1</code>.
Accessing the same database using this URL only works within the same virtual machine and
class loader environment.
</p><p>
To access an in-memory database from another process or from another computer,
you need to start a TCP server in the same process as the in-memory database was created.
The other processes then need to access the database over TCP/IP or TLS,
using a database URL such as: <code>jdbc:h2:tcp://localhost/mem:db1</code>.
</p><p>
By default, closing the last connection to a database closes the database.
For an in-memory database, this means the content is lost.
To keep the database open, add <code>;DB_CLOSE_DELAY=-1</code> to the database URL.
To keep the content of an in-memory database as long as the virtual machine is alive, use
<code>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</code>.
This may create a memory leak, when you need to remove the database, use
the <a href="commands.html#shutdown">SHUTDOWN</a> command.
</p>
<h2 id="file_encryption">Database Files Encryption</h2>
<p>
The database files can be encrypted.
Three encryption algorithms are supported:
</p>
<ul>
<li>"AES" - also known as Rijndael, only AES-128 is implemented.</li>
<li>"XTEA" - the 32 round version.</li>
<li>"FOG" - pseudo-encryption only useful for hiding data from a text editor.</li>
</ul>
<p>
To use file encryption, you need to specify the encryption algorithm (the 'cipher')
and the file password (in addition to the user password) when connecting to the database.
</p>
<h3>Creating a New Database with File Encryption</h3>
<p>
By default, a new database is automatically created if it does not exist yet
when the <a href="#database_url">embedded</a> url is used.
To create an encrypted database, connect to it as it would already exist locally using the embedded URL.
</p>
<h3>Connecting to an Encrypted Database</h3>
<p>
The encryption algorithm is set in the database URL, and the file password is specified in the password field,
before the user password. A single space separates the file password
and the user password; the file password itself may not contain spaces. File passwords
and user passwords are case sensitive. Here is an example to connect to a
password-encrypted database:
</p>
<pre>
String url = "jdbc:h2:~/test;CIPHER=AES";
String user = "sa";
String pwds = "filepwd userpwd";
conn = DriverManager.
getConnection(url, user, pwds);
</pre>
<h3>Encrypting or Decrypting a Database</h3>
<p>
To encrypt an existing database, use the <code>ChangeFileEncryption</code> tool.
This tool can also decrypt an encrypted database, or change the file encryption key.
The tool is available from within the H2 Console in the tools section, or you can run it from the command line.
The following command line will encrypt the database <code>test</code> in the user home directory
with the file password <code>filepwd</code> and the encryption algorithm AES:
</p>
<pre>
java -cp h2*.jar org.h2.tools.ChangeFileEncryption -dir ~ -db test -cipher AES -encrypt filepwd
</pre>
<h2 id="database_file_locking">Database File Locking</h2>
<p>
Whenever a database is opened, a lock file is created to signal other processes
that the database is in use. If database is closed, or if the process that opened
the database terminates, this lock file is deleted.
</p><p>
The following file locking methods are implemented:
</p>
<ul>
<li>The default method is <code>FILE</code> and uses a watchdog thread to
protect the database file. The watchdog reads the lock file each second.
</li><li>The second method is <code>SOCKET</code> and opens a server socket.
The socket method does not require reading the lock file every second.
The socket method should only be used if the database files
are only accessed by one (and always the same) computer.
</li><li>The third method is <code>FS</code>.
This will use native file locking using <code>FileChannel.lock</code>.
</li><li>It is also possible to open the database without file locking;
in this case it is up to the application to protect the database files.
Failing to do so will result in a corrupted database.
Using the method <code>NO</code> forces the database to not create a lock file at all.
Please note that this is unsafe as another process is able to open the same database,
possibly leading to data corruption.</li></ul>
<p>
To open the database with a different file locking method, use the parameter
<code>FILE_LOCK</code>.
The following code opens the database with the 'socket' locking method:
</p>
<pre>
String url = "jdbc:h2:~/test;FILE_LOCK=SOCKET";
</pre>
<p>
For more information about the algorithms, see
<a href="advanced.html#file_locking_protocols">Advanced / File Locking Protocols</a>.
</p>
<h2 id="database_only_if_exists">Opening a Database Only if it Already Exists</h2>
<p>
By default, when an application calls <code>DriverManager.getConnection(url, ...)</code>
with <a href="features.html#database_url">embedded</a> URL and the database specified in the URL does not yet exist,
a new (empty) database is created.
In some situations, it is better to restrict creating new databases, and only allow to open
existing databases. To do this, add <code>;IFEXISTS=TRUE</code>
to the database URL. In this case, if the database does not already exist, an exception is thrown when
trying to connect. The connection only succeeds when the database already exists.
The complete URL may look like this:
</p>
<pre>
String url = "jdbc:h2:/data/sample;IFEXISTS=TRUE";
</pre>
<h2 id="closing_a_database">Closing a Database</h2>
<h3>Delayed Database Closing</h3>
<p>
Usually, a database is closed when the last connection to it is closed. In some situations
this slows down the application, for example when it is not possible to keep at least one connection open.
The automatic closing of a database can be delayed or disabled with the SQL statement
<code>SET DB_CLOSE_DELAY <seconds></code>.
The parameter <seconds> specifies the number of seconds to keep
a database open after the last connection to it was closed. The following statement
will keep a database open for 10 seconds after the last connection was closed:
</p>
<pre>
SET DB_CLOSE_DELAY 10
</pre>
<p>
The value -1 means the database is not closed automatically.
The value 0 is the default and means the database is closed when the last connection is closed.
This setting is persistent and can be set by an administrator only.
It is possible to set the value in the database URL: <code>jdbc:h2:~/test;DB_CLOSE_DELAY=10</code>.
</p>
<h3 id="do_not_close_on_exit">Don't Close a Database when the VM Exits</h3>
<p>
By default, a database is closed when the last connection is closed. However, if it is never closed,
the database is closed when the virtual machine exits normally, using a shutdown hook.
In some situations, the database should not be closed in this case, for example because the
database is still used at virtual machine shutdown (to store the shutdown process in the database for example).
For those cases, the automatic closing of the database can be disabled in the database URL.
The first connection (the one that is opening the database) needs to
set the option in the database URL (it is not possible to change the setting afterwards).
The database URL to disable database closing on exit is:
</p>
<pre>
String url = "jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE";
</pre>
<h2 id="execute_sql_on_connection">Execute SQL on Connection</h2>
<p>
Sometimes, particularly for in-memory databases, it is useful to be able to execute DDL or DML
commands automatically when a client connects to a database. This functionality is enabled via
the INIT property. Note that multiple commands may be passed to INIT, but the semicolon delimiter
must be escaped, as in the example below.
</p>
<pre>
String url = "jdbc:h2:mem:test;INIT=runscript from '~/create.sql'\\;runscript from '~/init.sql'";
</pre>
<p>
Please note the double backslash is only required in a Java or properties file.
In a GUI, or in an XML file, only one backslash is required:
</p>
<pre>
<property name="url" value=
"jdbc:h2:mem:test;INIT=create schema if not exists test\;runscript from '~/sql/init.sql'"
/>
</pre>
<p>
Backslashes within the init script (for example within a runscript statement, to specify the folder names in Windows)
need to be escaped as well (using a second backslash). It might be simpler to avoid backslashes in folder names for this reason;
use forward slashes instead.
</p>
<h2 id="ignore_unknown_settings">Ignore Unknown Settings</h2>
<p>
Some applications (for example OpenOffice.org Base) pass some additional parameters
when connecting to the database. Why those parameters are passed is unknown.
The parameters <code>PREFERDOSLIKELINEENDS</code> and
<code>IGNOREDRIVERPRIVILEGES</code> are such examples;
they are simply ignored to improve the compatibility with OpenOffice.org. If an application
passes other parameters when connecting to the database, usually the database throws an exception
saying the parameter is not supported. It is possible to ignored such parameters by adding
<code>;IGNORE_UNKNOWN_SETTINGS=TRUE</code> to the database URL.
</p>
<h2 id="other_settings">Changing Other Settings when Opening a Connection</h2>
<p>
In addition to the settings already described,
other database settings can be passed in the database URL.
Adding <code>;setting=value</code> at the end of a database URL is the
same as executing the statement <code>SET setting value</code> just after
connecting. For a list of supported settings, see <a href="grammar.html">SQL Grammar</a>
or the <a href="https://h2database.com/javadoc/org/h2/engine/DbSettings.html">DbSettings</a> javadoc.
</p>
<h2 id="custom_access_mode">Custom File Access Mode</h2>
<p>
Usually, the database opens the database file with the access mode
<code>rw</code>, meaning read-write (except for read only databases,
where the mode <code>r</code> is used).
To open a database in read-only mode if the database file is not read-only, use
<code>ACCESS_MODE_DATA=r</code>.
Also supported are <code>rws</code> and <code>rwd</code>.
This setting must be specified in the database URL:
</p>
<pre>
String url = "jdbc:h2:~/test;ACCESS_MODE_DATA=rws";
</pre>
<p>
For more information see <a href="advanced.html#durability_problems">Durability Problems</a>.
On many operating systems the access mode <code>rws</code> does not guarantee that the data is written to the disk.
</p>
<h2 id="multiple_connections">Multiple Connections</h2>
<h3>Opening Multiple Databases at the Same Time</h3>
<p>
An application can open multiple databases at the same time, including multiple
connections to the same database. The number of open database is only limited by the memory available.
</p>
<h3>Multiple Connections to the Same Database: Client/Server</h3>
<p>
If you want to access the same database at the same time from different processes or computers,
you need to use the client / server mode. In this case, one process acts as the server, and the
other processes (that could reside on other computers as well) connect to the server via TCP/IP
(or TLS over TCP/IP for improved security).
</p>
<h3>Multithreading Support</h3>
<p>
This database is multithreading-safe.
If an application is multi-threaded, it does not need to worry about synchronizing access to the database.
An application should normally use one connection per thread.
This database synchronizes access to the same connection, but other databases may not do this.
To get higher concurrency, you need to use multiple connections.
</p>
<p>
An application can use multiple threads that access the same database at the same time.
Threads that use different connections can use the database concurrently.
</p>
<h3>Locking, Lock-Timeout, Deadlocks</h3>
<p>
Usually, <code>SELECT</code> statements will generate read locks. This includes subqueries.
Statements that modify data use write locks on the modified rows.
It is also possible to issue write locks without modifying data,
using the statement <code>SELECT ... FOR UPDATE</code>.
Data definition statements may issue exclusive locks on tables.
The statements <code>COMMIT</code> and
<code>ROLLBACK</code> releases all open locks.
The commands <code>SAVEPOINT</code> and
<code>ROLLBACK TO SAVEPOINT</code> don't affect locks.
The locks are also released when the autocommit mode changes, and for connections with
autocommit set to true (this is the default), locks are released after each statement.
The following statements generate locks:
</p>
<table class="main">
<tr>
<th>Type of Lock</th>
<th>SQL Statement</th>
</tr>
<tr>
<td>Read</td>
<td class="notranslate">SELECT * FROM TEST;<br />
CALL SELECT MAX(ID) FROM TEST;<br />
SCRIPT;</td>
</tr>
<tr>
<td>Write (row-level)</td>
<td class="notranslate">SELECT * FROM TEST WHERE 1=0 FOR UPDATE;</td>
</tr>
<tr>
<td>Write (row-level)</td>
<td class="notranslate">INSERT INTO TEST VALUES(1, 'Hello');<br />
INSERT INTO TEST SELECT * FROM TEST;<br />
UPDATE TEST SET NAME='Hi';<br />
DELETE FROM TEST;</td>
</tr>
<tr>
<td>Exclusive</td>
<td class="notranslate">ALTER TABLE TEST ...;<br />
CREATE INDEX ... ON TEST ...;<br />
DROP INDEX ...;</td>
</tr>
</table>
<p>
The number of seconds until a lock timeout exception is thrown can be
set separately for each connection using the SQL command
<code>SET LOCK_TIMEOUT <milliseconds></code>.
The initial lock timeout (that is the timeout used for new connections) can be set using the SQL command
<code>SET DEFAULT_LOCK_TIMEOUT <milliseconds></code>. The default lock timeout is persistent.
</p>
<h2 id="database_file_layout">Database File Layout</h2>
<p>
The following files are created for persistent databases:
</p>
<table class="main">
<tr><th>File Name</th><th>Description</th><th>Number of Files</th></tr>
<tr><td class="notranslate">
test.mv.db
</td><td>
Database file.<br />
Contains the transaction log, indexes, and data for all tables.<br />
Format: <code><database>.mv.db</code>
</td><td>
1 per database
</td></tr>
<tr><td class="notranslate">
test.newFile
</td><td>
Temporary file for database compaction.<br />
Contains the new MVStore file.<br />
Format: <code><database>.newFile</code>
</td><td>
0 or 1 per database
</td></tr>
<tr><td class="notranslate">
test.tempFile
</td><td>
Temporary file for database compaction.<br />
Contains the temporary MVStore file.<br />
Format: <code><database>.tempFile</code>
</td><td>
0 or 1 per database
</td></tr>
<tr><td class="notranslate">
test.lock.db
</td><td>
Database lock file.<br />
Automatically (re-)created while the database is in use.<br />
Format: <code><database>.lock.db</code>
</td><td>
1 per database (only if in use)
</td></tr>
<tr><td class="notranslate">
test.trace.db
</td><td>
Trace file (if the trace option is enabled).<br />
Contains trace information.<br />
Format: <code><database>.trace.db</code><br />
Renamed to <code><database>.trace.db.old</code> if too big.
</td><td>
0 or 1 per database
</td></tr>
<tr><td class="notranslate">
test.123.temp.db
</td><td>
Temporary file.<br />
Contains a temporary blob or a large result set.<br />
Format: <code><database>.<id>.temp.db</code>
</td><td>
1 per object
</td></tr>
</table>
<h3>Moving and Renaming Database Files</h3>
<p>
Database name and location are not stored inside the database files.
</p><p>
While a database is closed, the files can be moved to another directory, and they can
be renamed as well (as long as all files of the same database start with the same
name and the respective extensions are unchanged).
</p><p>
As there is no platform specific data in the files, they can be moved to other operating systems
without problems.
</p>
<h3>Backup</h3>
<p>
When the database is closed, it is possible to backup the database files.
</p><p>
To backup data while the database is running,
the SQL commands <code>SCRIPT</code> and <code>BACKUP</code> can be used.
</p>
<h2 id="logging_recovery">Logging and Recovery</h2>
<p>
Whenever data is modified in the database and those changes are committed, the changes are written
to the transaction log (except for in-memory objects). The changes to the main data area itself are usually written
later on, to optimize disk access. If there is a power failure, the main data area is not up-to-date,
but because the changes are in the transaction log, the next time the database is opened, the changes
are re-applied automatically.
</p>
<h2 id="compatibility">Compatibility</h2>
<p>
All database engines behave a little bit different. Where possible, H2 supports the ANSI SQL standard,
and tries to be compatible to other databases. There are still a few differences however:
</p>
<p>
In MySQL text columns are case insensitive by default, while in H2 they are case sensitive. However
H2 supports case insensitive columns as well. To create the tables with case insensitive texts, append
<code>IGNORECASE=TRUE</code> to the database URL
(example: <code>jdbc:h2:~/test;IGNORECASE=TRUE</code>).
</p>
<h3 id="compatibility_modes">Compatibility Modes</h3>
<p>
For certain features, this database can emulate the behavior of specific databases.
However, only a small subset of the differences between databases are implemented in this way.
Here is the list of currently supported modes and the differences to the regular mode:
</p>
<h3>REGULAR Compatibility mode</h3>
<p>
This mode is used by default.
</p>
<ul><li>Empty IN predicate is allowed.
</li><li>TOP clause in SELECT is allowed.
</li><li>OFFSET/LIMIT clauses are allowed.
</li><li>MINUS can be used instead of EXCEPT.
</li><li>IDENTITY can be used as a data type.
</li><li>Legacy SERIAL and BIGSERIAL data types are supported.
</li><li>AUTO_INCREMENT clause can be used instead of GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY.
</li></ul>
<h3>STRICT Compatibility Mode</h3>
<p>
To use the STRICT mode, use the database URL <code>jdbc:h2:~/test;MODE=STRICT</code>
or the SQL statement <code>SET MODE STRICT</code>.
In this mode some deprecated features are disabled.
</p>
<p>
If your application or library uses only the H2 or it generates different SQL for different database systems
it is recommended to use this compatibility mode in unit tests
to reduce possibility of accidental misuse of such features.
This mode cannot be used as SQL validator, however.
</p>
<p>
It is not recommended to enable this mode in production builds of libraries,
because this mode may become more restrictive in future releases of H2 that may break your library
if it will be used together with newer version of H2.
</p>
<ul><li>Empty IN predicate is disallowed.
</li><li>TOP and OFFSET/LIMIT clauses are disallowed, only OFFSET/FETCH can be used.
</li><li>MINUS cannot be used instead of EXCEPT.
</li><li>IDENTITY cannot be used as a data type and AUTO_INCREMENT clause cannot be specified.
Use GENERATED BY DEFAULT AS IDENTITY clause instead.
</li><li>SERIAL and BIGSERIAL data types are disallowed.
Use INTEGER GENERATED BY DEFAULT AS IDENTITY or BIGINT GENERATED BY DEFAULT AS IDENTITY instead.
</li></ul>
<h3>LEGACY Compatibility Mode</h3>
<p>
To use the LEGACY mode, use the database URL <code>jdbc:h2:~/test;MODE=LEGACY</code>
or the SQL statement <code>SET MODE LEGACY</code>.
In this mode some compatibility features for applications written for H2 1.X are enabled.
This mode doesn't provide full compatibility with H2 1.X.
</p>
<ul><li>Empty IN predicate is allowed.
</li><li>TOP clause in SELECT is allowed.
</li><li>OFFSET/LIMIT clauses are allowed.
</li><li>MINUS can be used instead of EXCEPT.
</li><li>IDENTITY can be used as a data type.
</li><li>MS SQL Server-style IDENTITY clause is supported.
</li><li>Legacy SERIAL and BIGSERIAL data types are supported.
</li><li>AUTO_INCREMENT clause can be used instead of GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY.
</li><li>If a value for identity column was specified in an INSERT command
the base value of sequence generator of this column is updated if current value of generator was smaller
(larger for generators with negative increment) than the inserted value.
</li><li>Identity columns have implicit DEFAULT ON NULL clause.
It means a NULL value may be specified for this column in INSERT command and it will be treated as DEFAULT.
</li><li>Oracle-style CURRVAL and NEXTVAL can be used on sequences.
</li><li>TOP clause can be used in DELETE and UPDATE.
</li><li>Non-standard Oracle-style WHERE clause can be used in standard MERGE command.
</li><li>Attempt to reference a non-unique set of columns from a referential constraint
will create an UNIQUE constraint on them automatically.
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
</li><li>IDENTITY() and SCOPE_IDENTITY() are supported, but both are implemented like SCOPE_IDENTITY()
</li><li>SYSDATE, SYSTIMESTAMP, and TODAY are supported.
</li></ul>
<h3>DB2 Compatibility Mode</h3>
<p>
To use the IBM DB2 mode, use the database URL <code>jdbc:h2:~/test;MODE=DB2;DEFAULT_NULL_ORDERING=HIGH</code>
or the SQL statement <code>SET MODE DB2</code>.
</p>
<ul><li>For aliased columns, <code>ResultSetMetaData.getColumnName()</code>
returns the alias name and <code>getTableName()</code> returns
<code>null</code>.
</li><li>Support the pseudo-table SYSIBM.SYSDUMMY1.
</li><li>Timestamps with dash between date and time are supported.
</li><li>Datetime value functions return the same value within a command.
</li><li>Second and third arguments of TRANSLATE() function are swapped.
</li><li>SEQUENCE.NEXTVAL and SEQUENCE.CURRVAL are supported
</li><li>LIMIT / OFFSET clauses are supported.
</li><li>MINUS can be used instead of EXCEPT.
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
</li></ul>
<h3>Derby Compatibility Mode</h3>
<p>
To use the Apache Derby mode, use the database URL <code>jdbc:h2:~/test;MODE=Derby;DEFAULT_NULL_ORDERING=HIGH</code>
or the SQL statement <code>SET MODE Derby</code>.
</p>
<ul><li>For aliased columns, <code>ResultSetMetaData.getColumnName()</code>
returns the alias name and <code>getTableName()</code> returns
<code>null</code>.
</li><li>For unique indexes, <code>NULL</code> is distinct.
That means only one row with <code>NULL</code> in one of the columns is allowed.
</li><li>Support the pseudo-table SYSIBM.SYSDUMMY1.
</li><li>Datetime value functions return the same value within a command.
</li></ul>
<h3>HSQLDB Compatibility Mode</h3>
<p>
To use the HSQLDB mode, use the database URL <code>jdbc:h2:~/test;MODE=HSQLDB;DEFAULT_NULL_ORDERING=FIRST</code>
or the SQL statement <code>SET MODE HSQLDB</code>.
</p>
<ul><li>Text can be concatenated using '+'.
</li><li>NULL value works like DEFAULT value is assignments to identity columns.
</li><li>Datetime value functions return the same value within a command.
</li><li>TOP clause in SELECT is supported.
</li><li>LIMIT / OFFSET clauses are supported.
</li><li>MINUS can be used instead of EXCEPT.
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
</li><li>SYSDATE and TODAY are supported.
</li></ul>
<h3>MS SQL Server Compatibility Mode</h3>
<p>
To use the MS SQL Server mode, use the database URL <code>jdbc:h2:~/test;MODE=MSSQLServer</code>
or the SQL statement <code>SET MODE MSSQLServer</code>.
</p>
<ul><li>For aliased columns, <code>ResultSetMetaData.getColumnName()</code>
returns the alias name and <code>getTableName()</code> returns
<code>null</code>.
</li><li>Identifiers may be quoted using square brackets as in <code>[Test]</code>.
</li><li>For unique indexes, <code>NULL</code> is distinct.
That means only one row with <code>NULL</code> in one of the columns is allowed.
</li><li>Text can be concatenated using '+'.
</li><li>Arguments of LOG() function are swapped.
</li><li>MONEY data type is treated like NUMERIC(19, 4) data type. SMALLMONEY data type is treated like NUMERIC(10, 4)
data type.
</li><li><code>IDENTITY</code> can be used for automatic id generation on column level.
</li><li>Table hints are discarded. Example: <code>SELECT * FROM table WITH (NOLOCK)</code>.
</li><li>Datetime value functions return the same value within a command.
</li><li>0x literals are parsed as binary string literals.
</li><li>TRUNCATE TABLE restarts next values of generated columns.
</li><li>TOP clause in SELECT, UPDATE, and DELETE is supported.
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
</li></ul>
<h3>MariaDB Compatibility Mode</h3>
<p>
To use the MariaDB mode, use the database URL <code>jdbc:h2:~/test;MODE=MariaDB;DATABASE_TO_LOWER=TRUE</code>.
When case-insensitive identifiers are needed append <code>;CASE_INSENSITIVE_IDENTIFIERS=TRUE</code> to URL.
Do not change value of DATABASE_TO_LOWER after creation of database.
</p>
<ul><li>Creating indexes in the <code>CREATE TABLE</code> statement is allowed using
<code>INDEX(..)</code> or <code>KEY(..)</code>.
Example: <code>create table test(id int primary key, name varchar(255), key idx_name(name));</code>
</li><li>When converting a floating point number to an integer, the fractional
digits are not truncated, but the value is rounded.
</li><li>ON DUPLICATE KEY UPDATE is supported in INSERT statements, due to this feature VALUES has special non-standard
meaning is some contexts.
</li><li>INSERT IGNORE is partially supported and may be used to skip rows with duplicate keys if ON DUPLICATE KEY
UPDATE is not specified.
</li><li>REPLACE INTO is partially supported.
</li><li>Spaces are trimmed from the right side of CHAR values.
</li><li>REGEXP_REPLACE() uses \ for back-references.
</li><li>Datetime value functions return the same value within a command.
</li><li>0x literals are parsed as binary string literals.
</li><li>Unrelated expressions in ORDER BY clause of DISTINCT queries are allowed.
</li><li>Some MariaDB-specific ALTER TABLE commands are partially supported.
</li><li>TRUNCATE TABLE restarts next values of generated columns.
</li><li>NEXT VALUE FOR returns different values when invoked multiple times within the same row.
</li><li>If value of an identity column was manually specified, its sequence is updated to generate values after
inserted.
</li><li>NULL value works like DEFAULT value is assignments to identity columns.
</li><li>LIMIT / OFFSET clauses are supported.
</li><li>AUTO_INCREMENT clause can be used.
</li><li>YEAR data type is treated like SMALLINT data type.
</li><li>GROUP BY clause can contain 1-based positions of expressions from the SELECT list.
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
</li></ul>
<p>
Text comparison in MariaDB is case insensitive by default, while in H2 it is case sensitive (as in most other databases).
H2 does support case insensitive text comparison, but it needs to be set separately,
using <code>SET IGNORECASE TRUE</code>.
This affects comparison using <code>=, LIKE, REGEXP</code>.
</p>