S2JDBC - JdbcManager - SQLファイルによる操作

S2Dao譲りのSQLファイルを使うこともできます。

SQLファイルとは、SQLをソースコードに記述するのではなく、 ファイルに記述したものです。 単純なSQLだとソースコードに直接記述したほうが、 めんどくさくなくて楽(わざわざファイルを作る必要がない)ですが、 複雑なSQLは、ファイルに記述したほうが、メンテナンスがしやすくなります。

SQLファイルは、クラスパス上にあるならどこにおいてもかまいませんが、 ルートパッケージ.entity.エンティティ名 のパッケージに対応したディレクトリ配下に置くことを推奨します。 例えば、 Employeeに関するSQLファイルは、 examples/entity/Employee ディレクトリにおくと良いでしょう。

SQLファイルのエンコーディングはUTF-8のみをサポートしています。

複数のテーブルを結合するような場合は、 一番主軸となっているテーブルを選びます。 プロシージャやファンクションの場合も同様です。

何のパラメータもない単純なSQLファイルは次のようになります。

= 1000 and salary <= 2000 ]]>

1000の部分を salaryMin というパラメータで置き換えるには、 次のように置き換えたいリテラルの左にSQLコメントでパラメータ名を埋め込みます。 リテラルを文字列として直接置き換えるのではなく、 PreparedStatment を使ったバインド変数に置き換えるので、SQLインジェクション対策も問題ありません。

= /*salaryMin*/1000 and salary <= 2000 ]]>

同様に2000の部分も salaryMax というパラメータで置き換えます。

= /*salaryMin*/1000 and salary <= /*salaryMax*/2000 ]]>

in を置き換える場合は次のようにします。 ()で囲まれている部分を置き換えます。 idのパラメータの型は、配列かリストになります。

like を置き換える場合は次のようにします。 '(シングルクオート)で囲まれている部分を置き換えます。 ワイルドカードを使いたい場合は、パラメータの値に埋め込んでください。

検索条件の入力画面などによくあるパターンで、何か条件が入力されていれば、 検索条件に追加し、入力されていなければ条件には追加しないということを実装してみましょう。 このような場合は、IFコメントとENDコメントを組み合わせます。

= /*salaryMin*/1000 /*END*/ /*IF salaryMax != null*/ and salary <= /*salaryMax*/2000 /*END*/ ]]>

IFコメントの内容が true なら、 IFコメントとENDコメントで囲んでいる内容が出力されます。 IFコメントの条件は、OGNLによって評価されます。 詳しくは、 OGNLガイド を参照してください。

上記のように記述すると、salaryMinがnullではなくて、 salaryMaxがnullのときには、 下記のように正しいSQLになります。

= ? ]]>

しかしsalaryMinがnullでsalaryMaxがnullではないときは、 次のような不正(andがwhereの直後にある)なSQLになります。

また、salaryMinとsalaryMaxがnullの場合も、 次のような不正(whereだけがある)なSQLになります。

この問題に対応するためには、where句の部分を次のように、 BEGINコメントとENDコメントで囲みます。

= /*salaryMin*/1000 /*END*/ /*IF salaryMax != null*/ and salary <= /*salaryMax*/2000 /*END*/ /*END*/ ]]>

このようにすると、salaryMinがnullでsalaryMaxがnullではないときは、 salaryMaxの条件は、BEGINコメントとENDコメントで囲まれた最初の条件なので、 and の部分が自動的に削除されて次のようになります。

また、salaryMinとsalaryMaxがnullの場合は、 BEGINコメントとENDコメントで囲まれた部分に有効なIFコメントが一つもないため、 BEGINコメントとENDコメントで囲まれた全体がカットされて次のようになります。

バインド変数ではなく、SQLに直接リテラルを埋め込むには、 埋め込み変数コメントを使います。
埋め込み変数コメントは、/*$パラメータ名*/のように指定します。 直接リテラルを埋め込むとSQLインジェクションの危険がありますが、 埋め込み変数の値に、セミコロン(;)が入っていると例外にしているので、 問題はありません。

order by 句をパラメータで変えたい場合は、 /*$orderBy*/と指定します。

ELSEコメントは、IFコメントとENDコメントの間に行コメントとして埋め込みます。

SQLファイルのパスは、 examples/sql/employee/selectAll.sql のように指定します。 このパスに対するデフォルトの物理的なファイルは、 そのまま examples/sql/employee/selectAll.sql になります。 ただし、 ダイアレクト のサフィックスに対応するファイルがあれば、 そちらが優先されます。

