Hibernate link to next and/or previous within an object

Question

I'm having the following situation which I do not succeed in writing correct hibernate annotations for.

The situation

Let's assume a business owner has 2 Stores. In these Stores he sells Articles. Now when users come in the shop they want to sort the Articles on their shoppinglist(non-important class) to be ordered in the order of appearance. The business owner has an overview per Store of each Article with a link to the previousArticle and to the nextArticle. Using this list, we can easily "calculate" the order. So for one store it's actually not an issue. The issue is whenever the second store comes in.

The code:

I removed some lombok annotations and other boilerplate code to get the example as clean as possible.

@Entity
@Table(name = STORES)
public final class StoreSequence {

    @EmbeddedId
    @AttributeOverride(name = "value", column = @Column(name = STORE_NUMBER))
    private StoreNumber storeNumber;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = STORE_NUMBER, referencedColumnName= STORE_NUMBER, nullable = false)
    private Set<Article> articles;
}

@Entity
@Table(name = ARTICLES)
class Article {

    //NOTICE NO STORENUMBER WAS ADDED EARLIER SINCE ONLY 1 STORE WAS THERE. 

    @EmbeddedId
    @Column(name = ARTICLE_ID)
    private ArticleNumber id;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = PREVIOUS_ARTICLE_ID, referencedColumnName = ARTICLE_ID)
    @Setter // to move things around
    private Article previous;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = NEXT_ARTICLE_ID, referencedColumnName = ARTICLE_ID)
    @Setter // to move things around
    private Article next;
}

This works... However, if I now "start up" the second store(B) (which of course has some same articleId's as store(A) but in a different order), I cannot for example create an article in the list of store B if it has the same id. (UniqueConstraintViolationException).

So probably, I have to add the storenumber also to the Article class (Since then an article can know for which store it is in which position... makes sense) BUT...

The Question

How do I do the mapping on the next and previous fields if I include storenumber in the article class as part of the id, since they now also need to have the storeNumber in their PK without adding db columns next_storenumber and previous_storenumber (there will be the same storenumber -> business rule) and keeping the fields updateable. The updateable is important: I can move somthing around in the store. I succeeded in reading with the field added by using:

    @JoinColumns({
            @JoinColumn(name = PREVIOUS_ARTICLE, referencedColumnName = NEXT_ARTICLE, insertable = false, updatable = false),
            @JoinColumn(name = STORE_NUMBER, referencedColumnName = STORE_NUMBER, insertable = false, updatable = false),
    })

but when omitting the insertable = false and updateable = false I get a startup exception stating that I reference the STORE_NUMBER table and tells me only allowed with insertable and updateable false.

EDIT: One option would be to duplicate the store number column to a next_store_number and a previous_store_number column, but then I can put a article next to an article of another store

Points to the person that actually read all of this!

Bonus points to the person that finds the solution!

Game/Set/Match to the person that comes up with a solution that works without adding store number in article class! Just out of curiosity to know if possible

Answer 1

All,

After analyzation of the problem, we saw two options:

• Adding a surrogate/artificial key (auto generated) and store the link via this number. Due to the nature of our db, it would result in columns with an article number, columns with a number that points to the next article number etc. No ideal for a maintenance/db point of view.
• Adding 2 additional columns to store the next_store_id and previous_store_id to link the field correctly. However, then I could put an article next to an article of another store, and i have additional columns with a copy in date (a constraint would be put on them to be the same or null)

By sheer luck I stumbled upon a third solution: Just adding an additional @Column annotated field for the nextId and previousId. Actually a clean "Hack"

@Entity
@Table(name = STORES)
public final class StoreSequence {

    @EmbeddedId
    @Column(name = STORE_NUMBER)
    private Long storeNumber;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = STORE_NUMBER, referencedColumnName= STORE_NUMBER, nullable = false, insertable = false, updatable = false)
    private Set<Article> articles;
}

@Entity
@Table(name = ARTICLES)
class Article {
 
    @EmbeddedId
    private ArticleInStore id; //CONTAINING STORE AND ARTICLE_ID

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumns({
            @JoinColumn(name = PREVIOUS_ARTICLE_ID, referencedColumnName = ARTICLE_ID, insertable = false, updatable = false),
            @JoinColumn(name = STORE_ID, referencedColumnName = STORE_ID, insertable = false, updatable = false)
    })
    //NOTICE THE SETTER IS CUSTOM NOW
    //CANNOT MAKE WRITABLE SINCE THEN WE NEED PREVIOUS_STORE_ID COLUMN.
    private Article previous;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumns({
            @JoinColumn(name = NEXT_ARTICLE_ID, referencedColumnName = ARTICLE_ID, insertable = false, updatable = false),
            @JoinColumn(name = STORE_ID, referencedColumnName = STORE_ID, insertable = false, updatable = false)
    })
    @JoinColumn(name = NEXT_ARTICLE_ID, referencedColumnName = ARTICLE_ID)
    //NOTICE THE SETTER IS CUSTOM NOW
    //CANNOT MAKE WRITABLE SINCE THEN WE NEED NEXT_STORE_ID COLUMN.
    private Article next;

    /***********/
    /* THE FIX */
    /***********/
    
    //ADDED FIELD
    @Column(name = NEXT_ARTICLE_ID)
    private Long nextArticleId

    //ADDED FIELD
    @Column(name = PREVIOUS_ARTICLE_ID)
    private Long previousArticleId

    //CUSTOM SETTER
    public void setNextArticle(Article nextArticle) {
        this.next = nextArticle;
        this.nextArticleId = nextArticle.getId().getArticleId();
    }

    //CUSTOM SETTER
    public void setPreviousArticle(Article previousArticle) {
        this.previous = previousArticle;
        this.previousArticleId = previousArticle.getId().getArticleId();
    }

}

CONCLUSION

We use the id fields to set the articleId link on db, and the object fields to use in the code (getting already worked before as mentioned but not insertable/updateable). Insertable/updatable = false will not add these fields to the resulting query for update/insert, but will add it for select. Adding the column will add that field to the query. Correctly setting the id's whenever the next field is updated will make sure that they get written to db.

REMARK

We were also very (gladly) surprised to see that the queries are optimised for the @OneToOne without an n+x select problem. We solved filling our articles list in StoreSequence by a custom getter/setter with @Access(Access.Property) filling a transient custom SortingCapable list object. So basically the concept of a LinkedList but persistable to db with quite good query performance, which does the sorting of the persisted set (by reference). So when adding an article between two articles, only updates are sent for the surrounding articles, not sending an update with an order for every further articles in the list.