Top > MyPage
 

hibernateで集約関数を使用する

hibernateがサポートしている集約関数一覧

avg(...)
平均値を求める
sum(...)
合計値を求める
min(...)
最小値を求める
max(...)
最大値を求める
count(*)
テーブルに含まれる全行の件数を求める
count(...)
テーブルの指定列の件数を求める
count(distinct ...)
テーブルの指定列の重複した値を除いた行数を求める
count(all ...)
allを省略した書き方と同じ。count(all *),count(all column_name)がある

前準備

テーブルの作成

hibernateで集約関数を使用するために、まずはテーブルを作成する。

prefecturesテーブル

CREATE TABLE prefectures
(
  id serial NOT NULL,
  prefecture_name character varying(5),
  area numeric(8,2),
  region character varying(5)
)

id:prefectureテーブルのid  prefecture_name:都道府県名  area:面積  region:地方(東北とか関東とか) 

DAO,DTOの作成

特別なものは作成していないので今回は割愛する。Prefectureクラスのクラス図は、

[<figure src="" alt=""></figure> is illegal in <p>]

prefecturesテーブルにデータをインサート

国土地理院の都道府県別面積から以下のようなcsvを作成しテープルにインサートする。

prefectures.csv
id prefecture_name area region
1 北海道 83,456.58 北海道
2 青森 8,918.51 東北
3 岩手 15,278.86 東北
4 宮城 6,862.10 東北
5 秋田 11,434.28 東北
6 山形 6,652.11 東北
7 福島 13,782.75 東北
8 茨城 6,095.69 関東
9 栃木 6,408.28 関東
10 群馬 6,363.16 関東
11 埼玉 3,767.09 関東
12 千葉 5,081.91 関東
13 東京 2,102.95 関東
14 神奈川 2,415.84 関東
15 新潟 10,363.39 中部
16 富山 2,045.73 中部
17 石川 4,185.54 中部
18 福井 4,189.54 中部
19 山梨 4,201.17 中部
20 長野 13,104.95 中部
21 岐阜 9,768.20 中部
22 静岡 7,329.39 中部
23 愛知 5,115.65 中部
24 三重 5,761.47 近畿
25 滋賀 3,766.90 近畿
26 京都 4,613.01 近畿
27 大阪 1,897.85 近畿
28 兵庫 8,395.84 近畿
29 奈良 3,691.09 近畿
30 和歌山 4,726.29 近畿
31 鳥取 3,507.26 中国
32 島根 6,707.86 中国
33 岡山 7,009.58 中国
34 広島 8,479.05 中国
35 山口 6,113.81 中国
36 徳島 4,146.55 四国
37 香川 1,862.28 四国
38 愛媛 5,677.73 四国
39 高知 7,105.13 四国
40 福岡 4,844.87 九州
41 佐賀 2,439.60 九州
42 長崎 4,104.48 九州
43 熊本 7,076.73 九州
44 大分 5,099.39 九州
45 宮崎 6,346.16 九州
46 鹿児島 9,044.34 九州
47 沖縄 2,275.91 九州

マッピングファイルの作成

Prefecture.hbm.xmlは以下のように作成した。

Prefecture.hbm.xml

<?xml version="1.0" encoding="Windows-31J"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" \
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.chikkun.hibernate.sato.Prefecture" table="prefectures" lazy="false">
    
        <id name="id" column="id">
            <generator class="sequence">
                <param name="sequence">prefectures_id_seq</param>
            </generator>
        </id>
    <property name="prefectureName" column="prefecture_name"/>
    <property name="area" column="area"/>
    <property name="region" column="region"/>
  </class>
</hibernate-mapping>

集約関数の使い方

avg(...)

Aggregation.java

 1:     public static void main(String[] args) {
 2:         
 3:         Configuration config = new Configuration().configure();
 4:         SessionFactory sessionFactory = config.buildSessionFactory();
 5:         Session sess = sessionFactory.openSession();
 6:         
 7:         Query query = getQuery(sess);
 8:         List list = query.list();
 9: 
10:         System.out.println(list.get(0));
11:         sess.close();
12:     }
13:     
14:     private static Query getQuery(Session sess) {
15:         String strQuery = "select avg(pref.area) from Prefecture as pref";
16: 
17:         Query query = sess.createQuery(strQuery);
18:         return query;
19:     }

mainメソッドを上記のように作成し、avg(...)関数を使用して都道府県の面積の平均を求めてみる。

hqlは、

String strQuery = "select avg(pref.area) from Prefecture as pref";

実行結果1

1:      [java] Hibernate: select avg(prefecture0_.area) as col_0_0_ from prefectures prefecture0_
2:      [java] 7736.528723404255