例えば、オラクルを使っている場合、 examples/sql/employee/selectAll_oracle.sql があれば、デフォルトより優先されます。

SQLファイルを使って複数件検索をする場合は、 selectBySqlFile() と getResultList() を組み合わせます。 selectBySqlFile() の2番目の引数は、 SQLファイルのパス です。

SQLファイルにパラメータがない場合は、次のようにして呼び出します。

results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql") .getResultList(); ]]>

SQLファイルのパラメータが1つの場合は、 selectBySqlFile() の3番目の引数で値を直接指定します。 SQLファイルの中では/*$1*/のようにパラメータを指定してください。

results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", 10) .getResultList(); ]]>

SQLファイルのパラメータが複数の場合は、 selectBySqlFile() の3番目の引数をJavaBeansまたは Map<String, Object> にして、 パラメータの名前とJavaBeansのプロパティ名または Map のキーを一致させます。

= /*salaryMax*/2000 ]]> results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", param) .getResultList(); ]]>

Mapを使うと次のようになります。

注意点

PostgreSQLの場合、引数に Map を使用すると、 String 以外の型の値に null を指定することは出来ません。 パラメータに null を指定する必要がある場合はDtoを使用してください。

param = new HashMap(); param.put("salaryMin", new BigDecimal(1200)); param.put("salaryMax", new BigDecimal(1800)); List results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", param) .getResultList(); ]]>

org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使うと、流れるようなインタフェースでMapを組み立てることも出来ます。

results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", params("salaryMin", new BigDecimal(1200)) .$("salaryMax", new BigDecimal(1800)) .$()) .getResultList(); ]]>

Map で java.util.Date ・ java.util.Calendar 型のパラメータを指定する場合は、時制を指定することができます。 時制の指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

date(Date) または date(Calendar)
パラメータをSQL標準のDATE型 (日付のみ) として扱います。
time(Date) または time(Calendar)
パラメータをSQL標準のTIME型 (時刻のみ) として扱います。
timestamp(Date) または timestamp(Calendar)
パラメータをSQL標準のTIMESTAMP型 (日付と時刻) として扱います。
results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", params("heredate", date(heredate)) //heredateはjava.util.Date型 .$())) .getResultList(); ]]>

Map で byte[] ・ String 型のパラメータを指定する場合は、ラージオブジェクトであることを指定することができます。 ラージオブジェクトの指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

lob(String)
パラメータをCLOBとして扱います。
lob(byte[]) または lob(Serializable)
パラメータをBLOBとして扱います。
results = jdbcManager .selectBySqlFile( EmployeeDto.class, "examples/sql/employee/selectAll.sql", params("largeName", lob(largeName)) //largeNameはCLOB型 .$()) .getResultList(); ]]>

パラメータ用のJavaBeansまたは Map にlimit、offsetという名前のプロパティまたはキーがあれば、 ページングが行なわれます。

注意点

limit, offsetはパラメータの予約語なので注意してください。

結果を Map で受け取ることもできます。

results = jdbcManager .selectBySqlFile( BeanMap.class, "examples/sql/employee/selectAll.sql") .getResultList(); ]]>

BeanMapはMap<String, Object>なクラスで、 存在しないキーにアクセスすると 例外が発生します。 キーの値は、AAA_BBBのような'_'記法の値ををaaaBbbのようなキャメル記法に 変換したものです。

このルールは、convention.diconで指定されている org.seasar.framework.convention.impl.PersistenceNamingConventionImpl のfromColumnNameToPropertyName()の実装を変えることで、カスタマイズすることができます。

デフォルトでは、結果がなかった場合は、 空の List が返されます。 disallowNoResult() を呼び出すと、 結果がなかった場合は javax.persistence.NoResultException が発生します。

results = jdbcManager .selectBySqlFile( BeanMap.class, "examples/sql/employee/selectAll.sql") .disallowNoResult() .getResultList(); ]]>

SQLファイルを使って1件検索をする場合は、 selectBySqlFile() と getSingleResult() を組み合わせます。 selectBySqlFile() の2番目の引数は、 SQLファイルのパス です。

SQLファイルにパラメータがない場合は、次のようにして呼び出します。

SQLファイルのパラメータが1つの場合は、 selectBySqlFile() の3番目の引数で値を直接指定します。

SQLファイルのパラメータが複数の場合は、 selectBySqlFile() の3番目の引数をJavaBeansにして、 パラメータの名前とJavaBeansのプロパティ名を一致させます。

