Requirements

The only requirement is that you must have Java JRE installed on your system, and PATH variable updated so that “java” command is available from the command line.

Install

Download the “Platform-Independent Zip” file from the official H2 website https://h2database.com/html/download.html and unzip the file contents into a directory of your choosing.

Using the Graphical SQL Client

Open a command line terminal and “cd” into the bin directory inside the newly extracted h2 directory.

cd c:\path_to_your_h2_directory\bin

For reference, the bin directory should include items similar to the below. Once inside the directory, execute the command below.

java -jar h2-<VERSION HERE>.jar

In my case, the version is 1.4.199, so my command is:

java -jar h2-1.4.199.jar

On launch, you will be greeted with the webpage below.  If not, go into http://192.168.1.112:8082 on your browser.  

This will allow you to create your stand-alone database file.  By default, the file’s name is “test” and is placed in your home directory as specified in the “JDBC URL”.  Change the database name as needed and click “Connect” to create it. Once connected, the SQL client will display.  Type any SQL command here and click on the Run button.  You can also use Ctrl+Enter to execute, which can be quite useful.

Convenient Edits

One really nice feature is the ability to edit and create records from the GUI.  Simply execute a SELECT statement on your table and you should see an”Edit” button appear.  Click on that and you will get options to create and edit fields in the table.

Using Command Line SQL Client

The installation also comes with a command line SQL client, similar to sqlplus, etc.

Open a command line terminal and “cd” into the bin directory inside the newly extracted h2 directory.

cd c:\path_to_your_h2_directory\bin

For reference, the bin directory should include items similar to the below. Once inside the directory, execute the command below.

java -cp h2-<VERSION HERE>.jar org.h2.tools.Shell

For my version this then becomes:

java -cp h2-1.4.199.jar org.h2.tools.Shell

If you left all defaults on the database creation screen for the GUI option (~/test location), then you can hit enter for each of the parameters requested.  Otherwise, update the URL as needed to include the path to your database file.

Welcome to H2 Shell 1.4.199 (2019-03-13)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL
[Enter]   org.h2.Driver
Driver
[Enter]   sa
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>

You can now type your commands.  Make sure to terminate the command with a semicolon.

sql> SELECT * FROM people;
ID | NAME
1  | Developer
(1 row, 22 ms)
sql>

One useful command is “show tables”, which lists all available tables in the database.

sql> show tables;
TABLE_NAME | TABLE_SCHEMA
PEOPLE     | PUBLIC
(1 row, 25 ms)
sql>

Super Easy CSV File Upload

One last item I’d like to mention is just how easy the database makes uploading CSV files into a table.  This can be done with the the “CSVREAD” function.

For my example, I will use a pipe delimited file named “people.txt” with the contents below.

id|name
2|Jane
3|Joe
4|Bob

We can then import the file into a staging table using the following.

    CREATE TABLE people_staging
    AS
    SELECT * FROM CSVREAD('/Users/DeveloperSoapbox/Documents/people.txt', null, 'charset=UTF-8 fieldSeparator=|');

The example above has a header in the file, but if not I could have easily specified the header, encoding, and delimiter as follows.

    SELECT * FROM CSVREAD('/Users/DeveloperSoapbox/Documents/people.txt', 'id|name', 'charset=UTF-8 fieldSeparator=|');

If we wanted to then insert the staging records into our final “people” table, we could use:

    INSERT INTO people SELECT * FROM people_staging

Summary

The H2 database is by far my favorite “single file” database.  It is equivalent to SQLite, but much more accessible if you are a Java developer.  I can honestly say that I’ve never had such a seamless experience with built in tools as I have with H2.  I highly recommend it for smaller projects where a full database server is overkill.