実行結果は、7736.528723404255平方キロメートルとなった。

静岡県の面積が、日本の平均位のようだ。

sum(...)

次はsum(...)関数を使用して全国の面積の合計を求めてみる。

hqlは

String strQuery = "select sum(pref.area) from Prefecture as pref";

実行結果2

1:      [java] Hibernate: select sum(prefecture0_.area) as col_0_0_ from prefectures prefecture0_
2:      [java] 363616.85

面積の合計は363616.85平方キロメートルとなった。

今度はregionをgroup by句に入れてもう一度実行する。

String strQuery = "select new map(sum(pref.area), pref.region) from Prefecture as pref group by pref.region";

実行結果3

 1:      [java] Hibernate: select sum(prefecture0_.area) as col_0_0_, prefecture0_.region as col_1_0_ 
 2:      from prefectures prefecture0_ group by prefecture0_.region
 3:      [java] -------------------
 4:      [java] 九州
 5:      [java] 41231.48
 6:      [java] -------------------
 7:      [java] 関東
 8:      [java] 32234.92
 9:      [java] -------------------
10:      [java] 四国
11:      [java] 18791.69
12:      [java] -------------------
13:      [java] 東北
14:      [java] 62928.61
15:      [java] -------------------
16:      [java] 中部
17:      [java] 60303.56
18:      [java] -------------------
19:      [java] 近畿
20:      [java] 32852.45
21:      [java] -------------------
22:      [java] 中国
23:      [java] 31817.56
24:      [java] -------------------
25:      [java] 北海道
26:      [java] 83456.58

地域ごとの面積の合計が求められる。

min(...)

次はmin(...)関数を使用する。

日本の都道府県の面積の最小値を求める。

String strQuery = "select min(pref.area) from Prefecture as pref";

実行結果4

1:      [java] Hibernate: select min(prefecture0_.area) as col_0_0_ from prefectures prefecture0_
2:      [java] 1862.28

この例だと面積しか表示されないが、日本で一番面積が小さいのは香川県。

group by句にregionを含めてもう一度実行する。

String strQuery = "select new list(min(pref.area), pref.region) from Prefecture as pref group by pref.region";

実行結果5

 1:      [java] Hibernate: select min(prefecture0_.area) as col_0_0_, prefecture0_.region as col_1_0_ 
 2:      from prefectures prefecture0_ group by prefecture0_.region
 3:      [java] -------------------
 4:      [java] 2275.91
 5:      [java] 九州
 6:      [java] -------------------
 7:      [java] 2102.95
 8:      [java] 関東
 9:      [java] -------------------
10:      [java] 1862.28
11:      [java] 四国
12:      [java] -------------------
13:      [java] 6652.11
14:      [java] 東北
15:      [java] -------------------
16:      [java] 2045.73
17:      [java] 中部
18:      [java] -------------------
19:      [java] 1897.85
20:      [java] 近畿
21:      [java] -------------------
22:      [java] 3507.26
23:      [java] 中国
24:      [java] -------------------
25:      [java] 83456.58
26:      [java] 北海道

max(...)

次は面積の最大値を求める。

間違いなく北海道の面積が表示されるはずだが念のため実行する。

String strQuery = "select max(pref.area) from Prefecture as pref";

実行結果6

1:      [java] Hibernate: select max(prefecture0_.area) as col_0_0_ from prefectures prefecture0_
2:      [java] 83456.58

count(*)

次はcount(*)を使用する。

String strQuery = "select count(*) from Prefecture as pref";

実行結果7

1:      [java] Hibernate: select count(*) as col_0_0_ from prefectures prefecture0_
2:      [java] 47

count(...)

続いて、count(...)関数。

String strQuery = "select count(pref.prefectureName) from Prefecture as pref";

実行結果8

1:      [java] Hibernate: select count(prefecture0_.prefecture_name) as col_0_0_ from prefectures prefecture0_
2:      [java] 47

一時的に東北6県のprefectureNameにnullをセットし、再び同じメソッドを実行する。

実行結果9

1:      [java] Hibernate: select count(prefecture0_.prefecture_name) as col_0_0_ from prefectures prefecture0_
2:      [java] 41

この結果から、nullの列はcount(...)に含まれないことが分かる。

count(distinct ...)

東北6県のprefectureNameを元に戻して、今度はcount(distinct ...)関数を使用する。

String strQuery = "select count(distinct pref.region) from Prefecture as pref";

実行結果10

1:      [java] Hibernate: select count(distinct prefecture0_.region) as col_0_0_ from prefectures prefecture0_
2:      [java] 8

地域(region)は北海道、東北、関東、中部、近畿、中国、四国、九州の計8地域なので

実行結果は正しいことが分かる。

