MyBatis logo

1. Overview

1.1. What is MyBatis Thymeleaf ?

The mybatis-thymeleaf is a plugin that helps applying the 2-way SQL/dynamic SQL feature to the MyBatis 3 using the template mechanism provided by Thymeleaf 3. If you are not familiar with MyBatis and Thymeleaf, you can see following official documentations.

1.2. What is 2-way SQL?

The 2-way SQL can be used by following two way.

  • It can be executed as-is in SQL execution tool (such as psql, mysql, sqlplus, plugins for IDE, etc…​)

  • It can be used as a SQL template for creating a bindable and dynamically SQL that can be parsed by MyBatis core module

1.2.1. Simple bindable 2-way SQL

The mybatis-thymeleaf support a simple bindable 2-way SQL as follow:

SQL Template
SELECT * FROM names
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/

1.2.2. Dynamically bindable 2-way SQL

The mybatis-thymeleaf support a dynamically bindable 2-way SQL as follow:

SQL Template
SELECT * FROM names
  WHERE 1 = 1
  /*[# th:if="${not #lists.isEmpty(ids)}"]*/
    AND id IN (/*[# mb:p="ids"]*/ 1 /*[/]*/)
  /*[/]*/
  ORDER BY id

1.2.3. Dynamically bindable SQL

The mybatis-thymeleaf support a dynamically bindable SQL(non 2-way SQL) as follow:

