I usually post about Persistence on Twitter - you can follow me there:

1. Overview

In-memory databases rely on system memory as opposed to disk space for storage of data. Because memory access is faster than disk access, these databases are naturally faster.

Of course, we can only use an in-memory database in applications and scenarios where data doesn’t need to be persisted or for the purpose of executing tests faster. They’re often run as embedded databases, which means they are created when a process starts and discarded when the process ends which is super comfortable for testing because you do not need to setup an external database.

In the following sections, we will take a look at some of the most commonly used in-memory databases for the Java environment and the configuration necessary for each of them.

2. H2 Database

H2 is an open source database written in Java that supports standard SQL for both embedded and standalone databases. It is very fast and contained within a JAR of only around 1.5 MB.

2.1. Maven Dependency

To use H2 databases in an application, we need to add the following dependency:

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

The latest version of the H2 database can be downloaded from Maven Central.

2.2. Configuration

To connect to an H2 in-memory database, we can use a connection String with the protocol mem, followed by the database name. The driverClassName, URL, username and password properties can be placed in a .properties file to be read by our application:

driverClassName=org.h2.Driver
url=jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1
username=sa
password=sa

These properties ensure the myDb database is created automatically on startup of the application.

By default, when a connection to the database is closed, the database is closed as well. If we want the database to last for as long as the JVM is running, we can specify the property DB_CLOSE_DELAY=-1

If we are using the database with Hibernate, we also need to specify the Hibernate dialect:

hibernate.dialect=org.hibernate.dialect.H2Dialect

H2 database is regularly maintained and provides a more detailed documentation on h2database.com.

3. HSQLDB (HyperSQL Database)

HSQLDB is an open source project, also written in Java, representing a relational database. It follows the SQL and JDBC standards and supports SQL features such as stored procedures and triggers.

It can be used in the in-memory mode, or it can be configured to use disk storage.

3.1. Maven Dependency

To develop an application using HSQLDB, we need the Maven dependency:

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.3.4</version>
</dependency>

You can find the latest version of HSQLDB on Maven Central.

3.2. Configuration

The connection properties we need have the following format:

driverClassName=org.hsqldb.jdbc.JDBCDriver
url=jdbc:hsqldb:mem:myDb
username=sa
password=sa

This ensures that the database will be created automatically on startup, reside in memory for the duration of the application, and be deleted when the process ends.

The Hibernate dialect property for HSQLDB is:

hibernate.dialect=org.hibernate.dialect.HSQLDialect

The JAR file also contains a Database Manager with a GUI. More information can be found on the hsqldb.org website.

4. Apache Derby Database

Apache Derby is another open source project containing a relational database management system created by the Apache Software Foundation.

Derby is based on SQL and JDBC standards and is mainly used as an embedded database, but it can also be run in client-server mode by using the Derby Network Server framework.

4.1. Maven Dependency

To use a Derby database in an application, we need to add the following Maven dependency:

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.13.1.1</version>
</dependency>

The latest version of Derby database can be found on Maven Central.

4.2. Configuration

The connection string uses the memory protocol:

driverClassName=org.apache.derby.jdbc.EmbeddedDriver
url=jdbc:derby:memory:myDb;create=true
username=sa
password=sa

For the database to be created automatically on startup, we have to specify create=true in the connection string. The database is closed and dropped by default on JVM exit.

If using the database with Hibernate, we need to define the dialect:

hibernate.dialect=org.hibernate.dialect.DerbyDialect

You can read more about Derby database at db.apache.org/derby.

5. SQLite Database

SQLite is a SQL database that runs only in embedded mode, either in memory or saved as a file. It is written in the C language but can also be used with Java.

5.1. Maven Dependency

To use an SQLite database, we need to add the JDBC driver JAR:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.16.1</version>
</dependency>

The sqlite-jdbc dependency can be downloaded from Maven Central.

5.2. Configuration

The connection properties use the org.sqlite.JDBC driver class and the memory protocol for the connection string:

driverClassName=org.sqlite.JDBC
url=jdbc:sqlite:memory:myDb
username=sa
password=sa

This will create the myDb database automatically if it does not exist.

Currently, Hibernate does not provide a dialect for SQLite, although it very likely will in the future. If you want to use SQLite with Hibernate, you have to create your HibernateDialect class.

To find out more about SQLite, go to sqlite.org.

6. In-Memory Databases in Spring Boot

Spring Boot makes it especially easy to use an in-memory database – because it can create the configuration automatically for H2, HSQLDB, and Derby.

All we need to do to use a database of one of the three types in Spring Boot is add its dependency to the pom.xml. When the framework encounters the dependency on the classpath, it will configure the database automatically.

7. Conclusion

In this article, we had a quick look at the most commonly used in-memory databases in the Java ecosystem, along with their basic configurations. Although they are useful for testing, keep in mind that in many cases they do not provide exactly the same functionality as the original standalone ones.

You can find the code examples used in this article over on Github.

I usually post about Persistence on Twitter - you can follow me there:


Sort by:   newest | oldest | most voted
Chandra Mani Gupta
Guest

Great…!!

Luis Trigueiros
Guest

Hi,
The article is very interesting.
Do you know which ones of these support:
1 – full text search,
2 – JSON as a built in data type, like Postgres
3 – Has geo functions, like distance between 2 points, or geo search.

Thank you.

Grzegorz Piwowarek
Guest

1, H2, HSQLDB or Apache Lucene will work fine for this.
2, I am afraid that none of them. This is why they will not substitute the real thing in complex scenarios
3, http://www.h2gis.org/

Csaba
Guest

SQLite is not RDBMS, I wouldn’t recommend (except for Android dev).

Derby is also way beyond H2 or HSQL. Just always choose H2, that was created by the same dev as HSQL, but it is better built (faster also).

Csaba
Guest

For full text index, use Lucene, that is compatible with both RDBMS.

wpDiscuz