JPA many to many with extra column

Question

Hi buddies. I have a following problem that I need to solve. The core issues is that I want to add additional column into JoinTable for ManyToMany relation in JPA. In my case I have following entities.

The Topic is a simple entity which has many RemoteDocument's (one RemoteDocument may be refered by many Topic's, hence it should be ManyToMany relation). Also RemoteDocument entity is read only because it may be read only from Oracle Materialized View moreover any altering of this Materialized View is forbidden. So I want to store order of RemoteDocuments related to some Topic. In fact I can do something like that with additional entity:

@Entity
public class Topic {
 @Id
 private Long id;
 @Basic
 private String name;

    @OneToMany
 private Set<TopicToRemoteDocument> association;
}

@Entity
public class RemoteDocument {
 @Id
 private Long id;
 @Basic
 private String description;
}

@Entity
public class TopicToRemoteDocument {
 @OneToOne
 private Topic topic;
 @OneToOne
 private RemoteDocument remoteDocument;
 @Basic
 private Integer order;
}

In this case additional entity TopicToRemoteDocument helps me to replace ManyToMany association with OneToMany and add extra field order.

But I want to have ManyToMany relation but with configured additional column in join table

Answer 1

Use list instead of set, together with the @OrderColumn annotation and JPA will automatically take care of the order:

@MappedSuperclass
public class BaseEntity{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    public Long getId(){
        return id;
    }

    public void setId(final Long id){
        this.id = id;
    }

}

@Entity
public class Topic extends BaseEntity{

    @ManyToMany(mappedBy = "topics")
    @OrderColumn
    private List<Document> documents = new ArrayList<Document>();

    public List<Document> getDocuments(){
        return documents;
    }

    public void setDocuments(final List<Document> documents){
        this.documents = documents;
    }

}

@Entity
public class Document extends BaseEntity{

    @ManyToMany
    @OrderColumn
    private List<Topic> topics = new ArrayList<Topic>();

    public List<Topic> getTopics(){
        return topics;
    }

    public void setTopics(final List<Topic> topics){
        this.topics = topics;
    }

}

Generated DDL (using hibernate and HSQL):

create table Document (
    id bigint generated by default as identity (start with 1),
    primary key (id)
);

create table Document_Topic (
    documents_id bigint not null,
    topics_id bigint not null,
    topics_ORDER integer not null,
    documents_ORDER integer not null,
    primary key (documents_id, topics_ORDER)
);

create table Topic (
    id bigint generated by default as identity (start with 1),
    primary key (id)
);

alter table Document_Topic 
    add constraint FK343B5D0B481100B2 
    foreign key (documents_id) 
    references Document;

alter table Document_Topic 
    add constraint FK343B5D0B558627D0 
    foreign key (topics_id) 
    references Topic;

Answer 2

I would try to avoid using a List unless you allow duplicates.

There is a @OrderColumn annotation that automatically does this. Have you tried it?

@Entity
public class Topic {
 @Id
 private Long id;
 @Basic
 private String name;

 @OneToMany
 @OrderColumn
 private Set<TopicToRemoteDocument> association;
}

Answer 3

Have you tried using @OrderBy annotation?:

@Entity
public class Topic {
 @Id
 private Long id;
 @Basic
 private String name;

 @ManyToMany
    @JoinTable(/* your mapping here */)
    @OrderBy("order ASC")
 private List<TopicToRemoteDocument> association;
}

@Entity
public class RemoteDocument {
 @Id
 private Long id;
 @Basic
 private String description;

 @ManyToMany
    @JoinTable(/* your mapping here */)
    @OrderBy("order ASC")
 private List<Topic> association;
}

Note the use of Lists instead Set, to keep items ordered.