jOOQ subQuery와 Enum Converter

복잡한 SQL의 핵심은 서브쿼리이다. jOOQ는 세 가지 타입의 서브쿼리(SELECT절, FROM절, WHERE절)를 타입 안전하게 작성할 수 있다

서브쿼리의 3가지 유형

• 스칼라 서브쿼리 (SELECT절): 단일 값을 반환하여 컬럼처럼 사용
• 인라인 뷰 (FROM절): 테이블처럼 사용되는 서브쿼리
• 조건절 서브쿼리 (WHERE/HAVING절): 조건 판단에 사용

스칼라 서브쿼리 (SELECT절)

SELECT절에서 서브쿼리를 사용하여 계산된 값을 조회한다

요구사항

영화별 대여료를 기준으로 가격 등급을 분류하고, 각 영화의 총 재고 수를 조회한다

• 대여료: <= 1.0: “Cheap”
• 대여료: <= 3.0: “Moderate”
• 대여료: > 3.0: “Expensive”

SELECT
    film.film_id,
    film.title,
    film.rental_rate,
    CASE
        WHEN rental_rate <= 1.0 THEN 'Cheap'
        WHEN rental_rate <= 3.0 THEN 'Moderate'
        ELSE 'Expensive'
    END AS price_category,
    (SELECT COUNT(*) 
     FROM inventory 
     WHERE film_id = film.film_id) AS total_inventory
FROM film
WHERE film.title LIKE '%EGG%';


film_id | title         | rental_rate | price_category | total_inventory
--------|---------------|-------------|----------------|----------------
5       | AFRICAN EGG   | 2.99        | Moderate       | 3
274     | EGG IGBY      | 2.99        | Moderate       | 7
709     | RACER EGG     | 2.99        | Moderate       | 6

DTO 정의

@Getter
public class FilmPriceSummary {
    private Long filmId;
    private String title;
    private BigDecimal rentalRate;
    private String priceCategory;
    private Long totalInventory;
}

jOOQ 구현

@Repository
public class FilmRepository {
    private final DSLContext dslContext;
    private final JFilm FILM = JFilm.FILM;

    public List<FilmPriceSummary> findFilmPriceSummaryByFilmTitle(String filmTitle) {
        final JInventory INVENTORY = JInventory.INVENTORY;

        return dslContext
                .select(
                    FILM.FILM_ID,
                    FILM.TITLE,
                    FILM.RENTAL_RATE,
                    // CASE WHEN 절
                    case_()
                        .when(FILM.RENTAL_RATE.le(BigDecimal.valueOf(1.0)), "Cheap")
                        .when(FILM.RENTAL_RATE.le(BigDecimal.valueOf(3.0)), "Moderate")
                        .else_("Expensive")
                        .as("price_category"),
                    // 스칼라 서브쿼리
                    selectCount()
                        .from(INVENTORY)
                        .where(INVENTORY.FILM_ID.eq(FILM.FILM_ID))
                        .asField("total_inventory")  // asField() 사용
                )
                .from(FILM)
                .where(FILM.TITLE.like("%" + filmTitle + "%"))
                .fetchInto(FilmPriceSummary.class);
    }
}

코드 분석

case when 절

case_()
    .when(조건1, 값1)
    .when(조건2, 값2)
    .else_(기본값)
    .as("별칭")

jOOQ의 case_() 메서드는 SQL의 CASE WHEN 절을 타입 안전하게 작성할 수 있게 한다

스칼라 서브쿼리

selectCount()
    .from(INVENTORY)
    .where(INVENTORY.FILM_ID.eq(FILM.FILM_ID))
    .asField("total_inventory")  // Field로 변환

중요: SELECT 절의 서브쿼리는 반드시 asField()로 Field 타입으로 변환해야 한다

생성된 SQL

SELECT `film`.`film_id`, 
       `film`.`title`, 
       `film`.`rental_rate`, 
       CASE 
           WHEN `film`.`rental_rate` <= 1.0 THEN 'Cheap' 
           WHEN `film`.`rental_rate` <= 3.0 THEN 'Moderate' 
           ELSE 'Expensive' 
       END AS `price_category`, 
       (SELECT COUNT(*) 
        FROM `inventory` 
        WHERE `inventory`.`film_id` = `film`.`film_id`) AS `total_inventory` 
FROM `film` 
WHERE `film`.`title` LIKE '%EGG%'

테스트

