Hibernate @Formula substraction and addition

Question

I want to calculate a value of field using Hibernate @Forumla annotation:

balance = intialBalance + (sum(Transactions of type Debit) - (sum(Transactions of type CREDIT))

I have table transaction :

CREATE TABLE transaction
(
  idtransaction serial NOT NULL,
  directionoftransaction character varying NOT NULL,
  amount numeric,
  observation text,
  datetransaction timestamp without time zone,
  idtypeoftransaction bigint,
  idaccount bigint,
  iduser bigint,
  CONSTRAINT prk_constraint_transaction PRIMARY KEY (idtransaction),
  CONSTRAINT fk_transaction_idaccount FOREIGN KEY (idaccount)
      REFERENCES account (idaccount) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_transaction_idtypeoftransaction FOREIGN KEY (idtypeoftransaction)
      REFERENCES typeoftransaction (idtypeoftransaction) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_transaction_iduser FOREIGN KEY (iduser)
      REFERENCES tuser (iduser) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT transaction_observation_key UNIQUE (observation)
)

Her is the account entity

@Entity
@Table(name = "account")
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "account_seq_gen")
    @SequenceGenerator(name = "account_seq_gen", sequenceName = "account_idaccount_seq", initialValue = 1, allocationSize = 1)
    @Column(name = "idaccount", unique = true, nullable = false)
    private Long idAccount;

    @Column(name = "namebank")
    private String nameBank;
    @Column(name = "accountnumber")
    private String accountNumber;
    @Column(name = "initialBalance")
    private BigDecimal intialBalance;
    @Formula("intialBalance + (select amount from Transaction t where directionoftransaction = 'CREDIT') minus (select amount from Transaction t where directionoftransaction = 'DEBIT')")
    private BigDecimal balance;


}

this give a syntax error, So how i can construct the query ?

score 0 · Answer 1 · answered Feb 28 '16 at 14:24

I found the solution, after correcting the syntax of the query.

@Entity
@Table(name = "account")
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "account_seq_gen")
    @SequenceGenerator(name = "account_seq_gen", sequenceName = "account_idaccount_seq", initialValue = 1, allocationSize = 1)
    @Column(name = "idaccount", unique = true, nullable = false)
    private Long idAccount;

    @Column(name = "namebank")
    private String nameBank;
    @Column(name = "accountnumber")
    private String accountNumber;
    @Column(name = "initialBalance")
    private BigDecimal intialBalance;
    @Formula("COALESCE(initialSolde,0) + ((select COALESCE(sum(t.amount),0) from transaction t where t.idaccount = idAccount and t.directionoftransaction = 'CREDIT') - " +
            "(select COALESCE(sum(d.amount),0) from transaction d where d.idaccount = idAccount and d.directionoftransaction = 'DEBIT'))")
    private BigDecimal balance;


}

Hibernate @Formula substraction and addition

1 Answers1