[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-5809) Mapping composite foreign key to composite primary key fails

[David (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=39433#action_39433 ] 

David edited comment on HHH-5809 at 12/27/10 11:44 AM:
-------------------------------------------------------

Ok, so having played with this a bit more, this is what I have determined...

I created two test tables:

*Table Person*
||Name||Type||Notes|| 
|id|number(30)|primary key| 
|full_name|varchar(16)| |
|state|varchar (2)|FK Billing(state, country)| 
|country|varchar (3)| |

*Table Billing*
||Name||Type||Notes|| 
|state|varchar (2)|primary composite key (state, country)| 
|country|varchar (3)| |
|price|number(16)| |

Then I ran Hibernate Tool 3.3.0, and it generated:

{code:title=Person.java|borderStyle=solid}
@Entity
@Table(name="PERSON")
public class Person implements java.io.Serializable {

    private BigDecimal id;
    private Billing billing;

    // Constructors...

    // getters / setters

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns ( { 
    @JoinColumn(name = "STATE", referencedColumnName = "STATE", nullable = false),
    @JoinColumn(name = "COUNTRY", referencedColumnName = "COUNTRY", nullable = false) } )
    @NotFound(action=NotFoundAction.IGNORE)
    public Billing getBilling()
    {
       return this.billing;
    }
}
{code}

{code:title=Billing.java|borderStyle=solid}
@Entity
@Table(name="BILLING")
public class Person implements java.io.Serializable {

    private BillingId id;
    private long price;
    private Set<Person> persons = new HashSet<Person> (0);

   // constructors...

   // getters / setters...

   @EmbeddedId
   @AttributeOverrides( {
      @AttributeOverride(name = "state", column = @Column(name = "STATE", nullable = false, length=2)),
      @AttributeOverride(name = "country", column = @Column(name = "COUNTRY", nullable = false, length=3)) })
   public BillingId getId()
   {
       return this.id;
   }

   @OneToMany(fetch = FetchType.LAZY, mappedBy = "billing")
   public Set<Person> getPersons()
   {
     return this.persons;
   }
{code}

{code:title=BillingId.java|borderStyle=solid}
@Embeddable
public class BillingId implements java.io.Serializable {

    private String state;
    private String country;

   // constructors...

   @Column(name = "STATE", nullable = false, length = 2)
   public String getState()
   {
       return this.state;
   }

   // other getters / setters...
{code}

All classes are read-only and they all implement hashcode and equals based on their primary keys.  There is not billing information for each state/country combination, so we don't want to throw an exception if that occurs, just return null.

The problem I am encountering is that when I do an HQL left fetch join on person.billing, the correct SQL is deployed and the Billing objects are loaded, but they are not put into the session/level2 cache.  So immediately after launching the query, it iterates through all the people objects and does a single query to load each person's billing information (N + 1).  I could potentially live with that, but when I set batchSize = 4 on the Billing object, it will correctly do the left join in SQL, then iterate through the results, and load 4 Billing objects at a time, but I see the same number of queries being sent.  So in the batchSize = 1, I see (N + X) queries, and with batchSize = 4, I see (N + X) instead of the expected (N + (X/4)) queries, but for each X, it generates SQL that loads 4 at a time.  Very peculiar.  So I cannot seem to get around (N + 1) and my N can be in the thousands.  Interestingly enough, if I execute the same HQL statement twice, it will correctly pull the Billing objects from the 2nd LvL Cache the second time, without the need to do (N + 1) again.

It's also odd, because the N SQL statements to load the N Billing objects occur within Hibernate somewhere, and are not invoked by my calling person.getBilling() (even though it's marked as lazy).

Right-joins work correctly (for those times I want to only see persons with billing information).  One SQL statement will be executed and it will load the Billing objects at that point.

Thanks.

      was (Author: davidemm):
    Ok, so having played with this a bit more, this is what I have determined...

I created two test tables:

*Table Person*
||Name||Type||Notes|| 
|id|number(30)|primary key| 
|full_name|varchar(16)| |
|state|varchar (2)|FK Billing(state, country)| 
|country|varchar (3)| |

*Table Billing*
||Name||Type||Notes|| 
|state|varchar (2)|primary composite key (state, country)| 
|country|varchar (3)| |
|price|number(16)| |

Then I ran Hibernate Tool 3.3.0, and it generated:

{code:title=Person.java|borderStyle=solid}
@Entity
@Table(name="PERSON")
public class Person implements java.io.Serializable {

    private BigDecimal id;
    private Billing billing;

    // Constructors...

    // getters / setters

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns ( { 
    @JoinColumn(name = "STATE", referencedColumnName = "STATE", nullable = false),
    @JoinColumn(name = "COUNTRY", referencedColumnName = "COUNTRY", nullable = false) } )
    @NotFound(action=NotFoundAction.IGNORE)
    public Billing getBilling()
    {
       return this.billing;
    }
}
{code}

{code:title=Billing.java|borderStyle=solid}
@Entity
@Table(name="BILLING")
public class Person implements java.io.Serializable {

    private BillingId id;
    private long price;
    private Set<Person> persons = new HashSet<Person> (0);

   // constructors...

   // getters / setters...

   @EmbeddedId
   @AttributeOverrides( {
      @AttributeOverride(name = "state", column = @Column(name = "STATE", nullable = false, length=2)),
      @AttributeOverride(name = "country", column = @Column(name = "COUNTRY", nullable = false, length=3)) })
   public BillingId getId()
   {
       return this.id;
   }

   @OneToMany(fetch = FetchType.LAZY, mappedBy = "billing")
   public Set<Person> getPersons()
   {
     return this.persons;
   }
{code}

{code:title=BillingId.java|borderStyle=solid}
@Embeddable
public class BillingId implements java.io.Serializable {

    private String state;
    private String country;

   // constructors...

   @Column(name = "STATE", nullable = false, length = 2)
   public String getState()
   {
       return this.state;
   }

   // other getters / setters...
{code}

All classes are read-only and they all implement hashcode and equals based on their primary keys.  There is not billing information for each state/country combination, so we don't want to throw an exception if that occurs, just return null.

The problem I am encountering is that when I do an HQL left fetch join on person.billing, the correct SQL is deployed and the Billing objects are loaded, but they are not put into the session/level2 cache.  So immediately after launching the query, it iterates through all the people objects and does a single query to load each person's billing information (N + 1).  I could potentially live with that, but when I set batchSize = 4 on the Billing object, it will correctly do the left join in SQL, then iterate through the results, and load 4 Billing objects at a time, but I see the same number of queries being sent.  So in the batchSize = 1, I see (N + X) queries, and with batchSize = 4, I see (N + X) instead of the expected (N + (X/4)) queries, but for each X, it generates SQL that loads 4 at a time.  Very peculiar.  So I cannot seem to get around (N + 1) and my N can be in the thousands.

Thanks.
  
> Mapping composite foreign key to composite primary key fails
> ------------------------------------------------------------
>
>                 Key: HHH-5809
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5809
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.5.6
>         Environment: Java Hibernate 3.5.6_FINAL, Oracle 11g
>            Reporter: David
>
> I am seeing a problem similiar to the problems described at:
> https://forum.hibernate.org/viewtopic.php?f=25&t=996445
> I have mapped a composite foreign key to a primary composite key and it does not cache the results properly. It's a bi-directional, many-to-one association mapped essentially a cut+paste (with variable name substitution) from:
> http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#d0e2177
> but with a @NotFound set to IGNORE.
> When I include the relationship in an HQL left fetch join, I can see that the Child's Parent entity is placed in the session-cache.  But when the Child class's getParent() method is invoked, the result is loaded from the database instead of the session.  It completely ignores the session and the second-level cache, so I'm left with the N + 1 problem.  If I enable batch fetch (size = 8), the entities loaded from the batch are also not placed into either session cache (or level 2), so I get 1 + N (8-batch) fetches.
> If the query runs again, everything correctly loads from the second-level cache.
> Thanks.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira