Home » Eclipse Projects » EclipseLink » @ManyToMany: how include columns from @Embeddable map key in primary keys to avoid duplicate errors
| @ManyToMany: how include columns from @Embeddable map key in primary keys to avoid duplicate errors [message #660538] |
Fri, 18 March 2011 20:20  |
 darren
Messages: 19
Registered: March 2011
Location: Sydney, Australia |
Junior Member |
|
|
I have an interesting @ManyToMany map mapping case and have run into problems with duplicates on the primary keys, which are taken to be the same as the @JoinColumns; I want to figure out how to include columns from a discriminating @Embeddable to remove the duplicate error:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '184-174' for key 1
Error Code: 1062
Call: INSERT IGNORE INTO Activity_involves_BlockList (Block_ID, Activity_ID, LISTNAME, OWNERID) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: DataModifyQuery(name="involvedBlockLists" sql="INSERT IGNORE INTO Activity_involves_BlockList (Block_ID, Activity_ID, LISTNAME, OWNERID) VALUES (?, ?, ?, ?)")
Background of mapping:
I am developing a web application that employs a systems engineering strategy where human resource activities are related to physical "blocks" (the blocks are said to be "involved in" the activities, the activities "involve" the blocks). Often however there are lists of blocks, and it is too tedious to relate an activity to individual blocks, I need to be able to relate an activity to a list of blocks. I also need to maintain reverse links from blocks back to the activities they are involved in.
There are 2 basic candidates:
1. Have a separate ListWrapper entity and relate activities and lists of blocks via them; this is resource intensive and I wish to avoid it if possible.
2. Activities keep track of the property names (only) of child lists of block, together with the id of the owning block. Reflection is then used to obtain the required list whenever needed.
I've nearly got the 2nd approach working (right through to a JSF user interface), however I run into problems when I have 2 child lists owned by the same Block and involved in the same Activity.
Case: Block subclass OfficeBuilding has 2 separate lists:
- List<OfficeFloor> floors;
- List<OfficeTenant> tenants;
There is an Activity named (for test purposes) "this involves all floors and all tenancies of the office bulding", where the office building instance is unique within the project.
The attempted mapping and the duplicate problem:
In entity class Activity I have the following (I omit the support code that leverages reflection to exploit the mapping):
private Map<ListKey,Block> involvedBlockLists;
@ManyToMany
@JoinTable(
name="Activity_involves_BlockList",
joinColumns={ @JoinColumn(name="Activity_ID", referencedColumnName="ID")},
inverseJoinColumns={@JoinColumn(name = "Block_ID", referencedColumnName = "ID")}
)
public Map<ListKey, Block> getInvolvedBlockLists() {
return involvedBlockLists;
}
..
Where the embeddable ListKey (which happens to use Pojomatic for equals() and hashCode(), but this probably plays no role in the duplicate problem) is:
@Embeddable
public class ListKey implements Serializable {
public ListKey() {}
public ListKey(String listName, Long ownerId) {
this.listName = listName;
this.ownerId = ownerId;
}
private String listName;
@Property
public String getListName() {
return listName;
}
public void setListName(String listName) {
this.listName = listName;
}
private Long ownerId;
@Property
public Long getOwnerId() {
return ownerId;
}
public void setOwnerId(Long ownerId) {
this.ownerId = ownerId;
}
@Override
public String toString() {
return "ListKey: listName("+getListName()+"), ownerId("+getOwnerId()+")";
}
@Override
public boolean equals(Object o) {
return Pojomatic.equals(this,o);
}
@Override
public int hashCode() {
return Pojomatic.hashCode(this);
}
}
And on the other side of the @ManyToMany the entity class Block has:
private List<Activity> activitiesInvolvingLists;
@ManyToMany(mappedBy="involvedBlockLists")
public List<Activity> getActivitiesInvolvingLists() {
return activitiesInvolvingLists;
}
It works like a charm until I attempt to "involve" 2 different lists from the same owning Block in the one Activity (and I note here it worked completely before I had a bi-directional mapping).
The problem is that the @JoinTable Activity_involves_BlockList has Activity_ID and Block_ID as primary keys, as revealed by direct inspection of the created table in MySQL. What I need is to be able to (using JPA and/or EclipseLink annotations) additionally include the LISTNAME column from the embeddable map key ListKey (the other ListKey column OWNERID is in fact redundant and always equals Block_ID in the join table, but is handy to have when I retrieve the ListKey object).
Example:
- the OfficeBuilding instance (a kind of Block) has ID 174.
- the Activity instance has ID 184.
- the OfficeBuilding has a list named 'floors', which is involved successfully in the activity to give a row:
[Activity_ID = 184, Block_ID = 174, LISTNAME = floors, OWNERID = 174]
- the OfficeBuilding has another list named 'tenancies', which causes MySQL to spit the duplicates dummy when addition of the following row with the same Activity_ID and Block_ID (primary keys) is attempted:
[Activity_ID = 184, Block_ID = 174, LISTNAME = tenancies, OWNERID = 174]
Q: How can I (using EclipseLink and JPA, without hacking the SQL after the tables are generated) include the LISTNAME column - from the embeddable ListKey used as map key - in the primary keys of the join table (as a discriminator w.r.t. otherwise identical activity and block) ?
Most grateful for any feedback and tips,
Webel IT Australia
|
|
|
Current Time: Tue Jun 18 00:54:45 EDT 2013
Powered by FUDForum. Page generated in 0.01936 seconds
|
] 
Search
Help
Register
Login
Home
