title: How to map native sql results to oneToMany field with SqlResultSetMapping in JPA
body: <p>For example, say I have the following database tables</p>
  
  <pre><code>Building
  -----------
  id
  name
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Room
  -----------
  id
  roomNo
  buildingId
  </code></pre>
  
  <p>the Building Java class will look something like</p>
  
  <pre><code>@Entity
  public class Building {       
     private int id;
     @OneToMany private List&lt;Room&gt; rooms;
  ...
  </code></pre>
  
  <p>I have the following SQL to fetch Building</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT   b.id,
           b.name,
           r.id AS rid,
           r.roomNo 
  FROM     Building b
    LEFT JOIN Room r ON
           b.id = r.buildingId
  </code></pre>
  
  <p>the result will be</p>
  
  <pre><code>id, name, rid, roomNo
  ---------------------
  1, 'b1', 10, 'r100'
  1, 'b1', 11, 'r101'
  2, 'b2', 12, 'r10'
  </code></pre>
  
  <p>Note that there are duplicate Building due to one-to-many relationship.</p>
  
  <p>I know that I can get the Building entity simply by using JPQL but I want to do this with native SQL. So how would I map the result above back to the Building class using JPA's @SqlResultSetMapping? </p>