Apache Derby Notes

Russell Bateman
June 2020

In-memory databases

This appellation is a bit ambiguous. I'm looking not just for something that resides in memory when running, but also resides a) in my same JVM with b) no need to have a separate process, and b) not need to use administration utilities to set up, prepopulate with data, etc.

I needed an in-memory database utility for mocking out Mirth in a test environment. Or, any other database situation. There are many databases that run completely in memory, but, as noted, I do not want a database that requires me to install into the filesystem, configure, run a process/dæmon, etc. I looked at

HSQLDB, formal name is HyperSQL Database, which I used long ago at GWAVA, but it requires explicit installation. Perhaps all of the examples just assumed that I would not mind running it as a server on localhost.
SQLite, which I used many years ago at Quest Software on the Vintela Authentication Services, but from C. No matter, my quick look makes me think that it's like HSQLDB.
H2, which I have never used and know nothing about, but a shallow look-see makes me think that, like HSQLDB and SQLite, it requires explicit installation into the filesystem and a running process. A friend who knows more, upon reviewing these notes, informed me that H2 can do this.
Redis is an in-memory key-value pair database, but not SQL and not in the least relevant to what I'm looking for.

I've pretty well got it working, I just need to package it for my specific need, something I won't show here. Nevertheless, here are the features important to me in a nutshell from which anyone can get it going:

Testcontainers

There is some level of risk in not testing with the same database as in production; Testcontainers alleviates this. It's worth looking into.

If you're reading these notes because looking for a way to test your data-access object layer (DAO), Testcontainers is a way to accomplish some or all of what you might be looking for.

However, Testcontainers creates an instance of the database engine (of your choice) in a Docker container and launches it. Hence, this is very far from unit testing. It would be good integration testings with an automated set-up.

Features, utility notes and examples

Everything you wanted to know, but were afraid to ask!

