Hibernate many-to-one composite key as foreign key

[Kamal Bhattad]

Hi,

I have two table. Table1 has composite primary key. Table2 has table1 composite primary key as foreign key.

CREATE TABLE Group (
gid varchar(128) NOT NULL default '',
cId integer NOT NULL,
name varchar(256) NOT NULL default '',
PRIMARY KEY ( gid, cId ),
foreign key (cId) references cluster(cId)
) ;

CREATE TABLE GroupMember (
gmid varchar(128) NOT NULL default '',
cId integer NOT NULL,
fkgroup varchar(128) NOT NULL default '',
priority integer default NULL,
PRIMARY KEY ( gmid, cId ),
foreign key (cId) references cluster(cId),
foreign key (fkgroup,cId) references Group(gid, cId)
) ;

cId is primary key of another table, hope this table definition is not required.

I used HibernateTool 3.2.4 to generate Mapping File and POJOs.

Here is the mapping File generated for table GroupMember

<hibernate-mapping>
<class name="my.test.pkg.Groupmember" table="GroupMember">
<composite-id name="id" class="my.test.pkg.GroupMemberId">
<key-property name="gmid" type="string">
<column name="gmid" length="128" />
</key-property>
<key-property name="cId" type="int">
<column name="cId" />
</key-property>
</composite-id>
<many-to-one name="Group" class="my.test.pkg.Group" update="false" insert="false" fetch="select">
<column name="fkgroup" length="128" not-null="true" />
<column name="cId" not-null="true" />
</many-to-one>
<many-to-one name="cluster" class="my.test.pkg.Cluster" update="false" insert="false" fetch="select">
<column name="cId" not-null="true" />
</many-to-one>
<property name="priority" type="java.lang.Integer">
<column name="priority" />
</property>
</class>
</hibernate-mapping>

Related POJO File looks like this.

public class GroupMemberId implements java.io.Serializable {
private String gmid;
private int cId;
}

public class GroupMember implements java.io.Serializable {

private GroupmemberId id;
private Group group;
private Cluster cluster;
private Integer priority;
}

Now when i attempt to Save GroupMember object by filling all member variable, Hibernate only does insertion for 3 column, one column fkgroup is not getting inserted.
insert into GroupMember (priority, gmid, cId) values (?, ?, ?)

I'm unable to save fkgroup column of GroupMember table, get following exception

SEVERE: SQL Anywhere Error -194: No primary key value for foreign key 'group' in table 'GroupMember'
org.hibernate.exception.ConstraintViolationException: could not insert: [my.test.pkg.GroupMember]


Then i realized that insert="false" is set many-to-one relation which is causing issue for fkgroup not to be inserted. Setting insert="true" here gives exception

org.hibernate.MappingException: Repeated column in mapping for entity: my.test.pkg.GroupMember column: cId (should be mapped with insert="false" update="false")

For now, i have no idea how can insert GroupMember with fkgroup foreign key.

I have checked forum, but couldn't find any solution.

Any help on how can i insert/save GroupMember object ??

[Mark Spritzler]

this sounds likes a similar issue we just had. Let me ask this question.

Is it that you have a composite ID with two fields, and one of those two fields is a FK to the Parent table/object?

If so this is our solution

   
public class Parent {

    @Id
    private Long someId;

    @OneToMany(mappedBy="pk.parent")
    @JoinColumns([@JoinColumn(name="someId"), @JoinColumn(name="otherField")])
    private Set<Child> children
}

public class Child {
    private ChildPK pk;

    private String someReallyOtherField;
}

class ChildPK {
    @ManyToOne
    @JoinColumn(name="someId")
    Parent parent;
  
    String otherField;
}
 

That is the basic solution we had for this problem. There is stuff out there in the web in which I found this solution. And in older posts they had the Child class have a faked out property like

@Column(name="someId"
private Long parent

But that is only needed for older versions of Hibernate 3.x

Mark

[Kamal Bhattad]

Hi Mark,

Thanks for your reply.

In my case i have Composite Primary key and foreign key which is also composite key. Now one of the column is same

i.e. Table Group has composite Id PRIMARY KEY(gId, cId) [ Where cId is primary key of other table ]

Now Table GroupMember has composite primary key PRIMARY KEY(gmid, cId ) [ Where cId is primary key of other table ].

GroupMember also has foreign key fkgroup REFERENCES Group(gid, cId) (which is composite key as mentioned above]

Now, when i attemt to save GroupMember instance, foreign key (fkgroup) is not inserted. In many-to-one relationship i cannot set insert=true because cId is part of primary key for both GroupMember and Group Table.

How do i persist column fkgroup of GroupMemeber table?

[Mark Spritzler]

Maybe I had a typo. But the way you describe it does sound like what I posted.

You have a PK in one object, where one of the properties of the PK is a FK to the parent object. That is what I posted.

Mark

[Kamal Bhattad]

Hi Mike,

Could you please help me with similar mapping in .hbm.xml file for both Parent and Child where composite PK and FK are used.

Thanks
Kamal