jooq-database-access

jOOQ DSL and Database Access Conventions

Context

• Apply when writing or modifying database access logic using jOOQ, typically within service or repository classes.
• Focuses on consistent use of the jOOQ DSL and mapping.
• Retry Logic: Retry mechanisms are framework-specific. Refer to:
  • Spring Boot: mdc:rules/java-spring-boot/spring-retry-conventions.mdc
  • Micronaut: mdc:rules/java-micronaut/micronaut-retry-conventions.mdc
• Transaction Management: Managed by the specific framework (Spring/Micronaut). Refer to framework convention rules.
• Auditing: For conventions on using the AuditLogger, see mdc:rules/java-common/audit-logging.mdc.

Requirements

• Database Interaction: Use jOOQ (DSLContext) injected via constructor for all database operations. Avoid Spring Data JPA repositories or raw JDBC.
• Type-Safe DSL: ALWAYS use the jOOQ type-safe DSL (generated tables/fields like BOOK.TITLE) over constructing queries with raw SQL strings.
• Mapping: Implement clear logic (e.g., dedicated private methods like mapToXyz) to map jOOQ Record objects to application DTOs/models (BookResponse, etc.).
• Connection Handling: Rely on the framework's transaction management (Spring/Micronaut) and jOOQ integration for connection handling; do not manage connections manually.

Examples

// ... inside a service method (retry/transaction/audit handled by framework/other rules) ... public List findCheapBooks() { // The actual DB call might be wrapped by retry logic elsewhere (see specific retry rules) List cheapBooks = dsl.selectFrom(BOOK) .where(BOOK.PRICE.lt(BigDecimal.TEN)) // Type-safe condition .orderBy(BOOK.TITLE.asc()) .fetchStream() .map(this::mapToBookResponse) // Mapping function .toList();

  // Auditing is handled separately (see mdc:rules/java-common/audit-logging.mdc)
  // if (!cheapBooks.isEmpty()) { AuditLogger.logBatchOperation(...); }

  return cheapBooks;

}

private BookResponse mapToBookResponse(BookRecord record) { // ... mapping implementation ... }

</example>