SQLテンプレート

概要

Doma は、 two-way SQL と呼ばれる SQL テンプレートをサポートしています。 two-way SQLは、SQL テンプレートを次の 2 通りの方法で使用できることを意味します。

• テンプレートから動的 SQL ステートメントを構築する

• SQLツールでテンプレートをそのまま実行する

すべての SQL テンプレートは DAO メソッドに対応する必要があります。たとえば、次のような SQL テンプレートと DAO メソッドのペアがあるとします。

select * from employee where employee_id = /* employeeId */99

Employee selectById(Integer employeeId);

/* と */ で囲まれた employeeId 式は、DAO のメソッドパラメータ employeeId に対応します。実行時には、SQL コメントとそれに続く数値 /*employeeId */99 がバインド変数 ? に置き換えられ、メソッド パラメータ employeeId がその変数に渡されます。 SQL テンプレートから生成された SQL ステートメントは次のとおりです。

select * from employee where employee_id = ?

SQL テンプレート内の数字 99 はテストデータであり、実行時には決して使用されません。テストデータは、SQL テンプレートをそのまま実行する場合にのみ役立ちます。つまり、お気に入りの SQL ツールを使用して、SQL テンプレートが文法的に正しいかどうかを確認できます。

各 SQL テンプレートは、テキストファイルまたはアノテーションのいずれかで表されます。

ファイル内の SQL テンプレート

SQL テンプレートはテキストファイルに記載できます。

@Dao
public interface EmployeeDao {
  @Select
  Employee selectById(Integer employeeId);

  @Delete(sqlFile = true)
  int deleteByName(Employee employee);
}

上記の selectById メソッドと deleteByName メソッドは、それぞれ独自の SQL ファイルにマップされます。 DAO メソッドには、次のいずれかのアノテーションを付ける必要があります。

• @Select

• @Insert(sqlFile = true)

• @Update(sqlFile = true)

• @Delete(sqlFile = true)

• @BatchInsert(sqlFile = true)

• @BatchUpdate(sqlFile = true)

• @BatchDelete(sqlFile = true)

エンコーディング

SQL ファイルは UTF-8 エンコードで保存する必要があります。

位置

SQL ファイルは、CLASSPATH に含まれる「META-INF」ディレクトリの下のディレクトリに配置する必要があります。

ファイルパスの形式

SQL ファイルのパスは次の形式に従う必要があります。

META-INF/path-format-of-dao-interface/dao-method.sql

たとえば、DAOインターフェース名が「aaa.bbb.EmployeeDao」、DAOメソッド名が「selectById」の場合、SQLファイルのパスは次のようになります。

META-INF/aaa/bbb/EmployeeDao/selectById.sql

特定の RDBMS への依存関係

特定の RDBMS への依存関係をファイル名で指定できます。これを行うには、拡張子「.sql」の前にハイフン「-」と RDBMS 名を置きます。たとえば、PostgreSQL に固有のファイル パスは次のとおりです。

META-INF/aaa/bbb/EmployeeDao/selectById-postgres.sql

RDBMS 固有の SQL ファイルが存在する場合、優先的に利用されます。例えば、PostgreSQLを使用する環境では、「META-INF/aaa/bbb/EmployeeDao/selectById.sql」ではなく「META-INF/aaa/bbb/EmployeeDao/selectById-postgres.sql」が選択されます。

RDBMS 名は ダイアレクト に由来しています。

RDBMS	ダイアレクト	RDBMS名
DB2	Db2Dialect	db2
H2 Database	H2Dialect	h2
HSQLDB	HsqldbDialect	hsqldb
Microsoft SQL Server	MssqlDialect	mssql
MySQL	MySqlDialect	mysql
Oracle Database	OracleDialect	oracle
PostgreSQL	PostgresDialect	postgres
SQLite	SqliteDialect	sqlite

アノテーション内の SQL テンプレート

@Sql アノテーションを使用して SQL テンプレートを DAO メソッドに指定できます。

@Dao
public interface EmployeeDao {
  @Sql("select * from employee where employee_id = /* employeeId */99")
  @Select
  Employee selectById(Integer employeeId);

  @Sql("delete from employee where employee_name = /* employee.employeeName */'aaa'")
  @Delete
  int deleteByName(Employee employee);
}

@Sql アノテーションは次のアノテーションと組み合わせる必要があります。

• @Select

• @Script

• @Insert

• @Update

• @Delete

• @BatchInsert

• @BatchUpdate

• @BatchDelete

ディレクティブ

