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.

• Building query string easily with Freemarker template
• Building query binding parameters

• Executing queries
• Mapping the query result to an object.

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

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

compile 'kr.pe.kwonnam.freemarkerdynamicqlbuilder:freemarker-dynamic-ql-builder:{version}'

<dependency>
    <groupId>kr.pe.kwonnam.freemarkerdynamicqlbuilder</groupId>
    <artifactId>freemarker-dynamic-ql-builder</artifactId>
    <version>{version}</version>
</dependency>

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();

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.

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

${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.

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

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.

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

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 converts one parameter value to another type or value.

Sometimes, a query parameter java object's type and a DB column's type are different.

For example, the query parameter is java.util.Date but DB column is varchar like 2015-12-25 or Java's Enum query parameters must be saved as ordinal or string or boolean query parameters must be converted to Y|N/T\F.

Or eventhough the types are the same, sometime the value must be changed. For example, when you java.util.Date object, you may want to save only the date part or only the time part.

You can implement kr.pe.kwonnam.freemarkerdynamicqlbuilder.paramconverter.ParameterConverter interface to make parameter object converters and register them to FreemarkerDynamicQlBuilder.

FreemarkerDynamicQlBuilder dynamicQlBuilder = 
    new FreemarkerDynamicQlBuilderFactory(freemarkerConfiguration)
        .addParameterConverter("booleanToYN", new BooleanToStringParameterConverter("Y", "N"))
        .addParameterConverter("dateToString", new DateToStringParameterConverter("yyyy-MM-dd HH:mm:ss"))
		// ....
        .getFreemarkerDynamicQlBuilder();

Then you can call like ${param(variable,"parameterConverterName")} in query templates, the variable will be converted by the specified converter.

${param(today, "dateToString")}
${param(areYouReady, "booleanToYN")}
==>
["2015-12-25 13:21:31", "Y"]

Basically the following ParameterConverters are available. You can use them after instantiating and registering to the FreemarkerDynamicQlBuilder instance.

• BooleanToNumberParameterConverter : boolean to number.
• BooleanToStringParameterConverter : boolean to String.
• DateToSqlDateParameterConverter : java.util.Date to java.sql.Date. Only date part is saved.
• DateToSqlTimeParameterConverter : java.util.Date to java.sql.Time. Only time part is saved.
• DateToStringParameterConverter : java.util.Date to String through a date format.
• EnumToNameParameterConverter : enum to the enum's name().
• EnumToOrdinalParameterConverter : enum to the enum's ordinal(). I DO NOT recommend saving ordinals.

FreemarkerDynamicQlBuilderFactory create FreemarkerDynamicQlBuilder instance with the following options.

• 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() : create FreemarkerDynamicQlBuilder instance.