GettingStarted_EN

Getting Started Freemarker Dynamic QL Builder

You can use freemarker-dynamic-ql-builder to build a query string(SQL, JPQL, HQL and etc.) dynamically and get the JDBC query binding parameters. In this document, QL means SQL, JPQL, HQL and etc.

After building QL and binding parameters' list/array, you can run the QL with Plain JDBC PreparedStatement or SpringFramework JdbcTemplate, JDBI, JPA, Hibernate and etc.

What Freemarker Dyanmic QL Builder DO

Building query string easily with Freemarker template
Building query binding parameters

What Freemarker Dynamic QL Builder DON'T DO

Executing queries
Mapping the query result to an object.

=> Use JDBC, Spring JdbcTemplate, JDBI, JPA, Hibernate and etc with Freemarker Dynamic QL Builder

Requirements

Java 6+
This library depends on only freemarker 2.3.23+ and slf4j.

Latest version

Dependency

Groovy

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>

Configure FreemarkerDynamicQlBuilder

First of all, you need to configure a freemarker's Configuration object, and create a FreemarkerDynamicQlBuilder object with the configuration.

You can register the final FreemarkerDynamicQlBuilder object as a SpringFramework's Bean.

FreemarkerDynamicQlBuilder objects are thread safe. But, you must not change the state of the freemarker's Configuration object after configuration completed.

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

// ...

// Configure freemarker what ever you want.
Configuration cfg = new Configuration(Configuration.VERSION_2_3_23);
cfg.setClassForTemplateLoading(this.getClass(), "/META-INF/dynamicqls");
cfg.setDefaultEncoding("UTF-8");

// set freemarker number format.
cfg.setNumberFormat("0.######");
// set template update check time period to 1 hour.
cfg.setTemplateUpdateDelayMilliseconds(3600000L); // ms 단위
// cache configuration for performance
cfg.setCacheStorage(new MruCacheStorage(500, 5000));

// create FreemarkerDynamicQlBuilder with the freemarker configuration
FreemarkerDynamicQlBuilder dynamicQlBuilder = new FreemarkerDynamicQlBuilderFactory(cfg)
		// etc...
		.getFreemarkerDynamicQlBuilder();

Simple Dynamic QL Template example

Dynamic QL freemarker template files must be under the directory which is configured in the freemarker Configuration object with file extension .ql.ftl.

In the previous configuraiton, template home directory was configured as /META-INF/dynamicqls in CLASSPATH, let's create /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

Now in Java code

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);
// you can get query string and query binding parameters from DynamicQuery object.

After running this code, you will get dynamicQuery object which has SQL string and JDBC binding parameters list/array that will be bound to JDBC positional parameters(question mark, ?).

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]

You can use thouse results with to execute query with PreparedStatement like the following

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

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

Of course, you can use SpringJdbcTemplate, JDBI, Hibernate, JPA and etc instead of plain JDBC PreapredStatement to execute the query.

Parameter Binding

The key feature of this library is not building SQL string but building JDBC binding parameters.

`${param(variable)}`

${param(variable)} is like #{variable} in MyBatis.

${param(variable)}

This statement just print a question mark(?) which is a prepared statement positional parameter.

At the same time, the variable's value is added to a List. After completing dynamic QL creation, DynamicQuery.getParameters() gives binding parameters as a java.util.List<Object> object also DynamicQuery.getParameterArray() gives the same data as an Object[] array.

Now you can bind the parameters List/Array to PreparedStatement.

DynaimcQuery.bindParameters(PreparedStatement) can bind parameters to PreparedStatement automatically.

JPQL/HQL Positional Parameter

JPA's JPQL and Hibernate's HQL positional parameters form is ?indexnumber. HQL support ? form but that is deperecated.

Set FreemarkerDynamicQlBuilder#buildQuery()'s last parameter (withPositionalIndex) to true the every ? will be followed by index numbers.

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

When you have the following template,

${param(firatVariable)}, ${param(secondVariable)}, ${param(thirdVariable)}

then the result is

?1, ?2, ?3

Output string directly

If you need to output the variable values directly, just use freemarker's default place holder form.

${variable}

But this is not good for SQL, because it can cause SQL Injection. Do it only when you know what you are doing.

Freemarker's default number format is like 123,456,789. You may want to set freemarker configuration's number format as 0..###### through cfg.setNumberFormat("0.######") then the numbers will be printed like 123456789.

Freemarker Custom Template Directives

You can use every freemarker directive. Please refer to Freemarker Manual.

Three custom directives are added for ease buliding QL. Those directives are inspired by MyBatis template elements.

`<@ql.where>`

<@ql.where> is same as the MyBatis' <where>. It creates WHERE clauses. When you have the following template, if the contents of <@ql.where> body after trimming, MyBatis의 <where>와 같은 역할을 한다. SQL의 동적 WHERE 조건 생성시 사용한다. 아래와 같은 구문이 있을 때 모든 앞 뒤 공백을 제거하고 지시자 안의 내용이

is not empty string, WHERE will be added to the head of contents, and the first appearing AND , and , OR , or will be removed.
is empty string, it will return just empty string.

<@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 = ?

The above example is just the same as the following example by <@ql.trim>

<@ql.trim prefix="WHERE " prefixOverrides=["AND ", "and ", "OR ", "or "]>
  -- etc..
</@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>는 생략 가능하다. 자세한 것은 프리마커 문서를 참조한다.

ParameterConverter

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

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 객체를 생성한다.

If you find any incorrect sentence or invalid information, please feel free to edit.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

GettingStarted_EN

Getting Started Freemarker Dynamic QL Builder

What Freemarker Dyanmic QL Builder DO

What Freemarker Dynamic QL Builder DON'T DO

Requirements

Latest version

Dependency

Groovy

Maven

Configure FreemarkerDynamicQlBuilder

Simple Dynamic QL Template example

Parameter Binding

`${param(variable)}`

JPQL/HQL Positional Parameter

Output string directly

Freemarker Custom Template Directives

`<@ql.where>`

`<@ql.set>`

`<@ql.trim>`

IN 절의 처리

ParameterConverter

FreemarkerDynamicQlBuilderFactory

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally