JPQLでハマった話
やりたいこと（要件）
DBの社員表（emp）から、社員名（ename）のイニシャルでグループ化して人数をカウントする。
イニシャルと人数の両方を結果に含める。
★2014/12/21追記
追加調査しました。
続・JPQLでハマった話 - Java EE 事始め！
SQLでやってみた
DBはPostgreSQLでやります。
これは正しく実行でき、上記の要件を満たすSQLです。
select substr(ename,1,1),count(*) from emp group by substr(ename,1,1)
以下、区別のために、SQLは小文字、JPQLは大文字で書きます。
EclipseLinkでやってみた
環境設定はこちら↓
NetBeansとMavenでJPAプロジェクトの作成～エンティティ自動生成まで - Java EE 事始め！
JPQLの実行確認ツールについてはこちら↓
NetBeans7.3で嬉しかったこと！ - Challenge Java EE !
JPQLで、こう書きます。
SELECT SUBSTRING(e.ename,1,1),COUNT(e) FROM Emp e GROUP BY SUBSTRING(e.ename,1,1)
いざ、実行！すると・・・
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function
何、例外！？
よく読んでみると
「column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function」
とあります。
つまりenameを、GROUP BY句に追加するか、集計関数（COUNT）で使わなければならない、と。
前者にすると、実行はできますが要件と違ってしまうので、後者でやってみます。
SELECT SUBSTRING(e.ename,1,1),COUNT(e.ename) FROM Emp e GROUP BY SUBSTRING(e.ename,1,1)
これで実行すると・・・
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function
うーん、同じ例外です。
ちなみに、SELECT句からSUBSTRINGを外せば実行でき、正しい結果が返ってきますが、カウントに対応するイニシャルが分からなくなってしまいます。
-- これは実行可能 SELECT COUNT(e) FROM Emp e GROUP BY SUBSTRING(e.ename,1,1)
コンストラクタ式でやってみた
JPQLのコンストラクタ式についてはこちら↓
金魚本に載ってないJpqlの話 #glassfishjp
こんなDTOクラスを作ります。
public class SampleDto { private String initial; private Long count; public SampleDto(String initial, Long count) { this.initial = initial; this.count = count; } // 以下、setter/getter
で、こんなJPQL。
SELECT NEW jp.co.uhd.jpasample.dto.SampleDto(SUBSTRING(e.ename,1,1),COUNT(e)) FROM Emp e GROUP BY SUBSTRING(e.ename,1,1)
いざ実行。
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function
また同じ例外ですねー。
で、ここで参考書籍を紐解いてみます。
Amazon.co.jp： マスタリングJavaEE5 第2版 (DVD付) (Programmer’s SELECTION): 三菱UFJインフォメーションテクノロジー株式会社 斉藤 賢哉: 本
P.327に、「グループ化を行う場合、SELECT句には、GROUP BY句で指定したフィールド、集合関数、またはそれらを利用したコンストラクタ式しか記述できない」との旨が書いてあります。
うーむ。
副問い合わせでやったみた
困ってしまったので、アプローチを変えましょう。
いったん、副問い合わせでイニシャルだけの結果を作って、そこからグループ化してみましょう。
SQLだとこんな感じ。
select x.initial,count(*) from (select substr(ename,1,1) as initial from emp) as x group by x.initial
JPQLではこんな感じ。
SELECT x.initial,count(x) FROM (SELECT SUBSTRING(e.ename,1,1) as initial FROM Emp e) AS x GROUP BY x.initial
実行！
Exception Description: Problem compiling [SELECT x.initial,count(x) FROM (SELECT SUBSTRING(e.ename,1,1) as initial FROM Emp e) AS x GROUP BY x.initial]. [32, 90] '(SELECT SUBSTRING(e.ename,1,1) as initial FROM Emp e) AS x' cannot be the first declaration of the FROM clause.
また例外。がーん。
困ったときのJSRを確認。JPA 2.1はJSR-338です。
PDFの189ページに、このような記述があります。
Subqueries may be used in the WHERE or HAVING clause.
※オレオレ翻訳
副問い合わせはWHERE句とHAVING句で利用できる。
ってことは、SQLのように、FROM句で副問い合わせを書くことは出来ないんですね。
さて、これは困ったなあ・・・。
Hibernateでやってみた
待てよ、もしかして・・・別のJPA実装ならば出来たり・・・するかもなコレ。
ということで、今度はHibernateを使ってみます。
準備方法はこちら↓
JPA実装としてHibernateを使う時のMaven依存関係 - Java EE 事始め！
最初に作ったこのJPQLを書いて、いざ実行！
SELECT SUBSTRING(e.ename,1,1),COUNT(e) FROM Emp e GROUP BY SUBSTRING(e.ename,1,1)
すると・・・
出来てしまったｗｗｗなんということだｗｗｗ
念のため、またJSRを確認。PDFの196ページには、こうあります。
The requirements for the SELECT clause when GROUP BY is used follow those of SQL: namely,
any item that appears in the SELECT clause
(other than as an aggregate function or as an argument to anaggregate function)
must also appear in the GROUP BY clause.
※オレオレ翻訳
GROUP BYを使う場合、SELECT句は以下の事項が要求される：すなわち、
SELECT句に現れる全ての項目（集合関数およびその引数を除く）は、
GROUP BY句にも現れなければならない。
この文言のみから考えると、「SELECT句に現れる全ての項目（集合関数およびその引数を除く）」が「GROUP BY句にも現れ」ているので、上記のJPQLは正しいと言えます。
じゃあ何故、EclipseLinkでは動かなかったのだろう・・・？
しかもこの記述、上記のJava EE 5本の解説と微妙に違ってない？
念のため、Java EE 5の頃のJSR（JSR-220(persistence) 98ページ）を確認しましたが、上記の部分のJSRの記述は、一言一句変わっていませんでいた。
JSRの他のページで、何か補足事項があるのかもしれませんが、そこまでは追えませんでした。
で、結局EclipseLinkでやるには？
ネイティブSQL使うか、先ほどの副問い合わせに当たる部分をVIEWとして作ってそこからSELECTするか、この2つしか思いつきません。
これだけのためにVIEWを作るのは微妙な気がするので、前者かなあ・・・。
