248 lines
15 KiB
Markdown
Executable File
248 lines
15 KiB
Markdown
Executable File
# 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)
|