IK.AM

@making's tech note


Thymeleafテンプレートを使ってJdbcTemplateで動的なSQLを実行する

🗃 {Programming/Java/org/mybatis/scripting/thymeleaf}
🏷 Java 🏷 PostgreSQL 🏷 Spring Boot 🏷 Thymeleaf 
🗓 Updated at 2021-08-04T22:36:54+09:00  🗓 Created at 2021-08-04T20:20:11+09:00 {✒️️ Edit  ⏰ History  🗑 Delete}

検索条件によってSQLのwhere句が変わるような動的なSQLをJdbcTemplateで扱おうとするとif文を多用してSQLのStringBuilder を組み立てる必要があります。 このようなケースではMyBatisやjOOQのような別のSQLマッパーを使うのが一般的です。

JdbcTemplateを使っていて、他のデータベースアクセスライブラリは使いたくない場合は MyBatis Thymeleaf を使用することでThymeleafテンプレートを使ってSQLを組み立てることができます。

MyBatis Thymeleafは元々はMyBatisのSQLをThymeleafテンプレートで書けるようにするプロジェクトでしたが、 バージョン1.0.2からテンプレートのレンダリングを行う SQL Generator をMyBatisに依存することなく使用できます。 Spring JDBCの NamedParameterJdbcTemplate で使うことを想定した実装も用意されています。 (Thanks to @kazuki43zoo!)

SQL Generatorによるテンプレートの展開

SQL Generatorによって次のSQLは

SELECT id, first_name, last_name, salary
FROM employee
WHERE id = /*[# mb:p="id"]*/ 1 /*[/]*/

次のSQLに展開できます。これはNamedParameterJdbcTemplateやJPQLがパラメータをバインドできる形式です。

SELECT id, first_name, last_name, salary
FROM employee
WHERE id = :id

こんな感じで試せます。

final SqlGeneratorConfig config = SqlGeneratorConfig.newInstanceWithCustomizer(c ->
        c.getDialect().setBindVariableRender(SPRING_NAMED_PARAMETER.getType()));
final SqlGenerator sqlGenerator = new SqlGenerator(config);
final Map<String, Object> 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

余談ですが、IntellJ IDEでSQLを実行した場合は:idを残したままでもその部分をプレースホルダと認識してidの入力フォームが出るので、必ずしも "2way SQL" が必要な訳ではありません。

sql2

動的なSQLはThymeleafの文法を使って次のように記述できます。(このSQLはfull-scan走りまくりのパフォーマンスの悪い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に展開されます。

SELECT id, first_name, last_name, salary
FROM employee
WHERE 1 = 1
    
    
    
    
ORDER BY first_name, id

salaryMinが設定されている場合は次のSQLに展開されます。

SELECT id, first_name, last_name, salary
FROM employee
WHERE 1 = 1
    
    
    
  AND salary >= :salaryMin
    
    
ORDER BY first_name, id

firstNamesalaryMinが設定されている場合は次の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<String, Object>を経由してSqlGeneratorの外に渡されます。

BiConsumer<String, Object>Map#putを使う場合は、元のMapはmutableである必要があるので、Map.ofで作成したimmutableなmapをそのままパラメータに使うことはできません。

JdbcTemplateでの利用

実際にJdbcTemplateでSQL Generatorを使用します。:param形式のパラメータを扱う NamedParameterJdbcTemplate を使用する必要があります。

その前に、クラスパス上のファイルを読み込み文字列を返す次のユーティリティを用意します。

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<String, String> 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

SqlGeneratorは次のようにBean定義します。

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

NamedParameterJdbcTemplateSqlGeneratorFileLoaderを使ってEmployeeMapperクラスを次のように書けます。

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<Employee> 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<Employee> 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クラスは次の通りです。

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
    // ...
}
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を呼び出します。

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<Employee> 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
  }
]

次のログレベルを指定しておくと、

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

完全なソースコードは こちら です。 テストコード もあります。


Thymeleafテンプレートを使ってNamedParameterJdbcTemplateで動的なSQLを実行できました。 R2DBCでもJPQLでも同じように利用できます。

既存のデータベースアクセス方法を変えることなく、ちょっとしたサプリメントとして気軽に使えるので便利ですね。