データベースの詳細 - SQL式

最終更新日: 2022年11月2日
R8 | R9

概要

Wagbyが発行するSQLの書式は次のようになります。 

SELECT 項目名 FROM モデルID WHERE 項目名=...

ここで紹介する「SQL式」とは、上記の「項目名」部分を、特定の式に置換するものです。

想定している利用方法は、この「項目名」がデータベース非保存のとき（テーブルには存在しない）、項目名部分に特定の式を適用してSQLを実行する、というものです。

定義方法

「モデル項目詳細定義>詳細>データベースの詳細>SQL式」に、SQL文（の一部）を記述することができます。

図1 SQL式の設定欄

データベースに保存しない項目であっても、ここに指定されたSQLを用いて項目を取得するようになります。

重要

本設定はご利用になるデータベースに依存します。本ページの記載は特に断りのない限り、MySQL 用の SQL となっています。

開発に関する Tips

開発者が記述した式は、Wagby に同梱されている Hibernate という O/R マッパーソフトの設定ファイル (hbmファイル) における formula 要素に反映されます。

具体的には、wagbyapp\webapps\[プロジェクト名]\WEB-INF\classes\[モデル名].hbm.xml という設定ファイルの formula 要素に出力されます。例を示します。 

<property name="item3_">
   <formula>`num1`+1</formula>
</property>

開発者はこの設定ファイルを修正して、Wagbyアプリケーションを再起動することにより、すぐに動作を確認することができます。式を試す場合は、この方法を推奨します。

動作する SQL 式ができましたら、Designer内の設定に反映させます。（これを忘れてビルドしないようにしてください。この設定ファイルは、ビルド時に再作成されます。）

[例1] モデル参照項目をデータベースに保存しないが、対応する参照連動項目を検索したい

staffモデルとstaff2モデルがあるとします。別モデルですが、同じIDであれば、同じスタッフを指すとします。（例えば ID="1000"なら、同一人物 "山田" を指します。）

図2 staffモデルとstaff2モデル

ここでstaffモデルの「住所」項目（参照連動項目）を検索で用いたいという要件があります。しかし、参照連動項目の元となる「staff2のID」はデータベース非保存としたため、検索することができません。

SQL式の設定

「staff2のID」項目に、自動計算式として id 項目を参照させます。これで表示は自IDとなります。（今回はstaffモデルのid項目と、staff2モデルのid項目は同じ値が入るという前提です。）

さらに、同項目のSQL式に、次の設定を行います。 

`ID`

検索時に、この式を解釈します。データベース非保存でも、この値を使うようになりますので、今回のケースでは検索が成功します。

図3 SQL式設定のイメージ
SQL式では、クォート(`)で囲むことで自テーブルに対応するエイリアスを付与します。この例では staff.ID として扱われます。（staff2.ID ではありません）

[例2] 計算式（自モデル）

Wagby の自動計算式を使わず、SQL の式で計算を行う例を紹介します。

モデル counttest は、入力値 num1 の値に 1 を加えた項目 calc1 と calc2 があります。いずれもデータベースには保存しない、としています。 

`num1`+1
図4 SQL式による計算

calc1 は（データベースに保存しないため）検索項目として用いることはできません。しかし calc2 は検索項目として用いることができます。計算によって導出される項目で、検索条件としても使いたいが、データベースには保存したくないという場合に SQL 式を用いることができます。

注意

calc1 項目は、登録・更新画面で入力値 num1 が変更されると即座に値が +1 され再描画されます。 しかし calc2 項目は、保存しないと値が反映されません。また、calc2 項目は (Wagbyの) 自動計算式が設定されていない状態だと入力可能となってしまうため、別途「読み込み専用」の設定を行うようにしてください。または、calc2 項目に計算式もあわせて指定してもかまいません。この場合は num1 の変更で即座に再描画されます。

[例3] 計算式（子モデル）

親モデル parent と、子モデル child を想定します。parent 側には、子モデルの数 (COUNT) を保持する項目を用意しますが、データベースに保存しない、とします。

図5 parentとchildの関係

そこでparentの項目「childの数」に、次のSQL式を用います。 

(SELECT COUNT(*) FROM child WHERE child.parentid = `id`)
この式は内蔵データベース(HSQLDB)では動作しません。MySQLで動作することを確認しています。列名を括る文字 ` は、MySQLで使える区切り文字です。

SQL文中の`id`はparentテーブルの列を用いたいので、クォート(`)で囲みます。これによって parent テーブルに対応するエイリアスを付与するようになります。

childやchild.parentidはそれぞれchildテーブルおよびchildテーブルのparentid列を指すため、クォートで囲まずに指定します。

注意

parentモデルはキャッシュを無効にする必要があるかも知れません。子モデルを追加した場合に、親モデルの詳細表示はキャッシュを使わず、常に計算した値を使うようにします。（古いデータを表示させないようにします。）

LIKE 演算子を使う

上記の SQL で、child.parentid = `id` の部分を部分一致検索にする場合は LIKE 演算子を使います。（id は文字列型と仮定します。）

LIKE 演算子の書き方は、利用するデータベースによって変わります。 例えば SQL Server の場合は、次のようになります。 

(SELECT COUNT(*) FROM child WHER child.parentid LIKE '%'+[parentid]+'%')
SQL Server では、列名を括る文字は [列名] と記述します。

[例4] 保存前の値を保持する項目を用意する

登録・更新処理では、保存ボタンを押下する前の情報がデータベースに格納されています。保存ボタンを押下することにより、データベースの値が更新されます。

そこで、データベースの値を保持する項目を用意することで、保存前の値を使う方法を紹介します。

例

入力値と、入力前の値の差を計算する例を示します。

図6 計算例(1)
図7 計算例(2)

定義方法

次の項目を用意しました。

項目 型 SQL式 計算式
num1 数値
num2 数値 `num1`
num3 数値 ${num1}-${num2}

num2項目はDB保存なしとし、SQL式を設定しています。また、読み込み専用としています。

図8 SQL式の設定

num3項目はDB保存とし、計算式を設定しています。

図9 計算式の設定

[例5] NULL 値のソートルールを変える

SQL Server の場合

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式の値はセットされません。

SQLの戻り値は1つの値であること

SQL式によって複数の結果セットが戻るような場合には対応できません。

データベースをまたぐSQLは記述できない

サブデータベースを利用時、メインデータベースとサブデータベースをまたぐ SQL 式を記述することはできません。（サブデータベースはメインデータベースと別のデータベースとなりますので、一つのSQL文の中で扱うことができないためです。）このような場合はスクリプトを利用するとよいでしょう。

SQL式の項目を一覧表示のソート項目にしない

一覧表示のソート（並び替え）は SQL の ORDER BY 句で実現しています。ここに SQL 式を適用するとインデックスが使われないためパフォーマンス低下の一因になります。

※ ORDER BY 句に SQL 式が適用できるかどうかは、ご利用のデータベースに依存します。
※ ORDER BY 句に SQL 式が適用された場合も、その項目がDB保存なしの場合、一覧表示の項目名クリックはできません。この制約は R8.5.13 で解消されます。

複雑なSQLは避ける

複雑なSQL式では、Hibernate の解釈によって接頭語 "this_." が含まれることがあります。開発者がこの制御を行うことは難しいため、このような場合は別途、データベースのビューを用意し、SQL式は単純にする（ビューの項目を示させるのみとする）アプローチを推奨します。