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의 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 문법에 어긋나게 되므로 매우 주의해야 한다.