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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5809?page=c... ] David edited comment on HHH-5809 at 12/27/10 11:21 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. 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. -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira