title: How to join tables on non Primary Key using JPA and Hibernate
body: <p>I have 3 models <code>User</code>, <code>House</code>, <code>UserHouseMap</code>. And I need to access a user's house through the map. Only problem is this is an old DB &amp; I can't change the fact that I need to map <code>User</code> to <code>UserHouseMap</code> using <code>user.name</code>, which is a non primary key.</p>
  
  <p>Hibernate keeps giving me errors saying I need to have it as the primary key or I get errors saying <code>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</code></p>
  
  <p>I have tried <code>@Formula</code> as a workaround, but that didnt work. I also tried <code>@JoinColumnOrFormula</code> but that didnt work either. Here is my solution with <code>@Formula</code></p>
  
  <pre><code>@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;
  </code></pre>
  
  <p>Here was my attempt at a <code>@JoinColumnOrFormula</code> solution.</p>
  
  <pre><code>@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;
  </code></pre>
  
  <p>Here is my mapping</p>
  
  <pre><code>@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;
  </code></pre>
  
  <p>Here are the DB schemas</p>
  
  <p><strong>Users</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>                               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
  </code></pre>
  
  <p><strong>Houses</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>                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
  </code></pre>
  
  <p><strong>UserHouseMap</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>         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
  </code></pre>