spring-boot-postgres-example.md

Spring Boot PostgreSQL Example

The canonical Spring Boot example lives in examples/spring-boot-postgres. For copyable repository recipes using the same patterns, see query-recipes.md.

What It Shows

• Spring Boot 4.1 wiring through java/spring-boot-starter.
• PostgreSQL rendering through java/dialect-postgres.
• JDBC execution boundary through MortarJdbcClient.
• Generated Java metamodels through java/processor.
• Generated Read facade usage for common findAll and findById read paths.
• Testkit assertions for SQL and parameters.

Run The Example Tests

gradlew.bat :examples:spring-boot-postgres:test

The test does not need a local database. It verifies the query contract by rendering SQL and by mocking the JDBC client boundary.

Important Files

• examples/spring-boot-postgres/src/main/java/dev/mortar/examples/springpostgres/Client.java
• examples/spring-boot-postgres/src/main/java/dev/mortar/examples/springpostgres/ClientRepository.java
• examples/spring-boot-postgres/src/test/java/dev/mortar/examples/springpostgres/ClientRepositoryTest.java
• examples/spring-boot-postgres/src/main/resources/application.yml
• examples/spring-boot-postgres/src/main/resources/schema.sql

Repository Pattern

Use the generated Read facade for common read paths:

public List<ClientSummary> findAll() {
    return jdbcClient.fetch(
            CLIENT.read(renderer)
                .findAll()
                .named("ClientRepository.findAll")
        )
        .stream()
        .map(row -> new ClientSummary(row.id(), row.name()))
        .toList();
}

public Optional<ClientSummary> findById(long id) {
    return jdbcClient.fetchOptional(
            CLIENT.read(renderer)
                .findById(id)
                .named("ClientRepository.findById")
        )
        .map(row -> new ClientSummary(row.id(), row.name()));
}

The generated facade renders SQL through the configured renderer, returns an immutable MortarBoundQuery, and still executes only when passed to MortarJdbcClient.

Mortar does not generate the repository. The repository keeps the Spring method name, maps generated rows to application DTOs, and chooses whether the result is Optional or List.

Repository tests can assert the generated facade without a database:

MortarBoundQuery<QClient.FindByIdRow> query = CLIENT.read(renderer)
    .findById(7L)
    .named("ClientRepository.findById");

MortarSqlAssertions.assertThatSql(query)
    .hasSql("select c.id, c.name, c.active from clients c where c.id = ?")
    .hasParameters(7L)
    .hasParameterTypes(Long.class);

Those assertions keep SQL visible in CI. If the generated columns, identifier binding, parameter Java type, or renderer output drifts, the repository test fails before the query reaches a database.

Starter Properties

The example configures the starter explicitly:

mortar:
  dialect: postgres
  sql-format: pretty
  jdbc:
    logging:
      enabled: true
  diagnostics:
    enabled: true

mortar.dialect currently supports PostgreSQL. The diagnostics endpoint reports the selected dialect, SQL format, JDBC logging flag, diagnostics flag, and renderer class so production applications can verify the active starter wiring.

Keep richer query shapes in the Java DSL:

public Optional<ClientSummary> findActiveById(long id) {
    List<ClientSummary> rows = jdbcClient.fetch(findActiveByIdQuery(id), ClientSummary.class);
    return rows.stream().findFirst();
}

Keep query construction in a named method so tests and editor tooling have a stable reference:

QuerySpec findActiveByIdQuery(long id) {
    return db.from(CLIENT)
        .projectRecord(ClientSummary.class, client -> client.id, client -> client.name)
        .where(client -> client.id.eq(id))
        .where(client -> client.active.eq(true))
        .named("ClientRepository.findActiveById")
        .build();
}

Generated Read facades intentionally do not cover optional filters, joins, writes, count, exists, generated projections, generated repositories, or self-executing query objects. Keep those shapes explicit until a later roadmap decision proves and documents a larger surface.

Local Database

The example application.yml expects:

jdbc:postgresql://localhost:5432/mortar_example

The module includes schema.sql and data.sql for a small clients table. For automated database compatibility, use the Testcontainers integration tests in the main PostgreSQL/runtime modules.