今度は秋田のregionにnullをセットした後に、再び同じメソッドを実行してみる。

id prefecture_name area region
5 秋田 11434.28

実行結果11

1:      [java] Hibernate: select count(distinct prefecture0_.region) as col_0_0_ from prefectures prefecture0_
2:      [java] 8

distinctに指定した列にnullが含まれていても、実行結果は変わらない事が分かる。

having,order byも一緒に用いる

地域ごとの面積の合計を求め、その値が4万平方km以上の地域のみを表示したい場合を仮定する。

地域ごとの面積の合計は、

    "select sum(pref.area) from Prefecture as pref "
        + "group by pref.region";

実行結果12

 1:      [java] Hibernate: select sum(prefecture0_.area) as col_0_0_ from prefectures prefecture0_ 
 2:      group by prefecture0_.region
 3:      [java] 41231.48
 4:      [java] 32234.92
 5:      [java] 18791.69
 6:      [java] 62928.61
 7:      [java] 60303.56
 8:      [java] 32852.45
 9:      [java] 31817.56
10:      [java] 83456.58

このように求められる。

ここで、面積が4万平方kmの地域のみを抽出したい場合、having句を用いる。

having

             String strQuery = "select new list(pref.region, sum(pref.area)) "
                          + "from Prefecture as pref "
                          + "group by pref.region "
                          + "having sum(pref.area) >= 40000 ";

この例ではhaving句に、地域の面積の合計が4万平方km以上の場合、という条件を追加している。

このhqlを実行すると、

実行結果13

 1:      [java] Hibernate: select prefecture0_.region as col_0_0_, sum(prefecture0_.area) as col_1_0_ 
 2:      from prefectures prefecture0_ group by prefecture0_.region having sum(prefecture0_.area)>=40000
 3:      [java] -------------------
 4:      [java] 九州
 5:      [java] 41231.48
 6:      [java] -------------------
 7:      [java] 東北
 8:      [java] 62928.61
 9:      [java] -------------------
10:      [java] 中部
11:      [java] 60303.56
12:      [java] -------------------
13:      [java] 北海道
14:      [java] 83456.58

上記のように、地域の面積の合計が4万平方km以上の地域のみ抽出することができる。

having句を使用する上での注意点として、 where句で置き換えることが可能な場合は、where句を使用した方が速度の向上が見込めるというものがある。 理由は、where句を使用した場合はインデックス検索を利用できるのに対し、 having句を使用した検索は全件検索になるからである。

order by

上記のhqlに、order by句を追加して実行する。

             String strQuery = "select new list(pref.region, sum(pref.area)) "
                          + "from Prefecture as pref "
                          + "group by pref.region "
                          + "having sum(pref.area) >= 40000 "
                          + "order by sum(pref.area)";

実行結果14

 1:      [java] Hibernate: select prefecture0_.region as col_0_0_, sum(prefecture0_.area) as col_1_0_ 
 2:      from prefectures prefecture0_ group by prefecture0_.region having sum(prefecture0_.area)>=40000 
 3:      order by sum(prefecture0_.area)
 4:      [java] -------------------
 5:      [java] 九州
 6:      [java] 41231.48
 7:      [java] -------------------
 8:      [java] 中部
 9:      [java] 60303.56
10:      [java] -------------------
11:      [java] 東北
12:      [java] 62928.61
13:      [java] -------------------
14:      [java] 北海道
15:      [java] 83456.58

このように、ソートを行うことができる。

order by句にDESCを付けた場合は、

        String strQuery = "select new list(pref.region, sum(pref.area)) "
            + "from Prefecture as pref "
            + "group by pref.region "
            + "having sum(pref.area) >= 40000 "
            + "order by sum(pref.area) desc";

実行結果15

 1:      [java] Hibernate: select prefecture0_.region as col_0_0_, sum(prefecture0_.area) as col_1_0_ 
 2:      from prefectures prefecture0_ group by prefecture0_.region having sum(prefecture0_.area)>=40000 
 3:      order by sum(prefecture0_.area) desc
 4:      [java] -------------------
 5:      [java] 北海道
 6:      [java] 83456.58
 7:      [java] -------------------
 8:      [java] 東北
 9:      [java] 62928.61
10:      [java] -------------------
11:      [java] 中部
12:      [java] 60303.56
13:      [java] -------------------
14:      [java] 九州
15:      [java] 41231.48

降順でのソートが行われる。

order by句の最後にASCを付けると昇順DESCを付けると降順でソートされる。 省略した場合はASCで実行される。

参考ページ

【初級】新人SEのためのSQLの基礎 第3回(後半) 集約関数,GROUP BY句,HAVING句の注意点

SQL で考える —— HAVING句の力