H2

External

• http://www.h2database.com/html/main.html

Internal

• Relational Databases
• Spring H2 Support

Concepts

• H2 Data Types

H2 URLs

Remote:

jdbc:h2:tcp://{host::localhost}[:{port::9092}]/{database::default}[;<;,user={user:param},password={password:param},{:identifier}={:param}>]

In-memory:

jdbc:h2:mem:{database::default}?[;<;,{:identifier}={:param}>]

Embedded:

jdbc:h2:!(tcp://)[file:]{path}[;<;,user={user:param},password={password:param},{:identifier}={:param}>]

This works for unit tests:

spring.datasource.url=jdbc:h2:mem:MOCKDRIVER_TEST_H2;DB_CLOSE_DELAY=-1

while this one does not:

spring.datasource.url=jjdbc:h2:tcp://localhost/mem:MOCKDRIVER_TEST_H2;DB_CLOSE_DELAY=-1

Figure out the difference.

Installation

cd $RUNTIME_DIR
unzip .../h2-2017-06-10.zip
mv h2 h2-2017-06-10
ln -s ./h2-2017-06-10 h2

Accessing an H2 Instance Embedded within a Different JVM

By default, the H2 instance is only accessible from the same address space. However, it can be made accessible remotely, over the network, if a org.h2.tools.Server is started in the same JVM:

org.h2.tools.Server server = org.h2.tools.Server.createTcpServer().start();

Note that for the types to be accessible, you need to declare the H2 dependency as "implementation" instead of "runtimeOnly".

Once the H2 TCP server is up and running, the instance can be accessed at:

jdbc:h2:tcp://localhost/mem:default;DB_CLOSE_DELAY=-1

The "DB_CLOSE_DELAY=-1" is important, because 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 ;DB_CLOSE_DELAY=-1 to the database URL.

If challenged for user/password, use "sa" and an empty password.

Operations

Run Database and H2 Console

cd $RUNTIME_DIR/h2/bin
./h2.sh

This will start a "test" database and make H2 console available on port 8082: http://localhost:8082

The "test" database will be available as "jdbc:h2:tcp://localhost/~/test", authenticating with a "sa" user and an empty password ("").

H2 DDL Operations

H2 DDL Operations

H2 DML Operations

H2 DML Operations