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 the same as MyBatis' <where>. It creates WHERE clauses. When you have the following template, if the contents of <@ql.where> body after trimming,

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 "]>
  -- ...
</@ql.trim>

`<@ql.set>`

<@ql.set> is the same as MyBatis' <set>. You can use this when you create UPDATE clauses. When you have the following template, if the contents of <@ql.set> body after trimming,

is NOT EMPTY string, SET will be added to the head of contents and the first appearing comma(,) in the tail of the contents will be removed.
is EMPTY string, it will return just empty string.

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

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

UPDATE sometable
<@ql.trim prefix="SET " suffixOverrides=[","]>
  -- ...
</@ql.trim>

`<@ql.trim>`

<@ql.trim> is the same as MyBatis' <trim>.

Basically, this trims the body string(removing heading/tailing spaces).
If the result of trimming is EMPTY, it will return just empty string.
prefix="str" : If the result of trimming is NOT EMPTY, prefix "str" to the result.
suffix="str" : If the result of trim is NOT EMPTY, suffix "str" to the result.
prefixOverrides=["str1", "str2", ...] : If the result of trimming is NOT EMPTY, the first appearing parameter string in the head of the result will be removed.
suffixOverrides=["str1", "str2", ...] : If the result of trimming is NOT EMPTY, the first appearing parameter string in the tail of the result will be removed.

You can refer to the examples of <@ql.where> and <@ql.set>.

building `IN` clause

MyBatis has foreach directive, but I don't add this kind custom directive because Freemarker already has more flexible directives. You can build IN clause like the following,

When you have 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>

if userIds are not empty,

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

if userIds is null or empty List/array, the <#else> will be processed.

WHERE
user_id IS NOT NULL

<#else> can be omitted.

Please refer to the freemarker document.

ParameterConverter

ParameterConverter converts one parameter value to another type or value.

객체를 쿼리의 파라미터로 넘길 때 때로는 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("string") : set custom directives' prefix like <@ql.trim> and etc. ql is the default value. You cannot add this value as a key to the template dataModel Map.
paramMethodName("string") : set ${param()}'s param name. param is the default value. You cannot add this value as a key to the template dataModel Map.
queryTemplateNamePostfix("string") : set the freemarker template file's default extension. .ql.ftl is the default value. When you call buildQuery("templateName", dataModel), you must not suffix this extension to the template name argument.
templateModelObjectUnwrapper(TemplateModelObjectUnwrapper) : When you call ${param()}, Freemarker gives the value wrapped with a TemplateModel instance. TemplateModelObjectUnwrapper unwraps the value. TemplateModelObjectUnwrapperDefaultImpl is the default value. I recommend the default.
addParameterConverter("converterName", ParameterConverter) : register a parameter converter.
addAllParameterConverters(Map<String, ParameterConverter>) : register all parameter converters.
clearParameterConverters() : remove all registered parameter converters.
getFreemarkerDynamicQlBuilder() : build FreemarkerDynamicQlBuilder instance.

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>`

building `IN` clause

ParameterConverter

FreemarkerDynamicQlBuilderFactory

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally

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>

building IN clause

ParameterConverter

FreemarkerDynamicQlBuilderFactory

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally

`${param(variable)}`

`<@ql.where>`

`<@ql.set>`

`<@ql.trim>`

building `IN` clause