パラメータ用のJavaBeansにlimit、offsetという名前のプロパティがあれば、 ページングが行なわれます。

注意点

limit, offsetはパラメータ用のJavaBeansのプロパティの予約語なので注意してください。

結果を Map で受け取ることもできます。

BeanMapはMap<String, Object>なクラスで、 存在しないキーにアクセスすると 例外が発生します。 キーの値は、AAA_BBBのような'_'記法の値ををaaaBbbのようなキャメル記法に 変換したものです。

このルールは、convention.diconで指定されている org.seasar.framework.convention.impl.PersistenceNamingConventionImpl のfromColumnNameToPropertyName()の実装を変えることで、カスタマイズすることができます。

デフォルトでは、結果がなかった場合は、 null が返されます。 disallowNoResult() を呼び出すと、 結果がなかった場合は javax.persistence.NoResultException が発生します。

検索結果が多くの行を返すため、 List でまとめて受け取ることが困難な場合は iterate(IterationCallback) を使います。

() { int count; public Integer iterate(EmployeeDto dto, IterationContext context) { if (...) { ++count; } return count; } }); ]]>

iterate(IterationCallback) の引数には、 次のインターフェースを実装したクラスのインスタンスを渡します。

  • org.seasar.extension.jdbc.IterationCallback<ENTITY, RESULT>

ENTITY は selectBySqlFile() で指定したクラス、 RESULT は iterate(IterationCallback) が返す結果の型を指定します。

問い合わせ結果の1行ごとに次のメソッドがコールバックされます。

  • RESULT iterate(ENTITY entity, IterationContext context)

コールバックメソッドが最後に返した値が iterate(IterationCallback) の戻り値となります。

コールバックメソッドの第2引数で渡される org.seasar.extension.jdbc.IterationContext の exit プロパティを true にすると、 問い合わせ結果のイテレーションは終了となり、 検索結果の残りは無視されます。 その時の戻り値が iterate(IterationCallback) の戻り値となります。

SELECT COUNT(*) ～による検索結果の行数を取得する場合は、getCountBySqlFile()を使います。

このメソッドは通常、 select count(*) from ( SQL ) を 実行した結果を返します。

SQLファイルのパラメータが複数の場合は、 getCountBySqlFile() の3番目の引数をJavaBeansにして、 パラメータの名前とJavaBeansのプロパティ名を一致させます。

ページングを指定する場合は、 limit(), offset() を使います。 limit() には、取得する行数を指定します。 offset() には、最初に取得する行の位置を指定します。 最初の行の位置は0になります。 ページングを指定するには、必ず ordey by 句が必要です。 order by句で指定するカラムは、selectリストにも含めるようにしてください。

SQLファイルを使ってエンティティを更新する場合は、 updateBySqlFile() 、 execute() を組み合わせます。 挿入、削除も updateBySqlFile() を使います。

SQLファイルにパラメータがない場合は、次のようにして呼び出します。

SQLファイルのパラメータが1つで値が null にならない場合は、 updateBySqlFile() の2番目の引数で値を直接指定します。

上記以外の場合は、 updateBySqlFile() の2番目の引数をJavaBeansまたは Map<String, Object> にして、 パラメータの名前とJavaBeansのプロパティ名または Map のキーを一致させます。

Mapを使うと次のようになります。

注意点

PostgreSQLの場合、引数に Map を使用すると、 String 以外の型の値に null を指定することは出来ません。 パラメータに null を指定する必要がある場合はDtoを使用してください。

param = new HashMap(); param.put("salary", new BigDecimal(1200)); param.id = 10; int count = jdbcManager .updateBySqlFile( "examples/sql/employee/update.sql", param) .execute(); ]]>

org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使うと、流れるようなインタフェースでMapを組み立てることも出来ます。

Map で java.util.Date ・ java.util.Calendar 型のパラメータを指定する場合は、時制を指定することができます。 時制の指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

date(Date) または date(Calendar)
パラメータをSQL標準のDATE型 (日付のみ) として扱います。
time(Date) または time(Calendar)
パラメータをSQL標準のTIME型 (時刻のみ) として扱います。
timestamp(Date) または timestamp(Calendar)
パラメータをSQL標準のTIMESTAMP型 (日付と時刻) として扱います。

Map で byte[] ・ String 型のパラメータを指定する場合は、ラージオブジェクトであることを指定することができます。 ラージオブジェクトの指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

