CODEWITHSUNDEEP
javajpaejb-3.0
Arisu
Arisu

Reputation: 13

jpa select can not work

select id,name,amount,sort 
from 
(select id,name,amount,sort,gift_id,count(gift_id) 
from 
(select * from t_lpw_gift g join t_lpw_dianxin_gift d on g.id=d.gift_id) 
group by gift_id,id,name,amount,sort order by count(gift_id) desc) 
where rownum <=5;

how to use jpa createNativeQuery to select,database is oracle

table:

SQL> desc t_lpw_gift;
Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
ID     INTEGER                                
NAME   VARCHAR2(32)                           
PRICE  NUMBER                                 
AMOUNT INTEGER                                
SORT   VARCHAR2(32) Y                         

SQL> desc t_lpw_dianxin_gift;
Name        Type    Nullable Default Comments 
----------- ------- -------- ------- -------- 
GIFT_ID     INTEGER                           
DIANXIN_ID  INTEGER                           
GIFT_NUMBER INTEGER Y

return Entity is t_lpw_gift

public List<Gift> getHotGifts(int topHotGiftsNum) {
    String sql = "select id,name,amount,sort from (select id,name,amount,sort,gift_id,count(gift_id) from (select * from t_lpw_gift g join t_lpw_dianxin_gift d on g.id=d.gift_id) group by gift_id,id,name,amount,sort order by count(gift_id) desc) where rownum <= :topHotGiftsNum";
return (List<Gift>)em.createNativeQuery(sql,Gift.class).setParameter("topHotGiftsNum", topHotGiftsNum).getResultList();
}

18:55:11,473 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 17006, SQLState: 99999 18:55:12,992 ERROR [org.hibernate.util.JDBCExceptionReporter] 列名无效()

Upvotes: 1

Views: 7809

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109613

The comment of @Kent make me search the keywords: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/reservewords.htm

It seems that NAME might be the culprit as reserved word. Was not aware of that (always using SURNAME and such). However NAME should be allowable. Try quoting NAME and SORT.

Upvotes: 0

Andrew Spencer
Andrew Spencer

Reputation: 16504

On native SQL parameters in JPA, you need to use a positional parameter instead of a named parameter. Named parameters are not supported according to the spec.

So, change :topHotGiftsNum to ?1, and setParameter(1, topHotGiftsNum).

See this answer

Edit: OK, that wasn't it. This should be it: the select clause is missing some property from the entity, or one of the columns in the select clause is mis-typed. Your select clause needs to include all of the columns from the entity, without any difference in names. As per this thread from Hibernate forums.

Upvotes: 1

Related Questions