How to join tables on non Primary Key using JPA and Hibernate
This is how your mapping should look like:
@Entity
public class User {
@Id
private Long id;
private String name;
@OneToMany(mappedBy = "user")
private List<UserHouseMap> houses = new ArrayList<>();
}
@Entity
public class House {
@Id
@Column(name = "house_name", nullable = false, unique = true)
private String house_name;
private String address;
@OneToMany(mappedBy = "house")
private List<UserHouseMap> users = new ArrayList<>();
}
@Entity
public class UserHouseMap implements Serializable {
@Id @ManyToOne
@JoinColumn(name = "user_name", referencedColumnName = "name")
private User user;
@Id @ManyToOne
@JoinColumn(name = "house_name", referencedColumnName = "house_name")
private House house;
}
Both User
and House
have access to their associated UserHouseMap
entities, matching the database schema.
user2158382
Updated on January 09, 2021Comments
-
user2158382 over 3 years
I have 3 models
User
,House
,UserHouseMap
. And I need to access a user's house through the map. Only problem is this is an old DB & I can't change the fact that I need to mapUser
toUserHouseMap
usinguser.name
, which is a non primary key.Hibernate keeps giving me errors saying I need to have it as the primary key or I get errors saying
A JPA error occurred (Unable to build EntityManagerFactory): Unable to find column with logical name: name in org.hibernate.mapping.Table(users) and its related supertables and secondary tables
I have tried
@Formula
as a workaround, but that didnt work. I also tried@JoinColumnOrFormula
but that didnt work either. Here is my solution with@Formula
@Expose @ManyToOne(targetEntity = House.class) @Formula("(select * from houses inner join user_house_map on houses.house_name = user_house_map.house_name where user_house_map.user_name=name)") public House house;
Here was my attempt at a
@JoinColumnOrFormula
solution.@Expose @ManyToOne(targetEntity = House.class) @JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula=@JoinFormula(value="select name from users where users.id= id", referencedColumnName="name")), @JoinColumnOrFormula(column = @JoinColumn(name= "house_name", referencedColumnName="house_name")) }) public House house;
Here is my mapping
@Id @GeneratedValue @Expose public Long id; @Expose @Required @ManyToOne @JoinTable( name="user_house_map", joinColumns= @JoinColumn(unique=true,name="user_name", referencedColumnName="name"), inverseJoinColumns= @JoinColumn(name="house_name", referencedColumnName="house_name")) private House house;
Here are the DB schemas
Users
Table "public.users" Column | Type | Modifiers -----------------------+-----------------------------+----------------------------- name | character varying(255) | id | integer | not null Indexes: "user_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "housing_fkey" FOREIGN KEY (name) REFERENCES user_house_map(user_name) DEFERRABLE INITIALLY DEFERRED
Houses
Table "public.houses" Column | Type | Modifiers ---------------+------------------------+----------- house_name | character varying(255) | not null address | text | city | text | state | text | zip | integer | zip_ext | integer | phone | text | Indexes: "house_pkey" PRIMARY KEY, btree (house_name) Referenced by: TABLE "user_house_map" CONSTRAINT "house_map_fkey" FOREIGN KEY (house_name) REFERENCES house(house_name) DEFERRABLE INITIALLY DEFERRED
UserHouseMap
Table "public.user_house_map" Column | Type | Modifiers -------------+------------------------+----------- user_name | character varying(255) | not null house_name | character varying(255) | not null Indexes: "user_house_map_pkey" PRIMARY KEY, btree (user_name) "user_house_map_house_key" btree (house_name) Foreign-key constraints: "user_house_map_house_fkey" FOREIGN KEY (house_name) REFERENCES houses(house_name) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "users" CONSTRAINT "housing_fkey" FOREIGN KEY (name) REFERENCES user_house_map(user_name) DEFERRABLE INITIALLY DEFERRED
-
Kevin Vasko about 8 yearsI know this is old but I had a hellva time with this same error. Your solution works, however I had to make a slight change but I had to add @Column(name = "name", nullable = false) private String name; If I had to take a guess as to why, my variable/property name is not the same as the column name as the database table. Just an FYI for anyone else that is having same/similar issues.
-
Vlad Mihalcea about 8 yearsThanks. I added both
nullable=false
andunique=true
, which must be enforced by any identifier column. -
Archi over 6 yearsThis won't work if user_name and house_name are non-primary keys!!