H2 Database Engine

What is H2 Database Engine

Introduction

The H2 Database Engine, H2 short for "Hypersonic 2", is a open-source relational database management system written in the Java programming language, supporting standard SQL API and JDBC API, as well as PostgreSQL ODBC driver. H2 is characterized by its fast performance and low memory requirement. H2 also has strong security features. The system can either be embedded directly in Java applications as a JAR or operated as a server. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk.

Because of embedded database it is not used for production development, but mostly used for development and testing.

History

H2 was first published on December 14th 2005. Thomas Mueller, the original author of H2, is also the original developer of Hypersonic SQL. He joined PointBase Inc in 2001, where he wrote PointBase Micro, a commercial Java SQL database. He had to discontinue to work on Hypersonic SQL at that point. Therefor the HSQLDB Group was formed to continued to work on the Hypersonic SQL codebase. The name H2 stands for Hypersonic 2, but was built from scratch and does not share code with Hypersonic SQL or HSQLDB.

Features

Main features

• Very fast, open source, JDBC API
• Embedded and server modes, in-memory databases
• ODBC driver
• Two full text search implementations are included, a native implementation and one using Lucene
• Multi version concurrency
• read-only database support, temporary tables
• Cost based optimizer, using a genetic algorithm for complex queries, zero-administration
• Scrollable and updatable result set support, large result set, external result sorting, functions can return a result set
• Browser based Console application
• Small footprint: around 2 MB jar file size, low memory requirements

Security

H2 is NOT designed to be run in an adversarial environment. You should absolutely not expose your H2 server to untrusted connections.
Running H2 in embedded mode is the best choice - it is not externally exposed.

• Role based access rights
• encryption of the password using SHA-256 and data using the Advanced Encryption Standard (AES) or the Tiny Encryption Algorithm XTEA.
  • The cryptographic features are available as functions inside the database as well.
• SSL / TLS connections are supported in the client-server mode, as well as when using the console application.
• The database supports protection against SQL injection by enforcing the use of parameterized statements.
  • In H2, this feature is called 'disabling literals'.
• For server mode connections, user passwords are never transmitted in plain text over the network (even when using insecure connections).
  • This only applies to the TCP server and not to the H2 Console however
• All database files (including script files that can be used to backup data) can be encrypted using the AES-128 encryption algorithm
• The remote JDBC driver supports TCP/IP connections over TLS
• The built-in web server supports connections over TLS
• Passwords can be sent to the database using char arrays instead of Strings

SQL-Support

A subset of the SQL (Structured Query Language) standard is supported.

• The main programming APIs are SQL and JDBC, however the database also supports using the PostgreSQL ODBC driver by acting like a PostgreSQL server
• Support for multiple schemas, information schema
• Referential integrity / foreign key constraints with cascade, check constraints
• Inner and outer joins, subqueries, read only views and inline views
• Triggers and Java functions / stored procedures
• read-only database support and temporary tables
• Isolation:
  • read uncommitted
  • read committed
  • repeatable read
  • snapshot
  • serializable (partially)
• 2-phase-commit, multiple connections, table level locking
• Many built-in functions, including XML and lossless data compression
• Wide range of data types including large objects (BLOB/CLOB) and arrays
• Sequence and autoincrement columns, computed columns (can be used for function based indexes)
• ORDER BY, GROUP BY, HAVING, UNION, OFFSET / FETCH (including PERCENT and WITH TIES), LIMIT, TOP, DISTINCT / DISTINCT ON (…)
• Support for users and roles
• Compatibility modes for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, MySQL, Oracle, and PostgreSQL

Other Features

• multi-dimensional indexes
• CSV file support
• linked tables, and a built-in virtual 'range ' table
• EXPLAIN PLAN statement
  • EXPLAIN command displays the execution plan for a statement.
  • When we execute a statement using EXPLAIN ANALYZE command, the query plan will include the actual row scan count for each table.
• Database closing can be delayed or disabled to improve the performance
  • e.g. jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
• Web-based Console application (translated to many languages) with autocomplete
• The database can generate SQL script files
• Contains a recovery tool that can dump the contents of the database
• Support for variables (for example to calculate running totals)
• Automatic re-compilation of prepared statements
• Uses a checksum for each record and log entry for data integrity
• Well tested (high code coverage, randomized stress tests)

Connection Modes

Embedded Mode

In embedded mode, the database is opened by an application from within the same JVM using JDBC. It is the fastest and easiest connection mode. The downside is that a database can only be open in one virtual machine (and class loader) at any time. As in all modes, both persistent and in-memory databases are supported. There is no limitation on the number of databases open concurrently, or on the number of open connections.

In embedded mode I/O operations can be performed by application's threads that execute a SQL command. The application may not interrupt these threads, it can lead to database corruption, because JVM closes I/O handle during thread interruption. Consider other ways to control execution of your application. When interrupts are possible the async file system can be used as a workaround, but full safety is not guaranteed. It's recommended to use the client-server model instead, the client side may interrupt own threads.

For in-memory database, if you want a constant DB-conntection, you have to close every other connection, because H2 cant run two connection to the same DB locally.

Server Mode

When using the server mode (also called client/server mode or remote mode), a database is opened remotely using the JDBC or ODBC API. A server needs to be started within the same or another virtual machine, or on another PC. Many applications can connect to the same database at the same time, by connecting to this server. Internally, the server process opens the database(s) in embedded mode.

Because all data is transferred over TCP/IP, the server mode is slower than the embedded mode.

Mixed Mode

A combination of the embedded and the server mode is called the mixed mode. The first application that connects to a database does that in embedded mode, but also starts a server so that other applications (running in different processes or virtual machines), so the same data can be accessed concurrently. while the remote connections are a bit slower, the local connections are as fast as if the database is used in just the embedded mode.

The server can be started and stopped from within the application (using the server API), or automatically (automatic mixed mode). If you are using the automatic mixed mode, all clients that want to connect to the database (no matter if it's an local or remote connection) can do so using the exact same database URL.

Installation

Supported Platforms

As this database is written in Java, it can run on many different platforms. It is tested with Java 8 and 11. All major operating systems (Windows, Mac OS X, Linux, ...) are supported.

Download

Download currtent version from H2 Download.

Installation

IMPORTANT: MAKE SURE TO HAVE JDK (latest version) INSTALLED

Step 1: set the path you want to install the H2 database in. Click "next" to proceed

Step 2: in the screenshot above click install to start the installation

Step 3: on the screenshot above click finish to finish the installation

Step 4: now, to start the database, run H2 Console or go to the installation folder (C:\Program Files (x86)\H2\bin) and run "h2-2.1.210.jar". H2 is now running.

Step 5: to access the database, if it not opens automatically, conntect to "http://localhost:8082" trought your browser.

Step 6: if everything went fine, you will see the page in the screenshot.

Step 7: fill in the details according to your database. The data in the screenshot are the default values of an embedded database.

Step 8: click on connect to establish the connection

Step 9: you are now logged in and have access to your tables

Build with Maven (dependency)

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.210</version>
</dependency>

Use

Create new database

Step 1: right-click on tray-icon

Step 2: fill in the details of your new DB and click "create"

Step 3: the browser opens the login-windows automatically

How to use SQL

Because H2 is supporting standard SQL, you can use all standard SQL-Statments:

If you want to know more, visit the H2 Homepage.

Carefully prepared by Pavel Dziakovitch / 2022