Possible serious limitation in composite key joins

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

I'm pretty sure the EJB 3.0 spec doesn't specify how composite primary keys join to composite foreign keys...at least that's what I was told on the Glassfish forums when I brought this up, back when I was using Glassfish.

I'm pretty sure, after my testing this afternoon that Hibernate has the same limitations that Toplink has.

I wanted to check here and get feedback before going any further.

Is it possible, where entityA has a composite primary key consisting of three fields, to join to entityB which has a composite primary key of two fields...and neither of the two composite keys necessarily match or join up?

So far, in my test project, I've only been able to join two entities that have composite keys if they match up perfectly (i.e. both FK and PK sides are the exact same type and name) and I'm not forced to use referencedColumnName in my @JoinColumn(s) annoations.

I've intentionally excluded code snippets because I have an entire project I can submit as part of a bug report/request for enhancement, if needed.

Any input is appreciated, thanks!

max · **Posted:** Sat Jan 27, 2007 4:20 am

if they don't necessarily match up how do you expect it to even work with pure good old SQL ?

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

max wrote:

if they don't necessarily match up how do you expect it to even work with pure good old SQL ?

I guess I didn't articulate the "problem" very well, let me try and explain in better detail.

I've got two entities, both have composite primary keys. They are related in a one-to-many. The child (OrderTrafficDetail) relies on only one of the fields from the parent (OrderTraffic) and only one of them is in the parent's composite primary key.

For example (entities & respective keys):

Code:

@Embeddable
public class OrderTrafficPK implements Serializable
{
   @Column(name="`load-no`", nullable=false)
   private String loadNo;
   
  @Column(name="`ar-entity`", nullable=false)
  private String arEntity;

  @Column(name="`plant`", nullable=false)
  private String plant;
}

@Entity
@Table(name="`ord-traf`")
public class OrderTraffic implements Serializable
{
  @EmbeddedId
  private OrderTrafficPK orderTrafficPK;

  @Column(name="`carrier-no`")
  private String carrierNo;
...................
}

public class OrderTrafficDetailPK implements Serializable
{
  @Column(name="`load-no`", nullable=false)
  private String loadNo;

  @Column(name="`stop-no`", nullable=false)
  private int stopNo;
}

@Entity
@Table(name="`ord-traf-d`")
public class OrderTrafficDetail implements Serializable
{
  @EmbeddedId
  protected OrderTrafficDetailPK orderTrafficDetailPK;

  @Column(name="`order-no`", nullable=false)
  private int orderNo;

  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumns({
    @JoinColumn(name="`load-no`", referencedColumnName="`load-no`", insertable=false, updatable=false),
  })
  private OrderTraffic orderTraffic;
.................................
}

I get this exception upon deployment:

Code:

  Reason: org.hibernate.AnnotationException: Column name load-no of com.abfoods.model.OrderTraffic not found in JoinColumns.referencedColumnName

I'm not sure how it's "not found" since both the tables and entities clearly have it, and the data-type is the same.

I've read the documentation...I understand that I have to use @JoinColumns but what happens when the relationship doesn't involve all fields of the primary and/or foreign key?

I've tried mapping this several different ways but can't get this relationship to work.

I tried a uni-directional w/ the @JoinColumn mapped on the OrderTraffic side:

Code:

@Entity
@Table(name="`ord-traf`")
public class OrderTraffic implements Serializable
{
  @EmbeddedId
  private OrderTrafficPK orderTrafficPK;

  @OneToMany(fetch=FetchType.LAZY)
  @JoinColumn(name="`load-no`", insertable=false, updatable=false)
  private List<OrderTrafficDetail> orderTrafficDetails;
....................................
}

...but I get this exception:

Code:

A Foreign key refering com.abfoods.model.OrderTraffic from com.abfoods.model.OrderTrafficDetail has the wrong number of column. should be 3

I'm stumped...either I'm doing something wrong or Hibernate just can't deal w/ this type of gnarly data model. I'm assuming I'm just not doing it right.

This is legacy data out of a very large, very old inventory system for which nothing in the schema can be changed. That being said, this seems like a very common scenario and I'd be surprised if Hibernate couldn't handle it?

Thanks again!

msystems · **Joined:** Mon Jan 29, 2007 6:05 pm **Posts:** 4