SQL テンプレートでは、特定のルールに従った SQL コメントがディレクティブとして認識されます。サポートされているディレクティブは次のとおりです。

• バインド変数ディレクティブ

• リテラル変数ディレクティブ

• 埋込み変数ディレクティブ

• 条件ディレクティブ

• ループディレクティブ

• 選択カラムリスト展開ディレクティブ

• 更新カラムリスト生成ディレクティブ

• パーサーレベルコメントディレクティブ

注釈

ディレクティブで使用できる式言語については、式言語 も参照してください。

バインド変数ディレクティブ

バインド変数ディレクティブは /*...*/ の形式で表されます。 /* と */ で囲まれた式が評価され、その評価結果がSQL文のバインド変数に渡されます。ディレクティブの後にはテストデータを続ける必要がありますが、テスト データは実行時には使用されません。

パラメータ

型が 基本クラス または ドメインクラス のいずれかのパラメータはバインド変数として認識されます。

次の例は、DAO メソッドと SQL テンプレートのペアです。

Employee selectById(Integer employeeId);

select * from employee where employee_id = /* employeeId */99

次の SQL ステートメントが上述の SQL テンプレートから生成されます。

select * from employee where employee_id = ?

IN句のパラメータ

java.lang.Iterable のサブタイプもしくは配列のパラーメータはIN句のバインド変数だと認識されます。

java.lang.Iterable のサブタイプもしくは配列の要素の型は 基本クラス または ドメインクラス でなければいけません。ディレクトティブの直後には ( と ) で囲まれたテストデータが必須です。

次の例は、DAO メソッドと SQL テンプレートのペアです。

List<Employee> selectByIdList(List<Integer> employeeIdList);

select * from employee where employee_id in /* employeeIdList */(1,2,3)

employeeIdList に 5 つの要素が含まれる場合、SQL テンプレートから次の SQL ステートメントが生成されます。

select * from employee where employee_id in (?, ?, ?, ?, ?)

employeeIdList が空の場合、実行時に IN 句は in (null) に置き換えられます。

select * from employee where employee_id in (null)

リテラル変数ディレクティブ

リテラル変数ディレクティブは /*^...*/ の形式で表されます。 /*^ と */ で囲まれた式を評価し、その評価結果をリテラル形式に変換してSQL文に埋め込みます。ディレクティブの後にはテスト データを続ける必要がありますが、テスト データは実行時には使用されません。

次の例は、DAO メソッドと SQL テンプレートのペアです。

Employee selectByCode(String code);

select * from employee where code = /*^ code */'test'

DAO メソッドは次のように呼び出されます。

EmployeeDao dao = new EmployeeDaoImpl();
List<Employee> list = dao.selectByCode("abc");

生成される SQL ステートメントは次のとおりです。

select * from employee where code = 'abc'

注釈

リテラル変数ディレクティブは、バインド変数を回避し、SQL 実行計画を固定するのに役立ちます。

警告

リテラル変数ディレクティブは、SQL インジェクションを引き起こす可能性のある文字列をエスケープしません。ただし、ディレクティブは一重引用符 ' を含むパラメータを拒否します。

埋め込み変数ディレクティブ

埋込み変数ディレクティブは /*#...*/ の形式で表現します。 /*# と */ で囲まれた式が評価され、その評価結果がSQL文に埋め込まれます。

次の例は、DAO メソッドと SQL テンプレートのペアです。

List<Employee> selectAll(BigDecimal salary, String orderBy);

select * from employee where salary > /* salary */100 /*# orderBy */

DAO メソッドは次のように呼び出されます。

EmployeeDao dao = new EmployeeDaoImpl();
BigDecimal salary = new BigDecimal(1000);
String orderBy = "order by salary asc, employee_name";
List<Employee> list = dao.selectAll(salary, orderBy);

生成される SQL ステートメントは次のとおりです。

select * from employee where salary > ? order by salary asc, employee_name

注釈

埋め込み変数ディレクティブは、ORDER BY 句などの SQL フラグメントを構築するのに役立ちます。

警告

SQL インジェクションの脆弱性を防ぐために、埋め込み変数ディレクティブは次の値を含むパラメータを拒否します。

• 一重引用符 '

• セミコロン ;

• ハイフン 2 つ --

• スラッシュとアスタリスク /*

条件ディレクティブ

条件ディレクティブを使用すると、条件に基づいて SQL ステートメントを作成できます。

概要

/*%if condition*/
  ...
/*%elseif condition2*/
  ...
/*%elseif condition3*/
  ...
/*%else*/
  ...
/*%end*/

式 condition、condition2、および condition3 は、boolean または java.lang.Boolean のいずれかに評価される必要があります。

