Hibernate 7 In-Memory Databases: H2, HSQLDB and Derby for Testing

A test suite that talks to a real Postgres or MySQL instance is slow, and slow tests get skipped. The usual fix — mock EntityManager or Session and stop hitting a database at all — throws away the one thing an ORM test is actually for: proving the SQL Hibernate generates is valid against your schema. An in-memory database gets you both: real SQL, real constraints, and a SessionFactory that boots in milliseconds.

This article rebuilds that setup against Hibernate 7.4.5.Final, and then goes one step further than the usual “here’s H2” tutorial: it runs the identical entity mapping against H2, HSQLDB and Apache Derby, side by side, and reports what actually happened — including a headline break for anyone still using the old Derby dialect class name, and a second-level-cache trap that will silently change what your tests are actually testing.

Which part is for you

Part	Read this if…
1 — Beginner	You want a working H2 setup and don’t have one yet
2 — Intermediate	You need to pick a database, understand what create-drop does and doesn’t isolate, or your test suite started failing for no obvious reason
3 — Advanced	You want the real cross-database divergences — not folklore — plus startup numbers and an honest Testcontainers verdict

Verified against. Hibernate ORM 7.4.5.Final, Spring Boot 4.1.1 (GA 2026-07), Spring Framework 7.0.9, Jakarta Persistence 3.2.0, Java 25 (Temurin). Databases: H2 2.4.240, HSQLDB 2.7.3, Apache Derby 10.16.1.1. Every claim below traces to a test in the companion repository’s testdb package or a captured output file — see the links as you go.

Part 1 — A working H2 setup

Stop thinking about “unit vs integration” for a moment and think about what you actually want: a real SQL engine, in the same JVM as the test, gone the instant the JVM exits. That’s an in-memory database. Whether you file the resulting tests under “unit” or “lightweight integration” is a taxonomy argument — they run in milliseconds either way, so most teams run them in the unit-test phase regardless of the label.

Dependencies

Hibernate 7 needs Jakarta Persistence 3.2 and Java 17+; this repo runs Java 25. Add H2 at test scope:

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-core</artifactId>
    <!-- version managed by Spring Boot 4.1.1's BOM: 7.4.5.Final -->
</dependency>

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.4.240</version>
    <scope>test</scope>
</dependency>

A plain entity, nothing unusual:

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String username;

    @Column(nullable = false)
    private String email;

    protected User() {}
    public User(String username, String email) {
        this.username = username;
        this.email = email;
    }

    public Long getId() { return id; }
}

And hibernate.cfg.xml in src/test/resources:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"></property>
        <property name="hibernate.hbm2ddl.auto">create-drop</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
    </session-factory>
</hibernate-configuration>

Note what’s missing versus older examples: no hibernate.dialect property. Hibernate reads the JDBC driver’s own DatabaseMetaData.getDatabaseProductName() and resolves H2Dialect automatically — this works for H2 and HSQLDB without you naming a dialect at all. Part 2 explains why that same auto-detection silently stops working for one specific database.

DB_CLOSE_DELAY=-1 is not cosmetic. Without it, H2 tears the entire in-memory database down the instant the last open connection closes. Part 2 shows the exact error you get if you forget it.

Two ways to write the test

// Option A -- native Hibernate Session
@Test
void testNativeSession() {
    try (Session session = sessionFactory.openSession()) {
        session.beginTransaction();
        session.persist(new User("ankur", "[email protected]"));
        session.getTransaction().commit();
    }
}

// Option B -- JPA EntityManager, verifying a real constraint violation
@Test
void testUniqueConstraint() {
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    em.persist(new User("duplicate", "[email protected]"));
    em.flush();

    User second = new User("duplicate", "[email protected]");

    assertThrows(PersistenceException.class, () -> {
        em.persist(second);
        em.flush();
    });

    em.getTransaction().rollback();
    em.close();
}

Feature	Session API	EntityManager API
Standardization	Hibernate-proprietary	Jakarta standard (JPA)
Portability	Low	High
Surface	Full Hibernate API	JPA standard subset

Either one is fine for this. What matters more is the entity mapping underneath — and Part 3 runs a mapping with the same shape as User above against three engines instead of one, which is where the interesting failures live.

Part 2 — How it actually works, and how to debug it when it doesn’t

Dialect auto-detection breaks for exactly one of the three — Derby

