[JOOQ] multiset

적용환경

• jdk 21
• Spring boot 3
• postgresql 15
• jookversion 3.19.14
• nu.studer.jooq 플러그인 버전 9.0 (3rd party)
• gradle

---

JOOQ를 사용하여 SQL 쿼리를 작성할 때, MULTISET 기능을 활용하면 관계형 데이터를 보다 직관적이고 성능 최적화된 방식으로 조회할 수 있습니다. 특히 1:N 관계 데이터를 서브쿼리 형태로 한번에 가져올 수 있어 다중 쿼리 실행을 줄이는 효과를 얻을 수 있습니다.

MULTISET 연산자란?
MULTISET 연산자는 여러 개의 1:N 관계 데이터를 계층적 구조로 집계(Aggregation)할 수 있도록 지원하는 jOOQ 기능입니다. 기존의 JOIN 방식과 달리, 각 자식 데이터를 그룹핑하여 부모 엔티티 안에 중첩된 리스트 형태로 표현할 수 있습니다.

✅ 장점:

효율적인 SQL 쿼리 생성: 불필요한 중복을 제거하여 성능 최적화
타입 안전성(Type Safety) 보장: 잘못된 쿼리는 컴파일 타임에 감지
간결한 코드 작성 가능: 복잡한 JOIN을 사용하지 않고도 직관적인 데이터 조회 가능

MULTISET을 활용한 서브쿼리 적용

예제에서 사용한 데이터베이스 테이블 입니다.

각 게시글과 해당 게시글의 댓글과 첨부파일을 함께 가져오려면 일반적으로 JOIN을 이용한 다중 쿼리를 실행하거나 별도의 API 호출을 해야 합니다. 하지만 MULTISET을 활용하면 단일 쿼리로 데이터를 가져올 수 있습니다.

📌 MULTISET 적용 예제 코드

List<BoardDtoV3> boardList = dslContext
        .select(
                BOARDS.ID,
                BOARDS.TITLE,
                BOARDS.CONTENT,
                BOARDS.CREATED_AT,
                BOARDS.UPDATED_AT,

                // 댓글
                DSL.multiset(
                                dslContext.select(
                                                COMMENTS.ID,
                                                COMMENTS.AUTHOR,
                                                COMMENTS.CONTENT,
                                                COMMENTS.CREATED_AT
                                        )
                                        .from(COMMENTS)
                                        .where(COMMENTS.BOARD_ID.eq(BOARDS.ID))
                                        .orderBy(COMMENTS.CREATED_AT)
                        )
                        .convertFrom(r -> r.into(CommentDto.class))
                        .as("comments"),

                // 첨부파일
                DSL.multiset(
                                dslContext.select(
                                                ATTACHMENTS.ID,
                                                ATTACHMENTS.FILE_URL
                                        )
                                        .from(ATTACHMENTS)
                                        .where(ATTACHMENTS.BOARD_ID.eq(BOARDS.ID))
                                        .orderBy(ATTACHMENTS.ID.desc())
                        )
                        .convertFrom(r -> r.into(AttachmentDto.class))
                        .as("attachments")
        )
        .from(BOARDS)
        .where(searchCondition)
        .orderBy(sortField)
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset())
        .fetchInto(BoardDtoV3.class);

📌 SQL 로그 (PostgreSQL)

select
  id,
  title,
  content,
  created_at,
  updated_at,
  (
    select coalesce(
      jsonb_agg(jsonb_build_array(v0, v1, v2, v3)),
      jsonb_build_array()
    )
    from (
      select
        public.comments.id as v0,
        public.comments.author as v1,
        public.comments.content as v2,
        public.comments.created_at as v3
      from public.comments
      where public.comments.board_id = public.boards.id
      order by v3
    ) as t
  ) as comments,
  (
    select coalesce(
      jsonb_agg(jsonb_build_array(v0, v1)),
      jsonb_build_array()
    )
    from (
      select
        public.attachments.id as v0,
        public.attachments.file_url as v1
      from public.attachments
      where public.attachments.board_id = public.boards.id
      order by v0 desc
    ) as t
  ) as attachments
from public.boards
where true
order by public.boards.created_at desc
offset 20 rows
fetch next 10 rows only

단일 쿼리로 1:N 관계 데이터를 한번에 가져옵니다.

서브쿼리 형태로 실행되기 때문에 테이블 별칭 설정이나 DISTINCT도 가능합니다.

var mr = MEDICAL_RECORD.as("mr");
var pd = PRESCRIBE_DRUG.as("pd");

DSL.multiset(
    dsl.selectDistinct(
            pd.MEDICINE_NAME,
            pd.DOSAGE,
            pd.DURATION
        )
        .from(pd)
        .join(mr)
        .on(pd.TREATMENT_ID.eq(mr.ID))
        .where(mr.PATIENT_ID.eq(patientId))
).convertFrom(r -> r.into(PrescribeDrugDto.class))
.as("prescribeDrugs")

✅ 정리

• 여러 개의 1:N 관계를 조회할 때 MULTISET을 사용하면 중복 없이 데이터를 그룹핑 가능
• 타입 안전성(Type Safety) 보장 → 컴파일 타임에 오류 감지 가능
• JOIN 방식보다 직관적이며, 계층 구조(JSON)와 유사한 데이터 반환 가능

---

Github : https://github.com/whwp4151/spring-boot-jooq-sample