@Test
@DisplayName("""
    영화별 대여료가
     1.0 이하면 'Cheap',
     3.0 이하면 'Moderate',
     그 이상이면 'Expensive'로 분류하고,
    각 영화의 총 재고 수를 조회한다.
    """)
void 스칼라_서브쿼리_예제() {
    // given
    String filmTitle = "EGG";

    // when
    List<FilmPriceSummary> result = filmRepository
        .findFilmPriceSummaryByFilmTitle(filmTitle);

    // then
    assertThat(result).isNotEmpty();
    assertThat(result).allSatisfy(summary -> {
        assertThat(summary.getTitle()).contains("EGG");
        assertThat(summary.getTotalInventory()).isGreaterThan(0L);
    });
}

인라인 뷰 (FROM절 서브쿼리)

FROM절에서 서브쿼리를 테이블처럼 사용한다

요구사항

평균 대여 기간이 가장 긴 영화부터 정렬하여 조회한다

원본 SQL

SELECT 
    film.film_id,
    film.title,
    rental_duration_info.average_rental_duration
FROM film
JOIN (
    SELECT 
        inventory.film_id, 
        AVG(DATEDIFF(rental.return_date, rental.rental_date)) AS average_rental_duration
    FROM rental 
    JOIN inventory ON rental.inventory_id = inventory.inventory_id
    WHERE rental.return_date IS NOT NULL
    GROUP BY inventory.film_id
) AS rental_duration_info
ON film.film_id = rental_duration_info.film_id
WHERE film.title LIKE '%EGG%'
ORDER BY rental_duration_info.average_rental_duration DESC;

film_id | title         | average_rental_duration
--------|---------------|------------------------
5       | AFRICAN EGG   | 7.0909
274     | EGG IGBY      | 5.7619
709     | RACER EGG     | 4.8333

DTO 정의

@Getter
public class FilmRentalSummary {
    private Long filmId;
    private String title;
    private BigDecimal averageRentalDuration;
}

jOOQ 구현

public List<FilmRentalSummary> findFilmRentalSummaryByFilmTitle(String filmTitle) {
    final JInventory INVENTORY = JInventory.INVENTORY;
    final JRental RENTAL = JRental.RENTAL;

    // 1. 서브쿼리 정의 (변수로 선언)
    var rentalDurationInfoSubquery = select(
            INVENTORY.FILM_ID,
            avg(
                localDateTimeDiff(DAY, RENTAL.RENTAL_DATE, RENTAL.RETURN_DATE)
            ).as("average_rental_duration")
        )
        .from(RENTAL)
        .join(INVENTORY)
            .on(RENTAL.INVENTORY_ID.eq(INVENTORY.INVENTORY_ID))
        .where(RENTAL.RETURN_DATE.isNotNull())
        .groupBy(INVENTORY.FILM_ID)
        .asTable("rental_duration_info");  // ⭐ asTable() 사용

    // 2. 메인 쿼리에서 서브쿼리 조인
    return dslContext
            .select(
                FILM.FILM_ID,
                FILM.TITLE,
                rentalDurationInfoSubquery.field("average_rental_duration")
            )
            .from(FILM)
            .join(rentalDurationInfoSubquery)
                .on(FILM.FILM_ID.eq(
                    rentalDurationInfoSubquery.field(INVENTORY.FILM_ID)
                ))
            .where(FILM.TITLE.like("%" + filmTitle + "%"))
            .orderBy(field(name("average_rental_duration")).desc())
            .fetchInto(FilmRentalSummary.class);
}

코드 분석

서브쿼리를 변수로 선언

var rentalDurationInfoSubquery = select(...)
    .from(...)
    .asTable("rental_duration_info");  // Table 타입으로 변환

FROM절의 서브쿼리는 변수로 먼저 선언해야 한다. 왜냐하면 메인 쿼리에서 이 서브쿼리의 컬럼을 참조해야 하기 때문이다

장점

• 서브쿼리를 재사용 가능한 함수로 추출할 수 있다
• 복잡한 쿼리의 가독성 향상

localDateTimeDiff() 함수

localDateTimeDiff(DAY, startDate, endDate)

MySQL의 DATEDIFF(end, start)와 달리, jOOQ의 localDateTimeDiff()는 시작일이 먼저, 종료일이 나중에 온다. 그 이유는 여러 데이터베이스의 일관성을 위해 jOOQ가 표준화된 순서를 사용한다

