JPQL Native Query using SUM and COUNT

问题:

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.

回答1:

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.

回答2:

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.