How to join tables on non Primary Key using JPA and Hibernate

Question

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 map User to UserHouseMap using user.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 

Answer 1

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.