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 the java application.

The biggest advantage of using the 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 database 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 that 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 modes.

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

1.1 Embedded Mode.

1. In the Embedded mode, the database performance is better. Because application and database are executed in the same JVM, the java application uses 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.

1. In the Server mode, the java application and H2 database run in different JVM or machines. H2 database is executed in an H2 server. Server mode supports multiple DB connections.

1.3 Mixed Mode.

1. Mixed mode is a combination of embedded mode and server mode.
2. The first application that connects to the h2 DB server starts h2 DB in embedded mode, it also starts the h2 DB server.
3. Then the later application can access h2 DB in server mode. All the applications can access the same data at the same time.

2. H2 Database Download & Install.

1. Go to the h2 DB download page to download the newest version. I recommend you download the platform-independent zip file which can run in Windows, Linux, and macOS.
2. Unzip the downloaded zip file to a local folder, and cd into the h2/bin folder, you can see the below files.

   tree ./
   ./
   ├── bin
   │   ├── h2-1.4.200.jar
   │   ├── h2.bat
   │   ├── h2.sh
   │   └── h2w.bat

3. h2.sh — start h2 DB script file for Linux or macOS.
4. h2.bat — start h2 DB script file for Windows.
5. h2w.bat — start h2 DB in windows that do not show dos window.
6. h2-1.4.200.jar — h2 db jar file which contains jdbc driver class.

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

1. Now, h2 DB has been installed successfully, we will start it in different modes.
2. Run the file bin / h2.sh, it will start a console server that can manage h2 DB in Embedded or Server mode.
   
3. The console server is accessed from a web browser. And the console server can manage all database systems, such as MySQL, Oracle, etc. But first, you need to contain the related DB JDBC jar files 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.

1. It is so easy to use h2 DB in Embedded mode in your java application. Please follow the below steps.
2. Add bin / h2-1.4.197.jar file in your application java classpath. The jar file does not depend on other jars.
3. Use org.h2.Driver as h2 db jdbc driver class.
4. 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 home directory.
5. In the above example, when the java application runs, the DB user_account will be created automatically. All the DB data will be saved in the file user_account.mv.db. And it is saved in the user home directory.
6. When you close the last h2 in-memory DB connection, the database will be closed also by default, this will lead to data loss. If you want to make the memory DB persist longer after JVM exit, you should add the 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 a java app running in other JVM later. For example jdbc:h2:~/user_account;DB_CLOSE_ON_EXIT=FALSE.
7. Below is the java code that creates h2 embedded DB and returns 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.

1. You can also use the h2 console server to start an embedded h2 DB.
2. Run bin / h2.sh in a terminal, it will open the console server web GUI in a web browser.
3. Then select Generic H2 (Embedded) in the Saved Settings drop-down list.
4. It will display the driver class and JDBC URL in the related fields below the drop-down list. 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 "$@"

2. Open a terminal and execute the command ./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 a web browser.
6. -webPort 8082: h2 console server listening port number, the default value is 8082.
7. Then you should use the below JDBC connection URL to access the h2 DB server.

   java.sql.Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");

8. The above code will create test.mv.db file in localhost machine user home directory. You can also specify another directory path.

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.
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 the 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.

1. 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 the same time, then later application will access it in server mode.
2. 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 the argument AUTO_SERVER_PORT=9090.

   jdbc:h2:~/test;AUTO_SERVER=TRUE

3. So when you use the above JDBC connection URL to start an h2 DB in embedded mode, later applications can also use the same URL to access the h2 DB in server mode.
4. When the mixed-mode h2 DB starts, 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 on h2 DB mixed mode, please refer to h2 DB auto mixed mode

4. Manage H2 DB Table.

1. When you log in to the h2 DB manager GUI web page, you can find some useful sample SQL script links in the bottom right area.
2. Click the link will add a sample SQL command in the execution area. You can use them to create your own SQL command to manage the DB table.