// MySQL DATEDIFF
DATEDIFF(return_date, rental_date)  // 종료 - 시작

// jOOQ localDateTimeDiff
localDateTimeDiff(DAY, rental_date, return_date)  // 시작, 종료

지원 데이터베이스

• Derby, Firebird, H2, HSQLDB, MariaDB, MySQL, PostgreSQL

서브쿼리 컬럼 참조

// 서브쿼리의 컬럼 가져오기
rentalDurationInfoSubquery.field("average_rental_duration")
rentalDurationInfoSubquery.field(INVENTORY.FILM_ID)

asTable()로 변환한 서브쿼리는 .field() 메서드로 컬럼을 참조한다

동적 필드로 정렬

.orderBy(field(name("average_rental_duration")).desc())

서브쿼리의 별칭 컬럼으로 정렬할 때는 field(name()) 패턴을 사용한다

SELECT `film`.`film_id`, 
       `film`.`title`, 
       `rental_duration_info`.`average_rental_duration` 
FROM `film` 
JOIN (
    SELECT `inventory`.`film_id`, 
           AVG(DATEDIFF(`rental`.`return_date`, `rental`.`rental_date`)) 
               AS `average_rental_duration` 
    FROM `rental` 
    JOIN `inventory` 
        ON `rental`.`inventory_id` = `inventory`.`inventory_id` 
    WHERE `rental`.`return_date` IS NOT NULL 
    GROUP BY `inventory`.`film_id`
) AS `rental_duration_info` 
ON `film`.`film_id` = `rental_duration_info`.`film_id` 
WHERE `film`.`title` LIKE '%EGG%' 
ORDER BY `average_rental_duration` DESC

테스트

@Test
@DisplayName("대여된 기록이 있는 영화만 조회")
void 조건절_서브쿼리_예제() {
    // given
    String filmTitle = "EGG";

    // when
    List<Film> filmList = filmRepository.findRentedFilmByTitle(filmTitle);

    // then
    assertThat(filmList).isNotEmpty();
    assertThat(filmList).allSatisfy(film ->
        assertThat(film.getTitle()).contains("EGG")
    );
}

서브쿼리 타입별 정리

위치	jOOQ 메서드	변환 메서드	용도
SELECT 절	select(…).from(…)	.asField(“별칭”)	계산된 단일 값
FROM 절	select(…).from(…)	.asTable(“별칭”)	임시 테이블
WHERE 절	whereExists().where().in()	없음	조건 판단

핵심 패턴

// SELECT 절: asField()
selectCount().from(TABLE).where(...).asField("alias")

// FROM 절: asTable()
select(...).from(...).groupBy(...).asTable("alias")

// WHERE 절: 바로 사용
.whereExists(select(...).from(...).where(...))
.where(FIELD.in(select(...).from(...)))

Enum Converter

SELECT 결과를 단순 문자열이 아닌 Enum으로 받으면타입 안전성이 향상된다

문제 상황

@Getter
public class FilmPriceSummary {
    private String priceCategory;  // "Cheap", "Moderate", "Expensive"
}

문자열은 오타나 잘못된 값에 취약하다

해결 – Enum 도입

@Getter
public class FilmPriceSummary {
    private Long filmId;
    private String title;
    private BigDecimal rentalRate;
    private PriceCategory priceCategory;  // Enum 사용
    private Long totalInventory;

    @Getter
    public enum PriceCategory {
        CHEAP("Cheap"),
        MODERATE("Moderate"),
        EXPENSIVE("Expensive");

        private final String code;

        PriceCategory(String code) {
            this.code = code;
        }

        public static PriceCategory findByCode(String code) {
            for (PriceCategory value : values()) {
                if (value.code.equalsIgnoreCase(code)) {
                    return value;
                }
            }
            return null;
        }
    }
}

EnumConverter 구현

package org.sight.jooqstart.config.converter;

import org.jooq.impl.EnumConverter;
import org.sight.jooqstart.film.FilmPriceSummary.PriceCategory;

public class PriceCategoryConverter 
    extends EnumConverter<String, PriceCategory> {
    
    public PriceCategoryConverter() {
        super(
            String.class,                   // FROM 타입
            PriceCategory.class,           // TO 타입
            PriceCategory::getCode         // Enum → String 변환 함수
        );
    }
}

EnumConverter의 동작 원리

