Relational databases from Java

Overview

Everything relational in Java sits on JDBC: a blocking, standardized driver API that every abstraction above it — JPA/Hibernate, jOOQ, MyBatis, Spring Data — ultimately compiles down to. The interesting decisions are which abstraction level to buy into (full ORM vs. typesafe SQL vs. raw templates), how to avoid the classic ORM traps (N+1, lazy loading outside a session), and how the frameworks package it all: Spring Data JPA, Quarkus Panache, Micronaut Data. Migrations and transaction demarcation round out the production picture.

Key points

• JDBC is the substrate: DataSource → Connection → PreparedStatement → ResultSet. Nobody hand-rolls this in application code any more, but every leak, timeout, and pool exhaustion is diagnosed at this layer.
• Pooling is non-negotiable: HikariCP is the default pool in Spring Boot and the de-facto standard; size it from measured concurrency (pool size ≈ cores × small factor), not gut feel.
• JPA/Jakarta Persistence + Hibernate is the dominant ORM: managed entity lifecycle, dirty checking, first-level cache per EntityManager/session, optional second-level cache.
• The ORM traps are lifecycle traps: N+1 selects from lazy collections iterated in a loop; LazyInitializationException from touching proxies after the session closes; fixes are fetch joins, @EntityGraph, or DTO projections — not FetchType.EAGER everywhere.
• Alternatives by abstraction: jOOQ (SQL as a typesafe DSL generated from the schema, you keep full SQL power), MyBatis (SQL in XML/annotations mapped to objects), Spring JdbcTemplate/JdbcClient (thin, explicit, no magic).
• Spring Data JPA derives queries from method names (findByEmailAndActiveTrue), generates repository implementations at runtime, adds paging/sorting/specifications.
• Quarkus ships Hibernate ORM with Panache: active-record (entity.persist()) or repository style, with build-time enhancement. Micronaut Data precomputes queries at compile time — no runtime proxies, minimal reflection, natural fit for GraalVM native image.
• R2DBC is the reactive relational lane (separate SPI, not JDBC); use it only when the whole call chain is reactive.
• Migrations belong in code: Flyway (versioned SQL scripts) or Liquibase (changelogs); never let Hibernate ddl-auto touch production schemas.

Details

Abstraction ladder

Rule of thumb: CRUD-heavy domain model → JPA; report/query-heavy or SQL-centric team → jOOQ; a few hot queries inside a JPA app → drop to JdbcClient for just those.

Hibernate lifecycle and caches

• Entity states: transient → managed (persist/find) → detached (session closed) → removed. Dirty checking flushes managed changes at commit — no explicit update() needed.
• First-level cache is the persistence context itself, per transaction. Second-level cache (Ehcache/Infinispan/Caffeine via provider) is opt-in per entity — worth it only for read-mostly reference data, and it complicates clustering.
• Long transactions holding many managed entities balloon memory and flush cost; prefer short transactions and DTO projections for reads.

The entity lifecycle, drawn:

stateDiagram-v2
    state "Transient" as Transient
    state "Managed" as Managed
    state "Detached" as Detached
    state "Removed" as Removed
    [*] --> Transient: new object
    Transient --> Managed: persist() or find()
    Managed --> Detached: session closed
    Managed --> Removed: remove()
    note right of Managed
        Dirty checking flushes managed changes
        at commit - no explicit update() needed.
    end note
    note right of Detached
        Touching a lazy proxy here throws
        LazyInitializationException.
    end note

Framework data layers

• Spring Data JPA: interface OrderRepository extends JpaRepository<Order, Long> + derived queries, @Query for JPQL/native, @Transactional on service layer by convention.
• Quarkus Panache: PanacheEntity active record (Order.findById(id), order.persist()) or PanacheRepository; simplified query strings (find("status", s)).
• Micronaut Data: same repository idiom, but query generation and validation happen at compile time; choose Micronaut Data JDBC (maps rows directly, no lifecycle) or Micronaut Data JPA (delegates to Hibernate).

Transactions

• Resource-local (one DataSource, default in Boot/Quarkus/Micronaut apps) vs. JTA (distributed, multiple resources — Quarkus ships Narayana). Two-phase commit is rarely worth it; prefer outbox patterns across services.
• @Transactional (Spring's or jakarta.transaction.Transactional) is proxy/interceptor based: self-invocation bypasses it, and only runtime exceptions roll back by default in Spring — a perennial audit item.

Examples

// Spring Data JPA: derived query + fetch-join escape hatch against N+1
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByCustomerIdAndStatus(Long customerId, Status status);

    @Query("select o from Order o join fetch o.lines where o.id = :id")
    Optional<Order> findWithLines(@Param("id") Long id);
}

// Quarkus Panache active record
@Entity
public class Order extends PanacheEntity {
    public Status status;
    public static List<Order> open() { return list("status", Status.OPEN); }
}

# HikariCP essentials (Spring Boot)
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=3000
spring.jpa.open-in-view=false   # disable OSIV; surface lazy-loading issues early

Related

• Storage access from Java — the map — parent overview by storage kind.
• Databases and other storage systems — engine internals, indexing, and isolation behind the driver.
• Integration testing — Testcontainers is how this whole stack gets tested against a real database.
• Jakarta EE correspondence — where JPA and JTA sit in the Jakarta spec landscape.
• Document and NoSQL stores from Java — where JPA instincts stop applying.