I have exactly the same problem ! It looks like Hibernate have some kind of limitations. Any help is appreciated.

Regards

Kenneth

max · **Posted:** Tue Jan 30, 2007 2:44 am

it is probably a quoting issue....try and do it without quotes and see if you can get pass the startup (it will fail when you start querying, but then we know it is the quotes that are doing it)

msystems · **Joined:** Mon Jan 29, 2007 6:05 pm **Posts:** 4

max wrote:

it is probably a quoting issue....try and do it without quotes and see if you can get pass the startup (it will fail when you start querying, but then we know it is the quotes that are doing it)

In my case, it's not a quoting issue because I'm not using quotes !
And I also get this exception:

A Foreign key refering <package and class> has the wrong number of column. should be 2

My problem is very similarly with tsar bomba !

I've got two entities, both have composite primary keys. They are related in a one-to-many. The child relies on only one of the fields from the parent and only one of them is in the parent's composite primary key.

I have tried every possible combinations with @JoinColumn or @JoinColumns or @JoinTable but nothing works.

Regards

Kenneth

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

max wrote:

it is probably a quoting issue....try and do it without quotes and see if you can get pass the startup (it will fail when you start querying, but then we know it is the quotes that are doing it)

I had this thought too and it is not the case. We have other projects here that sit atop an MSSQL 2000 database w/ the same scenario...and there is no quoting being used on the tables or fields. The same thing happens in this case, you can't do a join as I described above.

I'll included quoted and non-quoted versions of entities/tables in my JIRA test project, should we conclude that this is something that should be submitted as a bug/feature request.

Basically, I work for a company that has a strong AS/400 background. As my boss put it - they made composite keys all over the place, it was just the way they were taught to do it. So, we've got a *ton* of data here that will require the ability to do these kinds of funky joins. In some cases I've had to resort to use iBatis, which I most definitely do *not* prefer...but it was the only way to stay in the ORM realm until I could figure it out w/ Hibernate.

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

max wrote:

it is probably a quoting issue....try and do it without quotes and see if you can get pass the startup (it will fail when you start querying, but then we know it is the quotes that are doing it)

I've created a test-case in my sample app that simply illustrates the problem.

Two entities Carz and Lotz (named weird to prevent conflict w/ similarly named entities in same package):

Code:

@Entity
@Table(name="car")
public class Carz implements Serializable
{
  @Id
  private Integer id;
  
  @Column(name="make", nullable=false)
  private String make;

  @Column(name="model", nullable=false)
  private String model;

  @Column(name="manufactured", nullable=false)
  @Temporal(TemporalType.TIMESTAMP)
  private Date manufactured;
  
  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumns //Hibernate docs state that @JoinColumns must be used since Lotz has composite PK, a single @JoinColumn does not deploy anyhow
  ({
     @JoinColumn(name="loc_code", referencedColumnName="loc_code", insertable=false, updatable=false)
  })
  private Lotz lot;
...........................
}

@Entity
@Table(name="lot")
public class Lotz implements Serializable
{
   @EmbeddedId
   protected LotzPK lotPK;
   
  @Column(name="name", nullable=false)
  private String name;

  @Column(name="location", nullable=false)
  private String location;

  @OneToMany(mappedBy="lot", fetch=FetchType.LAZY, cascade=CascadeType.ALL)
  private List<Carz> cars;
...........................
}

@Embeddable
public class LotzPK implements Serializable
{
  @Column(name="id", nullable=false)
  private Integer id;
  
  @Column(name="loc_code", nullable=false)
  private String locCode;
...........................
}

In this case I need a one-to-many relationship on Carz.id = Lotz.locCode. However, there doesn't appear to be a way to do this, even though it's a very common use-case.

When this is deployed, this exception occurs:

Code:

