CODEWITHSUNDEEP
javaspringjpajpql
Benjamin Gowers
Benjamin Gowers

Reputation: 183

How do I Return SUM from JPA Query Using Hibernate and Spring-boot?

I am trying to use JPA and JPQL to query my entity and return the sum of a column (total days) from the table. I thought I had set it up right but I am getting this error:

Caused by: org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'myRepository':
Invocation of init method failed; nested exception is
java.lang.IllegalArgumentException: Validation failed for query for method
public abstract java.lang.Float
com.nissan.rca.repository.MyRepository.selectTotals()!

Here is a representation of my entity:

@Entity
@Table(name = "TABLENAME")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class MyEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private MyEntityCompositeKey myEntityCompositeKey;

    @Column(name = "raiser_id")
    private String raiserID;

    @Column(name = "total_days")
    private Float totalDays;

and here is my repository in which I make the query assigned to a method:

@Repository
public interface MyRepository extends JpaRepository<MyEntity, ID> {
    @Query("SELECT SUM(total_days) FROM MyEntity")
    Float selectTotals();
}

I call the selectTotals() method from myRepository object in my rest controller for the api mapping.

@GetMapping("/getForecastTotals")
public Float getForecastTotals() {
    return myRepository.selectTotals();
}

I'm unsure as to why it can't be returned as a float.

Upvotes: 18

Views: 76712

Answers (3)

sendon1982
sendon1982

Reputation: 11234

You can do like this by mapping a new POJO

@Repository
public interface MyRepository extends JpaRepository<MyEntity, ID> {
    @Query("SELECT new com.NewPojo(SUM(m.totalDays)) FROM MyEntity m")
    NewPojo selectTotals();
}

class NewPojo {
    Float days;
    public NewPojo(Float days) {
        this.days = days;
    }
}

Upvotes: 3

Yogendra Mishra
Yogendra Mishra

Reputation: 2609

change your JPQL to @Query("SELECT SUM(m.totalDays) FROM MyEntity m")

Upvotes: 0

Sedat Gokcen
Sedat Gokcen

Reputation: 3270

It's not a valid JPQL.

You either should have:

@Query("SELECT SUM(m.totalDays) FROM MyEntity m")

or, make it a native one:

@Query(value = "SELECT SUM(total_days) FROM MyEntity", nativeQuery = true)

Upvotes: 35

Related Questions