How to implement One-to-Many mapping when the associative table is one for all one-many relationships?
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
fr开发者_开发技巧om 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?
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.
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.
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.
@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,
I found a suitable solution with native queries.
In a nutshell:
- I made simple mapping for Project and Category without any relationships (like OneToMany)
- I add field
category
to Project entity and marked it as@Transient
- 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.
精彩评论