org.hibernate.AnnotationException: Column name id of hqb.model.Lotz not found in JoinColumns.referencedColumnName
   at org.hibernate.cfg.annotations.TableBinder.bindFk(TableBinder.java:306)
   at org.hibernate.cfg.FkSecondPass.doSecondPass(FkSecondPass.java:64)
   at org.hibernate.cfg.AnnotationConfiguration.processFkSecondPassInOrder(AnnotationConfiguration.java:433)
   at org.hibernate.cfg.AnnotationConfiguration.secondPassCompile(AnnotationConfiguration.java:287)
   at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1115)
   at org.hibernate.ejb.Ejb3Configuration.buildMappings(Ejb3Configuration.java:1211)
   at org.hibernate.ejb.EventListenerConfigurator.configure(EventListenerConfigurator.java:154)
   at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:847)
   at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:385)
   at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:126)
   at org.jboss.ejb3.entity.PersistenceUnitDeployment.start(PersistenceUnitDeployment.java:264)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.jboss.ejb3.ServiceDelegateWrapper.startService(ServiceDelegateWrapper.java:102)
   at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
   at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
   at sun.reflect.GeneratedMethodAccessor263.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
...........................

I can deploy the app if I map both fields in the Lotz PK class like so:

Code:

@Entity
@Table(name="car")
public class Carz implements Serializable
{
  @Id
  private Integer id;
...........................
  
  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumns
  ({
     @JoinColumn(name="loc_code", referencedColumnName="loc_code", insertable=false, updatable=false),
     @JoinColumn(name="", referencedColumnName="id", insertable=false, updatable=false) //INCORRECTLY DEFINED RELATIONSHIP!
  })
  private Lotz lot;
...........................
}

But obviously this is no good, it generates bad SQL at runtime:

Code:

    /* select
        c 
    from
        Carz c 
    left join
        fetch c.lot */ select
            carz0_.id as id198_0_,
            lotz1_.id as id199_1_,
            lotz1_.loc_code as loc2_199_1_,
            carz0_.make as make198_0_,
            carz0_.model as model198_0_,
            carz0_.manufactured as manufact4_198_0_,
            carz0_.lot_id as lot5_198_0_,
            carz0_.loc_code as loc6_198_0_,
            lotz1_.name as name199_1_,
            lotz1_.location as location199_1_ 
        from
            car carz0_ 
        left outer join
            lot lotz1_ 
                on carz0_.lot_id=lotz1_.id --oops! Hibernate defaulted a relationship here...
                and carz0_.loc_code=lotz1_.loc_code

In reality, this is the SQL I'm trying to achieve, by repairing the above query manually:

Code:

select
            carz0_.id as id198_0_,
            lotz1_.id as id199_1_,
            lotz1_.loc_code as loc2_199_1_,
            carz0_.make as make198_0_,
            carz0_.model as model198_0_,
            carz0_.manufactured as manufact4_198_0_,
            carz0_.loc_code as loc6_198_0_,
            lotz1_.name as name199_1_,
            lotz1_.location as location199_1_ 
        from
            car carz0_ 
        left outer join
            lot lotz1_ 
                on carz0_.loc_code=lotz1_.loc_code

As I've said, this is consistent w/ the EJB 3.0 spec (and Glassfish+Toplink) but that's only because the spec doesn't define exacltly how to handle this. However, that being said, this seems important enough for Hibernate to be able to manage, wouldn't you think?

Should I submit this sample Eclipse/JBoss project w/ a new JIRA issue?

Thanks again!

emmanuel · **Posted:** Tue Jan 30, 2007 2:24 pm

