検索条件によって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" と呼ばれていたやつです。
余談ですが、IntellJ IDEでSQLを実行した場合は
:id
を残したままでもその部分をプレースホルダと認識してid
の入力フォームが出るので、必ずしも "2way SQL" が必要な訳ではありません。
動的な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
firstName
とsalaryMin
が設定されている場合は次の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ではファイルパスの補間やパス名からファイルへの移動ができて便利です。
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);
}
}
NamedParameterJdbcTemplate
とSqlGenerator
とFileLoader
を使って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でも同じように利用できます。
既存のデータベースアクセス方法を変えることなく、ちょっとしたサプリメントとして気軽に使えるので便利ですね。