Wiring up Derby the “obvious” way — the same way you just wired up H2 — fails twice in sequence. First, with no hibernate.dialect set:

org.hibernate.HibernateException: 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 doesn’t recognize that product any more. The “obvious” fix every existing Stack Overflow answer gives — setting hibernate.dialect=org.hibernate.dialect.DerbyDialect explicitly — fails too:

Caused by: java.lang.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 with unzip -l hibernate-core-7.4.5.Final.jar | 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. Derby’s dialect now lives at org.hibernate.community.dialect.DerbyDialect, in hibernate-community-dialects:

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

With that dependency present and hibernate.dialect=org.hibernate.community.dialect.DerbyDialect set explicitly, Derby resolves and behaves normally for everything else in this article. Both failures verbatim: docs/output/testdb-derby-dialect-not-found.txt.

DB_CLOSE_DELAY=-1: what actually breaks without it

Skip the flag, create a table, insert a row, close the only open connection, then reconnect 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” isn’t a reconnect — it’s 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 there before the close. This is exactly why forgetting the flag produces intermittent, connection-timing-dependent failures rather than a clean, consistent error — whether you hit it depends on whether anything else still holds a connection open when your test reconnects.

Schema isolation: what create-drop does and doesn’t give you

hibernate.hbm2ddl.auto=create-drop isolates at the SessionFactory level — one schema for the whole test class’s lifetime. It does nothing to isolate individual tests from each other. Four ordered tests against one shared SessionFactory prove both the failure and the fix in the same run:

1. Test A inserts a row and commits. No cleanup.
2. Test B, which inserts nothing itself, immediately counts ≥1 row — A’s leftover is visible.
3. Test C inserts a row inside a transaction, then rolls back instead of committing.
4. Test D looks specifically for C’s row and finds zero — the rollback isolated it completely.

The isolation has to come from something outside create-drop: a transaction rolled back per test (what C/D show, and what Spring’s @Transactional gives you automatically in @SpringBootTest), a full schema re-creation per test method (correct, slower), or a fresh Spring context per test via @DirtiesContext (correct, heaviest). See SchemaIsolationTest for the ordered proof.

Your test suite is lying to you: hibernate-jcache turns on L2 whether you asked or not

This is the section that will save someone a debugging afternoon. If hibernate-jcache is anywhere on your test classpath — even added for one unrelated feature, like measuring @NaturalIdCache — it silently turns the second-level cache on for every SessionFactory in the process, with zero configuration asking for it:

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; merely finding one on the classpath is enough to enable L2. There is no explicit setting anywhere — grep -ic cache against this repo’s application.yml, before the fix, returned 0.

This wasn’t a hypothetical. It broke a real, unrelated test, and it’s order-dependent — the two classes involved pass fine when run alone:

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

A standalone natural-id-cache test closed the shared Ehcache CacheManager it had spun up; the next Spring-context test to commit a transaction hit a closed cache and failed. The fix is an explicit pin, not silence:

hibernate:
  cache:
    use_second_level_cache: false

Leaving it unset does not mean “off.” It resolves to true the moment a cache provider lands on the classpath, regardless of intent. If your project adds hibernate-jcache, Ehcache, or Infinispan for one narrow reason, assume it just turned L2 on globally until you pin the setting back down. Full failure: docs/output/testdb-jcache-classpath-pollution.txt.

Remaining pitfalls

• Dialect emulation gaps: H2/HSQLDB/Derby don’t cover 100% of vendor-specific SQL — Postgres JSONB operators being the common example. This is a real gap, not folklore; it’s why Part 3 ends on Testcontainers.
• Case sensitivity: production databases are often case-insensitive while H2’s default identifier handling is strict. Confirm behaviour for your own schema rather than assuming.

Part 3 — What the defaults quietly don’t tell you

The MODE= parameter does not change the Hibernate dialect

This is the single most misunderstood H2 feature, and it’s now settled with evidence rather than assumption. Plain H2, MODE=PostgreSQL, and MODE=Oracle all resolve the exact same org.hibernate.dialect.H2Dialect:

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

MODE= changes what SQL H2 itself will parse and accept — it does not change which Hibernate dialect gets selected, because dialect resolution reads DatabaseMetaData.getDatabaseProductName(), and H2 reports itself as H2 regardless of the URL parameter. The generated create table DDL is byte-for-byte identical across all three modes:

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))