Having composite pk is one thing and is pretty well supported
Using FK that points to non pk columns is optionally supported (HAN does though)
There might be a bug when the non pk columns are actually a subset of the pk columns, please open a JIRA issue with a unit test case (no eclipse, please I don't use it). Note that such a construct makes very few sense since a subset of the PK is already unique! so the other part is completely useless (as a PK). If the subset is not unique, it does not make sense at all

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

emmanuel wrote:

Having composite pk is one thing and is pretty well supported
Using FK that points to non pk columns is optionally supported (HAN does though)

Correct, I've never had an issue w/ these two cases, if they're isolated from each other.

emmanuel wrote:

Note that such a construct makes very few sense since a subset of the PK is already unique! so the other part is completely useless (as a PK). If the subset is not unique, it does not make sense at all

I'm not entirely sure I follow you here. A relationship where a table w/ a composite key joins to another table on only one if its key fields as a foreign key...seems quite common to me. My example where there is a "location code" that joins a Car to a Car Lot, while overly-simple, has been extremely common in my experience. We have quite a bit of data here that looks like this, most of it being ported to more modern databases from our AS/400 monolith monsters!

The subset, i.e. the "loc_code" field in the "lot" table, is unique in this case...and maybe that wasn't the best example...but it was simple enough to explain. In some of our legacy data there might be 3 or more keys as a composite...and one of them might be a code of some kind...and in that case would not be unique.

In other words, uniqueness of the subset of the PK can't always be guaranteed in many schemas.

However, I could just be misunderstanding...hopefully that makes sense.

emmanuel wrote:

no eclipse, please I don't use it

I know it sounds weird but...we don't have unit tests and don't really use them...I'm not set up for it. Our projects, thus far, while moving to Java EE from .NET and other technologies, have not been big enough to justify unit testing...yet.

Would posting the entity and session beans I used be sufficient? If not, please let me know what else I can do. You'll have to excuse my ignorance, I'm relatively new to Java in general and am not entirely unit test savvy. I'll try to be as helpful as possible.

msystems · **Joined:** Mon Jan 29, 2007 6:05 pm **Posts:** 4

tsar bomba wrote:

In other words, uniqueness of the subset of the PK can't always be guaranteed in many schemas.

True.

I really hope the Hibernate Team can solve this issue, because it is a really showstopper !

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

msystems wrote:

tsar bomba wrote:

In other words, uniqueness of the subset of the PK can't always be guaranteed in many schemas.

True.

I really hope the Hibernate Team can solve this issue, because it is a really showstopper !

I've gotten "around" this VERY temporarily by changing my entities to be a single PK field instead of a composite PK. This may or may not be an option for you...as for me...and only for this current project, was I able to get away w/ it.

What I've got is a small start to a potentially *very* large supply-chain management application that will bring all 12 of our company's divisions together for inventory, order fulfillment, forecasting, and much more.

Right now we're testing the waters. I've used Glassfish and am not happy w/ it in a lot of ways. I've already built our web storefront w/ JBoss + Hibernate-EJB3 and it went wonderfully - but I built the database and didn't have to worry about the model...it was a breeze. However, now we're in a situation where we need to integrate anywhere from 8 to 15 databases spread out across several divisions and servers...ranging from MS Fox Pro to OpenEdge Progress to MSSQL to AS/400 to whatever...the domain models will number in the several dozens, at least.

Much of our data is stored on tables w/ several keys...some of which don't really even make sense to me but obviously did to the developers at the time. This is a case where we can either proceed w/ Java EE 5 and invest a lot of time and money or we'll be forced to use something else...God forbid...dotnet or some other kludge that I'd prefer to pretend doesn't exist.

So, that's where I'm at! Back to the point - maybe you can fudge it along (until this is fixed/enhanced) like I did...by simply using single PK fields and/or just doing joins in separate queries and taking the performance hit.

I wish I was more Hibernate savvy, in the future I may be able to find these things and contribute patches of my own. I think it's worth the time to start investigating the source code closer and at least get familiar.

Good luck!

emmanuel · **Posted:** Fri Feb 02, 2007 4:05 pm

tsar bomba wrote:

In other words, uniqueness of the subset of the PK can't always be guaranteed in many schemas.

If this key is not conceptually unique, what you are describing is a many to many association without an association table. Hibernate does not support such constraint.

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

emmanuel wrote:

tsar bomba wrote:

In other words, uniqueness of the subset of the PK can't always be guaranteed in many schemas.

If this key is not conceptually unique, what you are describing is a many to many association without an association table. Hibernate does not support such constraint.

I guess, w/ some creative thinking applied, you could try and call it that.

Quite simply, it's just a case where two tables are related on one or two fields...but may have a composite key w/ 3 or more fields (for example) - and this just isn't possible in Hibernate right now.

This isn't the first time I've seen such relationships and I don't think I'm unique in believing that it's a poor design but it happens to a lot of databases over time....eventually some of those models can't be changed.

This is entirely do-able (and simple) w/ plain SQL and you can join entities together in just about every other imaginable way in Hibernate...so it seems logical that Hibernate should be as flexible.

emmanuel · **Posted:** Fri Feb 02, 2007 5:48 pm

tsar bomba wrote:

emmanuel wrote:

[Quite simply, it's just a case where two tables are related on one or two fields...but may have a composite key w/ 3 or more fields (for example) - and this just isn't possible in Hibernate right now.

It's not a composite key, it's not even a key, it's not unique. And that's a fairly uncommon "design"