I am trying to create two objects in which both objects have built-in identifiers. One of the objects has 2 links to another object, where both of these links are associated with ManyToOne.
The following are sample code,
@Embeddable
public class ItemPK {
@Column(nullable = false, length = 100)
private String itemId;
@Column(name = "item_client_id", nullable = false)
private int clientId;
...
}
@Entity
@Table(name = "item")
public class Item {
@EmbeddedId
private ItemPK id;
@ManyToOne
@JoinColumn(name = "item_client_id")
private Client client;
@OneToMany(mappedBy="item", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<RelatedItem> relatedItems;
@OneToMany(mappedBy="relatedItem", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<RelatedItem> relatedItemsRHS;
...
}
@Embeddable
public class RelatedItemPK {
@Column(name = "itemId", length = 100, nullable = false)
private String itemId;
@Column(name = "item_client_id", nullable = false)
private int clientId;
@Column(name = "relatedItemId", length = 100, nullable = false)
private String relatedItemId;
@Column(name = "related_item_client_id", nullable = false)
private int relatedItemClientId;
...
}
@Entity
@Table(name = "related_item")
public class RelatedItem {
@EmbeddedId
private RelatedItemPK id;
@ManyToOne(cascade = CascadeType.ALL, optional = false)
@JoinColumns({
@JoinColumn(name="itemId", referencedColumnName="itemId", insertable=false, updatable=false),
@JoinColumn(name="item_client_id", referencedColumnName="item_client_id", insertable=false, updatable=false)
})
private Item item;
@ManyToOne(cascade = CascadeType.ALL, optional = false)
@JoinColumns({
@JoinColumn(name="related_item_client_id", referencedColumnName="item_client_id", insertable=false, updatable=false),
@JoinColumn(name="relatedItemId", referencedColumnName="itemId", insertable=false, updatable=false)
})
private Item relatedItem;
...
}
The problem is creating foreign keys for the RelatedItem object, I got a SQLException. This is the second ManyToOne relationship that fails. Below is the sql key generation,
ALTER TABLE related_item ADD CONSTRAINT FK_related_item_related_item_client_id FOREIGN KEY (related_item_client_id, relatedItemId) REFERENCES item (item_client_id, itemId)
Since the item table is indexed first by itemId and then item_client_id, this statement causes MySQL to throw an error.
I would like to switch column locations so that SQL looks like this:
ALTER TABLE related_item ADD CONSTRAINT FK_related_item_relatedItemId FOREIGN KEY (relatedItemId, related_item_client_id) REFERENCES item (itemId,item_client_id)
"JoinColumn", . , , , .
, ?
p.s. :
- MySQL 5.1
- EclipseLink 2.0.0
- Java EE 6
- JPA 2
- GlassFish v3
: EclipseLink SQL, ;
CREATE TABLE related_item (SIMILARITY DOUBLE, widget_id INTEGER NOT NULL, relatedItemId VARCHAR(100) NOT NULL, itemId VARCHAR(100) NOT NULL, related_item_client_id INTEGER NOT NULL, item_client_id INTEGER NOT NULL, PRIMARY KEY (widget_id, relatedItemId, itemId, related_item_client_id, item_client_id));
CREATE TABLE item (IMAGEURL VARCHAR(2048), STATUS VARCHAR(64), URL VARCHAR(2048), PRICE DOUBLE, STOCK INTEGER, DESCRIPTION TEXT(64000), NAME VARCHAR(255), ITEMID VARCHAR(100) NOT NULL, item_client_id INTEGER NOT NULL, PRIMARY KEY (ITEMID, item_client_id));
ALTER TABLE related_item ADD CONSTRAINT FK_related_item_itemId FOREIGN KEY (itemId, item_client_id) REFERENCES item (itemId, item_client_id);
ALTER TABLE related_item ADD CONSTRAINT FK_related_item_related_item_client_id FOREIGN KEY (related_item_client_id, relatedItemId) REFERENCES item (item_client_id, itemId);
ALTER TABLE item ADD CONSTRAINT FK_item_item_client_id FOREIGN KEY (item_client_id) REFERENCES client (ID);