How to map native sql results to oneToMany field with SqlResultSetMapping in JPA
<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>