Java in-memory databases

Java in-memory databases
In-memory databases are those which are bundled along with the application. These are created from within the application and are destroyed once the application terminates.
These databases need not be installed on the machine, they reside in the system memory.
In-memory db are dependent on application memory(or RAM) rather than hard disk space. These are also called embedded databases.
All the java in-memory databases are available in the form of jar files that are added to the class path.

In-memory database benefits
Following are the benefits of using in-memory databases
1. Since these are placed in-memory, data access and persistence is faster.
2. These are embedded inside the application, there is no external dependency for any software being present on the system.
3. There is no issue of network connectivity as the database and the application are present on the same system.

In this article, we will take a look at some of the most common in-memory databases that work with java applications along with the required configuration such as driver class, url format etc.
1. HSQLDB
HSQLDB stands for Hyper SQL DataBase, which is written in java. It is an embedded database which means it can be bundled along with a java application.
HSQLDB is a relational database similar to MySQL. It can run as a complete in-memory database or it may also write data to a disk file and read that disk file at startup to populate tables.
HSQLDB supports both server and embedded mode. Server mode means that the database can also run external to the application.

Following are some important features of HSQLDB
1. Relational DBMS.
2. Supports all core features of SQL.
3. Multithreaded support.
4. Support for stored procedures, functions, transactions, views, joins, LOB data type.
5. Latest version supports Java 8 classes in java.time package.
HSQLDB Configuration
To provide HSQLDB support to the application, add the following dependency to your Maven pom.xml file

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

or build.gradle, if you use Gradle

runtime group: 'org.hsqldb', name: 'hsqldb', version: '2.6.0'

or you can also directly download its jar and add it to classpath.

Following are the parameters required to create database connection from java application

Driver class: org.hsqldb.jdbc.JDBCDriver
Connection URL format: jdbc:hsqldb:hsql://ip:port/dbName
User name: sa
Password:

Spring boot with HSQLDB
Spring boot provides support for HSQLDB. Following steps need to be followed:
1. Add the above HSQL dependency in the pom.xml or build.gradle file of your Spring boot project and it will automatically download and add HSQL dependencies to the classpath.
2. Provide following properties in the application.properties file of your project.

spring.datasource.url = jdbc:hsqldb:mem:db-name;DB_CLOSE_DELAY=-1
spring.datasource.username = sa
spring.datasource.password =
spring.datasource.driverClassName = org.hsqldb.jdbc.JDBCDriver
spring.jpa.hibernate.ddl-auto = update

URL will work only if you are using HSQL db in embedded mode. For server mode, use the URL format given above.

2. H2
H2 is also very popular database written in java. It is open source, relational database and can be used in embedded or server modes.
You can choose to create in-memory tables or on your hard disk so that they are not destroyed once the application exits.

Configuration
To use H2 database, add following dependencies in the pom.xml file for Maven,

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

build.gradle in Gradle

runtime group: 'com.h2database', name: 'h2', version: '1.4.200'

Following are the connection parameters of H2 for a standard JDBC connection.

Driver class: org.h2.Driver
Connection URL format: jdbc:h2:~/db-name
User name: sa
Password:

Spring boot with H2
Spring boot provides support for H2 database. For this,
1. Add above H2 dependency in the build file and spring boot will automatically download it and add to classpath.
2. Add below properties in the application.properties file for datasource configuration

spring.datasource.url=jdbc:h2:mem:db-name
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
springspring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

This will by default configure an in-memory H2 database, which will be destroyed upon application exit.
If you want to use file based database, then change the url to

spring.datasource.url=jdbc:h2:file:db-file-path

3. SQLite
SQLite is written in C but can be used with java based applications. It is the largest used and popular embedded database in the world.
SQLite does not have any server process, it runs only in embedded mode.
Most mobile applications, browsers, embedded systems use SQLLite to store their data due to its below features
1. It is light weight.
2. It is a relational database(RDBMS).
3. Provides text search functionality in documents.

SQLite reads from and writes to disk files and it stores everything in a file.
Everything covers database tables, views, triggers, indices etc.

Configuration
Add below dependencies in pom.xml for your Maven project,

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

or build.gradle file for Gradle project

implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.34.0'

Following are the connection parameters to create JDBC connection from a java application.

Driver class: org.sqlite.JDBC
Connection URL format: jdbc:sqlite:db-file-path

Usually, db file ends with .db extension.
Spring boot with SQLite
Spring boot does not directly support SQLite. You have to create a datasource yourself as opposed to HSQL or H2 where it automatically created.
For using SQLite with Spring boot, add following properties to application.properties file

spring.datasource.url = jdbc:sqlite:db-file-path
spring.datasource.driver-class-name = org.sqlite.JDBC
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLiteDialect
spring.datasource.username = sa
spring.datasource.password =

Note that hibernate dialect for SQLite was added in Hibernate v 5.1.o. Prior to this version, you need to create it yourself.

Also, do not forget to add SQLite dependency in the build file of your Spring boot application.

Apache Derby
Derby is also an open source embedded database written in java. Apache Derby can read and write data to files which is platform independent.
This means that these files can be moved from one system to another and they will work.
Derby provides an embedded database driver as well and its size is just 3.5 MB approx.

Derby Configuration
Add the below dependency to your application’s pom.xml or build.gradle as per your application’s build tool.
This will download derby dependencies and add them to classpath.

Maven

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.15.2.0</version>
    <scope>test</scope>
</dependency>

Gradle

runtime group: 'org.apache.derby', name: 'derby', version: '10.15.2.0'

Below are the connection parameters for Derby database from a Java application using JDBC

Driver class: org.apache.derby.jdbc.EmbeddedDriver
Connection URL format: "jdbc:derby:" + db-name + ";create=true"

If you want to connect with a user name, then append it to the connection string as

jdbc:derby:" + db-name + ";create=true;user="+user-name

Spring boot with Derby
To use Derby as an embedded database with a Spring boot application:
1. Add above Derby dependency to the build file to download dependencies.
2. Write following configuration properties to the application.properties file

spring.datasource.url=jdbc:derby:memory:local;create=true
spring.datasource.username=user-name
spring.datasource.password=db-password

This will set up an in-memory database which will be created when application starts and destroyed when application terminates.
If you want to persist data even after the application terminates, then it needs to be written to a file. In that case, modify the datasource URL as below

spring.datasource.url=jdbc:derby:directory:folderName;create=true

This will create a folder with the given name in current working directory.
Hope the article was useful.