ai-instructions: Instructions file for GitHub Copilot

Spring Boot JDBC Contract

These rules apply to real applications that use relational databases through Spring JDBC. Architecture already forbids JPA; this file owns how JDBC is done.

Exception handling, i18n, packaging, and pom coordinate rules stay in their own instruction files. Do not restate those contracts here.

Dependencies

• Use spring-boot-starter-jdbc plus the matching JDBC driver (for example org.postgresql:postgresql) and an explicit com.zaxxer:HikariCP runtime dependency. Dependency comments, ordering, and the JPA ban are owned by the pom contract.

Access API

• Use Spring JdbcClient for ordinary reads and writes.
• Use NamedParameterJdbcTemplate only when you need APIs JdbcClient does not cover cleanly (for example multi-row batchUpdate).
• Keep JdbcClient and JDBC templates inside repository implementations (architecture contract).
• Prefer the Boot-provided JdbcClient bean. Load SQL with @PropertySource on a dedicated config class; do not recreate JdbcClient unless auto-config is disabled.

SQL location and shape

• Store DML statements in classpath:sql/{feature}.xml (Java properties DTD) and bind them to a feature @ConfigurationProperties record (for example sql.users.find-by-id).
• Use named parameters only (:id, :name). Do not use ? placeholders in new SQL.
• Keep domain models as plain records with no JDBC or ORM annotations.
• Do not bury SQL strings as Java constants in real applications.

Generated keys and updates (PostgreSQL)

• When an insert must return the new row, use INSERT … RETURNING … and map the result with JdbcClient (.query(…).single()). Do not invent primary keys in Java.
• Prefer RETURNING on updates when the caller needs the persisted row.

Lookups, paging, and dynamic SQL

• Single-row repository lookups return Optional. The service turns empty into a feature AppException (exception contract). Do not return null from repositories.
• Unbounded collection reads that page in SQL use COUNT plus LIMIT/OFFSET (or the database equivalent) with Spring Pageable/Page.
• When building dynamic ORDER BY, allowlist column names. Never concatenate raw client sort fields into SQL.