MyBatis Thymeleaf User’s Guide

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

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

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

<dependencies>
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.16</version> <!-- Adjust to your application -->
  </dependency>
  <dependency>
    <groupId>org.mybatis.scripting</groupId>
    <artifactId>mybatis-thymeleaf</artifactId>
    <version>1.1.0</version>
  </dependency>
</dependencies>

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

dependencies {
  compile("org.mybatis:mybatis:3.5.16") // Adjust version to your application
  compile("org.mybatis.scripting:mybatis-thymeleaf:1.1.0")
}

repositories {
  maven { url "https://oss.sonatype.org/content/repositories/snapshots" }
}

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

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

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

public class NameMapper {
  Name findById(Integer id);
}

<mapper namespace="com.example..NameMapper">
  <select id="findById" resultType="com.example.Name">
    <![CDATA[
    SELECT * FROM names
      WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/
    ]]>
  </select>
</mapper>

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

Configuration configuration = new Configuration();
configuration.setDefaultScriptingLanguage(ThymeleafLanguageDriver.class); (1)
// ...
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

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

public class MyTemplateEngineCustomizer implements TemplateEngineCustomizer {
  @Override
  public void customize(TemplateEngine defaultTemplateEngine) {
    // ... (1)
  }
}

customizer = com.example.MyTemplateEngineCustomizer (2)

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)

SELECT * FROM names
  WHERE 1 = 1 (1)
  /*[# th:if="${firstName} != null"]*/ (2)
    AND firstName = /*[# mb:p="firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/ (3)
  ORDER BY id

UPDATE names
  SET id = id (4)
  /*[# th:if="${firstName} != null"]*/
    , firstName = /*[# mb:p="firstName"]*/ 'Taro' /*[/]*/
  /*[/]*/
  WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/

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)
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' /*[/]*/
  /*[/]*/

SELECT COUNT(*) FROM names
  /*[# th:insert="~{NameMapper/whereByCriteria.sql}" /]*/ (1)

SELECT * FROM names
  /*[# th:insert="~{NameMapper/whereByCriteria.sql}" /]*/ (1)
  LIMIT /*[# mb:p="pageable.pageSize"]*/ 20 /*[/]*/
  OFFSET /*[# mb:p="pageable.offset"]*/ 0 /*[/]*/
  ORDER BY id

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

SELECT * FROM names
  WHERE id = /*[# mb:p="id,typeHandler=com.example.IdTypeHandler"]*/ 1 /*[/]*/ (2)

SELECT * FROM names
  WHERE id IN (/*[# mb:p="ids"]*/ 1 /*[/]*/) (3)

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)
  /*[/]*/

/*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|, patternLastName=|${#likes.escapeWildcard(lastName)}%|" /]*/ (3)

dialect.bind-variable-render = org.mybatis.scripting.thymeleaf.support.spring.SpringNamedParameterBindVariableRender (1)

SqlGeneratorConfig config = SqlGeneratorConfig.newInstanceWithCustomizer(c ->
    c.getDialect().setBindVariableRenderInstance(
        BindVariableRender.BuiltIn.SPRING_NAMED_PARAMETER)); (1)
SqlGenerator sqlGenerator = new SqlGenerator(config); (2)

public class R2dbcMySQLBindVariableRender implements BindVariableRender { (1)
  public String render(String name) {
    return "?" + name;
  }
}

SqlGenerator sqlGenerator = new SqlGenerator();

Map<String, Object> conditionsMap = new HashMap<>();
conditionsMap.put("name", "Yamada");

// sql = "SELECT * FROM accounts WHERE name = #{patternName}"
// conditionsMap = {"name":"Yamada", "patternName":"Yamada%"}
String sql = sqlGenerator.generate(
    "/*[# mb:bind='patternName=|${#likes.escapeWildcard(name)}%|' /]*/" +
    "SELECT * FROM accounts WHERE name = /*[# mb:p='patternName']*/ 'Sato' /*[/]*/",
    conditionsMap, conditionsMap::put); (1)

SqlGenerator sqlGenerator = new SqlGenerator();
sqlGenerator.setDefaultCustomVariables(
    Collections.singletonMap("accountsTableName", "users")); (1)

Account account = new Account();
account.setName("Taro Yamada");

Map<String, Object> customVariables = new HashMap<>(); (2)
customVariables.put("now", LocalDateTime.now());
customVariables.put("loginId", loginId);

// sql = "INSERT INTO users (name, created_at, created_by) VALUES(#{name}, #{now}, #{loginId})"
String sql = sqlGenerator.generate(
    "INSERT INTO /*[# th:utext=\"${accountsTableName} ?: 'accounts'\"]*/ accounts /*[/]*/ " + (3)
      "(name, created_at, created_by) VALUES(" +
        "/*[# mb:p='name']*/ 'Hanako Yamada' /*[/]*/, " +
        "/*[# mb:p='now']*/ current_timestamp() /*[/]*/, " + (4)
        "/*[# mb:p='loginId']*/ 'A00000001' /*[/]*/" + (4)
      ")", account, customVariables); (5)

SqlGenerator sqlGenerator = new SqlGenerator();
sqlGenerator.setPropertyAccessor(new MyPropertyAccessor()); (1)

/*[# mb:bind="patternFirstName=|${#likes.escapeWildcard(firstName)}%|" /]*/
AND firstName LIKE /*[('#{patternFirstName}')]*/ 'Taro%' /**/ ESCAPE '\' (1)

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

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

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