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>2.1.214</version> </dependency>
The latest version of the H2 database can be downloaded from Maven Central.
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:
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.7.1</version> </dependency>
You can find the latest version of HSQLDB on Maven Central.
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:
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.
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:
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.
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.
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.