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.

Solution

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.

Comments

[Kevin]

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).

[DataNucleus]

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

[Kevin]

When I combined this advice with NOT using query parameters, and simply piecing together strings, the query worked, thanks.

[Prashant]

In my case it returns BigInteger, I typically cast it to Number, see also stackoverflow.com/questions/8342572/…