--- title: Thymeleafテンプレートを使ってJdbcTemplateで動的なSQLを実行する tags: ["PostgreSQL", "Java", "Spring Boot", "Thymeleaf"] categories: ["Programming", "Java", "org", "mybatis", "scripting", "thymeleaf"] date: 2021-08-04T11:20:11Z updated: 2021-08-04T13:36:54Z --- 検索条件によってSQLのwhere句が変わるような動的なSQLをJdbcTemplateで扱おうとするとif文を多用してSQLのStringBuilder を組み立てる必要があります。 このようなケースではMyBatisやjOOQのような別のSQLマッパーを使うのが一般的です。 JdbcTemplateを使っていて、他のデータベースアクセスライブラリは使いたくない場合は [MyBatis Thymeleaf](https://mybatis.org/thymeleaf-scripting) を使用することでThymeleafテンプレートを使ってSQLを組み立てることができます。 MyBatis Thymeleafは元々はMyBatisのSQLをThymeleafテンプレートで書けるようにするプロジェクトでしたが、 バージョン1.0.2からテンプレートのレンダリングを行う [SQL Generator](https://mybatis.org/thymeleaf-scripting/user-guide.html#_using_sql_generator) をMyBatisに依存することなく使用できます。 Spring JDBCの [NamedParameterJdbcTemplate](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) で使うことを想定した実装も用意されています。 (Thanks to [@kazuki43zoo](https://twitter.com/kazuki43zoo)!) ### SQL Generatorによるテンプレートの展開 SQL Generatorによって次のSQLは ```sql SELECT id, first_name, last_name, salary FROM employee WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/ ``` 次のSQLに展開できます。これは`NamedParameterJdbcTemplate`やJPQLがパラメータをバインドできる形式です。 ```sql SELECT id, first_name, last_name, salary FROM employee WHERE id = :id ``` こんな感じで試せます。 ```java final SqlGeneratorConfig config = SqlGeneratorConfig.newInstanceWithCustomizer(c -> c.getDialect().setBindVariableRender(SPRING_NAMED_PARAMETER.getType())); final SqlGenerator sqlGenerator = new SqlGenerator(config); final Map params = new HashMap<>(); params.put("id", 100); final String sql = sqlGenerator.generate(""" SELECT id, first_name, last_name, salary FROM employee WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/ """, params, params::put); System.out.println(sql); ``` (`SqlGenerator`というより`SqlRenderer`？) > 展開前のSQLはそのまま実行可能です。IDEでSQLの動作確認をしながら記述できるので便利です。かつてSeasarコミュニティで "2way SQL" と呼ばれていたやつです。 > >![sql](https://user-images.githubusercontent.com/106908/128168116-8fe3c7f3-788e-4f91-873a-6dcfee007821.gif) > > 余談ですが、IntellJ IDEでSQLを実行した場合は`:id`を残したままでもその部分をプレースホルダと認識して`id`の入力フォームが出るので、必ずしも "2way SQL" が必要な訳ではありません。 > > ![sql2](https://user-images.githubusercontent.com/106908/128169114-27186afb-0d0d-41a1-99da-6167df2ba225.gif) 動的なSQLはThymeleafの文法を使って次のように記述できます。(このSQLはfull-scan走りまくりのパフォーマンスの悪いSQLです!) ```sql SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 /*[# th:if="${firstName} != null"]*/ /*[# mb:bind="firstNamePattern=|%${#likes.escapeWildcard(#strings.toLowerCase(firstName))}%|" /]*/ AND lower(first_name) LIKE /*[# mb:p="firstNamePattern"]*/ '%a%' /*[/]*/ /*[/]*/ /*[# th:if="${lastName} != null"]*/ /*[# mb:bind="lastNamePattern=|%${#likes.escapeWildcard(#strings.toLowerCase(lastName))}%|" /]*/ AND lower(last_name) LIKE /*[# mb:p="lastNamePattern"]*/ '%c%' /*[/]*/ /*[/]*/ /*[# th:if="${salaryMin} != null"]*/ AND salary >= /*[# mb:p="salaryMin"]*/ 50000 /*[/]*/ /*[/]*/ /*[# th:if="${salaryMax} != null"]*/ AND salary <= /*[# mb:p="salaryMax"]*/ 100000 /*[/]*/ /*[/]*/ ORDER BY first_name, id ``` パラメータが空の場合は次のSQLに展開されます。 ```sql SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 ORDER BY first_name, id ``` `salaryMin`が設定されている場合は次のSQLに展開されます。 ```sql SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND salary >= :salaryMin ORDER BY first_name, id ``` `firstName`と`salaryMin`が設定されている場合は次のSQLに展開されます。 ```sql SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND lower(first_name) LIKE :firstNamePattern AND salary >= :salaryMin ORDER BY first_name, id ``` `firstNamePattern`というパラメータはテンプレート内でバインドした変数であり、ワイルドカード文字をエスケープした文字列が設定ます。 テンプレート内でバインドした変数は`SqlGenerator#generate`メソッドの`BiConsumer`を経由して`SqlGenerator`の外に渡されます。 > `BiConsumer`に`Map#put`を使う場合は、元の`Map`はmutableである必要があるので、`Map.of`で作成したimmutableなmapをそのままパラメータに使うことはできません。 ### JdbcTemplateでの利用 実際に`JdbcTemplate`でSQL Generatorを使用します。`:param`形式のパラメータを扱う [`NamedParameterJdbcTemplate`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) を使用する必要があります。 その前に、クラスパス上のファイルを読み込み文字列を返す次のユーティリティを用意します。 ```java package com.example.employee; import java.io.IOException; import java.io.InputStream; import java.io.UncheckedIOException; import java.nio.charset.StandardCharsets; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import org.springframework.core.io.ClassPathResource; import org.springframework.util.StreamUtils; public class FileLoader { private static final Map cache = new ConcurrentHashMap<>(); public static String load(String file) { return cache.computeIfAbsent(file, f -> { try (final InputStream stream = new ClassPathResource(file).getInputStream()) { return StreamUtils.copyToString(stream, StandardCharsets.UTF_8); } catch (IOException e) { throw new UncheckedIOException(e); } }); } } ``` このユーティリティはSQL Generator関係なく使えます。IntelliJ IDEAではファイルパスの補間やパス名からファイルへの移動ができて便利です。 ![fileloader](https://user-images.githubusercontent.com/106908/128167564-4db97d67-fe27-4d42-856b-fdb2ed55cb1b.gif) `SqlGenerator`は次のようにBean定義します。 ```java package com.example.employee; import org.mybatis.scripting.thymeleaf.SqlGenerator; import org.mybatis.scripting.thymeleaf.SqlGeneratorConfig; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import static org.mybatis.scripting.thymeleaf.processor.BindVariableRender.BuiltIn.SPRING_NAMED_PARAMETER; @Configuration public class AppConfig { @Bean public SqlGenerator sqlGenerator() { final SqlGeneratorConfig config = SqlGeneratorConfig.newInstanceWithCustomizer(c -> c.getDialect().setBindVariableRender(SPRING_NAMED_PARAMETER.getType())); return new SqlGenerator(config); } } ``` `NamedParameterJdbcTemplate`と`SqlGenerator`と`FileLoader`を使って`EmployeeMapper`クラスを次のように書けます。 ```java package com.example.employee; import java.util.List; import org.mybatis.scripting.thymeleaf.SqlGenerator; import org.springframework.jdbc.core.DataClassRowMapper; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import static com.example.employee.FileLoader.load; @Repository public class EmployeeMapper { private final NamedParameterJdbcTemplate jdbcTemplate; private final SqlGenerator sqlGenerator; private final RowMapper rowMapper = new DataClassRowMapper<>(Employee.class); public EmployeeMapper(NamedParameterJdbcTemplate jdbcTemplate, SqlGenerator sqlGenerator) { this.jdbcTemplate = jdbcTemplate; this.sqlGenerator = sqlGenerator; } public Employee findById(int id) { final MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", id); final String sql = this.sqlGenerator.generate(load("com/example/employee/EmployeeMapper/findById.sql"), params.getValues(), params::addValue); return this.jdbcTemplate.queryForObject(sql, params, this.rowMapper); } public List findAll(EmployeeCriteria criteria) { final MapSqlParameterSource params = criteria.toSqlParameterSource(); final String sql = this.sqlGenerator.generate(load("com/example/employee/EmployeeMapper/findAll.sql"), params.getValues(), params::addValue); return this.jdbcTemplate.query(sql, params, this.rowMapper); } } ``` `Employee`クラスと`EmployeeCriteria`クラスは次の通りです。 ```java package com.example.employee; public class Employee { private final int id; private final String firstName; private final String lastName; private final int salary; public Employee(int id, String firstName, String lastName, int salary) { this.id = id; this.firstName = firstName; this.lastName = lastName; this.salary = salary; } // getters // ... } ``` ```java package com.example.employee; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; public class EmployeeCriteria { private String firstName; private String lastName; private Integer salaryMin; private Integer salaryMax; public MapSqlParameterSource toSqlParameterSource() { return new MapSqlParameterSource() .addValue("firstName", this.firstName) .addValue("lastName", this.lastName) .addValue("salaryMin", this.salaryMin) .addValue("salaryMax", this.salaryMax); } // setters and getters // ... } ``` 最後に次の`EmployeeController`から`EmployeeMapper`を呼び出します。 ```java package com.example.employee; import java.util.List; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; @RestController public class EmployeeController { private final EmployeeMapper employeeMapper; public EmployeeController(EmployeeMapper employeeMapper) { this.employeeMapper = employeeMapper; } @GetMapping(path = "employees/{id}") public Employee getEmployee(@PathVariable Integer id) { return this.employeeMapper.findById(id); } @GetMapping(path = "employees") public List getEmployees(EmployeeCriteria criteria) { return this.employeeMapper.findAll(criteria); } } ``` アプリケーションを実行し、`firstName`を指定して検索します。 ``` $ curl -s "http://localhost:8080/employees?firstName=de" | jq . [ { "id": 38, "firstName": "Bernadette", "lastName": "Franecki", "salary": 60390 }, { "id": 75, "firstName": "Cade", "lastName": "Pfannerstill", "salary": 158840 }, { "id": 93, "firstName": "Derek", "lastName": "Labadie", "salary": 49500 }, { "id": 48, "firstName": "Kaden", "lastName": "McLaughlin", "salary": 45540 } ] ``` 次のログレベルを指定しておくと、 ```properties logging.level.sql=TRACE ``` 次のようなログが出力されます。バインドパラメータに`%de%`が設定されていることがわかります。 ``` 2021-08-04 20:11:44.249 DEBUG 72220 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query 2021-08-04 20:11:44.249 DEBUG 72220 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND lower(first_name) LIKE ? ORDER BY first_name, id] 2021-08-04 20:11:44.251 TRACE 72220 --- [nio-8080-exec-1] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [%de%], value class [java.lang.String], SQL type unknown ``` 次に`salaryMin`を指定します。 ``` $ curl -s "http://localhost:8080/employees?firstName=de&salaryMin=60000" | jq . [ { "id": 38, "firstName": "Bernadette", "lastName": "Franecki", "salary": 60390 }, { "id": 75, "firstName": "Cade", "lastName": "Pfannerstill", "salary": 158840 } ] ``` 次のようなログが出力されます。 ``` 2021-08-04 20:13:04.729 DEBUG 72220 --- [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query 2021-08-04 20:13:04.729 DEBUG 72220 --- [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND lower(first_name) LIKE ? AND salary >= ? ORDER BY first_name, id] 2021-08-04 20:13:04.731 TRACE 72220 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [%de%], value class [java.lang.String], SQL type unknown 2021-08-04 20:13:04.732 TRACE 72220 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 2, parameter value [60000], value class [java.lang.Integer], SQL type unknown ``` さらに`salaryMax`を指定します。 ``` $ curl -s "http://localhost:8080/employees?firstName=de&salaryMin=60000&salaryMax=100000" | jq . [ { "id": 38, "firstName": "Bernadette", "lastName": "Franecki", "salary": 60390 } ] ``` 次のようなログが出力されます。 ``` 2021-08-04 20:27:03.488 DEBUG 72976 --- [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query 2021-08-04 20:27:03.488 DEBUG 72976 --- [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND lower(first_name) LIKE ? AND salary >= ? AND salary <= ? ORDER BY first_name, id] 2021-08-04 20:27:03.491 TRACE 72976 --- [nio-8080-exec-2] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [%de%], value class [java.lang.String], SQL type unknown 2021-08-04 20:27:03.491 TRACE 72976 --- [nio-8080-exec-2] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 2, parameter value [60000], value class [java.lang.Integer], SQL type unknown 2021-08-04 20:27:03.491 TRACE 72976 --- [nio-8080-exec-2] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 3, parameter value [100000], value class [java.lang.Integer], SQL type unknown ``` ちなみに`firstName`に`%`(URLエンコード後は`%25`)を指定すると ``` $ curl -s "http://localhost:8080/employees?firstName=%25" | jq . [] ``` 次のログからわかるように`\%`にエスケープされて実行されます。 ``` 2021-08-04 21:46:40.898 DEBUG 72976 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query 2021-08-04 21:46:40.898 DEBUG 72976 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT id, first_name, last_name, salary FROM employee WHERE 1 = 1 AND lower(first_name) LIKE ? ORDER BY first_name, id] 2021-08-04 21:46:40.899 TRACE 72976 --- [nio-8080-exec-1] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [%\%%], value class [java.lang.String], SQL type unknown ``` 完全なソースコードは [こちら](https://github.com/making/employee-api) です。 [テストコード](https://github.com/making/employee-api/blob/main/src/test/java/com/example/employee/EmployeeMapperTest.java) もあります。 --- Thymeleafテンプレートを使って`NamedParameterJdbcTemplate`で動的なSQLを実行できました。 R2DBCでもJPQLでも同じように利用できます。 既存のデータベースアクセス方法を変えることなく、ちょっとしたサプリメントとして気軽に使えるので便利ですね。