[JOOQ-04] 좀 더 복잡한 질의 실행해보기

2019/03/05 - [Programming/JOOQ] - [JOOQ-03] 테스트 DB 을 생성하고 JOOQ 객체 생성하기

좀 더 복잡한 질의를 실행해보겠습니다.

저의 경우 JOOQ 으로 개발할 때에는 먼저 DBMS 에 직접 SQL 로 명령을 실행해본 뒤에 실제 본문을 작성해봅니다. JOOQ 는 Type Safe 등을 지원하긴 하지만, 이것이 올바른 질의인지는 판단하지 않기 때문입니다.

다음은 실제 질의해 본 것입니다.

  SELECT author.first_name, author.last_name, COUNT(*)
    FROM author
    JOIN book ON author.id = book.author_id
    JOIN `language` ON book.language_id = `language`.id
   WHERE `language`.cd = 'en'
     AND book.published_in > 1946
GROUP BY author.first_name, author.last_name
  HAVING COUNT(*) > 0
ORDER BY author.last_name ASC 
   LIMIT 1
  OFFSET 0

이렇게 정상적으로 실행되는 질의를 JOOQ 로 옮겨보겠습니다. 기존의 DAO 파일을 아래와 같이 수정합니다.

package com.zepinos.blog.jooq.dao;

import org.jooq.DSLContext;
import org.jooq.Record3;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.zepinos.blog.jooq.generated.tables.Author.AUTHOR;
import static com.zepinos.blog.jooq.generated.tables.Book.BOOK;
import static com.zepinos.blog.jooq.generated.tables.Language.LANGUAGE;
import static org.jooq.impl.DSL.count;

@Repository
public class JooqBoardDao {

	private final DSLContext create;
	private final JdbcTemplate jdbcTemplate;

	@Autowired
	public JooqBoardDao(DSLContext create, JdbcTemplate jdbcTemplate) {
		this.create = create;
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Map<String, Object>> list() {

		Result<Record3<String, String, Integer>> result =
				create
						.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
						.from(AUTHOR)
						.join(BOOK)
						.on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
						.join(LANGUAGE)
						.on(BOOK.LANGUAGE_ID.eq(LANGUAGE.ID))
						.where(LANGUAGE.CD.eq("en").and(BOOK.PUBLISHED_IN.gt(1946)))
						.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
						.having(count().gt(0))
						.orderBy(AUTHOR.LAST_NAME.asc())
						.limit(1)
						.offset(0)
						.fetch();

		List<Map<String, Object>> list = new ArrayList<>();

		if (result.size() > 0) {

			for (Record3<String, String, Integer> record3 : result) {

				Map<String, Object> map = new HashMap<>();
				map.put("firstName", record3.getValue(AUTHOR.FIRST_NAME));
				map.put("lastName", record3.getValue(AUTHOR.LAST_NAME));
				map.put("count", record3.get(2));

				list.add(map);

			}

		}

		return list;

	}

}

실행해보면 다음과 같이 웹브라우져에 출력됩니다.

결과는 제대로 나온 것 같습니다. 그럼 내부에서 어떤 질의를 만들어서 실행한 것일까요?

앞선 글에서는 질의를 생성하여 String 변수로 그 내용을 받은 뒤 Console 로 출력하였지만, 당연히 Slf4j 등으로 로그를 제어할 수 있게 되어 있습니다. 그래서 application.yml 파일에 아래와 같이 설정을 하면 생성된 질의와 함께 binding 된 변수 정보, 그리고 결과까지 한 번에 확인할 수 있습니다.

logging:
  level:
    org.jooq.tools.LoggerListener: debug

로그를 확인하면 아래와 같이 출력됩니다.

실제 생성된 질의는 아래와 같습니다.

select `jooq`.`author`.`first_name`, `jooq`.`author`.`last_name`, count(*) from `jooq`.`author` join `jooq`.`book` on `jooq`.`author`.`id` = `jooq`.`book`.`author_id` join `jooq`.`language` on `jooq`.`book`.`language_id` = `jooq`.`language`.`id` where (`jooq`.`language`.`cd` = 'en' and `jooq`.`book`.`published_in` > 1946) group by `jooq`.`author`.`first_name`, `jooq`.`author`.`last_name` having count(*) > 0 order by `jooq`.`author`.`last_name` asc limit 1

Java 으로 작성되었지만, 실제 질의와 흡사한 형태로 개발할 수 있다는 특징을 잘 보여주고 있습니다.

그리고, 위의 예제에서 count() 을 JOOQ 에서 선언할 경우 count(*) 로 질의를 생성한다는 것을 확인할 수 있습니다. 이를 위해 . as() 을 이용해 사용자가 별칭(Alias)을 선언할 수도 있습니다. 그 별칭에 String 뿐만 아니라 생성된 JOOQ Object 의 컬럼이나 Field 객체 등을 입력할 수 있기 때문에 String 으로 된 별칭으로 인해 Type Safe 나 오타에 대한 문제를 방지할 수도 있습니다.

2019/03/21 - [Programming/JOOQ] - [JOOQ-05] 객체(Object)에 질의 결과 저장하기