JPQL Native Query using SUM and COUNT

Question

I am trying to run the following code:

public BigDecimal valuate(String searchTerms, String categoryPath) {
    Query query = em.createNativeQuery("SELECT SUM(maxBidAmount) / COUNT(maxBidAmount) FROM Item WHERE MATCH(title) AGAINST(':searchTerms') AND categoryPath=':categoryPath'", Double.class);
    query.setParameter("searchTerms", searchTerms);
    query.setParameter("categoryPath", categoryPath);
    double value = (double) query.getSingleResult();
    return new BigDecimal(value);
}

When I do so, I get the following exception:

Exception Description: Missing descriptor for [class java.lang.Double].

When I remove Double.class, I get a different exception.

So, I'm just wondering the correct method of using COUNT and SUM with JPQL.

With hibernate 4 and Oracle driver and no explicitely specified type I get "Number" as the class, which can be used with Number#intValue(). Maybe you can try `Number.class` instead of `Double.class`? int value = ((Number)query.getSingleResult()).intValue(); — eckes, Jan 12 '16 at 14:57

score 6 · Accepted Answer · answered Jan 23 '12 at 13:56

6

IF the SQL is valid, you do not need to specify the Double.class in the query def - just use em.createNativeQuery(SQLString); The return type is used when you want the JPA provider to build an entity from the results, but in this case you want the raw data.

answered Jan 23 '12 at 13:56

Chris

20,138
2
29
43

When I combined this advice with NOT using query parameters, and simply piecing together strings, the query worked, thanks. – Kevin Jan 23 '12 at 20:59
In my case it returns BigInteger, I typically cast it to Number, see also http://stackoverflow.com/questions/8342572/retrieving-single-value-using-jpa/8342658 – eckes Jan 12 '16 at 15:13

score 0 · Answer 2 · answered Jan 22 '12 at 08:14

0

Native query is SQL, not JPQL, and you use those keywords just like any SQL for your RDBMS. Looks like your JPA provider doesn't accept Double as a result class (some only allow the result class to be an Entity).

DataNucleus JPA certainly allows non-Entity result classes.

answered Jan 22 '12 at 08:14

DataNucleus

15,497
3
32
37

Yes, you are right. It is not JPQL. Double is a simple type, so I would have assumed that it would be supported. I have a feeling it is my errornous approach rather than a defficiency in the JPA implementation (Eclipse Link). – Kevin Jan 22 '12 at 08:41
You should use numbered parameters ?1, ?2 etc instead of :param1 when using SQL via JPA. That doesn't fix the Double part, but as I already said ... some implementations would support that – DataNucleus Jan 22 '12 at 09:43

JPQL Native Query using SUM and COUNT

2 Answers2

Linked