Can I map a relationship inJPA with a unique target that is not the id?

Question

I'd like to be able to use the dot notation to get from one entity to another.

Let's say I have a table Users:

id  username   acronym
-----------------------
 1  john       NASA

And a table Institutions:

id  instname       acronym
---------------------------
 9  Natl. Air...   NASA

For historical reasons, the Users table does not contain a reference to the Institution id, but the acronym is unique.

Is there any possibility to dot my way from the entity for John to the name of NASA as in john.getInstitution().InstName()? I'd like to avoid having to run a query through the entity manager.

I think you should re think your data structure. If acronym is unique, you should declare acronym is foreign key. If acronym is not unique, you cannot map a relationship. — Sai Ye Yan Naing Aye, Jan 21 '16 at 10:04
Unfortunately I can't (easily) make changes to the model. But would that help? If it's declared a foreign key, doesn't it have to be the primary key in the target table? — Antares42, Jan 21 '16 at 10:09

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

The answer is "no, you can't", apparently: "[T]he JPA spec doesn't allow relationships/FKs to non-PK columns at all"

edited May 23 '17 at 12:31

Community

1
1

answered Jan 21 '16 at 10:09

Antares42

1,406
1
15
45

Alan Hay · Accepted Answer · 2016-01-21T15:34:49.700

Have you actually tried it?

As far as I can see then, depending on your JPA provider, the following mappings may or not be supported.

@Entity
public class Institution{

    @OneToMany(mappedBy = "institution")
    private Set<User> users;
}

@Entity
public class User {

    @ManyToOne
    @JoinColumn(name = "acronym", referencedColumName = "acronym")
    private Institution institution;

}

Failing this, the other possibility is to create a database view based on the following and map your User entity to this.

select 
    u.id, u.username, i.id as institution_id
from 
    users u
inner join 
    institutions i on u.acronym = i.acronym

Entity with 'proper' FK mapping:

@Entity
@Table(name = "vw_users")
public class User {

    @ManyToOne
    @JoinColumn(name = "institution_id")
    private Institution institution;

}

That's true, if you use Hibernate as your JPA provider it just works. But I like your view suggestion. — Antares42, Jan 21 '16 at 15:20

Can I map a relationship inJPA with a unique target that is not the id?

2 Answers2