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

FreemarkerDynamicQlBuilder 객체 설정

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

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

FreemarkerDynamicQlBuilder objects are thread safe. But, you must not change the freemarker' 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

동적으로 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 등에 쿼리와 바인딩할 파라미터를 넘겨서 쿼리를 실행할 수도 있다.