freemarker-dynamic-ql-builder 를 사용하면 Java에서 동적으로 SQL, JPQL, HQL 등을 생성하고 JDBC 쿼리 파라미터를 바인딩해줄 수 있다. 이제부터 나오는 QL은 SQL, JPQL, HQL 등을 의미한다. 이는 쿼리 문자열과 파라미터 목록 생성의 역할만 하고 쿼리의 실행은 다른 것에게 맡긴다.

이렇게 생성한 쿼리 문자열과 바인딩 파라미터 List(혹은 배열)을 Plain PreparedStatement나 SpringFramework JdbcTemplate, JDBI, JPA, Hibernate 등에서 Native 혹은 JPQL/HQL을 실행하는 용도로 사용할 수 있다.

• Java 6+
• 의존성이 freemarker 2.3.23+와 slf4j 단 두개뿐인 가벼운 동적 쿼리 생성 툴이다.

compile 'kr.pe.kwonnam.freemarkerdynamicqlbuilder:freemarker-dynamic-ql-builder:{version}'

<dependency>
    <groupId>kr.pe.kwonnam.freemarkerdynamicqlbuilder</groupId>
    <artifactId>freemarker-dynamic-ql-builder</artifactId>
    <version>{version}</version>
</dependency>

먼저 프리마커 Configuration 객체를 입맛대로 생성하고, 이를 사용하여 FreemarkerDynamicQlBuilder 객체를 생성한다.

최종 생성된 FreemarkerDynamicQlBuilder 객체를 SpringFramework Bean으로 등록해서 Spring과 함께 사용해도 된다.

FreemarkerDynamicQlBuilder는 Thread Safe 하다. 단 Configuration 객체를 한 번 설정한 이후 변경해서는 안된다.

import freemarker.template.Configuration;
import kr.pe.kwonnam.freemarkerdynamicqlbuilder.FreemarkerDynamicQlBuilder;
import kr.pe.kwonnam.freemarkerdynamicqlbuilder.FreemarkerDynamicQlBuilderFactory;

// ...

// 자신이 원하는 대로 Freemarker 설정을 해준다.
Configuration cfg = new Configuration(Configuration.VERSION_2_3_23);
cfg.setClassForTemplateLoading(this.getClass(), "/META-INF/dynamicqls");
cfg.setDefaultEncoding("UTF-8");

// 숫자형을 쉼표없이 숫자만 출력하게 포미팅
cfg.setNumberFormat("0.######");
// 템플릿 파일 변경 체크 주기를 1시간으로 설정
cfg.setTemplateUpdateDelayMilliseconds(3600000L); // ms 단위
// 성능 향상을 위한 캐시 설정
cfg.setCacheStorage(new MruCacheStorage(500, 5000));

// Freemarker 설정을 가지고 FreemarkerDynamicQlBuilder 객체를 생성한다.
FreemarkerDynamicQlBuilder dynamicQlBuilder = new FreemarkerDynamicQlBuilderFactory(cfg)
		// 중간에 기타 설정들..
		.getFreemarkerDynamicQlBuilder();

동적으로 QL을 생성하려면 프리마커 Configuration 에서 지정한 경로상에 디렉토리를 만들고 파일을 확장자가 .ql.ftl로 끝나게 생성해준다.

위의 설정에서는 CLASSPATH 내의 /META-INF/dynamicqls 디렉토리를 템플릿 Home으로 지정하였으므로 /META-INF/dynamicqls/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

이제 Java 코드상에서는

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 객체를 살펴보면 쿼리 실행에 필요한 SQL 문자열과, Positional Parameter(물음표, ?)에 바인딩 될 파라미터 객체들의 리스트 혹은 배열을 얻을 수 있게 된다.

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] 

이제 이것을 가지고 PreparedStatement에 배열을 for문 돌면서 바인딩하거나 아래와 같이 간편하게 바인딩 할 수 도 있다.

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

ResultSet rs = psmt.executeQuery();
// ...

