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.

@MarkRotteveel you're correct. I realized I didn't know what nativeQuery stands for and how I need to modify my query. I did some more research and fixed it. — Emin
– Emin, Commented Jan 11, 2023 at 12:20

Emin · Accepted Answer · 2023-01-10 22:30:23Z

0

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

edited Jan 10, 2023 at 22:30

answered Jan 10, 2023 at 22:29

Emin

35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JPQL Query with entity constructor

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related