Testing against H2 in a given MODE= is not the same thing as testing against PostgreSQLDialect. If you need Hibernate to actually emit Postgres- or Oracle-flavoured SQL, you set hibernate.dialect yourself. Full DDL capture: docs/output/testdb-create-table-ddl.txt.

GenerationType.AUTO: no divergence here, worth saying plainly

AUTO resolved to a native SEQUENCE (allocation size 50) on all three engines. AUTO’s reputation for unpredictability comes from databases like MySQL that lack native sequences entirely — for this trio, all three support sequences natively, so Hibernate has no reason to fall back to TABLE or IDENTITY for any of them.

The real “passes here, fails there” case — and it’s not the word you’d guess

The obvious first guess for a reserved-word column is order. Wrong probe: H2, HSQLDB and Derby all reject an unquoted order column identically. A 22-word survey was run to find one that actually splits the three engines. It’s value:

unquoted 'value' column, db=H2     -> succeeded=false, JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table reserved_word_test (id integer, [*]value integer)"; expected "identifier"
unquoted 'value' column, db=HSQLDB -> succeeded=true
unquoted 'value' column, db=DERBY  -> succeeded=true

An entity field named value with no @Column(name = "\"value\"") escaping builds a working schema on two of these three engines and throws a syntax error specifically on H2 — the reverse of what most people would guess, since H2 has a reputation as the permissive one. Full 22-word survey: docs/output/testdb-reserved-word-survey.txt.

CHAR padding: real, but not the Derby-exclusive quirk it’s reputed to be

A CHAR(10) column holding 'AB', read back via ResultSet.getString():

CHAR(10) holding 'AB', db=H2     -> getString() = [AB        ], length=10
CHAR(10) holding 'AB', db=HSQLDB -> getString() = [AB        ], length=10
CHAR(10) holding 'AB', db=DERBY  -> getString() = [AB        ], length=10

All three space-pad to the declared length. A naive "AB".equals(value) fails against all three engines, not just Derby — only VARCHAR avoids the padding. Attributing this specifically to Derby, which is the common framing, is wrong; the trap is universal. Same run: docs/output/testdb-crossdb-behavior.txt.

Startup time (indicative, not a benchmark)

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

Three SessionFactory builds per engine in one shared sandbox container. The first run of whichever engine happens to go first pays a one-time JVM/driver class-loading cost that has nothing to do with that database specifically — HSQLDB’s first run, second in the overall sequence, cost only 238ms. Steady-state, all three build an empty in-memory schema in well under 200ms. Treat these as order-of-magnitude only, not a benchmark: docs/output/testdb-startup-timing.txt.

Where in-memory engines stop being enough

Testcontainers 2.0.5 is the current GA and is genuinely where cross-database parity testing belongs once the gaps above matter — vendor SQL, real constraint behaviour, JSONB and similar functions none of H2/HSQLDB/Derby emulate. Docker was not available in the environment this article was built in, so nothing Testcontainers-based was run here; that’s stated plainly rather than implied. The standard split still holds: in-memory engines for fast unit-level ORM tests in the everyday CI loop, Testcontainers for a slower, separate integration stage against the real production engine.

Long tail

• Registering multiple entities without Spring’s classpath scanning — see SessionFactory bootstrapping.
• Schema-parity testing against real migration scripts (Flyway/Liquibase) by setting hbm2ddl.auto=validate and running your actual migrations against H2 first — catches SQL-file errors that entity-generation can’t.

Should you bother with three engines? No, not by default. Pick one — H2 is the sane default — and only add HSQLDB or Derby if your production database’s stored-procedure or dialect behaviour genuinely needs a closer proxy than H2 provides (Derby, for instance, runs real SQL/PSM stored procedures HSQLDB and H2 don’t). Running all three routinely for a project that ships to one production database is wasted CI time for a comparison you now already have.

Further reading

• Repo chapter: 09 — Testing with in-memory databases
• Repo chapter: 00 — Versions and the Spring Boot / Hibernate patch-pin trap
• Testing Hibernate 7: Mocking JNDI DataSources — testing enterprise setups without a full container
• Hibernate 7 Hello World — the H2 in-memory database used in the foundational setup guide
• Hibernate 7 SessionFactory Bootstrapping — programmatic configuration for test environments
• Hibernate 7 Documentation
• Official Hibernate 7 User Guide — Testing
• H2 Database Compatibility Modes