SQL Template
SELECT * FROM names
  WHERE 1 = 1
  [# th:if="${not #lists.isEmpty(ids)}"]
    AND id IN ([# mb:p="ids" /])
  [/]
  ORDER BY id

1.3. Mainly Features

The mybatis-thymeleaf provide following features using class that implements the LanguageDriver interface for integrating with template engine provide by Thymeleaf.

  • Can write 2-way SQL/dynamic SQL

  • Can use a 2-way SQL/dynamic SQL via an annotation and mapper xml

  • Can read an SQL template from a Thymeleaf template file on classpath

  • Can use a custom dialect(attribute tag and expression utility method) on your SQL template

  • Can fully customize a template engine configuration

2. Requirements

The mybatis-thymeleaf supports using on following Java and artifacts version.

2.1. Java

  • Java 8, Java 11+

About tested versions see the latest Travi CI build result.

2.2. MyBatis

  • MyBatis 3.4.3+

As basically policy, we do test using following versions.

  • latest release version on 3.5.x line

  • latest snapshot version on 3.5.x line

  • latest release version on 3.4.x line

2.3. Thymeleaf

  • Thymeleaf 3.0+

As basically policy, we do test using following versions.

  • latest release version on 3.0.x line

3. Getting Started

In this chapter, we explain basic usage of the mybatis-thymeleaf.

3.1. Installation

3.1.1. Maven

If you are using the Maven as build tool, you can add as follow:

pom.xml (dependencies)
<dependencies>
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version> <!-- Adjust to your application -->
  </dependency>
  <dependency>
    <groupId>org.mybatis.scripting</groupId>
    <artifactId>mybatis-thymeleaf</artifactId>
    <version>1.0.1</version>
  </dependency>
</dependencies>

If you use a snapshot version, you need add the configuration for using the "Sonatype OSS Snapshots Repository" as follow:

pom.xml (repositories)
<repositories>
  <repository>
    <id>sonatype-oss-snapshots</id>
    <name>Sonatype OSS Snapshots Repository</name>
    <url>https://oss.sonatype.org/content/repositories/snapshots</url>
  </repository>
</repositories>

3.1.2. Gradle

If you are using the Gradle as build tool, you can add as follow:

build.gradle (dependencies)
dependencies {
  compile("org.mybatis:mybatis:3.5.2") // Adjust version to your application
  compile("org.mybatis.scripting:mybatis-thymeleaf:1.0.1")
}

If you use a snapshot version, you need add the configuration for using the "Sonatype OSS Snapshots Repository" as follow:

build.gradle (repositories)
repositories {
  maven { url "https://oss.sonatype.org/content/repositories/snapshots" }
}

3.2. Configuration

Set the ThymeleafLanguageDriver as your default scripting language to MyBatis.

Configuration class (Java based configuration)
Configuration configuration = new Configuration();
configuration.setDefaultScriptingLanguage(ThymeleafLanguageDriver.class);
mybatis-config.xml (XML based configuration)
<settings>
  <setting name="defaultScriptingLanguage"
           value="org.mybatis.scripting.thymeleaf.ThymeleafLanguageDriver"/>
</settings>

3.3. Creating Mapper

The MyBatis provides two ways(annotation driven and XML driven) for creating a Mapper.

3.3.1. Annotation driven mapper

If you use the annotation driven mapper, you can specify an SQL as follow:

src/main/java/com/example/NameMapper.java
public class NameMapper {
  @Select("SELECT * FROM names WHERE id = /*[# mb:p='id']*/ 1 /*[/]*/")
  Name findById(Integer id);
}

You can specify a readable SQL on annotation using "Multiline String Literals" provided by some JVM language. About "Multiline String Literals", please see the Section 10.3, “Multiline String Literals”.

Also, you can specify an SQL on template file(e.g. NameMapper/findById.sql on class path) as follow:

@Select("NameMapper/findById.sql")
Name findById(Integer id);

By default behavior, the mybatis-thymeleaf read a specified template file from just under classpath.

src/main/resources/NameMapper/findById.sql
SELECT * FROM names
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/

Since 1.0.1, we support to read a template file from under classpath automatically using the TemplateFilePathProvider.

3.3.2. XML driven mapper

If you use the XML driven mapper, you can specify an SQL on mapper XML file as follow:

src/main/java/com/example/NameMapper.java
public class NameMapper {
  Name findById(Integer id);
}
src/main/resources/com/example/NameMapper.xml
<mapper namespace="com.example..NameMapper">
  <select id="findById" resultType="com.example.Name">
    <![CDATA[
    SELECT * FROM names
      WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
    ]]>
  </select>
</mapper>

Also, you can specify an SQL on template file (see 'Annotation driven mapper' section) as follow:

<select id="findById" resultType="com.example.Name">
  NameMapper/findById.sql
</select>

4. Configuration

In this chapter, we explain a way for applying mybatis-thymeleaf to the MyBatis in detail. About MyBatis core module configuration, please see the MyBatis reference document.

4.1. Basic configuration

You configure to use the org.mybatis.scripting.thymeleaf.ThymeleafLanguageDriver as scripting language driver.

4.1.1. Java based configuration

Configuration class
Configuration configuration = new Configuration();
configuration.setDefaultScriptingLanguage(ThymeleafLanguageDriver.class); (1)
// ...
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
1 Set the ThymeleafLanguageDriver class to a Configuration instance as default scripting language driver

4.1.2. XML based configuration

src/main/resources/mybatis-config.xml
<settings>
  <setting name="defaultScriptingLanguage"
           value="org.mybatis.scripting.thymeleaf.ThymeleafLanguageDriver"/> (1)
</settings>
SqlSessionFactory sqlSessionFactory;
try (Reader configReader = Resources.getResourceAsReader("mybatis-config.xml")) {
  sqlSessionFactory = new SqlSessionFactoryBuilder().build(configReader);
}
1 Set the ThymeleafLanguageDriver class to the defaultScriptingLanguage of setting item in configuration XML file

4.2. Customizing configuration

The mybatis-thymeleaf provides three ways for customizing template engine configuration.

4.2.1. Using properties file

The mybatis-thymeleaf provide the special properties file for customizing default configuration. By default behavior, the mybatis-thymeleaf load the mybatis-thymeleaf.properties stored just under classpath. About supported properties, please see the Section 10.1, “Configuration properties”.

Using an any properties file

You can use an any properties file instead of the default properties file. If you use an any properties file, please specify a properties file using the special system properties(mybatis-thymeleaf.config.file) as follow:

$ java -Dmybatis-thymeleaf.config.file=mybatis-thymeleaf_production.properties ...
Using an any file encoding

You can use an any file encoding instead of the default file encoding(UTF-8). If you use an any file encoding, please specify a file encoding using the special system properties(mybatis-thymeleaf.config.encoding) as follow:

$ java -Dmybatis-thymeleaf.config.encoding=Windows-31J ...

4.2.2. Using functional interface

The mybatis-thymeleaf provide the special functional interface(org.mybatis.scripting.thymeleaf.TemplateEngineCustomizer) for customizing configurations using Java code. This feature provide an opportunity to customize configuration that cannot customize it using properties file.

src/main/java/com/example/MyTemplateEngineCustomizer.java
public class MyTemplateEngineCustomizer implements TemplateEngineCustomizer {
  @Override
  public void customize(TemplateEngine defaultTemplateEngine) {
    // ... (1)
  }
}
src/main/resources/mybatis-thymeleaf.properties
customizer = com.example.MyTemplateEngineCustomizer (2)
1 Write a Java code for customizing template engine
2 Specify a FQCN of customizer class in mybatis-thymeleaf.properties

4.2.3. Using user-defined template engine

When your application requirements cannot be satisfied using above customizing features, you can apply a user-defined template engine(full managed template engine) to the mybatis-thymeleaf as follow:

Configuration class
TemplateEngine templateEngine = new TemplateEngine(); (1)
templateEngine.addDialect(new MyBatisDialect());
templateEngine.setEngineContextFactory(new MyBatisIntegratingEngineContextFactory(
    templateEngine.getEngineContextFactory()));
// ...

Configuration configuration = new Configuration();
configuration.getLanguageRegistry().register(new ThymeleafLanguageDriver(templateEngine)); (2)
configuration.setDefaultScriptingLanguage(ThymeleafLanguageDriver.class); (3)
1 Create an instance of class that implements org.thymeleaf.ITemplateEngine
2 Register an instance of ThymeleafLanguageDriver that associate with user-defined template engine instance
3 Set the ThymeleafLanguageDriver class as default scripting language driver

5. Writing 2-way SQL

In this section, we explain standard usage of 2-way SQL.

About cautions for usage 2-way SQL, please see the Section 9, “Cautions for usage”.

5.1. Binding value

The mybatis-thymeleaf use the default binding feature provided by MyBatis core module. Therefore, you need to write a 2-way SQL template for generating string that can be parsed by MyBatis core module as follow:

About default binding feature provided by MyBatis
SELECT * FROM names
  WHERE id = #{id} (1)
SQL template for generating string that can be parsed by MyBatis core module
SELECT * FROM names
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/ (2)
1 A bind value is specified by #{variable name} format
2 Can render bind variable expression that can be parsed by MyBatis core module using custom attribute tag(mb:p="variable name") provided by mybatis-thymeleaf

What can you bind?

Basically, you can bind a parameter object(Mapper method arguments or SqlSession method arguments). In addition, the mybatis-thymeleaf allow to be bind a registered value using mb:bind tag. About usage of mb:bind, please see Section 7.1, “Attribute tag”.

5.2. Dynamic SQL

The Thymeleaf supports to create an any string dynamically using conditional evaluation and iterating evaluation feature. By using this feature, you can write a dynamic SQL.

5.2.1. Using tag for specifying condition

If you add a SQL part when any condition is matches or not, you can use following attribute tags.

  • th:if

  • th:unless (denial version for th:if)

  • th:switch

  • th:case

Usage of conditional attribute tag on WHERE
SELECT * FROM names
  WHERE 1 = 1 (1)
  /*[# th:if="${firstName} != null"]*/ (2)
    AND firstName = /*[# mb:p="firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/ (3)
  ORDER BY id
Usage of conditional attribute tag on SET
UPDATE names
  SET id = id (4)
  /*[# th:if="${firstName} != null"]*/
    , firstName = /*[# mb:p="firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
1 Specify a non-dynamic condition at first position because the mybatis-thymeleaf does not provide the trimming feature such as <where> of XML based scripting language
2 Specify a condition on start tag using natural template
3 Specify an end tag of condition
4 Specify a non-dynamic updating column at first position because the mybatis-thymeleaf does not provide the trimming feature such as <set> of XML based scripting language

5.2.2. Using tag for iteration

The Thymeleaf supports to process for iteration object(List etc..) using th:each.

Basic usage of iteration tag
SELECT * FROM names
  WHERE 1 = 1
  /*[# th:if="${not #lists.isEmpty(ids)}"]*/
    AND id IN (
    /*[# th:each="id : ${ids}"]*/ (1)
      /*[# mb:p="id"]*/ 1 /*[/]*/ (2)
      /*[(${idStat.last} ? '' : ',')]*/ (3)
    /*[/]*/ (4)
    )
  /*[/]*/
  ORDER BY id
1 Specify an iterable object on th:each
2 Specify a SQL template of binding value per iterable element. A bind value specify by mb:p="{variable name of iterable element}" format (e.g. id).
3 Append comma character when element position is not last. You can access an iteration status object (IterationStatusVar) that named by "{variable name of iterable element}Stat" format (e.g. idStat).
4 Specify an end tag of iteration

An above SQL template can be replaced using mb:p attribute tag with following SQL template.

Use mb:p for creating bind variables string of IN clause
SELECT * FROM names
  WHERE 1 = 1
  /*[# th:if="${not #lists.isEmpty(ids)}"]*/
    AND id IN (/*[# mb:p="ids"]*/ 1 /*[/]*/)
  /*[/]*/
  ORDER BY id

About more advanced usage of th:each , please see Section 10.2, “Bulk insert”.

5.3. Fragment

The Thymeleaf supports to insert template string from an another template file. By using this feature, you can share an SQL on multiple SQL template.

The standard use case using this feature is paging query as follow:

Mapper interface
// Count a total record number that matches for criteria
@Select("NameMapper/countByCriteria.sql")
long countByCriteria(@Param("criteria") NameCriteria criteria);

// Search records that matches for criteria and specified page
@Select("NameMapper/findPageByCriteria.sql")
List<Name> findPageByCriteria(@Param("criteria") NameCriteria criteria, @Param("pageable") Pageable pageable);
src/main/resources/NameMapper/countByCriteria.sql
SELECT COUNT(*) FROM names
  WHERE 1 = 1
  /*[# th:if="${criteria.firstName} != null"]*/
    AND firstName = /*[# mb:p="criteria.firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/
  /*[# th:if="${criteria.lastName} != null"]*/
    AND lastName = /*[# mb:p="criteria.lastName"]*/ 'Yamada' /*[/]*/
  /*[/]*/
src/main/resources/NameMapper/findPageByCriteria.sql
SELECT * FROM names
  WHERE 1 = 1
  /*[# th:if="${criteria.firstName} != null"]*/
    AND firstName = /*[# mb:p="criteria.firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/
  /*[# th:if="${criteria.lastName} != null"]*/
    AND lastName = /*[# mb:p="criteria.lastName"]*/ 'Yamada' /*[/]*/
  /*[/]*/
  LIMIT /*[# mb:p="pageable.pageSize"]*/ 20 /*[/]*/
  OFFSET /*[# mb:p="pageable.offset"]*/ 0 /*[/]*/
  ORDER BY id

Probably looking at above SQLs many developers will look for a way to share the WHERE clause. In such case, you can share any SQL part by multiple SQL using fragment feature.

5.3.1. Creating a fragment SQL

At first, you create a fragment SQL template file for sharing by multiple SQL as follow:

src/main/resources/NameMapper/whereByCriteria.sql
/*[- */ (1)
SELECT * FROM names
/* -]*/ (2)
  WHERE 1 = 1
  /*[# th:if="${criteria.firstName} != null"]*/
    AND firstName = /*[# mb:p="criteria.firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/
  /*[# th:if="${criteria.lastName} != null"]*/
    AND lastName = /*[# mb:p="criteria.lastName"]*/ 'Yamada' /*[/]*/
  /*[/]*/
1 Start a block comment for ignoring "SELECT * FROM names" part at template processing time
2 End a block comment

5.3.2. Insert a fragment SQL

You can insert a fragment SQL template file on each template as follow:

src/main/resources/NameMapper/countByCriteria.sql
SELECT COUNT(*) FROM names
  /*[# th:insert="~{NameMapper/whereByCriteria.sql}" /]*/ (1)
src/main/resources/NameMapper/findPageByCriteria.sql
SELECT * FROM names
  /*[# th:insert="~{NameMapper/whereByCriteria.sql}" /]*/ (1)
  LIMIT /*[# mb:p="pageable.pageSize"]*/ 20 /*[/]*/
  OFFSET /*[# mb:p="pageable.offset"]*/ 0 /*[/]*/
  ORDER BY id
1 Insert a fragment SQL template file

5.4. Special variables

The MyBatis core module provides special variables that prefixed with _ as follows:

Table 1. Special variables
Variable Name Description Type

_parameter

The parameter object that passed to the MyBatis

Any type

_databaseId

The id for identifying the database on current session (If you want to this variable, you should be enabled the DatabaseIdProvider feature on MyBatis)

String

5.5. Using configuration properties

You can access the configuration properties of MyBatis from your SQL template. About configuration properties, please see the MyBatis reference documentation.

How to set configuration properties using Java based configuration
Configuration configuration = new Configuration();
Properties variables = new Properties();
variables.setProperty("tableNameOfUser", "accounts"); (1)
configuration.setVariables(variables);
How to set configuration properties using XML based configuration (mybatis-config.xml)
<properties>
  <property name="tableNameOfUser" value="accounts"/> (1)
</properties>
SQL template
SELECT * FROM /*[(${tableNameOfUser} ?: 'users')]*/ users (2)
1 Define an any property as MyBatis’s configuration properties
2 Access a configuration property using Thymeleaf’s expression

Above SQL template translate to as follows:

Translated SQL (when tableNameOfUser is defined)
SELECT * FROM accounts
Translated SQL (when tableNameOfUser is not defined)
SELECT * FROM users

6. Using non 2-way SQL mode

The non 2-way SQL is simple a little compare with 2-way SQL and limitations not found at now.

6.1. Configuration

By default, the mybatis-thymeleaf will be use the 2-way SQL mode. Therefore you should be configure explicitly to use the non 2-way SQL mode using configuration properties file or builder option as follow:

This configuration is optional. The non 2-way SQL can be use on the 2-way SQL mode.

How to configure using configuration properties file(src/main/resources/mybatis-thymeleaf.properties)
use2way = false (1)

or

How to configure using config class
configuration.getLanguageRegistry().register(new ThymeleafLanguageDriver(
        ThymeleafLanguageDriverConfig.newInstance(c -> c.setUse2Way(false)))); (2)
1 Set the use2way to false
2 Set the use2way property to false

6.2. Basic usage

The different with the 2-way SQL mode is that will be unnecessary to enclose the thymeleaf expressions as SQL comment(/*[…​]*/).

SQL Template
SELECT * FROM names
  WHERE 1 = 1
  [# th:if="${not #lists.isEmpty(ids)}"]
    AND id IN (
      [# mb:p="ids" /]
    )
  [/]
  ORDER BY id

7. Custom Dialect

The mybatis-thymeleaf provide the custom dialect class(org.mybatis.scripting.thymeleaf.MyBatisDialect) that help for generating SQL template.

7.1. Attribute tag

The mybatis-thymeleaf provides following attribute tags. By default, you can use it using mb dialect prefix (default prefix is initial letter of "MyBatis").

Table 2. Supported method list
Attribute Tag Name Description Attribute Value Format

p

Render bind variable(#{…​}) that can be parsed by MyBatis core module and register an iteration object to the MyBatis’s bind variables.

{variableName}(,{optionKey}={optionValue},…​)

Valid format is same with MyBatis’s inline parameter format.

bind

Register an any value to the MyBatis’s bind variables (Provides a feature similar to that of the <bind> provided by MyBatis core module)

{variable name}={variable value}(,…​)

Valid format is same with th:with provided by Thymeleaf.

7.1.1. p

Basic usage:
SELECT * FROM names
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/ (1)
Usage with option:
SELECT * FROM names
  WHERE id = /*[# mb:p="id,typeHandler=com.example.IdTypeHandler"]*/ 1 /*[/]*/ (2)
Usage for collection and array:
SELECT * FROM names
  WHERE id IN (/*[# mb:p="ids"]*/ 1 /*[/]*/) (3)
1 Render single bind variable(e.g. #{id}) that can be parsed by MyBatis core module when specify a simple value object
2 Can specify parameter options(key=value format) separate with comma
3 Render multiple bind variables(e.g. #{ids[0]}, #{ids[1]}, …​) that can be parsed by MyBatis core module when specify a collection or array object

7.1.2. bind

Basic usage:
SELECT * FROM names
  WHERE 1 = 1
  /*[# th:if="${firstName} != null"]*/
    /*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|" /]*/ (1)
    AND firstName LIKE /*[# mb:p="patternFirstName"]*/ 'Taro' /*[/]*/ (2)
  /*[/]*/
Usage for registering multiple variables:
/*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|, patternLastName=|${#likes.escapeWildcard(lastName)}%|" /]*/ (3)
1 Register an any value(e.g. editing value at template) as custom bind variables
2 Bind a custom bind variable
3 Also, you can register multiple custom bind variables separating with comma at the same time

Why need the bind tag?

The binding feature provide by MyBatis cannot access a variable that shared by the th:with because it can only access within a template. Hence, the mybatis-thymeleaf provide the bind attribute tag.

7.2. Expression utility method

7.2.1. #likes expression

The #likes expression provide utility methods for LIKE clause.

Table 3. Supported method list
Method Arguments Description

escapeWildcard

No Type Description

1

String

A target value

Return a value that escaped a wildcard character of LIKE condition. By default behavior, this method escape the "%", "_" and "\"(escape character itself) using "\".

escapeClause

None

Return a escape clause string of LIKE condition. By default behavior, this method return "ESCAPE '\'".

You can customize the default behavior for the escapeWildcard and escapeClause using Configuration properties.

escapeWildcard
Basic usage:
/*[# th:if="${firstName} != null"]*/
  /*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|" /]*/ (1)
  AND firstName LIKE /*[# mb:p="patternFirstName"]*/ 'Taro%' /*[/]*/
/*[/]*/
1 Register a value that escaped wildcard character of LIKE condition as custom bind variables
Translate to:
  AND firstName LIKE #{patternFirstName}
escapeClause
Basic usage:
/*[# th:if="${firstName} != null"]*/
  /*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|" /]*/
  AND firstName LIKE /*[# mb:p="patternFirstName"]*/ 'Taro%' /*[/]*/ /*[(${#likes.escapeClause()})]*/ (1)
/*[/]*/
1 Add ESCAPE clause at template processing time
Translate to:
  AND firstName LIKE #{patternFirstName} ESCAPE '\'

8. Support classes

We provides useful classes for supporting development.

8.1. TemplateFilePathProvider

The TemplateFilePathProvider is SQL provider class that return the SQL template file path(Available since 1.0.1). This class use with SQL provider annotation(@InsertProvider, @UpdateProvider, @DeleteProvider and @SelectProvider) as follow:

This class required to use on MyBatis 3.5.1+.

Usage:
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}.sql

  • com/example/mapper/NameMapper/NameMapper-{methodName}.sql
    (fallback using default database)

  • com/example/mapper/BaseMapper/BaseMapper-{methodName}-{databaseId}.sql
    (fallback using declaring class of mapper method)

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

If you want to customize the template file path format, you can customize using the configuration properties that start with template-file.path-provider.

9. Cautions for usage

The Thymeleaf 3 does not provide the template mode for SQL. Therefore there are some cautions for usage.

9.1. Using '\'(backslash)

If you are using 2-way SQL mode, there is case that cannot parse a 2-way SQL when specify '\'(backslash) within static template parts. We know that following case cannot be parsed 2-way SQL. If you are not using 2-way SQL mode, this limitation can be ignore.

9.1.1. ESCAPE clause for LIKE

If you specify the ESCAPE '\' directly as static template parts, the Thymeleaf cannot parse it.

Invalid usage
/*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|" /]*/
AND firstName LIKE /*[('#{patternFirstName}')]*/ 'Taro%' /**/ ESCAPE '\'
1 Specify the ESCAPE '\' directly as static template parts

As a solution for avoiding this behavior, the mybatis-thymeleaf provide the expression utility method for adding the ESCAPE clause. For detail, please see Section 7.2.1.2, “escapeClause”.

10. Appendix

10.1. Configuration properties

The mybatis-thymeleaf provides following properties for customizing configurations.

Table 4. Supported property list
Property Key Description Type Default value

General configuration

use2way

Whether use the 2-way SQL feature

Boolean

true (enable the 2-way SQL feature)

customizer

The FQCN of class that implements the TemplateEngineCustomizer (interface for customizing a default TemplateEngine instanced by the mybatis-thymeleaf)

Class

null (This instance is do-nothing)

Template file configuration

template-file.cache-enabled

Whether use the cache feature when load template resource file

Boolean

true (enable template cache feature)

template-file.cache-ttl

The cache TTL(millisecond) for resolved templates

Long

null (no TTL)

template-file.encoding

The character encoding for reading template resource file

String

"UTF-8"

template-file.base-dir

The base directory for reading template resource file

String

"" (just under class path)

template-file.patterns

The patterns for reading as template resource file (Can specify multiple patterns using comma(",") as separator character)

String[]

"*.sql"

Template file path provider configuration(for TemplateFilePathProvider)

template-file.path-provider.prefix

The prefix for adding to template file path

String

""

template-file.path-provider.includes-package-path

Whether includes package path part

Boolean

true (includes package path)

template-file.path-provider.separate-directory-per-mapper

Whether separate directory per mapper

Boolean

true (separate directory per mapper)

template-file.path-provider.includes-mapper-name-when-separate-directory

Whether includes mapper name into file name when separate directory per mapper

Boolean

true (includes mapper name)

template-file.path-provider.cache-enabled

Whether cache a resolved template file path

Boolean

true (cache a resolved template file path)

Dialect configuration

dialect.prefix

The prefix name of dialect provided by this project

String

"mb"

dialect.like-escape-char

The escape character for wildcard of LIKE condition

Character

'\' (backslash)

dialect.like-escape-clause-format

The format of escape clause for LIKE condition (Can specify format that can be allowed by String#format method)

String

"ESCAPE '%s'"

dialect.like-additional-escape-target-chars

Additional escape target characters(custom wildcard characters) for LIKE condition (Can specify multiple characters using comma(",") as separator character)

Character[]

"" (no specify)

src/main/resources/mybatis-thymeleaf.properties
use2way = false
customizer = com.example.MyTemplateEngineCustomizer
template-file.cache-enabled = true
template-file.cache-ttl = 3600000
template-file.encoding = UTF-8
template-file.base-dir = templates/
template-file.patterns = *sql, *.sql.template
template-file.path-provider.prefix = sqls/
template-file.path-provider.includes-package-path = false
template-file.path-provider.separate-directory-per-mapper = false
template-file.path-provider.includes-mapper-name-when-separate-directory = false
dialect.prefix = mybatis
dialect.like-escape-char = ~
dialect.like-escape-clause-format = escape '%s'
dialect.like-additional-escape-target-chars = %, _

These properties can be specified via factory method of ThymeleafLanguageDriverConfig as follow:

configuration.getLanguageRegistry().register(
  new ThymeleafLanguageDriver(ThymeleafLanguageDriverConfig.newInstance(c -> {
  c.setUse2way(false);
  c.setCustomizer(CustomTemplateEngineCustomizer.class);
  c.getTemplateFile().setCacheEnabled(false);
  c.getTemplateFile().setCacheTtl(3600000L);
  c.getTemplateFile().setEncoding(StandardCharsets.UTF_8);
  c.getTemplateFile().setBaseDir("templates/");
  c.getTemplateFile().setPatterns("*.sql", "*.sql.template");
  c.getTemplateFile().getPathProvider().setPrefix("sqls/");
  c.getTemplateFile().getPathProvider().setIncludesPackagePath(false);
  c.getTemplateFile().getPathProvider().setSeparateDirectoryPerMapper(false);
  c.getTemplateFile().getPathProvider().setIncludesMapperNameWhenSeparateDirectory(false);
  c.getDialect().setPrefix("mybatis");
  c.getDialect().setLikeEscapeChar('~');
  c.getDialect().setLikeEscapeClauseFormat("escape '%s'");
  c.getDialect().setLikeAdditionalEscapeTargetChars('', '_');
})));

If you specify the value both with properties file and customizer function, the properties file value applied. We provide following factory methods for creating a ThymeleafLanguageDriver instance.

  • newInstance()

  • newInstance(String resourcePath)

  • newInstance(Properties customProperties)

  • newInstance(Consumer<ThymeleafLanguageDriverConfig> customizer)

10.2. Bulk insert

This section describe about bulk insert for collection or array using SQL rather than JDBC feature. If you want to insert a collection or array by bulk, you can be realized it using the th:each and mb:p attribute tags as follow:

Domain objects
public class Person {
  private int id;
  private String firstName;
  private String lastName;
  private LocalDate dateOfBirth;
  private List<Mail> mails;
  // ...
}
public class Mail {
  private int id;
  private int personId;
  private String address;
  // ...
}
Mapper methods
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("sqls/PersonMapper_insertByBulk.sql")
void insertByBulk(List<Person> persons);

@Insert("sqls/PersonMapper_insertMailsByBulk.sql")
void insertMailsByBulk(List<Person> persons);
SQL template for top level collection/array
INSERT INTO persons
  (first_name, last_name, date_of_birth)
VALUES
/*[# th:each="person : ${list}"]*/ (1)
  (
    /*[# mb:p="person.firstName"]*/ 'Taro' /*[/]*/, (2)
    /*[# mb:p="person.lastName"]*/ 'Yamada' /*[/]*/,
    /*[# mb:p="person.dateOfBirth"]*/ '2000-03-28' /*[/]*/
  )/*[(${personStat.last} ? '' : ',')]*/
/*[/]*/
SQL template for nested property collection/array
INSERT INTO person_mails
  (person_id, address)
VALUES
/*[# th:each="person : ${list}"]*/ (1)
  /*[# th:each="mail : ${person.mails}"]*/ (3)
    (
      /*[# mb:p="person.id"]*/ 1 /*[/]*/,
      /*[# mb:p="mail.address"]*/ 'taro.yamada@mybatis.org' /*[/]*/ (4)
    )/*[(${personStat.last and mailStat.last} ? '' : ',')]*/
  /*[/]*/
/*[/]*/
1 Fetch and store to local variable(e.g. person) an element object of top level collection/array using th:each
2 Specify a property of element object fetched from top level collection/array as bind variable using mb:p
3 Fetch and store to local variable(e.g. mail) and element object of nested property collection or array
4 Specify a property of element object fetched from nested property collection/array as bind variable using mb:p

The mb:p register an element object that fetched from collection/array to the MyBatis’s bind variables.

10.3. Multiline String Literals

You can specify an SQL in annotation using "Raw String Literals" feature that support by future JDK version. Also,"Multiline String Literals" provided other JVM languages (such as Kotlin and Groovy) can be used. There is a good compatibility with annotation driven mapper.

10.3.1. Java

@Select(``
  SELECT * FROM names
    WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
``)
Name findById(Integer id);

10.3.2. Kotlin

Kotlin
@Select("""
  SELECT * FROM names
    WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
""")
fun findById(id: Int): Name

10.3.3. Groovy

Groovy
@Select('''
  SELECT * FROM names
    WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
''')
Todo findById(int id);
Table 5. Related resource list
Resource name Description

GiHub Page

The mybatis-thymeleaf GiHub top page

Usage on framework

Explain how to integrate with an application framework

Code completion

Explain about code completion

Quick Start

Explain how to use mybatis-thymeleaf quickly using the Spring Boot

Copyright ©2018-2019 MyBatis.org. All rights reserved.