CODEWITHSUNDEEP
springhibernatejpaspring-datah2
Tarwirdur Turon
Tarwirdur Turon

Reputation: 781

JpaRepository: using repositories in @PostPersist

I have two entities

class A {
    ...
    Integer totalSum;
    Set<B> b;
}

class B {
    ...
    Integer value;
    A container;
}

I want a.totalSum to be sum of each a.b.value;

Maybe best solution is view in db, but I want listen changes in BRepository and update A-records.

I do:

@PostPersist
@PostUpdate
@PostRemove
private void recalculateSums(B b) {
    AutowireHelper.autowire(this, this.aRepository);
    AutowireHelper.autowire(this, this.bRepository);
    A a =   b.getContainer();
    a.setTotalSum(bRepository.sumByA(s));
    aRepository.save(s);
}

And in BRepository:

@Query("select sum(b.value) from B b where b.container = :a")
Long sumByA(@Param("a") A a);

And I have error: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.B(ID)"; SQL statement: insert into b (VALUE, CONTAINER_ID, ID) values (?, ?, ?)

What I'm doing wrong?

If I do

    a.setTotalSum(a.getTotalSum()+1);
    aRepository.save(s);

All works, but if I do

    a.setTotalSum(a.getTotalSum()+1);
    aRepository.saveAndFlush(s);

I have the same error.

Upvotes: 0

Views: 793

Answers (1)

Alan Hay
Alan Hay

Reputation: 23226

I can see why you might want to do this but I think it creates a whole load of potential issues around data integrity.

If you want to have the sum of B available for an A without having to load and iterate all B there are three other options which you could implement all of which would be more robust than your proposal.

  1. As you noted, create a view. You can then Map an Entity say, SummaryData, to this view and map a one-to-one from A to SummaryData so that you can do a.getSummaryData().getTotalSum();

  2. Alternatively, you could use the Hibernate specific @Formula annotation which will issue an inline select when the entity is loaded.

    @Formula("(select sum(value) from B b inner join A a where b.a_id= a.id and a.id =id private int totalSum;

  3. Finally, and depending on the capabilities of your database, you could create a Virtual Column and Map a property as you would for any other field.

1 and 3 obviously require schema changes but your app would remain JPA compliant. 2 does not require any schema change but breaks strict JPA compliance if that was important.

Upvotes: 2

Related Questions