SELECT
,
TABLE
,
VALUES
,
EXPLAIN
,
CALL
,
SCRIPT
,
SHOW
,
HELP
.
EXECUTE
may return either a result set or an update count.
Result of a
WITH
statement depends on inner command.
All other statements return an update count.
FETCH
in a query
(example:
SELECT * FROM TEST FETCH FIRST 100 ROWS ONLY
),
or by using
Statement.setMaxRows(max)
.
SET MAX_MEMORY_ROWS
.
If
ORDER BY
is used, the sorting is done using an
external sort algorithm.
In this case, each block of rows is sorted using quick sort, then written to disk;
when reading the data, the blocks are merged together.
PreparedStatement.setBinaryStream
. To store a CLOB, use
PreparedStatement.setCharacterStream
. To read a BLOB, use
ResultSet.getBinaryStream
,
and to read a CLOB, use
ResultSet.getCharacterStream
.
When using the client/server mode, large BLOB and CLOB data is stored in a temporary file
on the client side.
CREATE LINKED TABLE
statement:
CREATE LINKED TABLE LINK('org.postgresql.Driver', 'jdbc:postgresql:test', 'sa', 'sa', 'TEST');
You can then access the table in the usual way.
Whenever the linked table is accessed, the database issues specific queries over JDBC.
Using the example above, if you issue the query
SELECT * FROM LINK WHERE ID=1
,
then the following query is run against the PostgreSQL database:
SELECT * FROM TEST WHERE ID=?
.
The same happens for insert and update statements.
Only simple statements are executed against the target database, that means no joins
(queries that contain joins are converted to simple queries).
Prepared statements are used where possible.
To view the statements that are executed against the target table, set the trace level to 3.
If multiple linked tables point to the same database (using the same database URL), the connection
is shared. To disable this, set the system property
h2.shareLinkedConnections=false
.
The statement
CREATE LINKED TABLE
supports an optional schema name parameter.
The following are not supported because they may result in a deadlock:
creating a linked table to the same database,
and creating a linked table to another database using the server mode if the other database is open in the same server
(use the embedded mode instead).
Data types that are not supported in H2 are also not supported for linked tables,
for example unsigned data types if the value is outside the range of the signed type.
In such cases, the columns needs to be cast to a supported type.
org.h2.samples.UpdatableView
.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SNAPSHOT
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
CreateCluster
tool without stopping
the remaining server. Applications that are still connected are automatically disconnected,
however when appending
;AUTO_RECONNECT=TRUE
, they will recover from that.
To initialize the cluster, use the following steps:
CreateCluster
tool to copy the database to
another location and initialize the clustering.
Afterwards, you have two databases containing the same data.
server1, server2
.
Each directory will simulate a directory on a computer.
CreateCluster
tool to initialize clustering.
This will automatically create a new, empty database if it does not exist.
Run the tool on the command line:
java org.h2.tools.CreateCluster
-urlSource jdbc:h2:tcp://localhost:9101/~/test
-urlTarget jdbc:h2:tcp://localhost:9102/~/test
-user sa
-serverList localhost:9101,localhost:9102
jdbc:h2:tcp://localhost:9101,localhost:9102/~/test
CreateCluster
tool.
''
(two single quotes), then the cluster mode is disabled. Otherwise, the list of
servers is returned, enclosed in single quote. Example:
'server1:9191,server2:9191'
.
It is also possible to get the list of servers by using Connection.getClientInfo().
The property list returned from
getClientInfo()
contains a
numServers
property that returns the
number of servers that are in the connection list. To get the actual servers,
getClientInfo()
also has
properties
server0
..
serverX
, where serverX is the number of servers minus 1.
Example: To get the 2nd server in the connection list one uses
getClientInfo('server1')
.
Note:
The
serverX
property only returns IP addresses and ports and not hostnames.
UUID(), RANDOM_UUID(), SECURE_RAND(), SESSION_ID(),
MEMORY_FREE(), MEMORY_USED(), CSVREAD(), CSVWRITE(), RAND()
[when not using a seed].
Those functions should not be used directly in modifying statements
(for example
INSERT, UPDATE, MERGE
). However, they can be used
in read-only statements and the result can then be used for modifying statements.
Identity columns aren't supported.
Instead, sequence values need to be manually requested and then used to insert data (using two statements).
When using the cluster modes, result sets are read fully in memory by the client, so that
there is no problem if the server dies that executed the query. Result sets must fit in memory
on the client side.
The SQL statement
SET AUTOCOMMIT FALSE
is not supported in the cluster mode.
To disable autocommit, the method
Connection.setAutoCommit(false)
needs to be called.
It is possible that a transaction from one connection overtakes a transaction from a different connection.
Depending on the operations, this might result in different results, for example when
conditionally incrementing a value in a row.
PREPARE COMMIT transactionName
SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT
COMMIT TRANSACTION transactionName
or
ROLLBACK TRANSACTION transactionName
There is a compatibility setting
SET NON_KEYWORDS
that can be used as a temporary workaround for applications that use keywords as unquoted identifiers.
h2/service
.
The service wrapper bundled with H2 is a 32-bit version.
To use a 64-bit version of Windows (x64), you need to use a 64-bit version of the wrapper,
for example the one from
Simon Krenger.
When running the database as a service, absolute path should be used.
Using
~
in the database URL is problematic in this case,
because it means to use the home directory of the current user.
The service might run without or with the wrong user, so that
the database files might end up in an unexpected place.
1_install_service.bat
.
If successful, a command prompt window will pop up and disappear immediately. If not, a message will appear.
2_start_service.bat
.
Please note that the batch file does not print an error message if the service is not installed.
3_start_browser.bat
to do that. The
default port (8082) is hard coded in the batch file.
4_stop_service.bat
.
Please note that the batch file does not print an error message if the service is not installed or started.
5_uninstall_service.bat
.
If successful, a command prompt window will pop up and disappear immediately. If not, a message will appear.
H2DRIVERS
or
CLASSPATH
before
installing the service. Multiple drivers can be set; each entry needs to be separated with a
;
(Windows) or
:
(other operating systems). Spaces in the path names are supported.
The settings must not be quoted.
c:/windows/syswow64/odbcad32.exe
.
At this point you set up your DSN just like you would on any other system.
See also:
Re: ODBC Driver on Windows 64 bit
psqlodbc-08_02*
) or newer is recommended.
The Windows version of the PostgreSQL ODBC driver is available at
https://www.postgresql.org/ftp/odbc/versions/msi/
.
-baseDir
to save databases in another directory, for example the user home directory:
java -cp h2*.jar org.h2.tools.Server -baseDir ~
The PG server can be started and stopped from within a Java application as follows:
Server server = Server.createPgServer("-baseDir", "~");
server.start();
server.stop();
By default, only connections from localhost are allowed. To allow remote connections, use
-pgAllowOthers
when starting the server.
To map an ODBC database name to a different JDBC database name,
use the option
-key
when starting the server.
Please note only one mapping is allowed. The following will map the ODBC database named
TEST
to the database URL
jdbc:h2:~/data/test;cipher=aes
:
java org.h2.tools.Server -pg -key TEST "~/data/test;cipher=aes"
odbcad32.exe
to open the Data Source Administrator. Then click on 'Add...'
and select the PostgreSQL Unicode driver. Then click 'Finish'.
You will be able to change the connection properties.
The property column represents the property key in the
odbc.ini
file
(which may be different from the GUI).
PropertyExampleRemarks
Data SourceH2 TestThe name of the ODBC Data Source
Database~/test;ifexists=true
The database name. This can include connections settings.
By default, the database is stored in the current working directory
where the Server is started except when the -baseDir setting is used.
The name must be at least 3 characters.
ServernamelocalhostThe server name or IP address.
org.h2.test.poweroff.Test
.
RandomAccessFile
supports the modes
rws
and
rwd
:
rwd
: every update to the file's content is written synchronously to the underlying storage device.
rws
: in addition to
rwd
, every update to the metadata is written synchronously.
org.h2.test.poweroff.TestWrite
) with one of those modes achieves
around 50 thousand write operations per second.
Even when the operating system write buffer is disabled, the write rate is around 50 thousand operations per second.
This feature does not force changes to disk because it does not flush all buffers.
The test updates the same byte in the file again and again. If the hard drive was able to write at this rate,
then the disk would need to make at least 50 thousand revolutions per second, or 3 million RPM
(revolutions per minute). There are no such hard drives. The hard drive used for the test is about 7200 RPM,
or about 120 revolutions per second. There is an overhead, so the maximum write rate must be lower than that.
Calling
fsync
flushes the buffers. There are two ways to do that in Java:
FileDescriptor.sync()
. The documentation says that this forces all system
buffers to synchronize with the underlying device.
This method is supposed to return after all in-memory modified copies of buffers associated with this file descriptor
have been written to the physical medium.
FileChannel.force()
. This method is supposed
to force any updates to this channel's file to be written to the storage device that contains it.
By default, MySQL calls
fsync
for each commit. When using one of those methods, only around 60 write operations
per second can be achieved, which is consistent with the RPM rate of the hard drive used.
Unfortunately, even when calling
FileDescriptor.sync()
or
FileChannel.force()
,
data is not always persisted to the hard drive, because most hard drives do not obey
fsync()
: see
Your Hard Drive Lies to You
.
In Mac OS X,
fsync
does not flush hard drive buffers. See
Bad fsync?
.
So the situation is confusing, and tests prove there is a problem.
Trying to flush hard drive buffers is hard, and if you do the performance is very bad.
First you need to make sure that the hard drive actually flushes all buffers.
Tests show that this can not be done in a reliable way.
Then the maximum number of transactions is around 60 per second.
Because of those reasons, the default behavior of H2 is to delay writing committed transactions.
In H2, after a power failure, a bit more than one second of committed transactions may be lost.
To change the behavior, use
SET WRITE_DELAY
and
CHECKPOINT SYNC
.
Most other databases support commit delay as well.
In the performance comparison, commit delay was used for all databases that support it.
org.h2.test.poweroff
.
Two computers with network connection are required to run this test.
One computer just listens, while the test application is run (and power is cut) on the other computer.
The computer with the listener application opens a TCP/IP port and listens for an incoming connection.
The second computer first connects to the listener, and then created the databases and starts inserting
records. The connection is set to 'autocommit', which means after each inserted record a commit is performed
automatically. Afterwards, the test computer notifies the listener that this record was inserted successfully.
The listener computer displays the last inserted record number every 10 seconds. Now, switch off the power
manually, then restart the computer, and run the application again. You will find out that in most cases,
none of the databases contains all the records that the listener computer knows about. For details, please
consult the source code of the listener and test application.
Recover
tool can be used to extract the contents of a database file, even if the database is corrupted.
It also extracts the content of the transaction log and large objects (CLOB or BLOB).
To run the tool, type on the command line:
java -cp h2*.jar org.h2.tools.Recover
For each database in the current directory, a text file will be created.
This file contains raw insert statements (for the data) and data definition (DDL) statements to recreate
the schema of the database. This file can be executed using the
RunScript
tool or a
RUNSCRIPT
SQL statement.
The script includes at least one
CREATE USER
statement. If you run the script against a database that was created with the same
user, or if there are conflicting users, running the script will fail. Consider running the script
against a database that was created with a user name that is not in the script.
The
Recover
tool creates a SQL script from database file. It also processes the transaction log.
To verify the database can recover at any time, append
;RECOVER_TEST=64
to the database URL in your test environment. This will simulate an application crash after each 64 writes to the database file.
A log file named
databaseName.h2.db.log
is created that lists the operations.
The recovery is tested using an in-memory file system, that means it may require a larger heap setting.
File.createNewFile
).
Then, the process waits a little bit (20 ms) and checks the file again. If the file was changed
during this time, the operation is aborted. This protects against a race condition
when one process deletes the lock file just after another one create it, and a third process creates
the file again. It does not occur if there are only two writers.
If the file can be created, a random number is inserted together with the locking method
('file'). Afterwards, a watchdog thread is started that
checks regularly (every second once by default) if the file was deleted or modified by
another (challenger) thread / process. Whenever that occurs, the file is overwritten with the
old data. The watchdog thread runs with high priority so that a change to the lock file does
not get through undetected even if the system is very busy. However, the watchdog thread
does use very little resources (CPU time), because it waits most of the time. Also, the watchdog only reads from the hard disk
and does not write to it.
If the lock file exists and was recently modified, the process waits for some time (up to two seconds).
If it was still changed, an exception is thrown (database is locked). This is done to eliminate race conditions with many concurrent
writers. Afterwards, the file is overwritten with a new version (challenge).
After that, the thread waits for 2 seconds.
If there is a watchdog thread protecting the file, he will overwrite the change
and this process will fail to lock the database.
However, if there is no watchdog thread, the lock file will still be as written by
this thread. In this case, the file is deleted and atomically created again.
The watchdog thread is started in this case and the file is locked.
This algorithm is tested with over 100 concurrent threads. In some cases, when there are
many concurrent threads trying to lock the database, they block each other (meaning
the file cannot be locked by any of them) for some time. However, the file never gets
locked by two threads at the same time. However using that many concurrent threads
/ processes is not the common use case. Generally, an application should throw an error
to the user if it cannot open a database, and not try again in a (fast) loop.
;FILE_LOCK=SOCKET
to the database URL.
The algorithm is:
;FILE_LOCK=FS
to the database URL.
This feature is relatively new. When using it for production, please ensure
your system does in fact lock files as expected.
i'sE2rtPiUKtT
from the sentence
it's easy to remember this password if you know the trick
.
javax.swing.JPasswordField
.
web.xml
file).
In addition to connecting with the plain text password,
this database supports connecting with the password hash.
This means that only the hash of the password (and not the plain text password)
needs to be stored in the configuration file.
This will only protect others from reading or re-constructing the plain text password
(even if they have access to the configuration file);
it does not protect others from accessing the database using the password hash.
To connect using the password hash instead of plain text password, append
;PASSWORD_HASH=TRUE
to the database URL, and replace
the password with the password hash. To calculate the password hash from a plain text password,
run the following command within the H2 Console tool:
@password_hash <upperCaseUserName> <password>
.
As an example, if the user name is
sa
and the password is
test
, run the command
@password_hash SA test
.
Then use the resulting password hash as you would use the plain text password.
When using an encrypted database, then the user password and file password
need to be hashed separately. To calculate the hash of the file password, run:
@password_hash file <filePassword>
.
' OR ''='
.
In this case the statement becomes:
SELECT * FROM USERS WHERE PASSWORD='' OR ''='';
Which is always true no matter what the password stored in the database is.
For more information about SQL Injection, see
Glossary and Links
.
WHERE NAME='abc'
or
WHERE CustomerId=10
will fail.
It is still possible to use prepared statements and parameters as described above. Also, it is still possible to generate
SQL statements dynamically, and use the Statement API, as long as the SQL statements
do not include literals.
There is also a second mode where number literals are allowed:
SET ALLOW_LITERALS NUMBERS
.
To allow all literals, execute
SET ALLOW_LITERALS ALL
(this is the default setting). Literals can only be enabled or disabled by an administrator.
CREATE CONSTANT
command.
Constants can be defined only
when literals are enabled, but used even when literals are disabled. To avoid name clashes
with column names, constants can be defined in other schemas:
CREATE SCHEMA CONST AUTHORIZATION SA;
CREATE CONSTANT CONST.ACTIVE VALUE 'Active';
CREATE CONSTANT CONST.INACTIVE VALUE 'Inactive';
SELECT * FROM USERS WHERE TYPE=CONST.ACTIVE;
Even when literals are enabled, it is better to use constants instead
of hard-coded number or text literals in queries or views. With constants, typos are found at compile
time, the source code is easier to understand and change.
ZERO()
:
SELECT * FROM USERS WHERE LENGTH(PASSWORD)=ZERO();
-webAllowOthers, -tcpAllowOthers, -pgAllowOthers
.
If you enable remote access using
-tcpAllowOthers
or
-pgAllowOthers
,
please also consider using the options
-baseDir
,
so that remote users can not create new databases
or access existing databases with weak passwords.
When using the option
-baseDir
, only databases within that directory may be accessed.
Ensure the existing accessible databases are protected using strong passwords.
If you enable remote access using
-webAllowOthers
,
please ensure the web server can only be accessed from trusted networks.
If this option is specified,
-webExternalNames
should be also specified with
comma-separated list of external names or addresses of this server.
The options
-baseDir
don't protect
access to the saved connection settings,
or access to other databases accessible from the system.
System.setProperty
by executing:
CREATE ALIAS SET_PROPERTY FOR "java.lang.System.setProperty";
CALL SET_PROPERTY('abc', '1');
CREATE ALIAS GET_PROPERTY FOR "java.lang.System.getProperty";
CALL GET_PROPERTY('abc');
To restrict users (including admins) from loading classes and executing code,
the list of allowed classes can be set in the system property
h2.allowedClasses
in the form of a comma separated list of classes or patterns (items ending with
*
).
By default all classes are allowed. Example:
java -Dh2.allowedClasses=java.lang.Math,com.acme.*
This mechanism is used for all user classes, including database event listeners,
trigger classes, user-defined functions, user-defined aggregate functions, and JDBC
driver classes (with the exception of the H2 driver) when using the H2 Console.
file@
and the file password is hashed using SHA-256. This hash value is
transmitted to the server.
When a new database file is created, a new cryptographically secure
random salt value is generated. The size of the salt is 64 bits.
The combination of the file password hash and the salt value is hashed 1024 times
using SHA-256. The reason for the iteration is to make it harder for an attacker to
calculate hash values for common passwords.
The resulting hash value is used as the key for the block cipher algorithm.
Then, an initialization vector (IV) key
is calculated by hashing the key again using SHA-256.
This is to make sure the IV is unknown to the attacker.
The reason for using a secret IV is to protect against watermark attacks.
Before saving a block of data (each block is 8 bytes long), the following operations are executed:
first, the IV is calculated by encrypting the block number with the IV key (using the same
block cipher algorithm). This IV is combined with the plain text using XOR. The resulting data is
encrypted using the AES-128 algorithm.
When decrypting, the operation is done in reverse. First, the block is decrypted using the key,
and then the IV is calculated combined with the decrypted text using XOR.
Therefore, the block cipher mode of operation is CBC (cipher-block chaining), but each chain
is only one block long. The advantage over the ECB (electronic codebook) mode is that patterns
in the data are not revealed, and the advantage over multi block CBC is that flipped cipher text bits
are not propagated to flipped plaintext bits in the next block.
Database encryption is meant for securing the database while it is not in use (stolen laptop and so on).
It is not meant for cases where the attacker has access to files while the database is in use.
When he has write access, he can for example replace pieces of files with pieces of older versions
and manipulate data like this.
File encryption slows down the performance of the database engine. Compared to unencrypted mode,
database operations take about 2.5 times longer using AES (embedded mode).
h2.delayWrongPasswordMin
and
h2.delayWrongPasswordMax
to change the minimum (the default is 250 milliseconds)
or maximum delay (the default is 4000 milliseconds, or 4 seconds). The delay only
applies for those using the wrong password. Normally there is no delay for a user that knows the correct
password, with one exception: after using the wrong password, there is a delay of up to (randomly distributed)
the same delay as for a wrong password. This is to protect against parallel brute force attacks,
so that an attacker needs to wait for the whole delay. Delays are synchronized. This is also required
to protect against parallel attacks.
There is only one exception message for both wrong user and for wrong password,
to make it harder to get the list of user names. It is not possible from the stack trace to see
if the user name was wrong or the password.
SSLServerSocket
.
There is a default self-certified certificate to support an easy starting point, but
custom certificates are supported as well.
SSLServerSocket, SSLSocket
). By default, anonymous TLS is enabled.
To use your own keystore, set the system properties
javax.net.ssl.keyStore
and
javax.net.ssl.keyStorePassword
before starting the H2 server and client.
See also
Customizing the Default Key and Trust Stores, Store Types, and Store Passwords
for more information.
To disable anonymous TLS, set the system property
h2.enableAnonymousTLS
to false.
RANDOM_UUID()
or
UUID()
.
Here is a small program to estimate the probability of having two identical UUIDs
after generating a number of values:
public class Test {
public static void main(String[] args) throws Exception {
double x = Math.pow(2, 122);
for (int i = 35; i < 62; i++) {
double n = Math.pow(2, i);
double p = 1 - Math.exp(-(n * n) / 2 / x);
System.out.println("2^" + i + "=" + (1L << i) +
" probability: 0" +
String.valueOf(1 + p).substring(1));
Some values are:
Number of UUIsProbability of Duplicates
2^36=68'719'476'7360.000'000'000'000'000'4
2^41=2'199'023'255'5520.000'000'000'000'4
2^46=70'368'744'177'6640.000'000'000'4
To help non-mathematicians understand what those numbers mean, here a comparison:
one's annual risk of being hit by a meteorite is estimated to be one chance in 17 billion,
that means the probability is about 0.000'000'000'06.
&&
, as in PostGIS:
SELECT * FROM GEO_TABLE
WHERE THE_GEOM &&
'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))';
You can verify that the spatial index is used using the "explain plan" feature:
EXPLAIN SELECT * FROM GEO_TABLE
WHERE THE_GEOM &&
'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))';
-- Result
SELECT
"PUBLIC"."GEO_TABLE"."GID",
"PUBLIC"."GEO_TABLE"."THE_GEOM"
FROM "PUBLIC"."GEO_TABLE"
/* PUBLIC.GEO_TABLE_SPATIAL_INDEX: THE_GEOM &&
GEOMETRY 'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))' */
WHERE "THE_GEOM" &&
GEOMETRY 'POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))'
For persistent databases, the spatial index is stored on disk;
for in-memory databases, the index is kept in memory.
UNION ALL
,
and the recursion needs to be on the second part of the query.
No tables or views with the name of the table expression may exist.
Different table expression names need to be used when using multiple distinct table
expressions within the same transaction and for the same session.
All columns of the table expression are of type
VARCHAR
,
and may need to be cast to the required data type.
Views with recursive queries are not supported.
Subqueries and
INSERT INTO ... FROM
with recursive queries are not supported.
Parameters are only supported within the last
SELECT
statement
(a workaround is to use session variables like
@start
within the table expression).
The syntax is:
WITH RECURSIVE recursiveQueryName(columnName, ...) AS (
nonRecursiveSelect
UNION ALL
recursiveSelect
select
-DpropertyName=value
. It is usually not required to change those settings manually.
The settings are case sensitive.
Example:
java -Dh2.serverCachedObjects=256 org.h2.tools.Server
The current value of the settings can be read in the table
INFORMATION_SCHEMA.SETTINGS
.
For a complete list of settings, see
SysProperties
.
h2.bindAddress
.
This setting is used for both regular server sockets and for TLS server sockets.
IPv4 and IPv6 address formats are supported.
file:
the default file system that uses
FileChannel
.
zip:
read-only zip-file based file system. Format:
zip:~/zipFileName!/fileName
.
split:
file system that splits files in 1 GB files (stackable with other file systems).
nioMapped:
file system that uses memory mapped files (faster in some operating systems).
Please note that there currently is a file size limitation of 2 GB when using this file system.
To work around this limitation, combine it with the split file system:
split:nioMapped:~/test
.
async:
experimental file system that uses
AsynchronousFileChannel
instead of
FileChannel
(faster in some operating systems).
memFS:
in-memory file system (slower than mem; experimental; mainly used for testing the database engine itself).
memLZF:
compressing in-memory file system (slower than memFS but uses less memory; experimental; mainly used for testing the database engine itself).
nioMemFS:
stores data outside of the VM's heap - useful for large memory DBs without incurring GC costs.
nioMemLZF:
stores compressed data outside of the VM's heap -
useful for large memory DBs without incurring GC costs.
Use "nioMemLZF:12:" to tweak the % of blocks that are stored uncompressed.
If you size this to your working set correctly,
compressed storage is roughly the same performance as uncompressed.
The default value is 1%.
As an example, to use the
async:
file system
use the following database URL:
jdbc:h2:async:~/test
.
To register a new file system, extend the classes
org.h2.store.fs.FilePath, FileBase
,
and call the method
FilePath.register
before using it.
For input streams (but not for random access files), URLs may be used in addition to the registered file systems.
Example:
jar:file:///c:/temp/example.zip!/org/example/nested.csv
.
To read a stream from the classpath, use the prefix
classpath:
, as in
classpath:/org/h2/samples/newsfeed.sql
.
split:
is used to split logical files into multiple physical files,
for example so that a database can get larger than the maximum file system size of the operating system.
If the logical file is larger than the maximum file size, then the file is split as follows:
<fileName>
(first block, is always created)
<fileName>.1.part
(second block)
More physical files (
*.2.part, *.3.part
) are automatically created / deleted if needed.
The maximum physical file size of a block is 2^30 bytes, which is also called 1 GiB or 1 GB.
However this can be changed if required, by specifying the block size in the file name.
The file name format is:
split:<x>:<fileName>
where the file size per block is 2^x.
For 1 MiB block sizes, use x = 20 (because 2^20 is 1 MiB).
The following file name means the logical file is split into 1 MiB blocks:
split:20:~/test.h2.db
.
An example database URL for this case is
jdbc:h2:split:20:~/test
.
OTHER
, using standard Java serialization/deserialization semantics.
To disable this feature set the system property
h2.serializeJavaObject=false
(default: true).
Serialization and deserialization of java objects is customizable both at system level and at database level providing a
JavaObjectSerializer
implementation:
At system level set the system property
h2.javaObjectSerializer
with the
Fully Qualified Name of the
JavaObjectSerializer
interface implementation.
It will be used over the entire JVM session to (de)serialize java objects being stored in column of type OTHER.
Example
h2.javaObjectSerializer=com.acme.SerializerClassName
.
At database level execute the SQL statement
SET JAVA_OBJECT_SERIALIZER 'com.acme.SerializerClassName'
or append
;JAVA_OBJECT_SERIALIZER='com.acme.SerializerClassName'
to the database URL:
jdbc:h2:~/test;JAVA_OBJECT_SERIALIZER='com.acme.SerializerClassName'
.
Please note that this SQL statement can only be executed before any tables are defined.
split:
.
In that case files are split into files of 1 GB by default.
An example database URL is:
jdbc:h2:split:~/test
.