Spring Hibernate JPQL Summary Query

Question

I have a Spring Data implementation with an entity

@Entity
public class Account extends AbstractEntity {

...

    @OneToMany(mappedBy = "account")
    private Set<AccountTransaction> accountTransactions;

AND AccountTransactions

@Entity(name="AccountTransactions")
public class AccountTransaction extends AbstractEntity {

    /**
     * 
     */
    private static final long serialVersionUID = -7045838402463741959L;

    @ManyToOne(optional = false)
    @JsonIgnore
    private Account account;

    @Column
    @Enumerated(EnumType.ORDINAL)
    private TransactionType transactionType;

    @Column
    private BigDecimal amount;

And my repository, where I am trying to execute an aggregate request: sum(amount)

Result nullhowever

public interface AccountTransactionRepository extends
        CrudRepository<AccountTransaction, Long> {

    // @Query(value =
    // "select sum(amount) from AccountTransaction where account.id = ?1")
    @Query(value = "SELECT sum(at.amount) FROM AccountTransactions at WHERE at.account.id = :accountId")
    public BigDecimal getAccountBalance(@Param("accountId") Long accountId);
}

What do I need to do to get the sum of a BigDecimalcolumn amount?

* UPDATE *

I managed to get the query to work by changing it to nativeQuery like this:

@Query(value = "SELECT sum(amount) FROM account_transaction WHERE account_id = :accountId", nativeQuery = true)
    public BigDecimal getAccountBalance(@Param("accountId") Long accountId);

This is clearly less than desirable ... since I don't want to write this for every request ...

Answer 1

You must add JoinColumnin order to establish a connection:

@ManyToOne(optional = false)
@JoinColumn(name="FOREIGN_KEY_HERE")
@JsonIgnore
private Account account;