Spring JPA one to many denormalized count field

Question

I have two entities, Books and Comments, in a one to many relationship (one book can have many comments). I want to be able to list books and number of comments about a book. I want it denormalized, meaning the books entity will have a counter that has number of comments for that book, and it will be updated every time a comment is entered (just playing with the concept, no need to discuss about the need of denormalizing here).

I think (correct me if I am wrong) this could be easily done with a trigger in the database (whenever a new comment is created, update a counter in the books table to the corresponding bookId), but for the sake of learning I want to do it through JPA, if it makes sense.

What I have so far: //omitted some annotations, just general info

Boks entity:

@Entity 
public class Books {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String title;
    private String author;
    private Long numComments;

  // getters and setters...
}

Comments entity:

@Entity
public class Comments {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String comment;
    private Long authorId;
    private Long bookId;

  // getters and setters...
}

Books repository: I added here a query to perform the update

/**
 * Spring Data JPA repository for the Books entity.
 */
public interface BooksRepository extends JpaRepository<Books,Long>         {
@Modifying
@Query("UPDATE Books v SET v.numComments = v.numComments + 1 WHERE v.id = :bookId")
int updateCounter(@Param("bookId")Long bookId);

}

And now the question: What next? I think I can put the update of the Books entity annotating with @PostPersist a method of the entity Comments, but I have been unsuccessful so far. I can imagine something like this:

    @PostPersist  //This function in the entity Comments
    protected void updateBooks() {
        //Likely some call to the repository here that updates the count
        //   in books the info we have from current entity.
    }

Any idea on how to do this? Some best practices about this kind of denormalization in JPA? Better to use the database triggers?

score 1 · Answer 1 · edited May 23 '17 at 12:07

spring not managed your entity classes and your idea is possible but you must inject BooksRepository in enttiy class then stay at you get Nullpointerexception because spring not managed enttiy classes,The reason your BooksRepository not initlaized, try also read this post Bean injection inside a JPA @Entity and anotate entity class @Configurable after

try this

@PostPersist 
protected void updateBooks(Comments comment) {

    int totalComment = BooksRepository.updateCounter(comment.getBookId());

    System.out.println(totalComment); // see totalComment in console
}

but good aprroach in service classes after call updateCounter when insert comment

example in your CommendService : when try a insert commend after call your updateCounter

    if(comment.getBookId() != null) //Simple Control
     {

        CommentRepository.save(comment);
        BooksRepository.updateCounter(comment.getBookId());
    }

Spring JPA one to many denormalized count field

1 Answers1