Plain JDBC가 아닌 SpringJdbcTemplate이나 jDBI, Hibernate, JPA 등에 쿼리와 바인딩할 파라미터를 넘겨서 쿼리를 실행할 수도 있다.

동적 쿼리 생성의 핵심은 동적으로 문자열을 만드는 것이 아니라, 동적으로 생성된 문자열에 JDBC 쿼리 파라미터를 어떻게 매칭시키느냐에 있다.

MyBatis에서 #{변수} 역할을 하는 것이 여기서 ${param(변수)}이다.

${param(변수)}

위 구문은 실제로는 Prepared Statement Positional Parameter인 물음표(?) 하나만 출력한다.

?

하지만, 이와 동시에 변수의 값이 순서대로 저장되어 동적 QL 생성이 종료되면 DynamicQuery.getParameters() 로는 java.util.List<Object> 객체 형태를, DynamicQuery.getParameterArray()로는 동일 데이터를 객체 배열(Object[]) 형태를 얻을 수 있게 된다. 이렇게 생성된 파라미터 리스트/배열을 가지고 PreparedStatement 에 순서대로 파라미터로 지정하면 된다.

Plain JDBC PreparedStatement는 DynaimcQuery.bindParameters(PreparedStatement) 메소드를 통해 바인딩을 자동으로 해줄 수도 있다.

JPA의 JPQL과 Hibernate의 HQL은 ?인덱스 형태로 파라미터를 지정하는 것이 표준이다. HQL은 ?도 지원하긴 하지만 앞으로 해당 파라미터 형태는 삭제할 예정이라고 한다.

FreemarkerDynamicQlBuilder#buildQuery()의 마지막 인자(withPositionalIndex)를 true로 지정하면 모든 파라미터 뒤에 숫자가 붙게 된다.

DynamicQuery dynamicQuery = 
    dynamicQlBuilder.buildQuery(queryTemplateName, dataModel, true);

예를 들어 다음과 같이 템플릿을 작성하였다면

${param(변수하나)}, ${param(변수둘)}, ${param(변수셋)}

출력된 결과는 다음과 같은 형태가 된다.

?1, ?2, ?3

파라미터 바인딩을 하지 않고 변수의 문자열 형태를 직접 출력하는 것은 프리마커의 기본 변수 출력 방식을 사용한다.

${변수}

변수의 내용이 문자열로 있는 그대로 출력된다. 물론 이 경우, SQL Injection 등의 위험이 있기 때문에 철저하게 검증된 데이터에 한해서만 이 방식을 사용해야 한다. 보통의 경우에는 사용해서는 안된다.

프리마커 Configuration 객체 설정시에 cfg.setNumberFormat("0.######")을 호출하지 않으면 숫자가 쉼표가 찍힌 형태(123,456,789)로 출력되어 SQL 문법에 어긋나게 되므로 매우 주의해야 한다.

기본적으로 당연히 모든 프리마커 의 템플릿 구문을 사용할 수 있다. 이에 관해서는 Freemarker Manual 을 참조한다.

프리마커의 기본 지시자 외에도, MyBatis의 지시자들을 흉내내어 쿼리 작성을 편리하게 도와주도록 하였다.

MyBatis의 <where>와 같은 역할을 한다. SQL의 동적 WHERE 조건 생성시 사용한다. 아래와 같은 구문이 있을 때 모든 앞 뒤 공백을 제거하고 지시자 안의 내용이

• 공백이 아닌 문자열을 포함한다면 WHERE 를 추가하고 처음 나오는 AND , and , OR , or 를 삭제한다.
• 모두 공백(빈칸, 새줄기호 등)이라면 아무것도 출력하지 않는다.

<@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>
</@ql.where>

==>
WHERE birthyear = ?
    AND employeeType = ?

이 내용은 <@ql.trim>으로 할 경우 다음과 동일하다.

<@ql.trim prefix="WHERE " prefixOverrides=["AND ", "and ", "OR ", "or "]>
  기타 내용들
</@ql.trim>

MyBatis의 <set>와 같은 역할을 한다. SQL의 동적 UPDATE문 생성시 함께 사용한다. 아래와 같은 구문이 있을 때 모든 앞 뒤 공백을 제거하고 지시자 안의 내용이

