How to Set Up an H2 Database

H2 is a database management system that is entirely implemented in Java and open source.

Its source code is available for viewing on GitHub (https://github.com/h2database/h2database), allowing you to follow its development. The system is licensed under Mozilla Public License Version 2.0 (MPL 2.0) and Eclipse Public License 1.0 (EPL 1.0).

The H2 database management system supports three different modes:

• Server mode: The system runs all day, waits for incoming connections, handles them, and continues to run endlessly. This mode is common for “big” RDBMSs.
• Embedded mode: The system is part of the application, and when the application starts up, H2 starts with a database. The application can read and write the database. If the application shuts down, the embedded database management system is also terminated.
• In-memory mode: In this special form of embedded mode, the database resides exclusively in memory, and H2 doesn’t persist any data. Data can be read and written, but everything takes place in main memory. When the program is terminated, all data is lost.

This flexibility makes H2 a versatile option for different use cases, from big data applications to small in-memory databases for testing and development purposes.

Note: Occasionally, in-memory databases are used for tests, but this is only useful if the productive system also uses the same database management system, such as H2. Otherwise, there can be significant discrepancies in SQL syntax and database capabilities when compared to other databases. To avoid these issues, there is an alternative solution called Project Testcontainer. This approach provides a viable option for testing by creating lightweight, isolated Open Container Initiative (OCI) containers that can be used for database testing.

Install and Launch H2

The next step is to install and start the H2 database locally on the computer. The H2 Database Engine web page (http://h2database.com) shows the different download options.

There are several ways to get H2 running: via a Windows Installer, as a ZIP archive for All Platforms, or by downloading the old versions under All Downloads.

Click on All Platforms, download the 10 mebibyte (MiB) ZIP archive, and extract its contents. This will create a directory named h2 with the following files:

• bin
• docs
• src
• bat
• iml
• sh

The bin subdirectory contains a runnable Java archive and batch files for Windows and Unix systems:

• h2-2.2.222.jar
• bat
• sh
• bat

Those on the graphical interface can try double-clicking on the JAR file. That should start H2, provided the .jar file extension has the Java virtual machine (JVM) associated with it. Alternatively, it can be started from the shell files or from the command line with java -jar h2-2.2.222.jar (on Windows also javaw -jar h2-2.2.222.jar).

Note: To run the examples, the H2 database server must always be running. However, after each system reboot, H2 needs to be manually restarted. To simplify this process, H2 can be set up as a service to automatically start the RDBMS upon system restart. This ensures that the H2 database is always available for use without requiring manual intervention.

When H2 starts, a GUI opens automatically in the web browser.

Troubleshooting Tips: If the web interface shows an error, the first suggested solution is to use the URL http://localhost:8082/. Because H2 uses port 8082 by default for the web interface, but the port may already be occupied by another application, H2 can use a different port. To archive this, modify the .h2.server.properties file in the user directory (or create a new one), and enter the following, for example: “webPort=8888”.

Create a New Database via the GUI

The H2 Console is a small admin interface so that you can connect to the database. However, we don’t have a database yet, so we need to create one because it’s not created on first access. This can be done via the GUI or via the shell.

In Windows operating systems, there is a system tray located at the bottom right of the screen. This tray contains various icons, some of which may be hidden. When H2 is running, a small yellow barrel icon can be seen in the system tray. For macOS users, the H2 barrel icon can be found in the upper area of the screen. Keeping an eye on this icon is useful for quickly checking the status of H2 and ensuring that it’s running properly.

Tip: Once you’ve closed the H2 Console, clicking on the barrel takes you to a new tab with the web interface.

An activation of the context menu on the barrel shows four options (see previous figure). We need one of them now because we need to create a new database, so we click on Create a new database. A new dialog follows, as shown here.

Using the dialog, you can create a new database on the file system. However, when performing a new installation, it’s not recommended to use the default database path ./test. This is because using this path would create a new database with the name test in the directory where H2 was started. This can cause confusion and potential issues, so it’s recommended to choose a more specific and appropriate path for the new database.

Enter the following in the text fields of the dialog:

• Database path: “~/unicorns”
• Username: “user
• Password: “pass”
• Password confirmation: “pass”

To create the database in the user directory, enter “~/unicorns” in the dialog box. You can select any username and password you like, but for simplicity, let’s keep them easy to remember. Click on Create to create the database. Once the database is created, a green message will appear in the lower text field. The actual database file, unicorns. mv.db, will be located in the user directory.

Create a New Database via the Shell *

A database can also be created via the command line:

$ java -cp h2-2.2.222.jar org.h2.tools.Shell

Welcome to H2 Shell 2.2.222 (2023-08-22)

Exit with Ctrl+C

[Enter]   jdbc:h2:~/test

URL       jdbc:h2:~/unicorns

[Enter]   org.h2.Driver

Driver

[Enter]

User      user

Password

Connected

Commands are case insensitive; SQL statements end with ';'

help or ?       Display this help

list            Toggle result list / stack trace mode

maxwidth        Set maximum column width (default is 100)

autocommit      Enable or disable autocommit

history         Show the last 20 statements

quit or exit    Close the connection and exit

sql> exit

Connection closed

The program is an interactive shell that can be used to create databases. SQL statements can also be executed via the shell.

Connect to the Database via the H2 Console

After the start, the H2 Console is displayed, and a connection to the new database can be established. (By the way, the H2 Console can connect to different databases, not just H2.)

We entered the following:

• JDBC URL: “jdbc:h2:tcp://localhost/~/unicorns”
• Username: “user”
• Password: “pass”

Clicking Connect will take us to a new page where tables can be viewed and SQL statements can be issued.

Date4u Database Schema

For the database to store information from the Date4u application, the four tables from this figure are used.

The four tables have the following properties:

• Profiles: At the center is the Profiles table, which stores the profile of a unicorn. Each profile has an id that is automatically generated using an identity column. The profile of the unicorn has a birthdate that must be given: it’s NOT NULL. The unicorn has a nickname, which is limited to 32 characters. It must be UNIQUE and must also be NOT NULL. The mane length (manelength) is of type SMALLINT and must be NOT NULL. The gender must also be NOT NULL. attracted_to_gender stores which gender the unicorn is attracted to; it’s a TINYINT and can be NULL to express that the unicorn is interested in any other gender. Each profile has a description with a maximum of 2,048 characters. The lastseen column stores when the unicorn was last seen as a date-time stamp, and this is also NOT NULL.
• Unicorn: Each unicorn is represented in the Unicorn table. Each unicorn has an id (which in turn is an identity column), an email address, a (hashed) password, and a reference to the corresponding profile in profile_fk. The direction of the 1:1 relationship is didactically motivated; of course, the profile can also reference the unicorn. This would have the advantage that unicorns can also be administrators.
• Photo: A profile can reference any number of photos in a 1:n relationship. Each photo has an id (again, an identity column). There is a reference to the corresponding profile via a foreign key relationship in the profile_fk column. Each photo has a name. Later, the PhotoService can load an image from the file system for the image name. Whether a photo is a profile photo is determined by is_profile_photo, whereby the software must ensure that a profile photo can only be set once per profile. The timestamp created indicates when the profile photo was uploaded. All columns must be NOT NULL.
• Likes: Join table Likes implements an n:m relationship between the profiles that like each other. The column type is BIGINT and NOT NULL. The relationship is directed.

Fill the Date4u Demo Database with Data

The database with demo data can be obtained from https://tinyurl.com/4fu3hwu4 as an SQL script.

The SQL statements can be placed in the text field of the H2 Console and sent by clicking Execute. This way, the database is filled.

Subsequently, four new tables appear on the left side (see next figure). A click on Clear clears the input field. If you click on one of the tables on the left, a SELECT statement is automatically generated, which Execute processes again.

By the way, it may well be that the table contents look a bit different by now.

Note: Naming Convention: To improve readability and distinguish SQL statements, a convention is often used where SQL keywords such as SELECT, FROM, and WHERE are written in all capital letters. Tables, on the other hand, are written with the first letter capitalized, such as Profiles, while columns are written in lowercase, such as id. This convention not only helps distinguish different parts of a SQL statement but also makes the code easier to read and understand for other developers who may work on the same project.

Editor’s note: This post has been adapted from a section of the book Spring Boot 3 and Spring Framework 6 by Christian Ullenboom.