GettingStarted_KO

freemarker-dynamic-ql-builder 시작하기

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 단 두개뿐인 가벼운 동적 쿼리 생성 툴이다.

최신 버전

Gradle 의존성 설정

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

Maven 의존성 설정

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

FreemarkerDynamicQlBuilder 객체 설정

먼저 프리마커 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(AbstractFreemarkerDynamicQlBuilderTest.class, "/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();

가장 간단한 Dynamic QL Template의 생성 살펴보기

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

위의 설정에서는 CLASSPATH 내의 /META-INF/dynamicqls 디렉토리를 템플릿 홈으로 지정하였으므로 /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.prepareCall(dynamicQuery.getQueryString());
dynamicQuery.bindParameters(psmt);

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

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

Parameter Binding

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

${param(변수)}를 통한 ? 출력과 파라미터 바인딩

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

${param(변수)}

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

?

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

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

JPQL/HQL Positional Parameter

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

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

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

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

${param(변수1)}, ${param(변수2)}, ${param(변수3)}

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

?1, ?2, ?3

문자열 직접출력

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

${변수}

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

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

Freemarker Template 지시자(Directive)

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

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

<@ql.where>

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>

<@ql.set>

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>

<@ql.trim>

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

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

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

IN 절의 처리

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>는 생략 가능하다. 자세한 것은 프리마커 문서를 참조한다.