lob(String)
パラメータをCLOBとして扱います。
lob(byte[]) または lob(Serializable)
パラメータをBLOBとして扱います。

SQLファイルを使ってエンティティをバッチ更新する場合は、 updateBatchBySqlFile() 、 execute() を組み合わせます。 挿入、削除も updateBatchBySqlFile() を使います。

SQLファイルのパラメータが1つで値が null にならない場合は、 updateBatchBySqlFile() の2番目の引数で値のリストを指定します。

paramList = new ArrayList(); ... int[] countArray = jdbcManager .updateBatchBySqlFile( "examples/sql/employee/update.sql", paramList) .execute(); ]]>

上記以外の場合は、 updateBatchBySqlFile() の2番目の引数をJavaBeansまたは Map<String, Object> のリストあるいは配列(可変長引数)にして、 パラメータの名前とJavaBeansのプロパティ名または Map のキーを一致させます。

注意点

バッチ更新で使用するSQLファイルでIFコメントや埋め込み変数コメントを使用する場合、 IFコメントや埋め込み変数コメントはリストまたは配列(可変長引数)の最初の要素だけで評価されます。 2番目以降の要素は、最初の要素で作成されたSQL文のバインド変数に適用されるだけで、 IFコメントや埋め込み変数コメントの評価には使われません。

リストや配列の要素ごとに異なったSQL文が使われるようにする必要がある場合は、 バッチ更新ではなく1件ずつの更新を使用してください。

IFコメントや埋め込み変数コメントを含むSQLファイルを間違ってバッチ更新で使ってしまった場合に 速やかに検出するには、 s2jdbc.dicon で、 JdbcManager の allowVariableSqlForBatchUpdate プロパティを false に設定します。 s2jdbc.dicon については「 セットアップ－概要 」を参照してください。

注意点

PostgreSQLの場合、引数に Map を使用すると、 String 以外の型の値に null を指定することは出来ません。 パラメータに null を指定する必要がある場合はDtoを使用してください。

paramList = new ArrayList(); ... int[] countArray = jdbcManager .updateBatchBySqlFile( "examples/sql/employee/update.sql", paramList) .execute(); ]]>

一意制約違反によりエンティティを挿入ができなかった場合は、 javax.persistence.EntityExistsException が発生します。

Map で java.util.Date ・ java.util.Calendar 型のパラメータを指定する場合は、時制を指定することができます。 時制の指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

date(Date) または date(Calendar)
パラメータをSQL標準のDATE型 (日付のみ) として扱います。
time(Date) または time(Calendar)
パラメータをSQL標準のTIME型 (時刻のみ) として扱います。
timestamp(Date) または timestamp(Calendar)
パラメータをSQL標準のTIMESTAMP型 (日付と時刻) として扱います。

Map で byte[] ・ String 型のパラメータを指定する場合は、ラージオブジェクトであることを指定することができます。 ラージオブジェクトの指定は org.seasar.extension.jdbc.parameter.Parameter のstaticメソッドを使います。

lob(String)
パラメータをCLOBとして扱います。
lob(byte[]) または lob(Serializable)
パラメータをBLOBとして扱います。

バッチ更新のサイズを設定するには batchSize() を使います。

paramList = new ArrayList(); ... int[] countArray = jdbcManager .updateBatchBySqlFile( "examples/entity/Employee/update.sql", paramList) .batchSize(50) .execute(); ]]>

一意制約違反によりエンティティを挿入ができなかった場合は、 javax.persistence.EntityExistsException が発生します。

SQLファイルを使ってストアドプロシージャを呼び出す場合は、 callBySqlFile() と execute() を組み合わせます。 callBySqlFile() の最初の引数は、 SQLファイルのパス です。

最初の例は、パラメータのない場合です。

INのパラメータが1つだけで、そのパラメータが null にならない場合は、 callBySqlFile() の2番目の引数で値を直接指定します。

上記以外の場合は、 callBySqlFile() の2番目の引数にJavaBeansを指定します。 プロシージャを呼び出すパラメータの順番にJavaBeansのフィールドを定義します。

注意点

