Hibernate 入門記 問い合せ その2 集計関数，継承，where

まずは昨日へぼへぼだった外部結合について．

from study.Magazine magazine left outer join magazine.model

HSQLDB のバージョンを 1.7.1 にもどしたところ，正しい結果を得ることが出来ました．

[CanCam(Shogakukan):[Yu Yamada(20), Asami Usuda(19), Yuri Ebihara(24)], Yuri Ebihara(24)]
[CanCam(Shogakukan):[Yu Yamada(20), Asami Usuda(19), Yuri Ebihara(24)], Yu Yamada(20)]
[CanCam(Shogakukan):[Yu Yamada(20), Asami Usuda(19), Yuri Ebihara(24)], Asami Usuda(19)]
[JJ(Kobunsha):[], null]
[ViVi(Kodansha):[Jun Hasegawa(18)], Jun Hasegawa(18)]
[Ray(Shufunotomo):[Karina(20)], Karina(20)]

これですよ．これが左外部結合というものですよ．
ちなみに 1.7.2 は 1.7.2.3 と同じでした．1.7.2.2 は試していませんが，きっと同じではないかと．
ということで，しばらく 1.7.1 でいくことにします．

問い合せ第二回目の今日は「11.5. Aggregate functions」から．
集計関数とか集約関数とか呼ばれるものですね．リファレンスには次のものが掲載されています．

• avg(...)
• sum(...)
• max(...)
• min(...)
• count(*)
• count(...)
• count(distinct ...)
• count(all ...)

ふむ．
点々の部分には，オブジェクトやプロパティを書けるようです．
まずは軽く試してみましょう．昨日のテーブルおよびサンプルコード，それにデータをそのまま使います．

select 
    count(*), min(model.age), max(model.age), avg(model.age) 
from 
    study.Model model

その実行結果．

[6, 18, 26, 21.0]

6人の平均年齢 21 歳かぁ．結構若かったのね．

group by を指定することも出来るそうです．

select 
    magazine.name, count(*), min(model.age), max(model.age), avg(model.age) 
from 
    study.Model model group by model.magazine

その実行結果．

net.sf.hibernate.QueryException: 
    could not resolve property: magazine of: study.Model [
        select magazine.name, count(*), min(model.age), 
            max(model.age), avg(model.age) 
        from study.Model model group by model.magazine]
．．．

ぐはぁっ，Model には雑誌への関連がありませんでした．(;_;)
外部キーはモデルのテーブルにあるのになぁ．もどかすぃ．．．
関連を双方向にしておけばよかったよ．しくしくしく．
ということで，雑誌ベースに変更．

select 
    magazine.name, count(*), min(model.age), max(model.age), avg(model.age) 
from 
    study.Magazine magazine inner join magazine.model model 
group by 
    magazine

その実行結果．

[CanCam, 3, 19, 24, 21.0]
[ViVi, 1, 18, 18, 18.0]
[Ray, 1, 20, 20, 20.0]

ふむふむ．いい感じ♪

distinct と all も指定できるそうです．
まずは distinct．

select 
    count(distinct model.age), min(model.age), max(model.age), avg(model.age) 
from 
    study.Model model

その実行結果．

[5, 18, 26, 21.0]

モデルは 6 人なのですが，優と香里奈は同い年なので最初の項目が 5 になってます．おっけー．
次に all．

select 
    count(all model.age), min(model.age), max(model.age), avg(model.age) 
from 
    study.Model model

その実行結果．

java.sql.SQLException: 
    Unexpected token: ALL in statement [
        select count(all model0_.age) as x0_0_, min(model0_.age) as x1_0_, 
            max(model0_.age) as x2_0_, avg(model0_.age) as x3_0_ 
        from Model model0_]
．．．

ぐはぁっ，HSQLDB では all は指定できないようです．
all って何も指定しないときと同じだよね？ だったよね？ だったら別に指定できなくてもいいや．

なぜかここで，集約関数の中ではなくて普通に SELECT 句に DISTINCT を指定する例が掲載されています．
なので，ちょっとやってみますか．

select distinct model.age from study.Model model

その実行結果．

18
19
20
24
26

ふむ．
ついでに昨日の fetch join も試してみましょう．

select 
    distinct magazine 
from 
    study.Magazine magazine 
        left outer join fetch magazine.model

その実行結果．

CanCam(Shogakukan):[Yuri Ebihara(24), Asami Usuda(19), Yu Yamada(20)]
CanCam(Shogakukan):[Yuri Ebihara(24), Asami Usuda(19), Yu Yamada(20)]
CanCam(Shogakukan):[Yuri Ebihara(24), Asami Usuda(19), Yu Yamada(20)]
JJ(Kobunsha):[]
ViVi(Kodansha):[Jun Hasegawa(18)]
Ray(Shufunotomo):[Karina(20)]

ぐはぁっ．(ToT)
なぜ？ なぜにそうなる？ distinct しているのにどうして CanCam が3回返ってくるわけ？
Hibernate が実行した SQL を見ると．．．

select 
    distinct magazine0_.id as id0_, model1_.id as id1_, magazine0_.name as name0_, 
    magazine0_.publisher as publisher0_, model1_.name as name1_, model1_.age as age1_, 
    model1_.magazine as magazine__, model1_.id as id__ 
from 
    Magazine magazine0_ 
        left outer join Model model1_ on magazine0_.id=model1_.magazine

そういうことかぁ．雑誌だけじゃなくてモデルを含めた行に distinct が効いちゃっているのですね．参ったな．．．
うーむ，

