[Bug?] Composite Primary key as Foreign Key in other entity
Hi all,
I've been struggling with a problem the past few days, and even though i found a 'solution' for the problem, i think i may have found a bug in JBoss 4.2.0. FYI, i'm using MySQL 5.0 as DB. I've been trying the following:
The Setup
I have a database with ContentItems which are available for certain Brands and for certain Countries. We want to display a Top 5 or 10 or whatever of these ContentItems on our website, which is where the problems began to arrise. I'm trying to model the following:
TopX
--------
brand_id <PK,FK> -------> FK to Brand entity
country_code <PK,FK> ------> FK to Country entity
content_type_id <PK,FK> -------> FK to ContentType entity
TopXContent
------------------
brand_id <PK,FK> -------> FK to TopX entity PK
country_code <PK,FK> ------> FK to TopX entity PK
content_type_id <PK,FK> -------> FK to TopX entity PK
content_item_id <PK,FK> -------> FK to ContentItem entity
position <>
So, the TopX is the parent entity in this story, with a composite PK, composed of 3 Foreign Keys to 3 different entities.
The TopXContent is the child class with a composite PK, composed of a foreigen key to TopX (so that foreign key has 3 columns in it) and one FK to ContentItem.
Consider the following entities:
TopX Entity
@Entity
@Table(name = "top_x", catalog = "content_module", uniqueConstraints = {})
public class TopX implements java.io.Serializable
{
private TopXId id;
private Brand brand;
private ContentType contentType;
private Country country;
private Set<TopXContent> topXContents = new HashSet<TopXContent>(0);
@EmbeddedId
@AttributeOverrides( { @AttributeOverride(name = "brandId", column = @Column(name = "brand_id", unique = false, nullable = false, insertable = true, updatable = true)), @AttributeOverride(name = "countryCode", column = @Column(name = "country_code", unique = false, nullable = false, insertable = true, updatable = true, length = 3)), @AttributeOverride(name = "contentTypeId", column = @Column(name = "content_type_id", unique = false, nullable = false, insertable = true, updatable = true)) })
public TopXId getId()
{
return this.id;
}
public void setId(TopXId id)
{
this.id = id;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "brand_id", unique = false, nullable = false, insertable = false, updatable = false)
public Brand getBrand()
{
return this.brand;
}
public void setBrand(Brand brand)
{
this.brand = brand;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "country_code", unique = false, nullable = false, insertable = false, updatable = false)
public Country getCountry()
{
return this.country;
}
public void setCountry(Country country)
{
this.country = country;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "content_type_id", unique = false, nullable = false, insertable = false, updatable = false)
public ContentType getContentType()
{
return this.contentType;
}
public void setContentType(ContentType contentType)
{
this.contentType = contentType;
}
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.EAGER, mappedBy = "topX")
public Set<TopXContent> getTopXContents()
{
return this.topXContents;
}
public void setTopXContents(Set<TopXContent> topXContents)
{
this.topXContents = topXContents;
}
}
TopXId
@Embeddable
public class TopXId implements java.io.Serializable
{
private Integer brandId;
private String countryCode;
private Integer contentTypeId;
@Column(name = "brand_id", unique = false, nullable = false, insertable = true, updatable = true)
public Integer getBrandId()
{
return this.brandId;
}
public void setBrandId(Integer brandId)
{
this.brandId = brandId;
}
@Column(name = "country_code", unique = false, nullable = false, insertable = true, updatable = true, length = 3)
public String getCountryCode()
{
return this.countryCode;
}
public void setCountryCode(String countryCode)
{
this.countryCode = countryCode;
}
@Column(name = "content_type_id", unique = false, nullable = false, insertable = true, updatable = true)
public Integer getContentTypeId()
{
return this.contentTypeId;
}
public void setContentTypeId(Integer contentTypeId)
{
this.contentTypeId = contentTypeId;
}
}
TopXContents Entity
@Entity
@Table(name = "top_x_content", catalog = "content_module", uniqueConstraints={})
public class TopXContent implements java.io.Serializable
{
private TopXContentId id;
private TopX topX;
private ContentItem contentItem;
private Integer position;
@EmbeddedId
@AttributeOverrides( { @AttributeOverride(name = "brandId", column = @Column(name = "brand_id", unique = false, nullable = false, insertable = true, updatable = true)), @AttributeOverride(name = "countryCode", column = @Column(name = "country_code", unique = false, nullable = false, insertable = true, updatable = true, length = 3)), @AttributeOverride(name = "contentTypeId", column = @Column(name = "content_type_id", unique = false, nullable = false, insertable = true, updatable = true)), @AttributeOverride(name = "contentItemId", column = @Column(name = "content_item_id", unique = false, nullable = false, insertable = true, updatable = true)) })
public TopXContentId getId()
{
return this.id;
}
public void setId(TopXContentId id)
{
this.id = id;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumns( { @JoinColumn(name = "brand_id", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "country_code", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "content_type_id", unique = false, nullable = false, insertable = false, updatable = false) })
public TopX getTopX()
{
return this.topX;
}
public void setTopX(TopX topX)
{
this.topX = topX;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "content_item_id", unique = false, nullable = false, insertable = false, updatable = false)
public ContentItem getContentItem()
{
return this.contentItem;
}
public void setContentItem(ContentItem contentItem)
{
this.contentItem = contentItem;
}
@Column(name = "position", unique = false, nullable = true, insertable = true, updatable = true)
public Integer getPosition()
{
return this.position;
}
public void setPosition(Integer position)
{
this.position = position;
}
}
TopXContentId
@Embeddable
public class TopXContentId implements java.io.Serializable
{
private Integer brandId;
private String countryCode;
private Integer contentTypeId;
private Integer contentItemId;
@Column(name = "brand_id", unique = false, nullable = false, insertable = false, updatable = false)
public Integer getBrandId()
{
return this.brandId;
}
public void setBrandId(Integer brandId)
{
this.brandId = brandId;
}
@Column(name = "country_code", unique = false, nullable = false, insertable = false, updatable = false, length = 3)
public String getCountryCode()
{
return this.countryCode;
}
public void setCountryCode(String countryCode)
{
this.countryCode = countryCode;
}
@Column(name = "content_type_id", unique = false, nullable = false, insertable = false, updatable = false)
public Integer getContentTypeId()
{
return this.contentTypeId;
}
public void setContentTypeId(Integer contentTypeId)
{
this.contentTypeId = contentTypeId;
}
@Column(name = "content_item_id", unique = false, nullable = false, insertable = false, updatable = false)
public Integer getContentItemId()
{
return this.contentItemId;
}
public void setContentItemId(Integer contentItemId)
{
this.contentItemId = contentItemId;
}
}
The Problem
When i deploy this, Jboss will try to generate the tables (because I configured it to do so, it's handy :)), but it fails on the generation of the foreign key from TopXContents to TopX. I get a MySQL errno: 150, saying it can't create a table with some weird name (error 150 = malformed foreign key). When i look at the query it tries to run i see the following:
alter table content_module.top_x_content add index FK7A0FB9482B69DB27 (brand_id, country_code, content_type_id), add constraint FK7A0FB9482B69DB27 foreign key (brand_id, country_code, content_type_id) references content_module.top_x (country_code, content_type_id, brand_id);
After looking at it for a while i notice the order of the columns is different in the foreign key declaration:
brand_id, country_code, content_type_id country_code, content_type_id, brand_id
If place the second brand_id as the first colum in the 'references' clause, the query works just fine... So i do this, redeploy, and JBoss remains quiet. But! when i try to use the entities, the TopX is retrieved just fine, but the TopXContents collection is returned empty! Even though the database is filled in correctly (i tested with a SQL query directly on MySQL).
I look at the debug log again, and it shows that JBoss is making the same mistake it made when it tried to apply the foreign key! The columns are all messed up! When it tries to join the TopX and TopXContent tables, it matches brand_id=country_code, country_code=content_type_id and content_type_id=brand_id. CHAOS!
So i started my search on the internet.. nothing... i started making desperate attempts to find a workaround... SUCCES!
I, quite simply, changed the JoinColumns in the TopXContents entity above the getTopX() function from
@JoinColumns( { @JoinColumn(name = "brand_id", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "country_code", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "content_type_id", unique = false, nullable = false, insertable = false, updatable = false) })
to
@JoinColumns( { @JoinColumn(name = "country_code", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "content_type_id", unique = false, nullable = false, insertable = false, updatable = false), @JoinColumn(name = "brand_id", unique = false, nullable = false, insertable = false, updatable = false) })
So, i put the brand_id column at the end instead of the beginning of the @JoinColumns! And tadaaaammm! It works! The DB is generated as it should, queries work, life is good again!
Sooo...
Is this a bug? Or am i just trying something stupid? I got it working though ^_^. Thanks for your time...