JPQL Query with entity constructor-CodePudding

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.

CodePudding user response：

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