わかりやすいJPA(6)
Where句で使える演算子

はじめに

これは「わかりやすいJPA」の6回目です。

SELECT文はデータベースからいろいろな条件でデータを抽出するのでレポートクエリともいいます。SELECT句で、抽出するデータをフィルタリングするためのいろいろな条件を書くのがWhere句です。これから3回に分けてサブクエリ、演算子、関数、集計関数、などを解説します。今回はサブクエリと演算子の解説です。

解説で出てくるエンティティやクエリの実行については、「連載:わかりやすいJPA」で必要なツールとドメインモデルについて を参照してください。

WHERE句で使える演算子

最初に、使用できるすべての演算子の一覧表を示します。

標準SQLと同じような演算子がありますが、そうでないものもあります。一通り目を通しておきましょう。

演算子 機 能 例 示 説 明
+ - * / 加減乗除 e.salary + 10000 e.salary に10000を足す
= 等しい p.type = ‘Cell’ p.typeは’Cell”と等しい
<> 等しくない e.salary <> 50000 e.salaryは50000ではない
> >= < <= 大小比較 e.salary >= 45000 e.salaryは4500以上
AND OR NOT 論理演算子 q.qa_rating>4 AND q.name=’QA’ q.qa_rating が4より大きく、かつ、q.nameが’QA’
[NOT]BETWEEN a AND b aとbの間 s.price BETWEEN 4000 AND 5000 s.priceは4000以上、5000以下
[NOT] LIKE 文字列のパターンマッチ d.name LIKE ‘%CNT_’ d.nameは任意の文字列の後に”CNT”が続き、末尾に任意の文字が1つある
[NOT]IN(・・・ ( )内のどれかと同じ c.code IN( ‘JP’, ‘US’ ) c.codeは ‘JP’ か ‘US’
IS [NOT]NULL NULLである d.address IS NOT NULL d.addreddはNULLではない
IS [NOT]EMPTY コレクションの要素数が0 e.phones IS EMPTY e.phones は空
e[NOT]MEMBER OF c エンティティeはコレクションcの要素である e1 MEMBER OF e2.directs e1は、e2.directsの要素
[NOT]EXISTS (sub-query) サブクエリの実行結果が1件以上ある 例示は解説を参照
v ope ALL (sub-query) 比較はサブクエリのすべての結果について、trueとなる 例示は解説を参照
※vは変数、opeは比較演算子です
v ope ANY (sub-query)
v ope SOME (sub-query)
比較はサブクエリの少なくとも1つの結果について、trueとなる 例示は解説を参照

※SOMEとANYは同じです

TYPE( p ) エンティティpにサブクラスがある時、pのエンティティ名を返す。instanceofに近い。 TYPE(p) = DesignProject p はDesignProjectエンティティである
TREAT( p as) エンティティpにサブクラスがある時、指定したサブクラスにダウンキャストする TREAT(p as DesignProject) pをサブクラスのDesignProjectにダウンキャストする

演算子の使い方

使い方が自明な演算子以外で、注意すべき演算子について例題を示して説明します。最初に条件式の一部として使うサブクエリについて解説します。サブクエリは、いくつかの演算子とともに使われます。

サブクエリ

サブクエリはWHERE句やHAVING句(次回解説)に書くSELECT文で、全体を( )で囲みます。実行結果は1つの値(リテラル)やコレクションになります。

 SELECT d FROM Department d
 WHERE (SELECT COUNT(de) FROM d.employees de ) > 3
Department no: 1, name: Engineering, employees: 6
Department no: 2, name: QA, employees: 4
※Employeesはemployeesコレクションの要素数を表示するようにしています。

上の例は、Deparetmentエンティティの、コレクション型のメンバであるemployeesの要素数をCOUNT関数(○回で解説)で求めるサブクエリで、それが 3 よりも大きいという条件式を作るのに利用しています。

サブクエリの結果がコレクションになる場合は、EXIST、INなどの演算子で利用できます。EXISTとINはこの後に解説しているので、それぞれの例題を見てください。

サブクエリでは、本体のSELECT文で宣言した変数を自由に使うことができますし、また、サブクエリで新しい変数を宣言することもできます。例題では、コレクションであるd.employees を変数 de と宣言しています。

v  BETWEEN  a  AND  b

vがa~bの範囲にあることを示します。v は単一の値(コレクションではない)を意味する変数で、数値だけでなく、文字列、日付・時間についても使えます。

次は採用年月日が、2003年1月1日から2005年12月31日まで社員の名前と採用年月日をリストアップします。JpqlTestで実行してみてください。

 SELECT e.name, e.startDate FROM Employee e 
 WHERE e.startDate BETWEEN {d '2003-1-1'} AND {d '2005-12-31'}
[Frank, Mon Feb 17 00:00:00 JST 2003]
[Scott, Sun Nov 14 00:00:00 JST 2004]
[Sue, Thu Aug 18 00:00:00 JST 2005]
[Joan, Wed Apr 16 00:00:00 JST 2003]

ここで、ついでに日付と時間のリテラルの書き方も覚えておきましょう。

日 付     {d  ‘yyyy-mm-dd’}
時 間     {t  ‘hh-mm-ss’}
タイムスタンプ {ts  ‘yyyy-mm-dd hh-mm-ss’}

例えば次のように書きます。

・12時13分14秒 →  {t ’12-13-14′}

・2030年1月1日12時13分14秒 → {ts ‘2013-01-01 12-13-14’}

なお、タイムスタンプには、ミリ秒の単位を小数点として付け加えることができます。

・2030年1月1日12時13分14.123秒 → {ts ‘2013-01-01 12-13-14.123’}

str LIKE exp

 LIKEは、str が文字列 exp とパターンマッチすることを表します。次は、CAで始まる所属名をもつ所属をリストアップします。

 SELECT d FROM Department d
 WHERE d.name LIKE 'CA%'
Department no: 4, name: CAEngOtt
Department no: 6, name: CADocOtt

パターンマッチのための特殊文字として%_を使います。また、特殊文字自体を文字として使いたい時は\%や¥_のように、左にを付け(てエスケープし)ます。

%   任意の文字列
_    任意の1文字
\_   文字としての _ (アンダーバー)
\%   文字としての % (パーセント記号)

v  IN( list )

v が( )に列記した list 中のどれかと同じであることを表します。v は単一の値(コレクションではない)を意味する変数です。次は、( )内の3つの所属名のどれかと等しい所属に属する従業員をリストアップします。

 SELECT e FROM Employee e 
 WHERE e.department.name IN('Accounting', 'USEngCal', 'QA_East')
Employee 12: name: Joe, ・・・(中略)・・・, managerNo: 11, deptNo: 3
Employee 13: name: Jack, ・・・(中略)・・・, managerNo: null, deptNo: 3
※depNO: 3 は Accounting の所属キーです。

( ) 内のリストとして、具体的な値の代わりに、コレクションを返すサブクエリを書くこともできます。次は、サブクエリによって人数が5名未満の所属のコレクションを求め、それをリストの代わりに使う例です。人数が5人未満の所属のどれかに所属する社員をリストアプします。

 SELECT e FROM Employee e
 WHERE e.department IN (SELECT d FROM Department d
                        WHERE SIZE(d.employees) < 5 )
Employee 1: name: John, ・・・(中略)・・・, managerNo: 9, deptNo: 2
Employee 2: name: Rob, ・・・(中略)・・・, managerNo: 9, deptNo: 2
Employee 3: name: Peter,・・・(中略)・・・, managerNo: 9, deptNo: 2
Employee 9: name: Sarah,・・・(中略)・・・, managerNo: 10, deptNo: 2
Employee 12: name: Joe,・・・(中略)・・・, managerNo: 11, deptNo: 3
Employee 13: name: Jack,・・・(中略)・・・, managerNo: null, deptNo: 3
※SIZE( ) は、( ) 内に書いたコレクションの要素数を求める集計関数(○回で解説)です。

c IS EMPTY

IS EMPTYはコレクション c の要素数がゼロであることを表します。次の例は、統括する部下を持つ社員をリストアップするクエリです。部下のコレクション(e.directs)の要素数が0でないような社員を抽出します。

SELECT e FROM Employee e
WHERE e.directs IS NOT EMPTY
Employee 9: name: Sarah, directs_size: 3, … 以下省略 …
Employee 10: name: Joan, directs_size: 5, … 以下省略 …
Employee 11: name: Marcus, directs_size: 1, … 以下省略 …

e  MEMBER OF  c

MEMBER OFは、エンティティe が コレクション c の要素であることを表します。

次は、IS EMPTYの例と同様に部下を持つ社員だけをリストアップしますが、どのdirectsにも含まれない社員を抽出するという方法を取っています。

SELECT distinct e FROM Employee e JOIN e.directs d
WHERE e NOT MEMBER OF e.directs
Employee 9: name: Sarah, directs_size: 3, … 以下省略 …
Employee 10: name: Joan, directs_size: 5, … 以下省略 …
Employee 11: name: Marcus, directs_size: 1, … 以下省略 …

EXISTS (subquery)

EXISTSは、サブクエリが1件以上の結果を返す時 true を返します。

次は、社員の電話番号リストから、タイプが携帯電話(’Cell’)であるものをリストアップするサブクエリを使って、携帯電話を持つ社員だけをリストアップします。これは、EXISTSが true を返すことをWHEREの条件としています。

SELECT e FROM Employee e
WHERE EXISTS ( SELECT p FROM e.phones WHERE p.type ='Cell' )
Employee 3: name: Peter, directs_size: 0, managerNo: 9, phones_size: 2, … 以下省略 …
Employee 5: name: Scott, directs_size: 0, managerNo: 10, phones_size: 2, … 以下省略 …
Employee 6: name: Sue, directs_size: 0, managerNo: 10, phones_size: 3, … 以下省略 …
Employee 9: name: Sarah, directs_size: 3, managerNo: 10, phones_size: 3, … 以下省略 …
Employee 10: name: Joan, directs_size: 5, managerNo: null, phones_size: 3, … 以下省略 …

ope ALL (subquery)

vは変数、opeは比較演算子、そしてサブクエリは1件以上の結果を返すクエリです。ALLは、サブクエリが返すすべての結果について、vとの比較演算がtrueになることを表します。

理解するために、まず、次の例を見ましょう。

SELECT e FROM Employee e
WHERE e.directs IS NOT EMPTY AND 
      e.salary < ALL (SELECT d.salary FROM e.directs d)
Employee 11: name: Marcus, directs_size: 1, managerNo: null, phones_size: 0, … 以下省略 …

太字の部分がALLを使っている部分です。ここでは、管理職社員の給与(e.salary)と右辺のサブクエリが返す給与(部下のsalary)を比較します。サブクエリは1件以上のsalaryを返しますが、ALLは、そのすべてについて管理職社員の給与の方が小さいと言うことを表します。

ope ANY (subquery)

ANYも使い方はALLと同じですが、違いは、サクブクエリの結果との比較において、少なくとも1つの結果について比較式が成立する、という条件になることです。なお、ANYのエイリアスとして、SOMEも使うことができます。

ここでは、ALLの場合の例をANYに変えて実行してみましょう。

SELECT e FROM Employee e
WHERE e.directs IS NOT EMPTY AND 
      e.salary < ANY (SELECT d.salary FROM e.directs d)
Employee 9: name: Sarah, directs_size: 3, managerNo: 10, phones_size: 3, … 以下省略 …
Employee 10: name: Joan, directs_size: 5, managerNo: null, phones_size: 3, … 以下省略 …
Employee 11: name: Marcus, directs_size: 1, managerNo: null, phones_size: 0, … 以下省略 ……

結果の件数が増えているのは、ALLからANYへと条件が緩くなったからです。

TYPE( e )

一般のクラスと同様に、エンティティクラスも継承でき、いくつでもサブクラスを作れます。そこで、Projectクラスのサブクラスとして、DesignProjectクラスとQualityProjectクラスが作ってあります(ソースコードはJpqlTestプロジェクトのbeansフォルダにあります)。

ただし、JPQLでは、SELECT p FROM Project p というクエリを実行すると、Projectエンティティだけでなく、すべてのサブクラスも同時に出力されます。次のクエリをJpqlTestで実行してみてください。

 SELECT p FROM Project p
[ Project [ no: 1, name: Implement Release1, employee_Sz: 4] ]
[ Project [ no: 2, name: Implement Release2, employee_Sz: 5] ]
[ DesignProject [ no: 3, name: Design Release1, employee_Sz: 4] ]
[ DesignProject [ no: 4, name: Design Release2, employee_Sz: 0] ]
[ DesignProject [ no: 5, name: Design Release3, employee_Sz: 0] ]
[ QualityProject [ no: 6, name: Test Release1, employee_Sz: 0, rating: 4] ]
[ QualityProject [ no: 7, name: Test Release2, employee_Sz: 2, rating: 5] ]
[ QualityProject [ no: 8, name: Test Release3, employee_Sz: 2, rating: 5] ]
[ Project [ no: 9, name: Implement Release3, employee_Sz: 2] ]

特定のクラスだけを対象としたい時、使うのがTYPE関数です。次の例は、DesignProjectクラスとQualityProjectクラスだけを出力するためにTYPE関数を使っています。

 SELECT p FROM Project p
 WHERE TYPE(p) = DesignProject OR TYPE(p) = QualityProject
[ DesignProject [ no: 3, name: Design Release1, employee_Sz: 4] ]
[ DesignProject [ no: 4, name: Design Release2, employee_Sz: 0] ]
[ DesignProject [ no: 5, name: Design Release3, employee_Sz: 0] ]
[ QualityProject [ no: 6, name: Test Release1, employee_Sz: 0, rating: 4] ]
[ QualityProject [ no: 7, name: Test Release2, employee_Sz: 2, rating: 5] ]
[ QualityProject [ no: 8, name: Test Release3, employee_Sz: 2, rating: 5] ]

なお、クラスのエンティティ指定する時2重引用符で囲わないことに注意してください。

TREAT (e as type)

TREATは特定のエンティティをダウンキャストしてサブクラスとみなして操作したい時に使います。というのも、サブクラスはスーパークラスが持たないフィールドを持つ場合があり、そのようなフィールドをアクセスするには、ダウンキャストする必要があります。

次は、DesignProjectとQualityProjectだけを抽出するクエリですが、QualityProjectでは、qa_ratingの値が3よりも大きいものだけを抽出します。qa_ratingは、QualityProjectサブクラスで拡張されたフィールドなので、QualityProjectにダウンキャストして条件を記述しています。

 SELECT p FROM Project p
 WHERE TREAT(p AS QualityProject).qa_rating > 3 OR
       TYPE(p) = DesignProject
 [ DesignProject [ no: 3, name: Design Release1, employee_Sz: 4] ]
[ DesignProject [ no: 4, name: Design Release2, employee_Sz: 0] ]
[ DesignProject [ no: 5, name: Design Release3, employee_Sz: 0] ]
[ QualityProject [ no: 6, name: Test Release1, employee_Sz: 0, rating: 4] ]
[ QualityProject [ no: 7, name: Test Release2, employee_Sz: 2, rating: 5] ]
[ QualityProject [ no: 8, name: Test Release3, employee_Sz: 2, rating: 5] ]

読者になる

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

%d人のブロガーが「いいね」をつけました。