How do I join tables on non-primary key columns?

Question

I have an issue with join tables on an object in an ORM class hierarchy where the join column is NOT the primary key of the base class due a lagacy database structure. Here is an example of the table design:

CREATE TABLE "SCH"."FOO"
(
        "OWNERID"       NUMBER(10,0) NOT NULL ENABLE,
        "FOOID"         NUMBER(10,0) NOT NULL ENABLE,
        CONSTRAINT "FOO_PK" PRIMARY KEY ("OWNERID", "FOOID")
        CONSTRAINT "FOO_FK1" FOREIGN KEY ("OWNERID") REFERENCES "SCH"."OWNERS" ("OWNERID") ENABLE
)

CREATE TABLE "SCH"."BAR"
(
        "BARID"             NUMBER(10,0) NOT NULL ENABLE,
        "FOOID"             NUMBER(10,0)
        CONSTRAINT "BAR_PK" PRIMARY KEY ("BARID")
)

And here are the mappings (unesessary infomation removed)

@Entity
@IdClass(FooId.class)
@Table(name = "FOO")
public class Foo implements java.io.Serializable
{
    @Id
    @Column(name = "OWNERID")
    private BigInteger ownerId;

    @Id
    @SequenceGenerator(name = "FOO_GENERATOR", sequenceName = "SEQ_FOO")
    @GeneratedValue(generator = "FOO_GENERATOR")
    @Column(name = "FOOID")
    private BigInteger id;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOOID", referencedColumnName = "FOOID")
    @Fetch(value = FetchMode.SUBSELECT)
    @Cascade(value = {CascadeType.ALL})
    private Set<Bar> bar = new LinkedHashSet<Bar>(0);
}


@Entity
@Table(name = "BAR")
public class Bar implements java.io.Serializable
{
    @Id
    @Column(name = "BARID")
    private BigInteger id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOOID", referencedColumnName = "FOOID")
    private Foo foo;
}

This fails with an exception:

Caused by: org.hibernate.AnnotationException: referencedColumnNames(FOOID) of com.package.Bar.foo referencing com.package.Foo not mapped to a single property
    at org.hibernate.cfg.BinderHelper.createSyntheticPropertyReference(BinderHelper.java:204)
    at org.hibernate.cfg.ToOneFkSecondPass.doSecondPass(ToOneFkSecondPass.java:114)
    at org.hibernate.cfg.Configuration.processEndOfQueue(Configuration.java:1580)
    at org.hibernate.cfg.Configuration.processFkSecondPassInOrder(Configuration.java:1503)
    at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1419)
    at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1375)

Could you please help with a solution?

score 6 · Accepted Answer · edited Oct 12 '16 at 15:59

6

You must not map the bidirectional association twice. The One side must be marked as the inverse of the Many side, using the mappedBy attribute:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "foo")
@Fetch(value = FetchMode.SUBSELECT)
@Cascade(value = {CascadeType.ALL})
private Set<Bar> bar = new LinkedHashSet<Bar>(0);

...

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "FOOID", referencedColumnName = "FOOID")
private Foo foo;

There is no reason to tell Hibernate twice that the association is mapped by the join column FOOID. And doing it is actually an error, because it defines two different unidirectional associations rather than one bidirectional association.

EDIT

The above should work, but doesn't due to the following Hibernate bug: It's a Hibernate bug. See HHH-4284.

To circumvent this problem, since the FOOID is enough to ensure uniqueness, a solution would be to remove the @Id annotation from the owner ID and the @IdClass annotation.

edited Oct 12 '16 at 15:59

Sergey Vyacheslavovich Brunov

17,291
7
48
81

answered Jun 05 '12 at 10:12

JB Nizet

678,734
91
1,224
1,255

2

Tried this before, not working: referencedColumnNames(FOOID) of com.package.Bar.foo referencing com.package.Foo not mapped to a single property – Dzmitry Jun 05 '12 at 10:22
1

It's a Hibernate bug. See https://hibernate.onjira.com/browse/HHH-4284. OTOH, couldn't you simply remove the @Id annotation from the owner ID, since the FOOID is enough to ensure uniqueness? – JB Nizet Jun 05 '12 at 10:34
Removing @ Id annotation from the owner ID and removing @ IdClass is works, not as expected, but works somehow. Could you please post is as a different answer so I could accept it as a solution? Thank you a lot – Dzmitry Jun 05 '12 at 11:20
The referenced bug is closed in 2013 as non-reproducible. – naXa stands with Ukraine Sep 18 '17 at 16:40

score 1 · Answer 2 · answered Jun 05 '12 at 10:05

U can do as follows.... it should work -

@Entity
@IdClass(FooId.class)
@Table(name = "FOO")
public class Foo implements java.io.Serializable
{
    @Id
    @Column(name = "OWNERID")
    private BigInteger ownerId;

    @Id
    @SequenceGenerator(name = "FOO_GENERATOR", sequenceName = "SEQ_FOO")
    @GeneratedValue(generator = "FOO_GENERATOR")
    @Column(name = "FOOID")
    private BigInteger id;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "FOOID",nullable=false)
    @ForeignKey(name = "fk")     
    private Set<Bar> bar = new LinkedHashSet<Bar>(0);
}


@Entity
@Table(name = "BAR")
public class Bar implements java.io.Serializable
{
    @Id
    @Column(name = "BARID")
    private BigInteger id;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "FOOID", updatable = false, insertable = false, nullable=false)  
     private Foo foo;
}

Not working, says: A Foreign key refering com.package.Foo from com.package.Bar has the wrong number of column. should be 2 — Dzmitry, Jun 05 '12 at 10:18

score 0 · Answer 3 · answered Jun 05 '12 at 10:51

0

You used composite primary key in FOO table. So you should try @EmbeddedId property and you should need two columns "OWNER_ID" and "FOO_ID" in BAR entity that join with FOO entity.

answered Jun 05 '12 at 10:51

Sai Ye Yan Naing Aye

6,622
12
47
65

1

I know, but I can't change db structure :( – Dzmitry Jun 05 '12 at 12:43

How do I join tables on non-primary key columns?

3 Answers3

EDIT

Linked