from study.Magazine

と同じ結果を返しながら，一回の SQL で雑誌もフェッチしてしまう HQL は書けないのだろうか？ 残念！！！！
次行きますよ！ 次，次！！ (エビちゃん風)

次は「11.6. Polymorphic queries」です．
しまった，サンプルでは継承使ってなぁーい！！ 残念！！！！
でも大丈夫です．こんなこともできるそうです．

from java.lang.Object

その実行結果．

Sayo Aizawa(26)
Yuri Ebihara(24)
Yu Yamada(20)
Asami Usuda(19)
Jun Hasegawa(18)
Karina(20)
CanCam(Shogakukan):[Asami Usuda(19), Yu Yamada(20), Yuri Ebihara(24)]
JJ(Kobunsha):[]
ViVi(Kodansha):[Jun Hasegawa(18)]
Ray(Shufunotomo):[Karina(20)]

全ての永続クラスは Object のサブクラスなので，この問い合せで全ての永続オブジェクトを取得することが出来ます．
FROM 句に interface を指定すると，その interface を implements した全ての永続クラスのインスタンスを取得することが出来るそうです．
便利そうですが，裏では永続クラスごとに複数の SQL が発行されることになるので注意が必要です．
また，問い合せ結果は，それぞれの永続クラスごとに発行された SQL の結果を単純につないだものになるようです．
SQL を発行する順序は指定できないようなので，ORDER BY を指定しても意図したとおりにならないとのこと．
例えば雑誌もモデルも name というプロパティを持っているので，

intervade Named {
    String getName();
}

のような interface を用意しておけば，

from Named named order by named.name

のような問い合せが出来ますが，その結果は例えば前半にモデルが名前順で，後半は雑誌が名前順で，となってしまうということです．
あまり使うとは思えないので，どうでもよさげ．

次は「11.7. The where clause」です．
当然ながら，WHERE 句を使うことが出来ます．

from study.Model model where model.age > 20

その実行結果．

Sayo Aizawa(26)
Yuri Ebihara(24)

あらら，二十歳以上は二人しかいないのかぁ．

FROM 句で明示的に結合しなくても，WHERE 句の条件で関連を辿ると勝手に結合 (inner join) してくれるようです．

from study.Magazine magazine where magazine.model.age > 20

その実行結果．

net.sf.hibernate.QueryException: 
    expecting 'elements' or 'indices' after: age [
        from study.Magazine magazine where magazine.model.age > 20]

ぐはぁっ，何が起きたわけ！？
いろいろ試行錯誤したのですが，どうにもうまくできません．
age の後に期待されている 'elements' or 'indices' って？？
どうやら，雑誌にとってモデルは対多関連であるため，この問い合わせでは magzin.model.age が集合になってしまうのでダメみたいです．対 1 関連か，もしくはリストやマップなど，要素を一つに絞れるものでないとダメみたい．
しかし，今のサンプルでは対 1 関連もリストも使っていません．残念！！！！
うーみゅ，とりあえず，宿題にしておきます．．． 心より恥じる．
次行きますよ！ 次，次！！ (エビちゃん風)

IS NULL とか IS NOT NULL とかも指定できるようです．

from 
    study.Magazine magazine 
        left outer join magazine.model model 
where 
    model is null

その実行結果．

[CanCam(Shogakukan):[Asami Usuda(19), Yuri Ebihara(24), Yu Yamada(20)], null]
[JJ(Kobunsha):[], null]
[ViVi(Kodansha):[Jun Hasegawa(18)], null]
[Ray(Shufunotomo):[Karina(20)], null]

あれれ？ JJ だけになるはずだったのに？
うーみゅ，HSQLDB めぇ，左外部結合だといっているのにまた同じ過ちをしやがったな．．．
このとき Hibernate が発行した SQL は次のもの．

select 
    magazine0_.id as id0_, model1_.id as id1_, magazine0_.name as name0_, 
    magazine0_.publisher as publisher0_, model1_.name as name1_, model1_.age as age1_ 
from 
    Magazine magazine0_ 
        left outer join Model model1_ on magazine0_.id=model1_.magazine 
where 
    (model1_.id is null )

別におかしくないような？
でも，この SQL を実行すると，なぜか全ての雑誌と NULL だらけのモデルの組が返ってきます．
試しに WHERE 句を外してみると，ちゃんとした結果が返ってきます．
WHERE 句を付けると結果セットに行が追加されるなんて！！ そんなのあり得なぁ〜い！！ よって，切腹！！！！
そうかぁ，1.7.1 なら大丈夫ってわけじゃないんだぁ．1.7.1 でも 1.7.2 でも，HSQLDB の外部結合はだめだめ！！ 間違いない．

気を取り直して．
小文字の id というプロパティは，永続オブジェクトの ID プロパティを表すそうです．
ということは，ID プロパティ以外で id という名前のプロパティがあったらやばそう．どっちが優先されるんだろ？ 僕はいい子なのでそんなことしませんけど．
念のためお試し．

from study.Model model where model.id = 0

その実行結果．

Sayo Aizawa(26)

ふむ．

この「11.7. The where clause」はもう少し続くのですが，継承やコンポジションなどを使っていないと試せない感じ．
なので，残りとさっきの宿題は次回ということで．心機一転，サンプルも新たに再スタートします！！！！
でも，モデルがネタであることに変わりはないはず．なんせこの日記のメインですから．残念！！！！