H2 Database Tutorial

H2 is an embedded database developed in Java, it is free from platform constraints. It is only a class library and can be embedded directly into java application. The biggest advantage of using H2 database is that it can be packaged and distributed with java applications, which makes it very convenient to store a small amount of structured data.

It can also be used for unit testing. H2 database startup speed is fast, and persistence can be turned off. Every use case is restored to its initial state immediately after execution.

H2 can also be used as a supplement to NoSQL db as a cache. When the data model must be relational in some scenarios, it can be used as a memory cache buffer for backend MySQL/Oracle db to cache data which do not change frequently but read frequently, such as dictionary tables and privilege tables.

1. H2 Database Execution Mode.

H2 database can be executed in three mode.

  1. Embedded mode.
  2. Server mode.
  3. Mixed mode.

1.1 Embedded Mode.

h2 database execution embedded mode

In embedded mode, the database performance is better. Because application and database are executed in the same JVM, the java application use JDBC to access H2 db. Data can be persisted to data file also, but at the same time, only one client can connect to the H2 db.

1.2 Server Mode.

h2 database execution server mode

In server mode, java application and H2 database run in different JVM or machine. H2 database is executed in a H2 server. Server mode support multiple db connection.

1.3 Mixed Mode.

h2 database execution mixed mode

Mixed mode is combination of embedded mode and server mode. The first application start h2 db in embedded mode, it also start h2 db server. Then latter application can access h2 db in server mode. All the application can access same data at same time.

2. H2 Database Download & Install.

Go to h2 db download page to download the newest version. I recommend you to download the platform independent zip file which can run in all windows, linux and macos.

Unzip the downloaded zip file to a local folder, and cd into the h2/bin folder, you can see below files.h2 database source file folder structure

  1. h2.sh — start h2 db script file in Linux or MacOS.
  2. h2.bat — start h2 db script file for Windows.
  3. h2w.bat — start h2 db in windows do not show dos window.
  4. h2-1.4.197.jar — h2 db jar file which contains jdbc driver class.

3. Run H2 Database In Embedded, Server & Mixed Mode.

Now h2 db has been installed successfully, we will start it in different mode. Run bin / h2.sh file, it will start a console server which can manage h2 db in embedded or server mode.

READ :   Archive For Required Library: '.m2/repository/spring-tx-5.0.10.release.jar' In Project Cannot Be Read Or Is Not A Valid Zip File

h2 web browser manage h2 db via console server

The console server is accessed from a web browser. And the console server can manage all database system, such as MySql, Oracle etc. But first you need to contain related db jdbc jar file in the classpath. Below is the console server web ui. The default console server url is http://192.168.1.5:8082/

3.1 Run H2 DB In Embedded Mode.

3.1.1 Run embedded h2 db in java code.

It is so easy to use h2 db in embedded mode in your java application. Please follow below steps.

  1. Add bin / h2-1.4.197.jar file in your application java class path. The jar fie do not dependent on other jars.
  2. Use org.h2.Driver as h2 db jdbc driver class.
  3. The h2 db jdbc url is jdbc:h2:~/db_name ( for example jdbc:h2:~/user_account), this url will create a h2 db named as user_account. ~ means the db file is saved in your user home directory.
  4. In above example, when java application run, the db user_account will be created automatically. All the db data will be saved in file user_account.mv.db. And it is saved in user home directory.
  5. When you close the last h2 in memory db connection, the database will be closed also by default, this will lead to data lost. If you want to make the memory db persist longer after JVM exit, you should add DB_CLOSE_ON_EXIT=FALSE argument in jdbc connection url, this will make the h2 db not closed after the JVM shutdown. Then it can be accessed by java app running in other JVM later.
    jdbc:h2:~/user_account;DB_CLOSE_ON_EXIT=FALSE
  6. Below is the java code that create h2 embedded db and return the db connection object.
    java.sql.Connection connection = DriverManager.getConnection("jdbc:h2:~/user_account", "sa", "");
3.1.1 Run embedded h2 db in h2 console server.

You can also use h2 console server to start an embedded h2 db.

  1. Run bin / h2.sh in terminal, it will open the console server web gui in a web browser.
  2. Then select Generic H2 (Embedded) in the Saved Settings drop down list.
    h2 embedded db console server web ui
  3. It will display the driver class and jdbc url in below related field. Click Connect button to create the h2 embedded db. Then user_account.mv.db file is created in your home directory. And the browser will display the h2 db management GUI.

3.2 Run H2 DB In Server Mode.

3.2.1 Run h2 db server mode by command.
  1. Copy h2.sh to h2_server.sh and input below command in h2_server.sh.
    dir=$(dirname "$0")
    java -cp "$dir/h2-1.4.197.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Server -tcpAllowOthers -webAllowOthers -webPort 8082 "[email protected]"
  2. Open a terminal and execute ./h2_server.sh,  it will open a web browser and start h2 db in server mode.
  3. org.h2.tools.Server : start h2 db in server mode.
  4. -tcpAllowOthers : Allow remote machine access h2 db via tcp.
  5. -webAllowOthers : Allow remote machine access h2 server via web browser.
  6. -webPort 8082 : h2 console server listening port number, the default value is 8082.
  7. Then you should use below jdbc connection url to access the serve mode h2 db.
    java.sql.Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");
  8. Above code will create test.mv.db file in localhost machine user home directory. You can also specify other directory path.
READ :   Spring Java Based Configuration Example
3.2.2 Run h2 db server mode by h2 console server.
  1. Run bin / h2.sh in terminal window.
  2. Then select Generic H2(Server) from the Saved Settings drop down list. Please note the jdbc url is different from embedded mode.
    h2 database console server web ui
  3. Click Connect button will create the database file test.mv.db in your home directory. Then you can access the h2 db from any machine. Below is the java code to get h2 db server connection object.
    java.sql.Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");

3.3 Run H2 DB In Mixed Mode.

In h2 db mixed mode, the first application that access h2 db will use embedded mode, it will start the h2 db server in daemon thread at same time, then later application will access it in server mode.

To implement mixed mode, add AUTO_SERVER=TRUE argument at the end of embedded mode jdbc url string. You can specify the server port number with argument AUTO_SERVER_PORT=9090.

jdbc:h2:~/test;AUTO_SERVER=TRUE

So when you use above jdbc connection url to start a h2 db in embedded mode, later application can also use same url to access the h2 db in server mode.

When mixed mode h2 db start, it will create both test.mv.db (save db data) and test.lock.db (save server mode db ip and random port) file.

For more info of h2 db mixed mode, please refer h2 db auto mixed mode

4. Manage H2 DB Table.

When you login to h2 db manager GUI web page, you can see picture like below. There are some useful sample sql script links in bottom right area. Click the link will add sample sql command in the execution area. You can use them to create your own sql command to manage db table.

h2 database web gui table management sample sql script

(Visited 28 times, 4 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.