• 공백이 아닌 문자열을 포함한다면 SET 를 추가하고 공백을 제외하고 맨 마지막에 나오는 쉼표(,)를 제거한다.
• 모두 공백(빈칸, 새줄기호 등)이라면 아무것도 출력하지 않는다.

UPDATE sometable
<@ql.set>
    <#if user.name??>name = ${param(user.name)},</#if>
    <#if user.birthyear gt 0>birthyear = ${param(user.birthyear)},</#if>
    <#if user.employeeType??>employeeType = ${para(user.employyType)}</#if>
</@ql.set>

==>
UPDATE sometable
SET name = ?,
    birthyear = ?

이 내용은 <@ql.trim>으로 할 경우 다음과 동일하다.

UPDATE sometable
<@ql.trim prefix="SET " suffixOverrides=[","]>
  기타 내용들
</@ql.trim>

MyBatis의 <trim>과 같은 역할을 한다. 범용으로 사용할 수 있다.

• 기본적으로는 해당 지시자 않에 생성된 문자열의 앞뒤 공백을 모두 제거한다.
• prefix="문자열" : trim 결과가 공백이 아닌 문자열을 포함할 경우, 생성된 문자열 맨 앞에 해당 문자열을 추가한다.
• suffix="문자열" : trim 결과가 공백이 아닌 문자열을 포함할 경우, 생성된 문자열 맨 뒤에 해당 문자열을 추가한다.
• prefixOverrides=["문자열1", "문자열2", ...] : trim 결과가 공백이 아닌 문자열을 포함할 경우, 생성된 문자열의 맨 앞에 배열로 지정된 문자열들 중 하나가 나오면 이를 삭제한다. 첫번째 매칭만 삭제한다.
• suffixOverrides=["문자열1", "문자열2", ...] : trim 결과가 공백이 아닌 문자열을 포함할 경우, 생성된 문자열의 맨 뒤에 배열로 지정된 문자열들 중 하나가 나오면 이를 삭제한다. 첫번째 매칭만 삭제한다.

예제는 <@ql.where>와 <@ql.set> 참조.

MyBatis에는 foreach 구문이 있는데, Freemarker는 이미 이와 같은 역할을 할 수 있는 훨씬 더 유연한 지시자가 존재한다. 그래서 foreach는 따로 만들지 않고 다음과 같이 구현하면 된다.

userIds = new int[] {1,2,3,4,5} 라고 할 때,

