JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » EJB / 其它Java EE 討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友   
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
作者 JPA NativeQuery Count SQL
hachiroku





發文: 33
積分: 0
於 2012-12-24 14:11 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
各位先進

小弟在 JPA NativeQuery 上下 COUNT 語法有些疑問

假設不使用 createQuery 而使用 createNativeQuery 的 getSingleResult()

1
Query query = entityManager.createNativeQuery("select count(*) from employee ");


嘗試以下方式輸出 COUNT 後的結果

1.失敗
1
2
int answer = ((Integer)query.getSingleResult()).intValue();
System.out.println(answer);

結果: java.lang.ClassCastException: java.util.Vector cannot be cast to java.lang.Integer

2.失敗
1
2
int answer = ((Number)query.getSingleResult()).intValue();
System.out.println(answer);

結果: java.util.Vector cannot be cast to java.lang.Number

3.失敗
1
2
3
Object[] obj = (Object[])query.getSingleResult();
int answer = (Integer)obj[0];
System.out.println(answer);

結果: java.lang.ClassCastException: java.util.Vector cannot be cast to [Ljava.lang.Object;

4.失敗
1
2
3
Object[] obj = (Object[])query.getSingleResult();
int answer = (Integer)obj[0];
System.out.println(answer);

結果: java.lang.ClassCastException: java.util.Vector cannot be cast to [Ljava.lang.Object;

5.失敗, 文件上似乎是寫明 COUNT 回傳是 LONG...
1
2
Long answer = (Long)query.getSingleResult();
System.out.println(answer);

結果: java.lang.ClassCastException: java.util.Vector cannot be cast to java.lang.Long

6.成功, 使用 getResultList()
1
2
System.out.println(((List)((List)query.getResultList()).get(0)).get(0).toString());
//再轉 Integer


會成功的原因應該是 JPA 1.0 (TopLink Essentials) 使用 getResultList() 回傳 List<List<Object>>
所以必須解開兩層...

本次疑問在...有沒有更聰明的寫法!!
像是透過 getSingleResult(), 畢竟 COUNT 是單一結果值
又或者為何文件上要寫明 COUNT 回傳 LONG ? 還是那是 JPA 2.0?

還麻煩大家幫忙解惑! 感恩

Smile


reply to postreply to post
作者 Re:JPA NativeQuery Count SQL [Re:hachiroku]
kentyeh





發文: 641
積分: 6
於 2012-12-24 22:39 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
剛剛看了一下JSR 220的規格 ,
顯然從JPA 1.0的開始 getSingoleResult的定義一直都沒有變過
The Query.getSingleResult method is used for this query because the query returns a single value

但是沒有看到位何說明有關這一單一回傳物件是什麼(Long?Vector?...),
看來只好各自表述,這裡查到一篇說明Oracle Native Query會回傳Vetcotr。

我的疑問是 什麼樣的情形 Count語法一定要用到Native Query?
1
SELECT COUNT(p) FROM employee  AS p WHERE ....
不行嗎?
就算是JOIN
1
SELECT COUNT(DISTINCT p) FROM employee p , in (p.projects) as prj WHERE ...

真的有必要用到Native SQL Query嗎?

如果是,那麼只好看您的資料庫回傳什麼,進行強迫轉型了


reply to postreply to post
作者 Re:JPA NativeQuery Count SQL [Re:kentyeh]
hachiroku





發文: 33
積分: 0
於 2012-12-25 11:36 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
感謝回覆!

剛剛又找了一下

http://download.oracle.com/otn-pub/jcp/ejb-3_0-fr-eval-oth-JSpec/ejb-3_0-fr-spec-persistence.pdf

上面那份檔案 JSR 220: Enterprise JavaBeans, Version 3.0 Java Persistence API

提到下述內容

4.8.4 Aggregate Functions in the SELECT Clause

....略

The Java type that is contained in the result of a query using an aggregate function is as follows:
• COUNT returns Long.
• MAX, MIN return the type of the state-field to which they are applied.
• AVG returns Double.


不過看起起來似乎是針對 JPQL 的 SELECT 語法 Sad

至於為何要用 Natice Query 是因為...改別人的 CODE 加上 JPQL 複雜一點的不熟 Sad

BTW, 請問一下 JPQL 語法如何在 DB 提供的界面進行語法測試 ?!! (這疑問一直沒解開...)

感謝解惑!


reply to postreply to post
作者 Re:JPA NativeQuery Count SQL [Re:hachiroku]
kentyeh





發文: 641
積分: 6
於 2012-12-25 12:34 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我沒有看過JPQL 有界面可以測試,可能得自已寫

其實JPQL沒有想像難,jee6 tutorial裡有很多範例,參考一下就會寫了


reply to postreply to post
作者 Re:JPA NativeQuery Count SQL [Re:kentyeh]
hachiroku





發文: 33
積分: 0
於 2012-12-26 15:35 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
感謝回覆, 會再多加練習

大感謝啊! SmileSmile


reply to postreply to post
» JWorld@TW »  EJB / 其它Java EE 討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8