Introduction

The mybatis-freemarker is a plugin that helps creating big dynamic SQL queries. You can use it selectively, to only queries that need if statmenets or foreach-loops, for example. But it is possible to use this syntax by default too.

If you are not familiar with FreeMarker syntax, you can view

• Template Language Reference

Install

If you are using maven, you can add this:

<dependency>
    <groupId>org.mybatis.scripting</groupId>
    <artifactId>mybatis-freemarker</artifactId>
    <version>1.3.0</version>
</dependency>

If you are using gradle, you can use this snippet:

dependencies {
    compile("org.mybatis.scripting:mybatis-freemarker:1.3.0")
}

Install from sources

• Checkout the source code
• Run mvn install to build and to automatically install it to your local maven repo
• Add maven dependency to your project

  
  <dependency>
    <groupId>org.mybatis.scripting</groupId>
    <artifactId>mybatis-freemarker</artifactId>
    <version>1.3.0</version>
  </dependency>

Configuration

Usage in annotations-driven mappers

Just write your queries using FreeMarker syntax:

@Lang(FreeMarkerLanguageDriver.class)
@Select("select * from names where id in (${ids?join(',')})")
List<Name> findNamesByIds(@Param("ids") List<Integer> ids);

If any whitespace found inside @Select text, it is interpreted as inline script, not template name. It is convenient to avoid creating templates when script is really small. If you have a large SQL script, you can place it in distinct template and write next code:

@Lang(FreeMarkerLanguageDriver.class)
@Select("findName.ftl")
Name findName(@Param("n") String name);

Template will be searched in classpath using basePackage property that has already been described above.

findName.ftl content can be:

SELECT *
FROM names
where firstName = <@p name="n"/>

<@p name="n"/> is a custom directive to generate #{n} markup. This markup further will be passed into MyBatis engine, and it will replace this to ?-parameter. You can't write #{paramName} directly, because FreeMarker supports this syntax natively (alghough it is deprecated). So, to get ?-parameters to prepared statements works, you need to use ${r"#{paramName}"} verbose syntax, or this directive. By the way, in XML files ${r"#{paramName}"} is more preferrable because you don't need wrap it using CDATA statements. In annotations and in external templates <@p/> directive is more neat.

Usage in XML-driven mappers

As in annotations, you can write inline scripts or template names.

<!-- This is handled by FreeMarker too, because it is included into select nodes AS IS -->
<sql id="cols">id, ${r"firstName"}, lastName</sql>

<select id="findName" resultType="org.mybatis.scripting.freemarker.Name" lang="freemarker">
    findName.ftl
</select>

<select id="findNamesByIds" resultType="org.mybatis.scripting.freemarker.Name" lang="freemarker">
    select <include refid="cols"/> from names where id in (${ids?join(',')})
</select>

<!-- It is not very convenient - to use CDATA blocks. Better is to create external template
    or use more verbose syntax: ${r"#{id}"}. -->
<select id="find" resultType="org.mybatis.scripting.freemarker.Name" lang="freemarker">
    select * from names where id = <![CDATA[ <@p name='id'/>]]> and id = ${id}
</select>

Prepared statements parameters

<@p/> directive can be used in two scenarios:

• To pass parameters to prepared statements AS IS: <@p name='id'/> (will be translated to #{id}, and value already presents in parameter object)
• To pass any value as prepared statements parameter <@p value=someValue/> will be converted to #{_p0}, and _p0 parameter will be automatically added to parameters map. It is convenient to use in loops like this:

  
  select * from names where firstName in (
      <#list ids as id>
          <@p value=id/>
          <#if id_has_next>,</#if>
      </#list>
  )

  This markup will be translated to

  select * from names where firstName in (#{_p0}, #{_p1}, #{_p2})

  and there are no need to care about escaping. All this stuff will be done automatically by JDBC driver. Unfortunately, you can't use this syntax if passing one object as parameter and without @Param annotation. The UnsupportedOperationException will be thrown. It is because appending additional parameters to some object in general is very hard. When you are using @Param annotated args, MyBatis will use Map to store parameters, and it is easy to add some generated params. So, if you want to use auto-generated prepared parameters, please don't forget about @Param annotation.

TemplateFilePathProvider

The TemplateFilePathProvider is SQL provider class that return the SQL template file path(Available since 1.2.0, and this class required to use on MyBatis 3.5.1+). This class use with SQL provider annotation(@InsertProvider, @UpdateProvider, @DeleteProvider and @SelectProvider) as follow:

package com.example.mapper;

public interface BaseMapper<T> {

  @Options(useGeneratedKeys = true, keyProperty = "id")
  @InsertProvider(type = TemplateFilePathProvider.class)
  void insert(T entity);

  @UpdateProvider(type = TemplateFilePathProvider.class)
  void update(T entity);

  @DeleteProvider(type = TemplateFilePathProvider.class)
  void delete(T entity);

  @SelectProvider(type = TemplateFilePathProvider.class)
  T findById(Integer id);

}

package com.example.mapper;

public interface NameMapper extends BaseMapper {

  @SelectProvider(type = TemplateFilePathProvider.class)
  List<Name> findByCondition(NameCondition condition);

}

By default implementation, a template file path resolve following format and priority order. If does not match all, it throw an exception that indicate not found a template file.

• com/example/mapper/NameMapper/NameMapper-{methodName}-{databaseId}.ftl
• com/example/mapper/NameMapper/NameMapper-{methodName}.ftl (fallback using default database)
• com/example/mapper/BaseMapper/BaseMapper-{methodName}-{databaseId}.ftl (fallback using declaring class of mapper method)
• com/example/mapper/BaseMapper/BaseMapper-{methodName}.ftl (fallback using declaring class of mapper method and default database)

If you want to customize the template file path format, you can customize using following configuration properties that start with templateFile.pathProvider.

• You can specify the prefix for adding to template file path. Default value is "".

  
  # 'template-file.path-provider.prefix' (kebab-case) can be used too
  templateFile.pathProvider.prefix = mapper
  

• You can specify whether includes package path part. Default value is true(= includes package path).

  
  # 'template-file.path-provider.includes-package-path' (kebab-case) can be used too
  templateFile.pathProvider.includesPackagePath = false
  

• You can specify whether separate directory per mapper. Default value is true(= separate directory per mapper).

  
  # 'template-file.path-provider.separate-directory-per-mapper' (kebab-case) can be used too
  templateFile.pathProvider.separateDirectoryPerMapper = false
  

• You can specify whether includes mapper name into file name when separate directory per mapper. Default value is true(= includes mapper name).

  
  # 'template-file.path-provider.includes-mapper-name-when-separate-directory' (kebab-case) can be used too
  templateFile.pathProvider.includesMapperNameWhenSeparateDirectory = false
  

• You can specify whether cache a resolved template file path. Default value is true(= cache a resolved template file path).

  
  # 'template-file.path-provider.cache-enabled' (kebab-case) can be used too
  templateFile.pathProvider.cacheEnabled = false
  

TIPS
Since the MyBatis 3.5.6, you can omit the type attribute at each annotations when specify the default provider class as global configuration as follow:
Mapper interface:

public interface NameMapper extends BaseMapper {
  @SelectProvider
  List<Name> findByCondition(NameCondition condition);
}

Configuration configuration = new Configuration();
configuration.setDefaultSqlProviderType(TemplateFilePathProvider.class);

<settings>
  <setting name="defaultSqlProviderType" value="org.mybatis.scripting.freemarker.support.TemplateFilePathProvider"/>
</settings>

Examples

You can view full-featured example of configuring and of both XML-mapper and annotations-driven mapper usage in test suite