Use H2 Database to connect to any database

H2 database is a powerful Java SQL database that is very fast, implements JDBC API, provides an html console, and has a very small footprint, around 2MB.

Because of its size and the SQL compatibility that the H2 database provides, it can be used as an embedded database instead of HSQLDB or other, and of course in the test suite of an application instead of using the DB used in production (for example MySQL, Postgres etc)

The H2 database can connect to any remote or local DB that supports JDBC for checking DB connection, checking or modify data etc. The html console is a very handy tool for all those cases.

Setup JDBC connection

We start by downloading the h2 standalone archive from https://h2database.com/html/main.html

Next we need to provide the JDBC driver for the DB server we want to connect.

For example, for MS SQL DB server, proceed to https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017 and download the version of JDBC driver you need for the SQL Server.

Create a folder drivers inside /h2 and place the download JDBC jar file there.

Now we need to tell H2 to load this JDBC jar in the classpath and make it available so we need to define the H2DRIVERS system variable.

export H2DRIVERS=/home/user/h2/drivers/mssql-jdbc-6.1.0.jre8.jar

You might want to have more JDBC jars in the classpath, for MySQL for example, in that case the H2DRIVERS will have to include all of them like this

export H2DRIVERS=/home/user/h2/drivers/mssql-jdbc-6.1.0.jre8.jar:/home/user/h2/drivers/mysql-connector-java-8.0.16.jar

Make sure you change the folder location from /home/user/h2 to the actual location in your machine.

The h2 folder will look like this

Next we can start h2. Head to bin folder and use the startup script for your OS (Windows → /bat or for Linux/Mac → .sh)

When the h2 start, the console will start at the console and now we can setup the connection details.

First thing is to pick the proper DB server from the dropdown menu, for MS SQL DB you need to pick Generic MS SQL Server 2005. By setting the server, the Driver class will be set for you automatically.

The only thing left for you to provide are:

• JDBC URL, for example jdbc:sqlserver:/SQLSERVER.company.com\INSTANCEID;databaseName=MyDB

• Username

• Password

The Login screen will look like this:

Now press Connect and you are in!!!

Bonus, allow web connections from remote hosts

By default H2 database does not allow connections from other machines when starting the H2 Console. So if you deploy and start H2 on a remote server, lets say in a dev environment, you won't be able to reach the H2 Console unless you instruct H2 to allow connections from remote hosts.

Remote access can be enabled using the command line options -webAllowOthers

To use this option run H2 like this

./h2.sh -webAllowOthers

This will allow web connection to H2 Console from remote hosts.

You can check more details about remote access here https://h2database.com/html/advanced.html?highlight=webAllowOthers&search=webAllowOth#remote_access