First, ensure that you have everything set up properly. What you get away with in your IDE (mine's IntelliJ IDEA), you might not from the command line.

Ensure you are using JDK 9 or later and that you've set JAVA_HOME. From ~/.profile:
```
export JAVA_HOME=/home/russ/dev/jdk11.0.2
```

Ensure you are using maven-compiler-plugin and that you set release to 9. (I set it to 8, and it still works, but it's not supposed to. I think the real important thing is probably to have Java 9 or later in the compilation process no matter what language level you cripple Java to be.)

<properties>
  <maven.compiler.plugin.version>3.8.1</maven.compiler.plugin.version>
  .
  .
  .
<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>${maven.compiler.plugin.version}</version>
      <configuration>
        <release>8</release>  <!-- notwithstanding suggested Java 9 requirement -->
      </configuration>
    </plugin>
    .
    .
    .

Apache Derby from Maven...

...requires no installation whatsoever beyond a simple Maven dependency:


  org.apache.derby
  derby
  10.15.2.0
  test

 
  org.apache.derby
  derbyshared
  10.15.2.0
  test

 
  org.apache.derby
  derbytools
  10.15.2.0
  test

(Note: without the second one, you'll run just fine from an IDE, but when your unit tests run at the (Maven) command line, they will fail with something like: java.sql.SQLException: No suitable driver found for jdbc:derby:memory:sampledb;create=true.)

Using Derby in in-memory mode...

...runs completely in memory. This is accomplished by using what's in bold in this URL:

jdbc:derby:memory:database-name;create=true

where database-name is specifiable and created at runtime. The create=true property is mandatory in this case because meaningless without it.

Using Derby in other modes

It is possible to retain what gets done in a Derby database by specifying that the database be created (or already exist) on a specific filesystem path. The path goes all the way down to the Derby database filename:

jdbc:derby:/var/fun/database/filename;create=true

This doesn't interest me here; I haven't done it in many years; I am leaving out discussion of side-effects and other particulars for another time, but I assume that the use of the create=true property would smoke any existing database at that filesystem path and, if you wanted to keep it, you must not specify this property subsequent times you launch your JVM process.

Is it possible to run Derby in a more traditional, database server mode, just as HSQLDB, H2, etc. are run? I have not done that, researched that nor remember stumbling upon notes about how it is done (or even can be done). So, I don't know if this goes anywhere:

jdbc:derby:https://localhost:port/database-name

Derby example and notes

Database creation is automatic...

...just by cranking up the JVM that uses it. See here the creation of a database (automatic), a table (by hand), three rows and a query. These activities go like this:

package com.windofkeltia.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static java.util.Objects.nonNull;

import org.junit.Test;

@SuppressWarnings( { "SqlDialectInspection", "SqlNoDataSourceInspection" } )
public class ApacheDerbyTest
{
  @Test
  public void test()
  {
    final String DATABASE = "jdbc:derby:memory:sampledb;create=true";
    final String USERNAME = "sa";
    final String PASSWORD = "sa";

    final String CREATE_TABLE = "CREATE TABLE names ( oid INT GENERATED ALWAYS AS IDENTITY, name VARCHAR( 20 ) )"; // *see note
    final String INSERT_NAME1 = "INSERT INTO names ( name ) VALUES ( 'Herman Munster' )";
    final String INSERT_NAME2 = "INSERT INTO names ( name ) VALUES ( 'Lily Dracula Munster' )";
    final String INSERT_NAME3 = "INSERT INTO names ( name ) VALUES ( 'Marilyn Munster' )";
    final String QUERY        = "SELECT oid, name FROM names";

    Connection connection = null;

    try
    {
      connection = DriverManager.getConnection( DATABASE, USERNAME, PASSWORD );

      Statement statement = connection.createStatement();
      statement.execute( CREATE_TABLE );
      statement.executeUpdate( INSERT_NAME );
      ResultSet resultSet = statement.executeQuery( QUERY );

      while( resultSet.next() )
        System.out.println( "oid: " + resultSet.getString( "oid" ) + ", name: " + resultSet.getString( "name" ) );

      resultSet.close();
      statement.close();
    }
    catch( Exception e )
    {
      e.printStackTrace();
    }
    finally
    {
      try
      {
        if( nonNull( connection ) )
          connection.close();
      }
      catch( SQLException e )
      {
        e.printStackTrace();
      }
    }
  }
}

This code did not require the use of derbyshared or derbytools.

The output...

...from testing the code above looks like this:

oid: 1, name: Herman Munster
oid: 2, name: Lily Dracula Munster
oid: 3, name: Marilyn Munster

Auto-generated, auto-incrementing oids

^* The syntactic element GENERATED ALWAYS AS IDENTITY appears to be peculiar to Derby. Note that this sort of specification of OID is frequently proprietary and unpredictable between databases. See Autogenerated keys.

Derby errors

This nasty bit appears mysteriously when porting a CREATE TABLE statement from another database. Given that "sa" is the default username and password for Derby (at least, from examples), this is confusing. Ignore this. For instance, any of the schema below...

Bad datatype	Derby-suitable datatype
BIGINT(n)	BIGINT
LONGBLOB	CLOB
TEXT	VARCHAR(n)
PRIMARY KEY (fieldname)	fieldname NOT NULL GENERATED ALWAYS AS IDENTITY

...leads to this something similar to the error generated below:

java.sql.SQLSyntaxErrorException: Schema 'SA' does not exist

	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
	at com.windofkeltia.processor.derby.DerbySchemaTest.testSimplifiedSchema(DerbySchemaTest.java:74)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: ERROR 42Y07: Schema 'SA' does not exist
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(Unknown Source)
	at org.apache.derby.iapi.sql.StatementUtil.getSchemaDescriptor(Unknown Source)
	at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(Unknown Source)
	... 35 more

Useful links

How JDBC drivers work

The latest Derby version, 10.15.2.0, can be made to work, but it does not support "Approach I" below. This is because of the peculiar way Derby has evolved and how JDBC drivers work. Instead, version 10.11.1.1 appears to be the latest driver that supports the canonical JDBC approach.

One of the best tutorials on this is JDBC - Database Connections.

When DriverManager.getConnection() is called, this code ultimately traverses a list of known drivers compiled from what DriverManager can find on the CLASSPATH or, using Java reflection, gathered from the Java package of the caller.

One way to cause this to happen is, before asking for a connection, make a call such as one of these:

Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
DriverManager.registerDriver( new MockDriver() );

The first line is sometimes termed "Approach I" and it cause the driver's class file to be loaded into memory. The second line is termed "Approach II" and it explicitly registers a static method, typically in a non-compliant JDK (such as provided by Microsoft).

The second method illustrates how a non-standard driver might have been written to mock a JDBC driver for unit testing.

Either of these approaches gets the driver in a static list inside DriverManager named registeredDrivers. IntelliJ IDEA's debugger will step through DriverManager.getConnection through internal code if you need to demonstrate this to yourself.