Hibernate: Mapping a One-To-Many-Reference from a non primary key to a primary key

Question

I got a legacy database where (unfortunately) foreign keys are placed rarely. If i create my own foreign key (column A.testnr points on B.nummer) in java hibernate is using the primary key of A (column id) which results (as expected) in an error.

Ive read that i have to place a mappedBy-Reference on the column of class B. The primary key in class B is a number, I tried to change it to a Set(A) but then im getting some persistence error.

Mapping ManyToOne-Relation - A.testnr -> B.nummer

Class A    

@Id
@Column(name = "NUMMER", unique = true, nullable = false, precision = 22, 
scale = 0)
public BigDecimal getNummer() {
 return this.nummer;
}

public void setNummer(BigDecimal nummer) {
  this.nummer = nummer;
 }

@ManyToOne
@JoinColumn(name = "nummer", referencedColumnName = "nummer")
public B getTestnr() {
  return this.testnr;
}

public void setTestnr(B testnr) {
  this.testnr = testnr;
}


Class B

@Id
@Column(name = "NUMMER", unique = true, nullable = false, precision = 22, 
  scale = 0) 
public BigDecimal getNummer() {
  return this.nummer;
}

public void setNummer(BigDecimal nummer) {
  this.nummer = nummer;
}

Error Message: javax.persistence.EntityNotFoundException cause the primary key in A (A.nummer) is understandably not part of B.nummer.

As I already told i tried using a MappedBy-Reference and changed the BigDecimal PK in class B to a Set(A).

@Id
@OneToMany(fetch = FetchType.EAGER, mappedBy = "testnr")
@Column(name = "NUMMER", unique = true, nullable = false, precision = 22, 
 scale = 0)
public Set<A> getNummern() {
  return this.nummern;
}

public void setNummern(Set<A> nummern) {
  this.nummern = nummern;
}

With this change im getting a runtime error probably due to discrepancies in the persistence layer.

Is my goal, to Map a non-primary-key to a primary key of another class even possible with hibernate or is this a situation where im forced to use native sql ?

Answer 1

In order for you to make a join on a non-indexed column, that's absolutely right, you have to use referencedColumnName. However in your second code snipped you marked your @ManyToOne collection as id, which is incorrect. If I understood you correctly, multiple A.nummer belong to a single B.testnr.

This is how you get what you want:

Class A, nummer which belongs to testnr.

@Entity
@Table(name = "a_table")
public class A {

    @Id // a_table_id
    @Column(name = "NUMMER", unique = true, nullable = false, precision = 22)
    private BigDecimal nummer;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "nummer", referencedColumnName = "nummer")
    // make sure referenced column name matches column in table B
    private B testnr;

    public BigDecimal getNummer() {
        return this.nummer;
    }

    public void setNummer(BigDecimal nummer) {
        this.nummer = nummer;
    }

    public B getTestnr() {
        return this.testnr;
    }

    public void setTestnr(B testnr) {
        this.testnr = testnr;
    }

}

Class B, testnr that has multiple nummers.

@Entity
@Table(name = "b_table")
public class B {

    @Id // b_table_id
    @Column(name = "b_table_id")
    private BigDecimal testnr;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "testnr")
    private Set<A> nummern;

    public Set<A> getNummern() {
        return this.nummern;
    }

    public void setNummern(Set<A> nummern) {
        this.nummern = nummern;
    }
}