H2: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(18 intermediate revisions by the same user not shown)
Line 5: Line 5:
=Internal=
=Internal=


* [[Databases#Databases|Databases]]
* [[Relational_Databases#Overview|Relational Databases]]
* [[Spring H2 Support]]


=Overview=
=Overview=
=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}>]
<font color=darkgray>
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.
</font>


=Installation=
=Installation=
Line 15: Line 40:
  mv h2 h2-2017-06-10
  mv h2 h2-2017-06-10
  ln -s ./h2-2017-06-10 h2
  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:
<syntaxhighlight lang='java'>
org.h2.tools.Server server = org.h2.tools.Server.createTcpServer().start();
</syntaxhighlight>
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=
=Operations=
Line 23: Line 66:
  ./h2.sh
  ./h2.sh


This will start the database and make H2 console available on port 8082.
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==
 
{{Internal|H2 DDL Operations|H2 DDL Operations}}


=H2 Console=
==H2 DML Operations==


http://localhost:8082
{{Internal|H2 DML Operations|H2 DML Operations}}

Latest revision as of 22:02, 7 November 2021

External

Internal

Overview

Concepts

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