S2JDBCは、ソースコード上に記述したフィールドの順番と、 コンパイルされた.classファイル内のフィールドの順番が同じになることを前提としていますが、 これはJavaの仕様では保証されていません． SunのJDKやEclipseではソースコード上と.classファイル内のフィールド順は同じになっていますが、 フィールドの順番が変わってしまう環境ではストアドの呼び出しが失敗します。 フィールドの順番が変わってしまう環境があった場合は Seasar-userメーリングリスト までお知らせください．

  • フィールドにアノテーションが付けられていない場合、 IN パラメータになります。

  • フィールドに @Out アノテーションが付けられている場合、 OUT パラメータになります。

  • フィールドに @InOut アノテーションが付けられている場合、 INOUT パラメータになります。

  • フィールドに @ResultSet アノテーションが付けられている場合、 パラメータ以外で戻される結果セットになります。 ただし、 OracleやPostgreSQLのように、 パラメータ以外で結果セットを返すことが出来ないRDBMSの場合は、 OUT パラメータとして扱われます。

  • フィールドに @Lob が付けられている場合、 そのパラメータはLOBとして扱われます。 @Lob アノテーションは他のアノテーションと組み合わせて使用することが出来ます。

result; // 結果セット } ]]>

ストアドプロシージャが複数のカラムを持つ結果セットを返す場合は、 対応するフィールドの型をList<結果セットの行に対応するJavaBeansの型>にします。

result; // 結果セット } ]]>

オラクルとPostgreSQLの場合は、結果セットをパラメータで受け取る必要があります。 これらのRDBMSでは、 @ResultSet アノテーションが付けられたパラメータは OUT パラメータとして扱われるので、 ストアドプロシージャ呼び出しのSQLの中に対応するバインド変数を付け加えます。

result; // 第2引数 (OUT) } ]]>

SQLファイルを使ってストアドファンクションを呼び出す場合は、 callBySqlFile() と、 getSingleResult() または getResultList() を組み合わせます。 callBySqlFile() の1番目の引数でストアドファンクションの戻り値の型を指定します。 2番目の引数は、 SQLファイルのパス です。

最初の例はパラメータがなく、 戻り値が結果セットでない場合です。

OracleやPostgreSQLのように、 ストアドファンクションの戻り値で結果セットを返すことが出来る場合は getResultList() で結果の List を受け取ります。 callBySqlFile() の1番目の引数で List の要素の型を指定します。

result = jdbcManager .callBySqlFile( String.class, "examples/sql/employee/callMyFunc.sql") .getResultList(); ]]>

結果セットの行が複数のカラムを持つ場合は List の要素をJavaBeansにします。

result = jdbcManager .callBySqlFile( MyDto.class, "examples/sql/employee/callMyFunc.sql") .getResultList(); ]]>

INのパラメータが1つだけで、そのパラメータが null にならない場合は、 callBySqlFile() の3番目の引数で値を直接指定します。

上記以外の場合は、 callBySqlFile() の3番目の引数にJavaBeansを指定します。 ストアドファンクションを呼び出すパラメータの順番にJavaBeansのフィールドを定義します。

注意点

S2JDBCは、ソースコード上に記述したフィールドの順番と、 コンパイルされた.classファイル内のフィールドの順番が同じになることを前提としていますが、 これはJavaの仕様では保証されていません． SunのJDKやEclipseではソースコード上と.classファイル内のフィールド順は同じになっていますが、 フィールドの順番が変わってしまう環境ではストアドの呼び出しが失敗します。 フィールドの順番が変わってしまう環境があった場合は Seasar-userメーリングリスト までお知らせください．

  • フィールドにアノテーションが付けられていない場合、 IN パラメータになります。

  • フィールドに @Out アノテーションが付けられている場合、 OUT パラメータになります。

  • フィールドに @InOut アノテーションが付けられている場合、 INOUT パラメータになります。

  • フィールドに @ResultSet アノテーションが付けられている場合、 パラメータ以外で戻される結果セットになります。 ただし、 OracleやPostgreSQLのように、 パラメータ以外で結果セットを返すことが出来ないRDBMSの場合は、 OUT パラメータとして扱われます。

  • フィールドに @Lob が付けられている場合、 そのパラメータはLOBとして扱われます。 @Lob アノテーションは他のアノテーションと組み合わせて使用することが出来ます。

result; // 結果セット } ]]>

ストアドファンクションが複数のカラムを持つ結果セットを返す場合は、 対応するフィールドの型をList<結果セットの行に対応するJavaBeansの型>にします。

result; // 結果セット } ]]>

オラクルとPostgreSQLの場合は、戻り値以外の結果セットをパラメータで受け取る必要があります。 これらのRDBMSでは、 @ResultSet アノテーションが付けられたパラメータは OUT パラメータとして扱われるので、 ストアドファンクション呼び出しのSQLの中に対応するバインド変数を付け加えます。

result; // 第2引数 (OUT) } ]]>