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.