동적 Native SQL 생성 어떻게 할까 - Freemarker Dynamic QL Builder

나는 ORM 그중에서 JPA/Hibernate를 주로 사용하는 사람이다.

작년 이맘때쯤 꽤 오래된 DB 구조에 iBatis로 만들어진 프로젝트를 JPA로 전환하는 작업을 했는데, 그 와중에 딱 한 개의 쿼리가 도저히 Native SQL을 사용하지 않으면 안되는데, 게다가 매우 동적이라 WHERE 조건문이 10여개 이상의 값의 상태에 따라 달라지게 구성되었다.

이 상황에서는 딱히 방법이 없어 문자열 조합으로 동적 Native SQL을 생성해서 JPA의 Native Query 기능으로 해결했다(JPA/Hibernate는 쿼리 실행은 Native SQL로 해도 그 결과를 JPA Entity로 받을 수 있다).
나 같은 경우에는 Native SQL로 실행하더라도 가급적 Entity로 결과를 받도록 하며, 그럴 펼요가 없는 경우의 쿼리는 그냥 Spring JdbcTemplate 같은 것을 사용한다.

이 프로젝트를 겪으면서 수 많은 종류의 쿼리 중에서 Native SQL은 단 한 개만 필요했다(호출량은 별개의 문제이다). 많은 사람들이 Legacy DB에 ORM을 적용하면 Native SQL을 사용할 일이 엄청 많을 것이라고 지레 짐작하는데 실제로 해보면 별로 그렇지 않다. 현재까지 우리 회사에서 진행된 프로젝트들을 보면 Native SQL이 필요한 경우는 1% 미만이거나 아예 Native SQL을 사용하지 않는 프로젝트도 있는 것으로 보인다.

어쨌든, 매우 동적인 Native SQL이 필요한 상황이 온다고 했을 때, iBatis/MyBatis 없이 현재 있는 솔루션(보통은 JPA/Hibernate와 Spring 기반 프로젝트의 경우 Spring JdbcTemplate이 기본으로 갖춰져 있다)으로 처리하고자 할 때 최대 방해요소는 문자열 연결로 난잡하기 짝이 없는 SQL을 생성해야 하는 것이다.

비록 확률은 적더라 이 문제를 근본적으로 해결하고, 혹여나 Legacy DB를 JPA/Hibernate로 전환하는 작업을 하는 사람들의 불안을 좀 없애줄 수 있는 방안을 1년 전부터 두가지 정도 구상했고 그 중 2번째 안을 무려 1년만에 먼저 구현했다(실제 핵심 logic 구현은 이틀 정도 밖에 안 걸리는 거였다.... 귀차니즘을 극복해야해..).

바로 Freemarker로 동적 SQL을 생성하는 것이다. Freemarker는 특정 포맷에 치우치지 않은 Java의 초강력 범용 문자열 템플릿 처리 엔진이다. MyBatis나 iBatis와는 비할바 없이 강력한 템플릿 기능을 지원한다. 동적으로 SQL 문자열 생성하는데 이보다 더 좋은게 있을까? 그리고 MyBatis가 갖는 장점-DBA와 협업할 때 파일 전달이 편하다는 점도 함께 이를 선택하게 하는데 작용했다.


이를 사용하여 Freemarker Dynamic QL Builder라는 것을 만들었다.

• Freemarker Dynamic QL Builder
• Freemarker Dynamic QL Builder 시작하기 : 그냥 이 문서로 점프해서 보는 것이..

이를 통해 다음과 같은 QL 템플릿('users/select.ql.ftl')을 만들고

