Skip to content
Ameen Abozaid
Englishالعربية
MID-022

Alternative persistence

  • 5 Lessons
  • 2.5 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • jOOQ
  • JDBC vs JPA trade-offs
  • CQRS read models

Learn first

2.5 h of practice (estimate)

Learning outcomes

  1. Write type-safe SQL with jOOQ, generated from the schema.
  2. Compare JDBC, jOOQ, and JPA for a given use case and justify a choice.
  3. Build a separate read model for queries (CQRS-lite) inside one service.

Lessons

  1. 01When JPA is the wrong tool
  2. 02jOOQ in practice
  3. 03Choosing a persistence approach
  4. 04Read models and CQRS-lite
  5. 05Demo: rewriting the sales report with jOOQ

Lab and exercises

Lab

MID-022-LAB1 · Dashboard read model

Outcomes: 1–3 · Time: ~3 h Build an admin dashboard for Shop (orders and revenue per day, top products): a dashboard_daily read-model table updated when orders are placed, queried with jOOQ, with a rebuild command. Write a decision record comparing JDBC, jOOQ, and JPA for this feature. Acceptance: a written decision record comparing the three approaches; the dashboard served from the read model with a working rebuild.

Exercises (assessment: decision-record review)

IDOutcomeTask
MID-022-EX011Configure jOOQ code generation from the Flyway-migrated schema in the build.
MID-022-EX021Write a jOOQ query with a join, group by, and filter, and map it to a DTO.
MID-022-EX032Choose JDBC, jOOQ, or JPA for: order placement, monthly report, bulk price update, product CRUD. Justify.
MID-022-EX042Explain how JPA and jOOQ can share one transaction in a Spring service.
MID-022-EX053Design a read-model table for the dashboard and decide how it is updated (same transaction or event) and why.
MID-022-EX063Write the rebuild query that recreates the read model from the write tables.
Show solutions
  • EX01 jOOQ code generator plugin pointing to a Testcontainers/PostgreSQL database after Flyway migration; generated sources added to the build.
  • EX02 dsl.select(...).from(...).join(...).on(...).where(...).groupBy(...).fetchInto(Dto.class).
  • EX03 Order placement: JPA. Monthly report: jOOQ. Bulk price update: set-based SQL via jOOQ or JDBC. Product CRUD: JPA. Check the justification.
  • EX04 Both use the same DataSource and Spring transaction manager; DSLContext uses the transaction-bound connection.
  • EX05 For example dashboard_daily(day pk, orders, revenue); update in the same transaction for exact numbers or from an after-commit event for looser coupling; state staleness.
  • EX06 INSERT ... SELECT date_trunc('day', created_at), count(*), sum(total_cents) FROM orders GROUP BY 1 ON CONFLICT (day) DO UPDATE ....
  • Mixing without boundaries and full event-sourced CQRS for simple reads are covered by EX03, EX05, and the decision record.

Quiz

Outcomes: O1 jOOQ · O2 comparing approaches · O3 read models.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1jOOQ generates classes from:O1
  2. 2A main benefit of jOOQ over SQL strings:O1
  3. 3jOOQ queries run in the same Spring transaction when they:O1
  4. 4Best fit for JPA:O2
  5. 5Best fit for jOOQ:O2
  6. 6JDBC (JdbcClient) trades convenience for:O2
  7. 7Mixing persistence approaches in a service is acceptable when:O2
  8. 8CQRS-lite means:O3
  9. 9A read model updated in the same transaction as the order is:O3
  10. 10A needed safety feature for a read model:O3

Practical part

Decision-record review: assess a written record comparing JDBC, jOOQ, and JPA for the dashboard (criteria, trade-offs, decision, consequences). Pass: quiz 8/10, a decision record covering all three options with a justified choice, and the lab dashboard running from its read model.

Common mistakes

Mixing approaches without boundaries; full CQRS with event sourcing for simple reads.

Assessment

Decision-record review.

Career relevance

CQRS and materialized views (SRB-001).

Type to search levels, phases, modules and lessons.