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

i have 3 models user, house, userhousemap. , need access user's house through map. problem old db & can't change fact need map user userhousemap using user.name, non primary key.

hibernate keeps giving me errors saying need have primary key or errors saying a jpa error occurred (unable build entitymanagerfactory): unable find column logical name: name in org.hibernate.mapping.table(users) , related supertables , secondary tables

i have tried @formula workaround, didnt work. tried @joincolumnorformula didnt work either. here solution @formula

@expose @manytoone(targetentity = house.class) @formula("(select * houses inner join user_house_map on houses.house_name = user_house_map.house_name user_house_map.user_name=name)") public house house; 

here attempt @ @joincolumnorformula solution.

@expose @manytoone(targetentity = house.class) @joincolumnsorformulas({         @joincolumnorformula(formula=@joinformula(value="select name users users.id= id", referencedcolumnname="name")),         @joincolumnorformula(column = @joincolumn(name= "house_name", referencedcolumnname="house_name")) }) public house house; 

here 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 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 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 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 deferred referenced by:     table "users" constraint "housing_fkey" foreign key (name) references user_house_map(user_name) deferrable deferred 

this how mapping should 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 , house have access associated userhousemap entities, matching database schema.

using two one-to-many associations better relying on many-to-many relations.