6.4. Repository Layer Writing Rules

6.4.1. Repository Structure

Repository classes must inject and use DSLContext.

@Repository
@RequiredArgsConstructor
public class UserRepository {

  private final DSLContext dsl;
}

6.4.2. Query (SELECT)

Single record query:

public Optional<UsersRecord> findById(Long id) {
  return dsl.selectFrom(USERS)
      .where(USERS.ID.eq(id))
      .fetchOptional();
}

public Optional<UsersRecord> findByEmail(String email) {
  return dsl.selectFrom(USERS)
      .where(USERS.EMAIL.eq(email))
      .fetchOptional();
}

List query:

public List<UsersRecord> findAll() {
  return dsl.selectFrom(USERS)
      .orderBy(USERS.CREATED_AT.desc())
      .fetch();
}

public List<UsersRecord> findByIsActive(boolean isActive) {
  return dsl.selectFrom(USERS)
      .where(USERS.IS_ACTIVE.eq(isActive))
      .orderBy(USERS.NAME.asc())
      .fetch();
}

6.4.3. Create (INSERT)

public UsersRecord insert(String name, String email) {
  return dsl.insertInto(USERS)
      .set(USERS.NAME, name)
      .set(USERS.EMAIL, email)
      .returning()
      .fetchOne();
}

6.4.4. Update (UPDATE)

public int update(Long id, String name) {
  return dsl.update(USERS)
      .set(USERS.NAME, name)
      .set(USERS.UPDATED_AT, OffsetDateTime.now())
      .where(USERS.ID.eq(id))
      .execute();
}

6.4.5. Delete (DELETE)

public int deleteById(Long id) {
  return dsl.deleteFrom(USERS)
      .where(USERS.ID.eq(id))
      .execute();
}

6.4.6. Conditional Search (Dynamic Queries)

Dynamic queries are constructed by composing Condition objects.

public List<UsersRecord> search(String name, String email, Boolean isActive) {
  Condition condition = DSL.noCondition();

  if (name != null) {
    condition = condition.and(USERS.NAME.containsIgnoreCase(name));
  }
  if (email != null) {
    condition = condition.and(USERS.EMAIL.eq(email));
  }
  if (isActive != null) {
    condition = condition.and(USERS.IS_ACTIVE.eq(isActive));
  }

  return dsl.selectFrom(USERS)
      .where(condition)
      .orderBy(USERS.CREATED_AT.desc())
      .fetch();
}

6.4.7. Join Queries

public List<Record> findOrdersWithUserName(Long userId) {
  return dsl.select(
          ORDERS.ID,
          ORDERS.PRODUCT_NAME,
          ORDERS.AMOUNT,
          USERS.NAME.as("user_name"))
      .from(ORDERS)
      .join(USERS).on(ORDERS.USER_ID.eq(USERS.ID))
      .where(ORDERS.USER_ID.eq(userId))
      .orderBy(ORDERS.CREATED_AT.desc())
      .fetch();
}

6.4.8. Pagination

public List<UsersRecord> findPage(int page, int size) {
  return dsl.selectFrom(USERS)
      .orderBy(USERS.CREATED_AT.desc())
      .limit(size)
      .offset(page * size)
      .fetch();
}

public long count() {
  return dsl.fetchCount(USERS);
}

The page response is assembled in the service layer:

public PageResponse<UserResponse> findUsers(int page, int size) {
  List<UsersRecord> records = userRepository.findPage(page, size);
  long totalElements = userRepository.count();

  List<UserResponse> content = records.stream()
      .map(UserResponse::from)
      .toList();

  return new PageResponse<>(content, page, size, totalElements);
}