Wagbyが発行するSQLの書式は次のようになります。
SELECT 項目名 FROM モデルID WHERE 項目名=...
ここで紹介する「SQL式」とは、上記の「項目名」部分を、特定の式に置換するものです。
想定している利用方法は、この「項目名」がデータベース非保存のとき(テーブルには存在しない)、項目名部分に特定の式を適用してSQLを実行する、というものです。
「モデル項目詳細定義>詳細>データベースの詳細>SQL式」に、SQL文(の一部)を記述することができます。
データベースに保存しない項目であっても、ここに指定されたSQLを用いて項目を取得するようになります。
開発者が記述した式は、Wagby に同梱されている Hibernate という O/R マッパーソフトの設定ファイル (hbmファイル) における formula 要素に反映されます。[Hibernateマニュアル...]
具体的には、wagbyapp\webapps\[プロジェクト名]\WEB-INF\classes\[モデル名].hbm.xml という設定ファイルの formula 要素に出力されます。例を示します。
<property name="item3_"> <formula>`num1`+1</formula> </property>
開発者はこの設定ファイルを修正して、Wagbyアプリケーションを再起動することにより、すぐに動作を確認することができます。式を試す場合は、この方法を推奨します。
動作する SQL 式ができましたら、Designer内の設定に反映させます。(これを忘れてビルドしないようにしてください。この設定ファイルは、ビルド時に再作成されます。)
staffモデルとstaff2モデルがあるとします。別モデルですが、同じIDであれば、同じスタッフを指すとします。(例えば ID="1000"なら、同一人物 "山田" を指します。)
ここでstaffモデルの「住所」項目(参照連動項目)を検索で用いたいという要件があります。しかし、参照連動項目の元となる「staff2のID」はデータベース非保存としたため、検索することができません。
「staff2のID」項目に、自動計算式として id 項目を参照させます。これで表示は自IDとなります。(今回はstaffモデルのid項目と、staff2モデルのid項目は同じ値が入るという前提です。)
さらに、同項目のSQL式に、次の設定を行います。
`ID`
検索時に、この式を解釈します。データベース非保存でも、この値を使うようになりますので、今回のケースでは検索が成功します。
Wagby の自動計算式を使わず、SQL の式で計算を行う例を紹介します。
モデル counttest は、入力値 num1 の値に 1 を加えた項目 calc1 と calc2 があります。いずれもデータベースには保存しない、としています。
`num1`+1
calc1 は(データベースに保存しないため)検索項目として用いることはできません。しかし calc2 は検索項目として用いることができます。計算によって導出される項目で、検索条件としても使いたいが、データベースには保存したくないという場合に SQL 式を用いることができます。
親モデル parent と、子モデル child を想定します。parent 側には、子モデルの数 (COUNT) を保持する項目を用意しますが、データベースに保存しない、とします。
そこでparentの項目「childの数」に、次のSQL式を用います。
(SELECT COUNT(*) FROM child WHERE child.parentid = `id`)
SQL文中の`id`はparentテーブルの列を用いたいので、クォート(`)で囲みます。これによって parent テーブルに対応するエイリアスを付与するようになります。
childやchild.parentidはそれぞれchildテーブルおよびchildテーブルのparentid列を指すため、クォートで囲まずに指定します。
上記の SQL で、child.parentid = `id` の部分を部分一致検索にする場合は LIKE 演算子を使います。(id は文字列型と仮定します。)
LIKE 演算子の書き方は、利用するデータベースによって変わります。 例えば SQL Server の場合は、次のようになります。
(SELECT COUNT(*) FROM child WHER child.parentid LIKE '%'+[parentid]+'%')
登録・更新処理では、保存ボタンを押下する前の情報がデータベースに格納されています。保存ボタンを押下することにより、データベースの値が更新されます。
そこで、データベースの値を保持する項目を用意することで、保存前の値を使う方法を紹介します。
入力値と、入力前の値の差を計算する例を示します。
次の項目を用意しました。
項目 | 型 | SQL式 | 計算式 |
---|---|---|---|
num1 | 数値 | ||
num2 | 数値 | `num1` | |
num3 | 数値 | ${num1}-${num2} |
num2項目はDB保存なしとし、SQL式を設定しています。また、読み込み専用としています。
num3項目はDB保存とし、計算式を設定しています。
SQL Server ではソート対象項目に NULL 値が含まれていた場合、NULL を最も小さい値として扱うため、一覧表示の先頭に表示されます。これを変更し、NULL を含む行を(一覧表示の)最後に表示させる方法を紹介します。
SQL式に下記の値を指定した項目を用意し、この項目をソート順の1番目とします。2番目に対象項目を指定します。
(CASE WHEN [対象項目の列名] IS NULL THEN 1 ELSE 0 END)
SQL は利用するデータベースに依存します。発行される SQL を確認しながら記述してください。 SQL の確認方法は"Wagby Developer Network > Javaを用いたカスタマイズ > 発行されるSQLを確認する"をお読みください。
サブデータベースを利用時、メインデータベースとサブデータベースをまたぐ SQL 式を記述することはできません。(サブデータベースはメインデータベースと別のデータベースとなりますので、一つのSQL文の中で扱うことができないためです。)このような場合はスクリプトを利用するとよいでしょう。