How to implement One-to-Many mapping when the associative table is one for all one-many relationships?

Question

General case: first table represents one-side, second table represents many-side. Third table serves as a link between the two.

My case: first and second tables are the same. Third table serves as a link between all pairs of tables which have one-to-many relationship. This third table has additional field (String) which contains information about 2 tables.

Little example. Suppose we have tables Project and Category (one category --> many projects). To obtain all projects with categories we need to perform next query:

select project.name, category.name 
from nodeassociation, project, category 
where nodeassociation.association_name='ProjectCategory'
 and nodeassociation.source_id=project.id 
 and nodeassociation.sink_id=category.id

How can I specify association_name criterion by means of JPA?

UPD: if it's impossible with JPA but hibernate handles it then it's ok, what's the hibernate solution?

Answer 1

Suggest you create multiple views in the database filtered on the association_name column, and then define many-to-many relationships in your mapping based on these, and just enforce the one-to-many semantics in your code. This situation with an intermediate table is only really handled in Hibernate using a many-to-many mapping.

Answer 2

This is normally called a qualified association. Such association should be realized with Map instead of List. There is not built in support for these in hibernate. If you don't care about the qualifier, the association become a plain many-to-many that can be handled with hibernate.

But in your case this isn't a qualified association at the logical level. If I understand well, one association table is used to represent what is actually several associations:

• If you can change the db schema to have on table per logical association, the problem vanishes.
• Using database views to "simulate" having separate tables (as suggested by David M) is an alternative. Create one view per logical association and map it as a many-to-many.

Answer 3

Another solution that work maybe would be to have a polymorphic entity for NodeAssociation in hibernate. There would be one NodeAssociationX entity per type of association. There is then a one-to-many association between Project and NodeAssociation. And there is a one-to-many per NodeAssociationX and the corresponding table/entity (Category, etc.). You can map the private fields so that you can provide public getter/setter that provide the correct view at the logical level.

public List getCategories()
{
     List categories = new ArrayList();
     for( NodeAssociation na : nodeAssociations )
     {
           if( typeof( na ) = NodeAssociationCategory )
           {
                categories.addAll( ((NodeAssociationCategory)na).getCategories() ); 
           }
     }
     return categories;
}

But this is rather ugly. Try the other solution if possible.

Answer 4

@Entity
public class Category {

    private Integer id;

    @Id
    public Integer getId() {
        return this.id;
    }

    private LinkBetweenOneCategoryAndManyProject linkClass = new  LinkBetweenOneCategoryAndManyProject();

    @OneToOne
    public LinkBetweenOneCategoryAndManyProject getLinkClass() {
        return this.linkClass;
    }

    public void addProject(Project project) {
        getLinkClass().getProjectList().add(project);
    }

    // delegates to LinkClass
    public String getAdditionalProperty() {
        getLinkClass().getAdditionalProperty();
    }

}

Now our Project

@Entity
public class Project {

    private Integer id;

    @Id
    public Integer getId() {
        return this.id;
    }

}

Now our LinkBetweenOneCategoryAndManyProject class

@Entity
public class LinkBetweenOneCategoryAndManyProject {

    private Integer categoryId;

    private String additionalField;

    List<Project> projectList;

    @Id
    public Integer getCategoryId() {
        return this.categoryId;
    }

    @OneToMany
    public List<Project> getProjectList() {
        return projectList;
    }

    public String additionalProperty() {
        return this.additionalField;
    }

}

So if you want to retrieve a Category and its project, do as follows

select distinct c from Category c left join fetch c.linkedClass lc left join fetch lc.projectList

Takes care i suppose the relationship between Category and LinkBetweenOneCategoryAndManyProject is OneToOne

regards,

Answer 5

I found a suitable solution with native queries.

In a nutshell:

1. I made simple mapping for Project and Category without any relationships (like OneToMany)
2. I add field category to Project entity and marked it as @Transient
3. In ProjectDAO I manually insert category to project, using native queries.

Article about JPA Native Queries and related jboss docs were a startpoint for me.