# 09 — In-memory databases for testing: H2, HSQLDB and Derby, run side by side and verified

[← Previous: 08 — Stored procedures](08-stored-procedures.md) | [Next: 10 — Mocking JNDI datasources →](10-mocking-jndi-datasources.md)

Backs [ankurm.com: configuring in-memory databases for bulletproof unit testing](https://ankurm.com/master-hibernate-7-configuring-in-memory-databases-for-bulletproof-unit-testing/).

Companion code: [`TestDbWidget`](../src/main/java/com/ankurm/hibernatedemo/testdb/TestDbWidget.java) (one entity,
run against all three engines) and [`src/test/java/com/ankurm/hibernatedemo/testdb/`](../src/test/java/com/ankurm/hibernatedemo/testdb/) (six test
classes, 16 tests, all green together -- `docs/output/testdb-*.txt`). Environment: Hibernate ORM
7.4.5.Final, JDK 25, H2 2.4.240, HSQLDB 2.7.3, Apache Derby 10.16.1.1. All three bootstrapped
directly through plain Hibernate (`StandardServiceRegistryBuilder`/`MetadataSources`), not
through Spring Boot's single-datasource autoconfiguration, specifically so all three could be
stood up side by side against the exact same mapping without needing three separate Spring
contexts.

## Headline correction: Derby has no dialect in Hibernate 7 without an extra dependency

This is the biggest surprise in this chapter, and it isn't in the article being replaced at all.
Wiring up Derby against Hibernate 7.4.5.Final the "obvious" way fails, twice, in sequence:

1. With no `hibernate.dialect` set (which works fine for both H2 and HSQLDB, relying on JDBC
   metadata auto-detection): `Unable to determine Dialect for Apache Derby 10.16 (please set
   'hibernate.dialect' or register a Dialect resolver)`. The JDBC connection itself is fine --
   the product name and version ("Apache Derby 10.16") were read successfully. Hibernate's
   dialect resolver chain simply no longer recognizes it.
2. The "obvious" fix -- set `hibernate.dialect=org.hibernate.dialect.DerbyDialect`, the FQCN
   every existing blog post and Stack Overflow answer uses -- fails too:
   `ClassNotFoundException: Could not load requested class: org.hibernate.dialect.DerbyDialect`.
   That class does not exist anywhere in `hibernate-core-7.4.5.Final.jar` (confirmed:
   `unzip -l ... | grep -i derby` -- zero matches).

Hibernate 6.2+ moved a set of less-common dialects out of `hibernate-core` into a separate,
opt-in artifact, `org.hibernate.orm:hibernate-community-dialects`. Derby's dialect now lives
there, under a **different package**: `org.hibernate.community.dialect.DerbyDialect` (confirmed
present via `unzip -l hibernate-community-dialects-7.4.5.Final.jar | grep -i derby`). Both
failures, verbatim, and the working fix are in
[`docs/output/testdb-derby-dialect-not-found.txt`](output/testdb-derby-dialect-not-found.txt). The dependency this needed is already wired into this
repo's shared [`pom.xml`](../pom.xml):

```xml
<dependency>
  <groupId>org.hibernate.orm</groupId>
  <artifactId>hibernate-community-dialects</artifactId>
  <scope>runtime</scope>
</dependency>
```

With that dependency and `hibernate.dialect=org.hibernate.community.dialect.DerbyDialect` set
explicitly, Derby resolves and works completely normally for everything else in this chapter.

## `hibernate-jcache` on the classpath turns on L2 for everyone, whether you asked or not

This is the second headline finding in this chapter, and it is not scoped to Derby, or even to
testing in-memory databases specifically -- it is a repo-wide gotcha that this chapter is the
right place to document because [`JCacheOnClasspathAutoEnablesL2Test`](../src/test/java/com/ankurm/hibernatedemo/testdb/JCacheOnClasspathAutoEnablesL2Test.java) lives in this package.
Chapter 06 needs `hibernate-jcache` + `ehcache` on the classpath to measure `@NaturalIdCache`
(see [chapter 06](06-natural-ids.md#turning-on-l2-hibernate-jcache--ehcache)), and that dependency
is shared across the whole project's single `pom.xml` -- there's no way to scope it to only the
natural-id tests.

`JCacheOnClasspathAutoEnablesL2Test` boots a bare `SessionFactory` with **zero** cache settings
configured anywhere -- no `hibernate.cache.*` property, no `@Cache`/`@NaturalIdCache` annotation
in sight -- and finds:

```
second-level cache enabled = true
region factory            = org.hibernate.cache.jcache.internal.JCacheRegionFactory
```

Hibernate 7.4.5 resolves a `RegionFactory` through the `ServiceLoader` at boot, and merely
*finding one on the classpath* is enough for it to turn the second-level cache on by itself.
There is no explicit configuration anywhere that asks for this -- `grep -ic cache` against
[`application.yml`](../src/main/resources/application.yml) before this was pinned down returned `0`.

This was not a hypothetical risk -- it broke a real, unrelated test. Before
`hibernate.cache.use_second_level_cache: false` was pinned explicitly in `application.yml`, a
full `mvn test` run failed with an order-dependent error: a standalone natural-id-cache test
closed the shared Ehcache `CacheManager` it had spun up, and the next Spring-context test to
commit a transaction failed with `Cache[...] is closed`, verbatim in
[`docs/output/testdb-jcache-classpath-pollution.txt`](output/testdb-jcache-classpath-pollution.txt):

```
[ERROR]   ImmutableEntityTest.nativeSqlUpdate_onImmutableEntity_alwaysWorks:184 » Rollback Error while committing the transaction [Unable to perform afterTransactionCompletion callback: Cache[com.ank
```

The fix is the explicit pin in `application.yml`:

```yaml
hibernate:
  cache:
    use_second_level_cache: false
```

Not "leave it unset and hope no cache provider ever lands on the classpath" -- an unset value in
this codebase resolves to `true` the moment `hibernate-jcache` is present, regardless of intent.
Any project that adds `hibernate-jcache` (or any other JCache/Ehcache/Infinispan provider) for
one narrow use case should assume it just turned L2 on globally unless it pins the setting back
down explicitly, the same way this repo does.

## Dialect auto-selection, confirmed for all three (`DialectAndDdlTest`)

| Database | Resolved `Dialect` class |
|---|---|
| H2 (plain) | `org.hibernate.dialect.H2Dialect` |
| H2 `MODE=PostgreSQL` | `org.hibernate.dialect.H2Dialect` |
| H2 `MODE=Oracle` | `org.hibernate.dialect.H2Dialect` |
| HSQLDB | `org.hibernate.dialect.HSQLDialect` |
| Derby | `org.hibernate.community.dialect.DerbyDialect` (explicit setting required, see above) |

## The MODE= question, settled

This is the single most misunderstood H2 feature, and the answer is unambiguous once you
actually build a `SessionFactory` against each URL and read off the resolved `Dialect` class
(`DialectAndDdlTest.h2WithPostgresModeInTheUrl_...` / `...OracleMode...`, both green): **`MODE=`
changes what SQL H2 itself will parse and accept -- it does not change which Hibernate `Dialect`
class gets selected.** Hibernate's dialect resolution reads the JDBC driver's own
`DatabaseMetaData.getDatabaseProductName()`, and H2 reports itself as `H2` regardless of the
`MODE=` parameter in the URL. `H2Dialect` is what generates DDL and SQL in all three cases; the
resulting `create table` statements in [`docs/output/testdb-create-table-ddl.txt`](output/testdb-create-table-ddl.txt) are
byte-for-byte identical across plain H2, `MODE=PostgreSQL` and `MODE=Oracle`. If you need
Hibernate to actually emit PostgreSQL- or Oracle-flavoured SQL, you set `hibernate.dialect`
yourself; `MODE=` alone will not do it, and testing against H2 in a given `MODE=` is not the
same thing as testing against `PostgreSQLDialect`.

## Generated DDL, and where it turned out to be identical, not different

The same `TestDbWidget` mapping (`GenerationType.AUTO` id, `varchar(5)` SKU, an `@Lob` clob
description, a `boolean`, and a reserved-word-shaped column defensively quoted as `"order"`)
produces **textually identical** `create table` and `create sequence` statements across H2,
HSQLDB and Derby ([`docs/output/testdb-create-table-ddl.txt`](output/testdb-create-table-ddl.txt)):

```sql
create sequence TestDbWidget_SEQ start with 1 increment by 50
create table TestDbWidget (active boolean not null, "order" integer, sku varchar(5), id bigint not null, description clob, primary key (id))
```

This corrects an assumption going in: the expectation was to find `varchar` vs `character
varying`, or different boolean/clob keyword choices, across these three specific engines.
They didn't diverge -- all three dialects happen to accept the same ANSI-shaped keywords
(`boolean`, `varchar(n)`, `clob`) for this particular mapping. The real divergence, it turned
out, is not in the DDL keywords Hibernate chooses, but in which *identifiers* each engine's
parser accepts unquoted -- see below.

## `GenerationType.AUTO`: identical across all three here

`AUTO` resolved to a native `SEQUENCE` (`TestDbWidget_SEQ`, allocation size 50) on **all three**
engines -- H2, HSQLDB and Derby all support native sequences, so Hibernate 7 has no reason to
fall back to `TABLE` or `IDENTITY` for any of them. This is worth stating plainly because AUTO's
reputation for being unpredictable comes from databases like MySQL that lack native sequences
entirely; for this specific trio, AUTO does not actually diverge.

## The concrete cross-database failure case, proven with real SQL (`CrossDatabaseBehaviorTest`)

The brief for this chapter specifically asked for a genuine "passes on X, fails on Y" case, not
a folklore restatement. Two were found, one confirmed and one corrected:

**Reserved-word divergence -- real, and NOT the word you'd expect.** The obvious first guess,
an unquoted `order` column, turned out to be the wrong probe: H2, HSQLDB and Derby all reject it
identically ([`docs/output/testdb-reserved-word-survey.txt`](output/testdb-reserved-word-survey.txt), a 22-word survey run to find one
that actually diverges). The word that genuinely splits the three: **`value`**. H2 rejects an
unquoted `value` column outright:

```
JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table reserved_word_test (id integer, [*]value integer)"; expected "identifier"
```

HSQLDB and Derby both accept it without complaint. An entity field named `value` with no
`@Column(name = "\"value\"")` escaping builds a working schema on two of these three engines and
throws a SQL syntax error, specifically on H2 -- the reverse of what most people would guess,
since H2 has a reputation as the "permissive" one.

**CHAR padding -- real, but NOT Derby-exclusive, contrary to widely-repeated folklore.** The
brief called this "a classic Derby thing." Measured directly (`CHAR(10)` holding `'AB'`, read
back via `ResultSet.getString()`): H2, HSQLDB and Derby **all** return `"AB        "` (length
10, space-padded) -- not just Derby. A naive `"AB".equals(value)` fails against all three
engines for a `CHAR` column; only `VARCHAR` avoids the padding. The trap is real and worth
keeping in the article; attributing it to Derby specifically is not.

## Schema isolation: pollution proven, then the fix proven (`SchemaIsolationTest`)

Four ordered tests against one shared `SessionFactory`, deliberately built to show both failure
and fix in the same run:

1. `testA` inserts a row and does nothing to clean up.
2. `testB` -- which inserts nothing itself -- immediately sees `count >= 1`. The leftover row
   from `testA` is visible because nothing isolated the two tests from each other.
3. `testC` inserts a row inside a transaction, then **rolls back** instead of committing.
4. `testD` looks specifically for `testC`'s row by its unique SKU and finds zero -- rollback
   isolated it completely.

`create-drop` (used to build the shared `SessionFactory` here) only isolates at the
*SessionFactory* level -- one schema for the whole test class's lifetime, dropped and recreated
once. It does nothing to isolate individual *tests* from each other; that isolation has to come
from something else -- a transaction rolled back per test (what `testC`/`testD` show, and what
Spring's `@Transactional` does for you automatically in `@SpringBootTest` classes), or a full
`create-drop` re-run per test method (correct, but noticeably slower since it re-creates the
whole schema every time), or a fresh `@DirtiesContext`-forced new `ApplicationContext` (correct,
but the heaviest option of the three since it rebuilds the entire Spring context, not just the
schema).

## `DB_CLOSE_DELAY=-1`, proven with a before/after (`DbCloseDelayTest`)

Two tests, same shape, one difference in the URL. Without `DB_CLOSE_DELAY=-1`: create a table,
insert a row, close the only open connection, reconnect with a fresh connection to the *same*
URL --

```
JdbcSQLSyntaxErrorException: Table "T" not found (this database is empty)
```

H2 tore the entire in-memory database down the instant the last connection closed; the
"reconnect" actually created a brand-new, empty database that happens to share a name. With
`DB_CLOSE_DELAY=-1` on the same URL, the identical close-then-reconnect sequence sees the row
that was inserted before the close. This is exactly why every entry in `TestDbSupport.Db`'s H2
variants carries this flag, and why forgetting it produces intermittent, connection-timing-
dependent test failures rather than a clean, consistent error.

## Startup time (indicative only -- shared sandbox container)

Three `SessionFactory` builds per engine, `System.nanoTime()`, in this specific shared sandbox
([`docs/output/testdb-startup-timing.txt`](output/testdb-startup-timing.txt)):

```
H2:     1628 ms, 83 ms, 62 ms
HSQLDB:  238 ms, 79 ms, 74 ms
DERBY:   637 ms, 176 ms, 125 ms
```

The first run of whichever engine happens to go first pays a one-time JVM/driver class-loading
cost (1.6s for H2 here) that has nothing to do with that database specifically -- note HSQLDB's
first run, second in sequence, only cost 238ms. Steady-state, all three build a `SessionFactory`
against an empty in-memory schema in well under 200ms. Treat the specific numbers as
order-of-magnitude only; this container is shared and not isolated for benchmarking.

## Testcontainers: not runnable here, kept honest and short

Testcontainers 2.0.5 is the current GA and is where real cross-database parity testing belongs
once H2/HSQLDB/Derby's dialect emulation gaps (MODE= not actually changing SQL dialect, Derby's
now-separate community dialect, the CHAR-padding and reserved-word divergences documented above)
matter enough to require the real database. **Docker was not available in this sandbox** (no
`docker` binary, no `/var/run/docker.sock`), so nothing Testcontainers-based was attempted or
run here -- this is stated plainly rather than faked. The recommendation, unchanged from
standard practice: use the in-memory engines for fast unit-level ORM tests in the everyday CI
loop, and Testcontainers for a slower, separate integration stage that exercises the real
production database engine.

[← Previous: 08 — Stored procedures](08-stored-procedures.md) | [Next: 10 — Mocking JNDI datasources →](10-mocking-jndi-datasources.md)