Java DB as an In-Memory Database

Jun 15, 2013

This post is about using Java DB as an in-memory database.

1. Java DB

Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation's open source Derby project. Java DB is included in the Java SE 7 SDK.

A Java DB database can be used as an in-memory database.

2. Java DB In-Memory

An in-memory database (IMDB) resides completely in the main memory of a computer. It does not use the file system.

An in-memory database is useful:

for testing applications
during application development
for processing temporary (transient) data
for faster data processing, as there is no disk I/O
for the simplicity, of not requiring to deleting the database when done using it

3. Create, Use, Remove and Persist an In-Memory Java DB Database

3.1. Creating:

Create an in-memory database using a database connection URL. The URL syntax is jdbc:derby:subsubprotocol:databaseName[;attribute=value]*. For the in-memory option, the subsubprotocol value is memory. This is required.

The following Java code creates an in-memory database with the name testDB. The Connection object is used to create database objects and data. The code uses JDBC API and the Java DB database is created in Embedded mode.

	String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	String connectionURL = "jdbc:derby:memory:testDB;create=true";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(connectionURL);

3.2. Using:

Create a database table and insert a data row, using the connection object.

	String DDL = "CREATE TABLE test_table (" +
			"id INT NOT NULL, " +
			"name VARCHAR(20) NOT NULL, " +
			"PRIMARY KEY (id))";
	Statement stmnt = conn.createStatement();
	stmnt.executeUpdate(DDL);
	String DML = "INSERT INTO test_table VALUES (1, 'test data')";
	stmnt = conn.createStatement();
	stmnt.executeUpdate(DML);

3.3. Removing:

Remove (drop) the database using a connection URL with the drop attribute.

First, shutdown the database; this step is optional. If used, specify the URL attribute with key/value shutdown=true.

Next, drop the database; this also shuts down the database. The URL attribute key/value drop=true is required.

	String connectionURL = "jdbc:derby:memory:testDB;drop=true";
	DriverManager.getConnection(connectionURL);

Exiting the JVM (exiting a Java program or 'ij') also drops the in-memory database.

3.4. Persisting:

Java DB in-memory database can be persisted to file system, if required. A copy of the database is created using the Java DB online backup system stored procedure SYSCS_UTIL.SYSCS_BACKUP_DATABASE. The backup copy is later restored as an in-memory, or a regular file system database.

The following are the restore connection URLs example for the in-memory and the file system options, respectively:

	jdbc:derby:memory:testDB;restoreFrom=backupDbPath;
	jdbc:derby:testDB;restoreFrom=backupDbPath;

Note that, to use the copy as a file system database, the URL jdbc:derby:backupDbPath, can also be used without the restoreFrom attribute.

4. Notes

The in-memory database can be used interactively using the Java DB ij tool, as with a file system database. Note that the connection URL requires the subsubprotocol value (i.e., memory).
The Java DB in-memory database can also be used with the Network Server mode. The example code shown above uses the Embedded driver and connection URL.

5. References

Apache Derby > Documentation (10.8 Manuals > Derby Reference Manual).
Oracle's Java DB.

Return to top