WHERE
<#list userIds!>
user_id in (<#items as userId>${param(userId)}<#sep>,</#sep></#items>)
<#else>
user_id IS NOT NULL
</#list>

userIds에 값이 존재하면 다음과 같이 된다.

WHERE
user_id in (?, ?, ?, ?, ?)

userIds가 null이거나 빈 List 혹은 배열이면 <#else> 구문뒤가 실행된다.

WHERE
user_id IS NOT NULL

물론 <#else>는 생략 가능하다. 자세한 것은 프리마커 문서를 참조한다.

ParameterConverter는 파라미터 객체의 값을 다른 타입 혹은 값으로 변경하는 기능을 한다.

객체를 쿼리의 파라미터로 넘길 때 때로는 Java 의 객체 Type과 DB의 Column Type이 다른 경우가 있다.

예를들면 Java Date 객체이지만 DB Column은 2015/12/25 같은 형태의 문자열일 수도 있고, Java Enum을 문자 혹은 숫자로 저장하고 싶거나, Boolean을 Y|N 혹은 T|F 로 저장할 때도 있다.

혹은 동일 객체라 하더라도 데이터의 값을 변경하길 원할 때도 있다. 예를들면 Date객체이지만 어떨 때는 날짜 부분만, 어떨 때는 시간 부분만 파라미터로 넘겨야 할 때도 있다.

이러한 역할을 하는 객체 변환기를 kr.pe.kwonnam.freemarkerdynamicqlbuilder.paramconverter.ParameterConverter 인터페이스를 구현하여 만들고 FreemarkerDynamicQlBuilder 생성 설정시에 등록해주면,

FreemarkerDynamicQlBuilder dynamicQlBuilder = 
    new FreemarkerDynamicQlBuilderFactory(freemarkerConfiguration)
        .addParameterConverter("booleanToYN", new BooleanToStringParameterConverter("Y", "N"))
        .addParameterConverter("dateToString", new DateToStringParameterConverter("yyyy-MM-dd HH:mm:ss"))
		// ....
        .getFreemarkerDynamicQlBuilder();

이제 템플릿에서 다음처럼 ${param(변수,"parameterConverterName")} 으로 호출해주면 변수가 컨버터를 거쳐 변환된 상태로 파라미터 목록에 저장되게 된다.

${param(today, "dateToString")}
${param(areYouReady, "booleanToYN")}
==>
["2015-12-25 13:21:31", "Y"]

기본적으로 이미 다음과 같은 ParameterConverter들을 미리 만들어 두었다. 객체를 생성하여 등록만 해주면 사용할 수 있다.

• BooleanToNumberParameterConverter : boolean을 숫자로 변경한다.
• BooleanToStringParameterConverter : boolean을 문자열로 변경한다.
• DateToSqlDateParameterConverter : java.util.Date를 java.sql.Date 타입으로 변경한다. 날짜 부분만 저장한다.
• DateToSqlTimeParameterConverter : java.util.Date를 java.sql.Time 타입으로 변경한다. 시간 부분만 저장한다.
• DateToStringParameterConverter : java.util.Date를 문자열로 변경한다. 문자열 포맷을 지정해줘야 한다.
• EnumToNameParameterConverter : enum을 해당 name() 문자열로 변경한다.
• EnumToOrdinalParameterConverter : enum을 해당 ordinal() 숫자로 변경한다. enum의 oridnal을 저장하는 것은 추후 해당 enum의 순서 변경이 생기면 버그가 되기 때문에 기본적으로는 해서는 안된다. 정말 필요한지 생각해보고 부득이한 경우에만 DB에 ordinal을 저장해야 한다.

FreemarkerDynamicQlBuilderFactory를 통해 FreemarkerDynamicQlBuilder 객체를 생성할 때 줄 수 있는 옵션들을 살펴본다.

• qlDirectivePrefix("문자열") : <@ql.trim> 등의 사용자 정의 지시자의 prefix를 지정한다. 기본값은 ql이다. 템플릿 생성 dataModel Map에 여기 지정된 값을 Key로 하는 데이터는 넣을 수 없다.
• paramMethodName("문자열") : ${param()}의 param 이름을 원하는 대로 지정한다. 기본값은 param이다. 템플릿 생성 dataModel Map에 여기 지정된 값을 Key로 하는 데이터는 넣을 수 없다.
• queryTemplateNamePostfix("문자열") : 프리마커 템플릿 파일을 찾을 때 뒤에 붙을 확장자를 지정한다. 기본값은 .ql.ftl이다. 템플릿 이름 지정시 이 부분은 빼고 지정한다.
• templateModelObjectUnwrapper(TemplateModelObjectUnwrapper객체) : ${param()}을 호출하면 Freemarker는 실제 값 객체가 아닌 Freemarker 전용 TemplateModel이라는 인터페이스를 구현한 객체로 감싸서 값을 넘겨 준다. 여기서 실제 값을 구할 수 있는 Unwrapper를 직접 지정할 수 있다. 기본값은 TemplateModelObjectUnwrapperDefaultImpl이다. 기본값을 그냥 두는 것이 좋다.
• addParameterConverter("컨버터이름", ParameterConverter객체) : ParameterConverter를 등록한다.
• addAllParameterConverters(Map<String, ParameterConverter>객체) : 한번에 모든 ParameterConverter를 등록한다.
• clearParameterConverters() : 등록된 모든 ParameterConverter를 삭제한다.
• getFreemarkerDynamicQlBuilder() : 설정에 따라 FreemarkerDynamicQlBuilder 객체를 생성한다.