How to use a SQL sum group using Spring JPA data?

Question

I want to download best-selling items by quantity. These are my tables:

Product id name 1 AA 2 BB Productorder order_id product_id quantity 1 1 10 2 1 100 3 2 15 4 1 15 

This is my Spring data repository:

 @Repository public interface ProductRepository extends JpaRepository<Product, Long> { @Query(value = "select top 5 p.name, sum(po.quantity) as total_quantity from product p " + "inner join productorder po " + "on p.id = po.product_id " + "group by p.id, p.name " + "order by total_quantity desc", nativeQuery = true) List<Product> findTopFiveBestSeller(); } 

I get an HsqlException: column not found: id

I think this error has nothing to do with the id column, as it exists for both tables. Does the "sum group" execute Spring data? Since it seems a little strange to me, since Spring Data should select only product attributes from the database, and with this sql we also select the sum (po.quantity). Can Spring data handle this and convert the result to a list?

PS: I am using HSQLDB built into DB.

Answer 1

After changing the prediction of select p.name from p.name to p.* , To indicate that I select multiple values, not just String objects that should be magically converted to Product objects, this works:

 @Repository public interface ProductRepository extends JpaRepository<Product, Long> { @Query(value = "select top 5 p.*, sum(po.quantity) as total_quantity from product p " + "inner join productorder po " + "on p.id = po.product_id " + "group by p.id, p.name " + "order by total_quantity desc", nativeQuery = true) List<Product> findTopFiveBestSeller(); } 

Thanks @JMK und @JB Nizet.