title: Hibernate Eager loading (many to one) ,Jersey Rest to produce JSON
body: <p>This is long one.
  I am developing Restful Web Services to produce JSON data using jersey ,Hibernate and database is MySQL.
  I have been using Hibernate and been doing all native-sql in my project to communicate to database.</p>
  
  <p>I have 2 schema in database and have respective entity classes, </p>
  
  <p><strong>Schema 1</strong> </p>
  
  <p>Tables:Jobs,Events,Clubs,Entries</p>
  
  <ol>
  <li>Jobs ----- 1 to many---> </li>
  <li>Events Jobs ----- 1 to many---> Clubs</li>
  <li>Events ----- 1 to many ---> Entries</li>
  <li>Clubs---------->Entries   (one to many)</li>
  </ol>
  
  <p><strong>Schema 2</strong> </p>
  
  <p>Tables: Jobs, Events,Clubs,Races,Results,Splits</p>
  
  <ol>
  <li><p>Jobs ---->Events ,Clubs (one to many)</p></li>
  <li><p>Events------>Races      (one to many)                               </p></li>
  <li><p>Races ------> Results   (one to many)    </p></li>
  <li><p>Clubs---------->Entries (one to many)                                                       </p></li>
  <li><p>Results------>Splits      (one to many) Also has eagerly mapped</p></li>
  </ol>
  
  <p>All tables in Schema 2 have key which matches to Schema 1 tables , For eg:
  Schema 2 Jobs has schema1.job_id , Schema 2 events has schema 1.event_id  key .
  To be simple we can access data from Schema_2/Schema_1 tables using Schema_1/Schema_2 table keys  </p>
  
  <p>I have all endpoints which return data from both database. I been doing cross schema queries. And doing good so far with out major trouble. But I have been struck with this issue.</p>
  
  <p>I have to get results data (also splits data which is eagerly mapped)  from Results, Splits tables from Schema 2 along with event,clubs,entries info from Schema 1 as well. I am unable to achieve this.</p>
  
  <p><strong>Results.java (Entity)</strong></p>
  
  <pre><code>  @XmlRootElement
    @Entity
    @Table(name = "results", catalog = "schema2")
    public class Results implements java.io.Serializable {
  
  private BigDecimal id;
  private Set&lt;Splits&gt; splits = new HashSet&lt;Splits&gt;(0);
  /* some more fields*/
  
  //getters and setters
  
      @XmlID
      @XmlJavaTypeAdapter(IDAdapter.class)
      @JsonProperty("resultId")
      @Id
      @Column(name = "Id", unique = true, nullable = false, precision = 12, scale = 6)
      public BigDecimal getId() {
          return this.id;
  
      @OneToMany(fetch=FetchType.EAGER, mappedBy="result", cascade= CascadeType.ALL)
      public Set&lt;Splits&gt; getSplits() {
          return splits;
      }
  
      public void setSplits(Set&lt;Splits&gt; splits) {
          this.splits = splits;
      }
  
      public void setId(BigDecimal id) {
          this.id = id;
      }
  
  }
  </code></pre>
  
  <p><strong>Splits.java (Entity class)</strong></p>
  
  <pre><code>private BigDecimal id;
  private Results result;
  
     // Property accessors
  @Id
  @Column(name = "Id", unique = true, nullable = false, precision = 12, scale = 6)
  public BigDecimal getId() {
      return this.id;
  }
  
  public void setId(BigDecimal id) {
      this.id = id;
  }
  
  
  @JsonIgnore
  @XmlIDREF
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumn(name="Pid",nullable=false,insertable=false,updatable=false)
  public Results getResult() {
      return result;
  }
  
  public void setResult(Results result) {
      this.result = result;
  </code></pre>
  
  <p>In my Dao, I been doing native sql queries using Hibernate</p>
  
  <pre><code> String sql=   "SELECT c1.organization, group_concat(c1.short_name separator     '/'),e1.event_description, res2.* FROM Schema_2.results res2 
      left outer join Schema_2.splits s2 on (s2.pid=res2.id)  
      left outer join Schema_2.races ra2 on(res2.pid=ra2.id) 
      join Schema_2.events e2 on(ra2.pid=e2.id) 
      left outer join Schema_2.entries en2 on (res2.entrypid=en2.id) 
      left outer join Schema_2.clubs c2 on(en2.pid=c2.id) 
      left outer join Schema_1.clubs c1 on (c1.org_id=c2.regOrgId)  
      left outer join Schema_1.events e1 on (e1.event_id=e2.regeventId)
      where res2.rmrsid=:jobId and en.entryStatus='Entered' 
      and e2.regEventId=:eventId group by en2.entryId order by res2.finishPlace";
              Query query=session.createSQLQuery(sql)
      .addEntity("res2",Schema_2.Results.class)
      .addEntity("c1" ,Schema_1.Clubs.class)
      .addEntity("e1",Schema_1.Events.class)
      .setParameter("jobId", jobId).setParameter("eventId", eventId);
  
                resultList=query.list();
                tx.commit();
  </code></pre>
  
  <p>What I am expecting  in my Json feed is :</p>
  
  <pre><code>{ "success":true, "count":some number,"msg":"","data":[ "organization":"ABC","eventDescription":"Event 1", **"resultNo":1,"resultTime":"8:30","ResultFinishTime":"xxxxx","splits":[{splits1 data},{splits2 data},{ splits 3 data}..]** ]}
  </code></pre>
  
  <p>But I am running into error: </p>
  
  <p>java.sql.SQLException: column  not found</p>
  
  <p>When I only select res2.* with the same query and addEntity("res2",Schema_2.Results.class)</p>
  
  <p>I have no issues and getting data from Results as well as eagerly loaded splits data like this</p>
  
  <pre><code>{ "success":true, "count":some number,"msg":"","data":[**"resultNo":1,"resultTime":"8:30","ResultFinishTime":"xxxxx","splits":[{splits1 data},{splits2 data},{ splits 3 data}..]** ]}
  </code></pre>
  
  <p>Does any one have encountered similar situation, Is there any work around for this.</p>
  
  <p>P.S</p>
  
  <p>When used Transformer , EntityToBean or EntityToMap, child collections(Splits) are included in my data set, I get all data but except splits data.</p>
  
  <p>Thank you in advance.</p>