JPQL Query with entity constructor

Question

I'm having trouble with building a query that groups 'Smoke' entity by their 'activity' column and sums 'quantity' of them. Query:

@Query(value = "SELECT new com.eminyilmazz.smoketracker.dto.ActivityBasedQuantity(s.activity, SUM(s.quantity)) FROM smoke s WHERE s.smoked_date BETWEEN :beginDate AND :endDate GROUP BY s.activity", nativeQuery = true)
List<ActivityBasedQuantity> getTotalQuantityGroupedByActivityWithMinuteInterval(@Param("beginDate")LocalDateTime beginDate, @Param("endDate") LocalDateTime endDate);

DTO:

public class ActivityBasedQuantity {
    private String activity;
    private Long quantity;

    public ActivityBasedQuantity(String activity, Long quantity) {
        this.activity = activity;
        this.quantity = quantity;
    }

The first line in the error:

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT new com.eminyilmazz.smoketracker.dto.ActivityBasedQuantity(s.activity, SUM(s.quantity)) FROM smoke s WHERE s.smoked_date BETWEEN ? AND ? GROUP BY s.activity]; SQL [n/a]

I've tried removing "nativeQuery = true" that is suggested from this stackoverflow question but it throws an error that says spring bean factory and hibernate couldn't resolve 'smoke' table and its attributes.

Answer 1

Fixed the issue with these changes;

Replaced 'smoke' to 'Smoke' (typo)

Removed 'nativeQuery = true'

@Query("SELECT new com.eminyilmazz.smoketracker.dto.ActivityBasedQuantity(s.activity, SUM(s.quantity)) FROM Smoke s WHERE s.smokedDate BETWEEN :beginDate AND :endDate GROUP BY s.activity")
    List<ActivityBasedQuantity> getTotalQuantityGroupedByActivityWithMinuteInterval(@Param("beginDate")LocalDateTime beginDate, @Param("endDate") LocalDateTime endDate);