[FIXED] How to use @OrderColumn with soft delete

Issue

I have a class PostItem and PostItemSigner. They have an onetomany-relation. Also class PostItemSigner is soft-deleted, which means the record is never deleted in the database, only a flag is set to true or false.

public class PostItem {

    // ...

    @OneToMany(mappedBy = "postItem", fetch = FetchType.EAGER)
    @OrderColumn(name = "sequence")
    private List<PostItemSigner> signers;

    // ...
}

public class PostItemSigner {

    // ... 

    @ManyToOne
    @JoinColumn(name = "post_item_id")
    private PostItem postItem;

    private boolean deleted;
    
    // ...
}

Create is working fine and all of my records get a sequence number. Now I have some problems with my update.

I created f.e. 4 postitemsigners for 1 postitem, and in my update I remove 2 of them (setDeleted(true)).

How do I get my 2 postitemsigners, who are not deleted, have sequence number 0 and 1, and my deleted postitemsigners sequence number 2 and 3 when saving to the database. And if I add a new postitemsigner I want this one to get sequence number 2 and the deleted ones 3 and 4 f.e..

I tried saving the non-deleted first and then the deleted after, but then some of them got the same sequence number for some reason.

EDIT:

With the update I used a map Map<Long, PostItemSigner> instead of a list List<PostItemSigner>. A map puts them in a different order, while the list keeps the order. So problem solved!

Solution

You can't have both decrease a sequence number and not have the same sequence number for some items. The sequence should increase only while getting the new value.

The soft-deleted items shouldn't apper in the results because they are deleted. But if you want to use someware or undelete them, then the number might be inconsistent with the current sequence number which you decrease for some reasons.

To remove inconsistency you should update the sequence number for the items that undeleted. If you update items that deleted then remove the old sequence number.

It's similar like you want to create orphans that doesn't have a parent reference because a parent was deleted. Note, that you can't delete a parent if it violates a foreign key constraint.

Answered By - Roman C
Answer Checked By - Cary Denson (JavaFixing Admin)