SELECT *
FROM somewhere
<@ql.where>
    <#if user.name?has_content>
    name = ${param(user.name)}
    </#if>
    <#if user.birthyear gt 0>
    AND birthyear = ${param(user.birthyear)}
    </#if>
    <#if user.employeeType??>
    AND employeeType = ${param(user.employeeType, 'enumToName')}
    </#if>
    <#list userIds!>
    AND userId IN (<#items as userId>${param(userId)}<#sep>,</#sep></#items>)
    </#list>
</@ql.where>

ORDER BY userId
LIMIT 10

파라미터가 될 객체들을 넘겨서 실행해보면(iBatis/MyBatis 사용자들은 FreemarkerDynamicQlBuilder를 쿼리를 실행만 안하는 SqlMapClient라고 생각해보자)

FreemarkerDynamicQlBuilder dynamicQlBuilder = ....;

User user = new User();
user.setName(""); // empty on purpose
user.setBirthyear(2015);
user.setEmployeeType(EmployeeType.FULLTIME);

Map<String,Object> dataModel = new HashMap<String,Object>();
dataModel.put("user", user);
dataModel.put("userIds", new int[]{100, 200, 300});

DynamicQuery dynamicQuery = dynamicQlBuilder.buildQuery("users/select", dataModel);
// dynamicQuery 에 생성된 QL과 파라미터 목록이 들어 있다.

여기서 dynamicQuery 객체를 살펴보면 아래와 같은 프라퍼티들이 생성돼 있다.

dynamicQuery.getQueryString()
==> String
"SELECT *
FROM somewhere
WHERE birthyear = ?
    AND employeeType = ?
    AND userId IN (?,?,?)
ORDER BY userId
LIMIT 10"

dynamicQuery.getParameters()
==> List<Object> : [2015, FULLTIME, 100, 200, 300] 

dynamicQuery.getQueryParameterArray()
==> Object[] : [2015, FULLTIME, 100, 200, 300] 

이 결과를 보면 동적으로 SQL을 생성해줄 뿐만 아니라, PreparedStatement 파라미터 바인딩에 필요한 값들을 리스트와 배열로 순서대로 저장해서 넘겨주는 것을 볼 수 있다.
이렇게 생성된 SQL과 파라미터는 다음과 같이 PreparedStatement로 실행하거나 Spring JdbcTemplate, JDBI, JPA, Hibernate 등의 JDBC 실행/객체 매핑 프레임워크를 통해 실행하면 된다.

PreparedStatement psmt = connection.prepareStatement(dynamicQuery.getQueryString());
dynamicQuery.bindParameters(psmt);

ResultSet rs = psmt.executeQuery();
// 쿼리 결과 처리

${param(변수)}
Freemarker 템플릿을 보고 한 방에 눈치채신 분들도 있을 것 같은데, 동적인 쿼리 생성의 핵심은 문자열이 동적으로 생기는 것에 있는 것이 아니라는 점이다. 그렇게 동적으로 생성된 문자열 중 PreparedStatement 파라미터로 지정되어야 할 위치는 물음표(?)로 대체하고, 해당 물음표에 들어갈 실제 쿼리 파라미터를 순서대로 모아주는 것이 진짜 핵심이다.

그리고 그 핵심적인 역할을 하는 것이 바로 ${param(변수)}라는 이 프리마커 메소드의 역할이다. 이 메소드를 호출하면 그 부분은 물음표(?)로 문자열이 바뀌고 거기 지정한 변수는 순서대로 List에 저장되어 마지막에 전체 파라미터 List를 순서대로 얻을 수 있게 되는 것이다.
사실상 내가 구현한 것의 핵심은 이 ParamMethod 클래스 하나가 다이고, 나머지는 모두 부차적인 것들이라고 봐도 된다.

<@ql.trim>, <@ql.when>, <@ql.set>
MyBatis에 있는 템플릿 지시자들 중 SQL WHERE, SET 생성에 도움을 주는 <trim>,<where>,<set> 을 흉내낸 Freemarker 지시자를 만들었다. <foreach>는 프리마커가 이미 문법적으로 더 깔끔하게 지원하기 때문에 제외했다.

ParameterConverter
Legacy iBatis 프로젝트를 JPA로 전환하면서 나를 제일 당황케 한 것은 마로 매핑 객체에 있는 String의 향연이다.
Legacy DB의 컬럼들 중 많은 것들이 boolean을 문자열 "Y"/"N"으로 표현하고 날짜는 "20151126" 형태의 문자열로 돼 있다.
enum을 넣을 수 있지만, iBatis가 enum을 자동으로 문자열로 안바꾸는지 대부분 enum없이 그냥 일반 String으로 필드가 지정돼 있다. 이로 인해 문서를 보기 전에는 도대체 해당 필드에 유효한 값이 무엇인지 알수가 없다. 이런 특징으로 인해 코드의 아주 많은 부분이 문자열을 boolean, Date, enum 등의 타입으로 변환하고 또 역변환하는 것들로 채워져 있었다. 물론 변환시의 오류처리도 꼼꼼하게 하지 않으면...

JPA/Hibernate 는 Custom Type Converting 기능이 있기 때문에 여러 종류의 Java 객체를 DB 컬럼에 자유롭게 매핑할 수 있다. 그래서 Entity 객체가 올바른 Java 타입의 필드들로 자연스럽게 활용 가능하다.

올바른 Java 타입을 쉽게 사용하려면 동적 SQL을 생성했다 하더라도 거기에 파라미터로 바인딩되는 Java 객체를 DB Column에 맞게 변환해줄 필요가 있다. 이러한 역할을 하는 ParameterConverter라는 개념을 만들어서 이 문제를 넘어가게 했다. 이를 사용하면 Java Date 객체를 문자열로 변경해서 파라미터로 저장 한다던가 boolean을 "Y"/"N" 혹은 "T"/"F" 같은 것으로, 그 외에 JodaTime 같은 것으로 생성한 날짜 객체를 java.sql.Date 로 변환한다던가하는 일을 할 수 있게 된다.
위 FTL에서 ${param(user.employeeType, 'enumToName')} 이 부분이 파라미터 컨버터로 enum을 문자열로 변환한 것이다.

자세한 것은 문서를 참조하면 된다.

QL Builder?
왜 SQL Builder가 아니고 QL Builder일까?
SQL뿐만 아니라 JPQL(JPA), HQL(Hibernate)도 생성 가능하기 때문이다. JPQL의 ?1, ?2, ... 형태의 Positional Parameter 생성에 대해서는 문서를 참조한다.

주의점
앞서 Freemarker가 매우 강력하다고 했는데, 사실 나는 그런 이유로(그리고 기타 다른 몇가지 이유로) Freemarker를 HTML 템플릿 엔진으로는 사용하지 않는다.

프로그래밍 언어 코드가 아닌 특히 서버 프로그래머가 아닌 사람들과 함께 소통해야 하는 영역에 과도한 로직은 Unit Test 등도 사실상 불가능하게 만들고 유지보수성을 현저히 떨어뜨리게 된다.

따라서 Freemarker가 아무리 강력해도 지나치게 로직을 많이 사용하지는 않는 것이 좋다.

그에 반해 Freemarker의 include 같은 기능을 통해 column 목록 같은 반복되는 데이터를 따로 빼서 저장해놓고 include 하는식으로 개발하는 것도 가능하니 활용처를 찾아보는 것도 좋을 것 같다.

마지막으로, 이것을 만든 이유는 자유롭게 Native SQL을 사용하기 위해서가 아니다. 단순히 MyBatis의 1:1 대체재로 이를 사용하는 것은 좋지 않다고 본다. 가급적 ORM을 사용하고 ORM의 보완책으로 어쩔 수 없을 때만 사용하길 바란다.

조만간 원래 구상했던 1안도 구현해야겠다.

- Freemarker Dynamic QL Builder
- Freemarker Dynamic QL Builder 시작하기

원래 구상했던 1안 - 동적 Native SQL 생성 어떻게 할까 - 순수 Java 코드로 생성하기