現在、JavaEEで作っているアプリケーションに画面を追加する事になり、それまではJPAのJPQLを使ってのcreateQueryで済んでいたのですが、今回はGROUP BYとかUNIONとかでSQLを作る必要があり、JPQLでは無理そうなのでcreateNativeQueryで実装する事にしました。
で、最初はcreateQueryをcreateNativeQueryに置き換えてSQL文を噛ませばいいかなと思っていたのですが、どうやら結構違うらしい。。
一つ目の違いは、createQueryならテーブル定義に沿って作ったEntityに直接入れる事が出来ますが、ちょっと複雑なSQLだと問い合わせ結果に一致したEntityを作る事自体が難しい。
二つ目はバインド変数の違い。createQueryならバインド変数は[:PARAM]のように文字として指定して下記のように変数をセットして結果を取得できますが、
|
1 2 3 4 |
List<LoginUser> result = em.createQuery( "SELECT c FROM LoginUser c WHERE c.USER_TYPE < '5' AND c.STATUS_FLG < :PARAM ORDER BY c.USER_ID") .setParameter("PARAM", "9") .getResultList(); |
createNativeQueryでは[?]でないとダメという解りにくい相違点。。
結果的に下記のようになりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
String SQL="SELECT KEY,SUBKEY,SUM(AMOUNT) FROM TABLE_A WHERE REGDATE = ? AND (? IS NULL OR SUBKEY = ? ) " + "UNION SELECT KEY,SUBKEY,SUM(AMOUNT) FROM TABLE_B WHERE REGDATE = ? AND (? IS NULL OR SUBKEY = ? ) "; Query q = em.createNativeQuery(SQL); for (int i = 0 ; i < 6 ; i = i + 3){ q.setParameter(i+1, StringUtils.remove(StringUtils.remove(getDate(),"/"),":")); q.setParameter(i+2, getSubKey()); q.setParameter(i+3, getSubKey()); } List<Summary> list = new ArrayList<Summary> (); List<Object[]> results = q.getResultList(); for (Object[] it : results) { Summary sum = new Summary(); sum.setKey((String)it[0]); sum.setSubKey((String)it[1]); BigDecimal bd = (BigDecimal)it[2]; sum.setCount((int)bd.intValue()); list.add(sum); } |
※上記のSummaryは上記クエリ結果をセットする為だけのクラスです。
なお、数値はBigDecimalで扱うようです。
バインド変数の仕様違いにはちょっとハマりました。。