elseif ディレクティブと else ディレクティブはオプションです。

if

次の SQL テンプレートがあるとします。

select * from employee where
/*%if employeeId != null */
    employee_id = /* employeeId */99
/*%end*/

employeeId が null でない場合、生成される SQL ステートメントは次のようになります。

select * from employee where employee_id = ?

employeeId が null の場合、生成される SQL ステートメントは次のようになります。

select * from employee

SQL キーワード where は自動的に削除されます。

elseifとelse

次の SQL テンプレートがあるとします。

select
  *
from
  employee
where
/*%if employeeId != null */
  employee_id = /* employeeId */9999
/*%elseif department_id != null */
  and
  department_id = /* departmentId */99
/*%else*/
  and
  department_id is null
/*%end*/

employeeId != null が true と評価された場合、生成される SQL ステートメントは次のようになります。

select
  *
from
  employee
where
  employee_id = ?

employeeId == null && department_id != null が true と評価された場合、生成される SQL ステートメントは次のようになります。

select
  *
from
  employee
where
  department_id = ?

SQL キーワード and は自動的に削除されます。

employeeId == null && department_id == null が true と評価された場合、生成される SQL ステートメントは次のようになります。

select
  *
from
  employee
where
  department_id is null

SQL キーワード and は自動的に削除されます。

入れ子になった条件ディレクティブ

次のように条件ディレクティブをネストできます。

select * from employee where
/*%if employeeId != null */
  employee_id = /* employeeId */99
  /*%if employeeName != null */
    and
    employee_name = /* employeeName */'hoge'
  /*%else*/
    and
    employee_name is null
  /*%end*/
/*%end*/

条件ディレクティブの句の削除

条件ディレクティブでは次の句が不要になる場合があります。

• WHERE

• HAVING

• ORDER BY

• GROUP BY

その場合は自動的に削除されます。

次の SQL テンプレートがあるとします。

select * from employee where
/*%if employeeId != null */
    employee_id = /* employeeId */99
/*%end*/

employeeId != null が false と評価された場合、生成される SQL ステートメントは次のようになります。

select * from employee

SQL 句 where の後に /* %i f ...*/ が続く部分は不要なため、自動的に削除されます。

条件ディレクティブの AND および OR キーワードの削除

AND キーワードと OR キーワードは、条件ディレクティブでは不要になる場合があります。その場合は自動的に削除されます。

次の SQL テンプレートがあるとします。

select * from employee where
/*%if employeeId != null */
    employee_id = /* employeeId */99
/*%end*/
and employeeName like 's%'

employeeId != null が false と評価された場合、生成される SQL ステートメントは次のようになります。

select * from employee where employeeName like 's%'

/* %end*/ に続くSQLキーワード and は不要なため、自動的に削除されます。

条件ディレクティブの制限

/* %if condition*/ と /* %end*/ は、同じ SQL 句および同じ文レベルに含める必要があります。

次のテンプレートは無効です。 /* %ifcondition*/ が FROM 句にあり、/* %end*/ が WHERE 句にあるためです。

select * from employee /*%if employeeId != null */
where employee_id = /* employeeId */99 /*%end*/

次のテンプレートは、外側のステートメントに /* %if condition*/ があり、内側のステートメントに /* %end*/ があるため、無効です。

select * from employee
where employee_id in /*%if departmentId != null */(select ...  /*%end*/ ...)

ループディレクティブ

ループディレクティブを使用すると、ループを使用して SQL ステートメントを構築できます。

概要

/*%for item : sequence*/
  ...
/*%end*/

item はループ変数です。式 sequence は java.lang.Iterable のサブタイプまたは配列型として評価される必要があります。

/* %for item : sequence*/ と /* %end*/ の間の内部では、2 つの追加のループ変数が利用可能です。

item_index:

ループ内の現在の項目のインデックス (0 から始まる番号)

item_has_next:

現在の項目がシーケンスの最後であるかどうかを示すブール値

接頭辞 item はループ変数の名前を示します。

for と item_has_next

次の SQL テンプレートがあるとします。

select * from employee where
/*%for name : names */
employee_name like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end*/

シーケンス names に 3 つの項目が含まれる場合、生成される SQL ステートメントは次のようになります。

select * from employee where
employee_name like ?
or
employee_name like ?
or
employee_name like ?

ループディレクティブの句の削除

ループ ディレクティブでは次の句が不要になる場合があります。

• WHERE

• HAVING

• ORDER BY

• GROUP BY

その場合は自動的に削除されます。

