Data Access: JPA and Beyond

JPA/Hibernate is the default at 90% of Java shops, which means you have to know it — but knowing it well requires unlearning the “just add @Entity and go” mindset that ships bugs to production every week. This file covers JPA the way it survives contact with a real Postgres, plus the ecosystem around it (Flyway, HikariCP, jOOQ, JDBC) so you know when to abandon JPA rather than beat it into submission.

The uncomfortable truth: JPA is a productivity tool for the boring 70% of CRUD. For everything else — reporting queries, bulk updates, complex joins, performance-critical paths — dropping down to jOOQ or plain JDBC is not a defeat, it’s the pro move.

---

1. Entities: The Rules That Bite

@Entity
@Table(name = "urls", indexes = {
    @Index(name = "idx_urls_code", columnList = "code", unique = true),
    @Index(name = "idx_urls_created_at", columnList = "createdAt")
})
public class UrlEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 30)
    private String code;

    @Column(nullable = false, length = 2048)
    private String targetUrl;

    @Column(nullable = false, updatable = false)
    private Instant createdAt;

    @Version
    private Long version;   // optimistic locking

    protected UrlEntity() {}    // Hibernate needs this. protected, not public.

    public UrlEntity(String code, String targetUrl, Instant createdAt) {
        this.code = code;
        this.targetUrl = targetUrl;
        this.createdAt = createdAt;
    }
    // getters only; setters only where mutation is a real business operation
}

Rules that new devs miss:

• No-arg constructor is mandatory (Hibernate uses reflection). Make it protected so app code can’t use it.

• Prefer Long over long for IDs — unassigned entities can then be null, which Hibernate uses to distinguish transient from persistent.

• Do not implement equals/hashCode on generated IDs alone — they’re null before flush, breaking Set membership. Use a stable business key (e.g., code) or don’t override at all if the entity’s identity is by reference.

• @Version for optimistic locking — concurrent updates get an OptimisticLockException instead of silent overwrites. Nearly free, always turn it on.

• Never use @Entity classes as DTOs. See 02_rest_apis_and_web_layer.md.

Records as entities?

Hibernate 6 supports Java records as embeddables (@Embeddable) but not as top-level entities — records are final and immutable, which conflicts with proxy-based lazy loading and dirty checking. Use classes for entities, records for embeddables and everywhere else.

---

2. Relationships: The N+1 Trap

@Entity
public class Order {
    @Id private Long id;

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)   // LAZY by default — keep it
    private List<OrderItem> items;
}

The N+1 problem, in one study-ready sentence: for a query returning N parents, LAZY loading the same association on each triggers N additional queries.

// This looks innocent. It is not.
List<Order> orders = orderRepo.findAll();       // 1 query
for (Order o : orders) {
    log.info("Order {} has {} items", o.getId(), o.getItems().size());   // N queries
}

Detection:

• Turn on spring.jpa.properties.hibernate.generate_statistics=true and log statistics — counts will explode.

• Use Hypersistence Utils or Datasource Proxy to fail tests when queries exceed a threshold.

• Read the Hibernate SQL log during dev (spring.jpa.show-sql=true + a real SQL formatter).

Fixes:

1. JOIN FETCH in JPQL — explicit, per-query:

   @Query("select o from Order o join fetch o.items where o.customerId = :cid")
   List<Order> findByCustomerWithItems(@Param("cid") Long cid);
   

2. @EntityGraph on the repository method — declarative:

   @EntityGraph(attributePaths = "items")
   List<Order> findByCustomerId(Long customerId);
   

3. @BatchSize(size = 50) on the collection — issues one query per batch instead of one per parent.

4. DTO projection — skip entities entirely for read-heavy paths:

   @Query("select new com.example.OrderSummary(o.id, count(i)) from Order o left join o.items i group by o.id")
   List<OrderSummary> summaries();
   

⚠️ What most people get wrong: Setting fetch = FetchType.EAGER to “solve” N+1. This just makes every query pull everything, including relationships you didn’t ask for. EAGER is almost always the wrong answer. Keep LAZY, fetch what you need per-query.

---

3. @Transactional — Where and Why

• Put @Transactional on service methods, not repositories, not controllers.

• Read-only? Add @Transactional(readOnly = true). Hibernate skips dirty checking — real performance win on list endpoints.

• Self-invocation doesn’t work. Calling a @Transactional method from another method in the same class bypasses the proxy — no transaction. Extract to a different bean if you need cross-method transactionality.

• Rollback rules: default rolls back on RuntimeException only. Checked exceptions do NOT roll back unless you specify rollbackFor = Exception.class. This is a common bug.

• Propagation: REQUIRED (default) is right 95% of the time. REQUIRES_NEW for “log this action even if the outer transaction fails” audit patterns.

---

4. Spring Data JPA Repositories

public interface UrlRepository extends JpaRepository<UrlEntity, Long> {

    Optional<UrlEntity> findByCode(String code);

    // Derived query — method name becomes SQL
    List<UrlEntity> findByCreatedAtAfterOrderByCreatedAtDesc(Instant after);

    // Explicit JPQL
    @Query("select u from UrlEntity u where u.clickCount > :min")
    List<UrlEntity> popular(@Param("min") long min);

    // Native SQL when JPQL isn't enough
    @Query(value = "select code, target_url from urls where code = :code", nativeQuery = true)
    Optional<UrlEntity> findByCodeNative(@Param("code") String code);

