Spring Data Hibernate JPQL Aggregate 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 the 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'm trying to execute an aggregate query: sum(amount)

The result is null however

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 BigDecimal sum of the column amount?

*UPDATE*

I was able to get the query to work changing it to a nativeQuery like so:

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

This is obviously less than desirable ... as I don't want to have to write this for every query ...

I don't see any JoinColumn annotations to define the relationship between Account and AccountTransaction are your database columns using the appropriate defaults? — Kevin Bowersox
– Kevin Bowersox, Commented Feb 7, 2014 at 20:43
I thought the @OneToMany(mappedBy = "account") and @ManyToOne(optional = false) options took care of making the association ... so far everything else has loaded normally... — jordan.baucke
– jordan.baucke, Commented Feb 7, 2014 at 20:47
What is the name of the foreign key in AccountTransaction? It depends if the name uses the appropriate defaults — Kevin Bowersox
– Kevin Bowersox, Commented Feb 7, 2014 at 20:49
@KevinBowersox it should be account_id after it's been created in the database, and it's just Account in the @Entity annotated class... — jordan.baucke
– jordan.baucke, Commented Feb 7, 2014 at 22:44

Kevin Bowersox · Accepted Answer · 2014-02-07 20:53:54Z

1

You must add a JoinColumn to establish the relationship:

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

edited Feb 7, 2014 at 20:53

answered Feb 7, 2014 at 20:20

Kevin Bowersox

94.6k19 gold badges164 silver badges198 bronze badges

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

4 Comments

jordan.baucke Over a year ago

So in the hbm2ddl statements I see

Hibernate: alter table account_transactions add constraint FK_lpcrni2dw78hajvb0gfd5xcr foreign key (account_transactions) references account

which is a randomly generated FK constraint. I added @JoinColumn(name="AccountTransactions") per the naming convention I set for the AccountTransaction entity - but it's still returning null.

jordan.baucke Over a year ago

Also wondering how using improved naming strategy (defaulted when I started Spring Boot) is affecting this? account_id is the column created on AccountTransaction which mappedBy is defaulting to see: stackoverflow.com/questions/3818797/… (about the JoinColumn, states it already has one?)

Kevin Bowersox Over a year ago

Name the join column ACCOUNT_ID

jordan.baucke Over a year ago

thanks @KevinBowersox that worked ... capitalization!

Collectives™ on Stack Overflow

Spring Data Hibernate JPQL Aggregate Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related