次の SQL テンプレートがあるとします。

select * from employee where
/*%for name : names */
employee_name like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end*/

シーケンス names が空の場合、生成される SQL ステートメントは次のようになります。

select * from employee

SQL 句 where の後に /* %for ...``*/ が続く部分は不要なため、自動的に削除されます。

ループディレクティブの AND および OR キーワードの削除

AND キーワードと OR キーワードは、ループ ディレクティブでは不要になる場合があります。その場合は自動的に削除されます。

次の SQL テンプレートがあるとします。

select * from employee where
/*%for name : names */
employee_name like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end*/
or
salary > 1000

シーケンス names が空の場合、生成される SQL ステートメントは次のようになります。

select * from employee where salary > 1000

/* %end*/ に続く SQL キーワード or は不要なため、自動的に削除されます。

ループディレクティブの制限事項

/* %for ...*/ と /* %end*/ は、同じ SQL 句および同じ文レベルに含める必要があります。

条件ディレクティブの制限 も参照してください。

選択カラムリスト展開ディレクティブ

選択カラムリスト展開ディレクティブを使用すると、エンティティクラス の定義から SELECT 句のカラムリストを構築できます。

概要

/*%expand alias*/

式 alias はオプションです。指定した場合は、 java.lang.String として評価される必要があります。

ディレクティブの後にはアスタリスク * を付ける必要があります。

expand

次の SQL テンプレートと、そのテンプレートにマップされたエンティティ クラスがあるとします。

select /*%expand*/* from employee

@Entity
public class Employee {
    Integer id;
    String name;
    Integer age;
}

生成される SQL ステートメントは次のとおりです。

select id, name, age from employee

テーブルに別名を指定する場合は、展開ディレクティブにも同じ別名を指定します。

select /*%expand "e" */* from employee e

生成される SQL ステートメントは次のとおりです。

select e.id, e.name, e.age from employee e

更新カラムリスト生成ディレクティブ

更新カラムリスト生成ディレクティブを使用すると、エンティティクラス の定義から UPDATE SET 句のカラムリストを構築できます。

概要

/*%populate*/

populate

次の SQL テンプレートと、そのテンプレートにマップされたエンティティ クラスがあるとします。

update employee set /*%populate*/ id = id where age < 30

@Entity
public class Employee {
    Integer id;
    String name;
    Integer age;
}

生成される SQL ステートメントは次のとおりです。

update employee set id = ?, name = ?, age = ? where age < 30

パーサーレベルコメントディレクティブ

パーサー レベルコメントディレクティブを使用すると、テンプレートが解析された後に削除されるコメントを SQL テンプレートに含めることができます。

概要

/*%! comment */

次の SQL テンプレートがあるとします。

select
  *
from
  employee
where /*%! This comment will be removed */
  employee_id = /* employeeId */99

上記の SQL テンプレートは次の SQL に解析されます。

select
  *
from
  employee
where
  employee_id = ?

コメント

このセクションでは、ディレクティブと通常の SQL コメントを区別する方法を説明します。

一行コメント

2 つのハイフン -- で構成される文字列は常に一行コメントになります。それは決して特別な意味を持つディレクティブではありません。

複数行のコメント

/* に続く文字が Java 識別子の最初の文字として許可されておらず、かつそれが %、#、@、"、' のいずれでもない場合、/* は複数行コメントの始まりです。

以下は複数行のコメントの始まりです。

• /**...*/

• /*+...*/

• /*=...*/

• /*:...*/

• /*;...*/

• /*(...*/

• /*)...*/

• /*&...*/

一方、以下はディレクティブの始まりです。

• /* ...*/

• /*a...*/

• /*$...*/

• /*@...*/

• /*"...*/

• /*'...*/

• /*#...*/

• /*%...*/

注釈

複数行のコメントを開始するには、簡単なので常に /**...*/ を使用することをお勧めします。

doma-template モジュール

doma-template モジュールは、SQL テンプレートから準備された SQL ステートメントを構築するのに役立ちます。モジュールには次のクラスのみが含まれています。

• SqlArgument

• SqlStatement

• SqlTemplate

Gradle

dependencies {
    implementation("org.seasar.doma:doma-template:3.6.0")
}

使用法

String sql = "select * from emp where name = /* name */'' and salary = /* salary */0";
SqlStatement statement =
    new SqlTemplate(sql)
        .add("name", String.class, "abc")
        .add("salary", int.class, 1234)
        .execute();
String rawSql = statement.getRawSql(); // select * from emp where name = ? and salary = ?
List<SqlArgument> arguments = statement.getArguments();