    // Modifying — batch update, bypasses entity cache
    @Modifying
    @Query("update UrlEntity u set u.clickCount = u.clickCount + 1 where u.code = :code")
    int incrementClicks(@Param("code") String code);
}

Derived queries are elegant for simple cases but become unreadable past ~4 predicates. When method names sprawl (findByCreatedAtBetweenAndTargetUrlContainingAndClickCountGreaterThanOrderByCreatedAtDesc), switch to @Query or Specifications.

---

5. When to Drop JPA

JPA is the wrong tool for:

• Reporting queries with 6+ joins, window functions, CTEs.

• Bulk operations (UPDATE ... WHERE ... across millions of rows).

• Read-only projections where you don’t need identity/dirty checking.

• Any query where SQL is what you’d naturally write.

The alternatives

Tool	Style	When
jOOQ	Type-safe SQL DSL, generated from schema	You want SQL, in Java, without stringly-typed queries. The best pick for read-heavy or reporting-heavy services.
Spring JDBC / JdbcClient (Spring 6.1+)	Templated SQL, manual mapping	Simple, fast, no ORM overhead. Ideal for a handful of custom queries alongside JPA.
MyBatis	SQL in XML (or annotations), manual mapping	Popular in Asian enterprise stacks, especially Alibaba/Zoho-adjacent. Very explicit.
Hibernate Reactive / R2DBC	Non-blocking DB access	Only if the entire stack is reactive. Do not mix reactive DB access with a servlet-based Spring MVC app.

Honest recommendation: JPA for CRUD on aggregates, jOOQ for reads and reports in the same service. This hybrid is the sweet spot for most MNCs.

---

6. Flyway Migrations: Version Your Schema

Hibernate’s ddl-auto=update is the fastest way to nuke production. Turn it off. Use Flyway or Liquibase — Flyway is simpler, so use Flyway.

spring:
  jpa:
    hibernate:
      ddl-auto: validate         # validate schema matches entities on startup
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true

-- src/main/resources/db/migration/V1__initial.sql
create table urls (
    id            bigserial primary key,
    code          varchar(30)  not null unique,
    target_url    varchar(2048) not null,
    click_count   bigint       not null default 0,
    version       bigint       not null default 0,
    created_at    timestamptz  not null
);
create index idx_urls_created_at on urls (created_at desc);

Rules:

• Migrations are immutable — never edit a versioned migration once merged. Fix mistakes with a new migration.

• Naming: V{n}__snake_case_description.sql. Two underscores between version and name.

• Repeatable migrations (R__seed_countries.sql) for reference data that changes with each schema iteration.

• Test migrations in Testcontainers before shipping — see 04_testing_the_full_stack.md.

---

7. HikariCP: The Connection Pool You’re Already Using

HikariCP is Spring Boot’s default. It’s already tuned for most workloads. The values that matter:

spring:
  datasource:
    hikari:
      maximum-pool-size: 10          # start here. tune from metrics, not intuition.
      minimum-idle: 10               # keep = max in prod. warm connections beat cold ones.
      connection-timeout: 30000      # 30s to get a connection before failing
      idle-timeout: 600000           # 10min
      max-lifetime: 1800000          # 30min (must be < DB server's timeout)
      leak-detection-threshold: 60000 # warn if a connection is held > 60s

Pool sizing math (the canonical HikariCP guidance):

connections = ((core_count * 2) + effective_spindle_count)

For a service on a 4-core node hitting a Postgres on SSD: ~10 connections. A bigger pool is not faster. Past a point, connections queue in the database and everything gets worse. Measure hikaricp_connections_active in Micrometer, not vibes.

---

8. Caching

Second-level cache (Hibernate/Ehcache/Redis): rarely worth the complexity in modern services. Cache invalidation across nodes is a distributed systems problem you don’t want.

Application-level caching with @Cacheable:

@Service
public class UrlLookupService {

    @Cacheable(value = "urlByCode", key = "#code")
    public Optional<UrlEntity> findByCode(String code) { ... }

    @CacheEvict(value = "urlByCode", key = "#url.code")
    public UrlEntity save(UrlEntity url) { ... }
}

Backed by Caffeine (in-process) or Redis (shared). See ../08_distributed_systems_applied_integration/03_caching_and_state.md for the distributed cache reality.

---

9. Common JPA Sins (a checklist)

• EAGER fetching on @OneToMany — always LAZY.

• Not using @Transactional(readOnly = true) on read endpoints.

• Calling .save() inside a loop of 10,000 items — flush + clear in batches, or drop to JDBC.

• Missing @Version — silent lost updates.

• ddl-auto=update in prod — replace with Flyway + validate.

• Exposing @Entity as JSON — lazy-init exceptions in the wild.

• List<Entity> return types without pagination — unbounded result set.

• Repository returning Stream<T> but caller doesn’t wrap in try-with-resources — connection leak.

---

Practice Exercises

1. Build a Book/Author many-to-one relationship. Write a repository method that returns books with authors and confirm via SQL log that it’s one query, not N+1.

2. Add a @Version field. Simulate concurrent updates in two test methods; assert one gets OptimisticLockException.

3. Write a Flyway migration to add a deleted_at soft-delete column. Update the repository to filter it out.

4. Add Micrometer, watch hikaricp_connections_active under load, and observe what happens when you set pool size to 2.

5. Stretch: Add jOOQ alongside JPA. Rewrite one “reporting” query in jOOQ and benchmark against the JPQL version.

---

Return to README.md · Previous: 02_rest_apis_and_web_layer.md · Next: 04_testing_the_full_stack.md