Interlated Interlated - 2 months ago 8
Java Question

How to match a hibernate/JPA table without a foreign key

I would like to map a property (virtually) into the Entity linking to another entity that has no foreign key and without changing the database structure. There is already a getter for the key that maps to the related table.

An existing database.

Table egg

+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| pid | varchar(60) | NO | | | |


Table basket

+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| pid | varchar(60) | NO | | | |


The Entity Egg

@SuppressWarnings("serial")
@Entity
@Table(name = "egg")
public class Egg implements java.io.Serializable {
private long id;
private String pid;
...
@Column(name = "pid")
public String getPid() {
return this.pid;
}


The Entity for basket is similar with the pid getter already set as for egg.

I would like to model a join.

LEFT JOIN egg ON basket.pid = egg.pid


There is no foreign key on either table mapping pid together.

I have tried creating a basket property in the egg entity.

e.g.

@JoinColumn(name = "pid", referencedColumnName = "pid", updatable = false, insertable = false)
public Basket getBasket(


Of course hibernate complains that there is no 'basket' column in the table.

How do I get a the basket for this egg?

Answer

Try using @JoinColumnsOrFormulas annotation.

Something like this should work:

@ManyToOne
@JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT b.id FROM Basket b WHERE b.pid = pid)", referencedColumnName="id")),
    @JoinColumnOrFormula(column = @JoinColumn(name = "pid", referencedColumnName="pid"))
})
private Basket getBasket() {
    return this.basket;
}