public EnumConverter(
    Class<T> fromType,      // String.class
    Class<U> toType,        // PriceCategory.class
    Function<U, T> to       // PriceCategory::getCode
) {
    super(fromType, toType);
    this.to = to;
    this.lookup = new LinkedHashMap<>();

    // 모든 Enum 값을 순회하며 LookUp 테이블 생성
    for (U enumValue : toType.getEnumConstants()) {
        T key = to(enumValue);  // getCode() 호출
        if (key != null) {
            this.lookup.put(key, enumValue);
        }
    }
}

LookUp 테이블 생성 결과

{
    "Cheap"     → PriceCategory.CHEAP,
    "Moderate"  → PriceCategory.MODERATE,
    "Expensive" → PriceCategory.EXPENSIVE
}

DB에서 “Moderate”가 반환되면, LookUp 테이블에서 PriceCategory.MODERATE를 찾아 반환한다.

Repository에서 Converter 사용

public List<FilmPriceSummary> findFilmPriceSummaryByFilmTitle(String filmTitle) {
    final JInventory INVENTORY = JInventory.INVENTORY;

    return dslContext
            .select(
                FILM.FILM_ID,
                FILM.TITLE,
                FILM.RENTAL_RATE,
                case_()
                    .when(FILM.RENTAL_RATE.le(BigDecimal.valueOf(1.0)), "Cheap")
                    .when(FILM.RENTAL_RATE.le(BigDecimal.valueOf(3.0)), "Moderate")
                    .else_("Expensive")
                    .as("price_category")
                    .convert(new PriceCategoryConverter()),  // Converter 적용
                selectCount()
                    .from(INVENTORY)
                    .where(INVENTORY.FILM_ID.eq(FILM.FILM_ID))
                    .asField("total_inventory")
            )
            .from(FILM)
            .where(FILM.TITLE.like("%" + filmTitle + "%"))
            .fetchInto(FilmPriceSummary.class);
}

대안 – convertTo() 사용

.as("price_category")
.convertTo(PriceCategory.class, PriceCategory::getCode)

.convertTo()를 사용하면 별도의 Converter 클래스 없이 인라인으로 변환할 수 있지만, 가독성이 떨어지고 재사용이 어렵다

테스트

@Test
void Enum_Converter_테스트() {
    // when
    List<FilmPriceSummary> result = filmRepository
        .findFilmPriceSummaryByFilmTitle("EGG");

    // then
    assertThat(result).allSatisfy(summary -> {
        assertThat(summary.getPriceCategory())
            .isInstanceOf(FilmPriceSummary.PriceCategory.class);
        assertThat(summary.getPriceCategory())
            .isIn(
                FilmPriceSummary.PriceCategory.CHEAP,
                FilmPriceSummary.PriceCategory.MODERATE,
                FilmPriceSummary.PriceCategory.EXPENSIVE
            );
    });
}

실전 팁

서브쿼리 최적화

// N+1 문제 발생
films.forEach(film -> {
    Long count = getInventoryCount(film.getId());  // 개별 쿼리
});

// 스칼라 서브쿼리로 한 번에 조회
select(
    FILM.fields(),
    selectCount().from(INVENTORY)
        .where(INVENTORY.FILM_ID.eq(FILM.FILM_ID))
        .asField("inventory_count")
)

서브쿼리 재사용

// 공통 서브쿼리를 유틸리티로 추출
public class FilmSubqueries {
    public static Table<?> rentalDurationSubquery() {
        return select(...)
            .from(...)
            .groupBy(...)
            .asTable("rental_duration_info");
    }
}

// 여러 곳에서 재사용
.from(FILM)
.join(FilmSubqueries.rentalDurationSubquery())
    .on(...)

EXISTS vs IN

// EXISTS: 존재 여부만 확인 (성능 우수)
.whereExists(
    selectOne().from(TABLE).where(...)
)

// IN: 값 목록 비교
.where(FIELD.in(
    select(COLUMN).from(TABLE).where(...)
))

단순 존배 여부 확인은 EXISTS가 더 효율적이다

jOOQ의 서브쿼리와 Converter 기능

• 세 가지 서브쿼리: SELECT(asField), FROM(AsTable), WHERE(직접 사용)
• 타입 안전성: 컴파일 타임에 서브쿼리 오류 검증
• Enum 매핑: EnumConverter로 문자열 → Enum 자동 변환
• 재사용성: 서브쿼리를 변수/함수로 추출하여 재사용

출처 – 실전 jOOQ! Type Safe SQL with Java