title: Joining two tables from two different libraries using java, DB2, spring and hibernate
body: <p>I’m using DB2 on AS400 (iSeries), hibernate 3, spring 2.0 and Java 6 and I have two tables (physical files) on two different libraries like this: Library1/TableA and Library2/Table2, so normally I need to a sessionFactory for each library like this:</p>
  
  <pre><code>&lt;bean id="sessionFactory1AS400"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
          &lt;property name="dataSource" ref="dataSourceAS400" /&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;com.appllication.model.TableA&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;props&gt;
                  …
                  &lt;prop key="hibernate.default_schema"&gt;Library1&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>And</p>
  
  <pre><code>&lt;bean id="sessionFactory2AS400"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
          &lt;property name="dataSource" ref="dataSourceAS400" /&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;com.appllication.model.TableB&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;props&gt;
                  …
                  &lt;prop key="hibernate.default_schema"&gt;Library2&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>I’m trying to join the tables on my class like this:</p>
  
  <pre><code>@Entity(name = "TableA")
  public class TableA {
  
  @ManyToOne(targetEntity=TableB.class, fetch=FetchType.LAZY)
      @JoinColumns(
              {
                  @JoinColumn(name="column1", referencedColumnName="column1", insertable=false, updatable=false),
                  @JoinColumn(name="column2", referencedColumnName="column2", insertable=false, updatable=false)
              })
      private TableB tableB;
  
  …
  }
  </code></pre>
  
  <p>But when I run my unit test it fails because the DAO class can only load one sessionFactory at a time and my TableADao loads the sessionFactory1AS400 which has no knowledge of the existence of the TableB.
  To overcome that problem I moved my TableB to the same sessionFactory as TableA:</p>
  
  <pre><code>&lt;bean id="sessionFactory1AS400"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
          &lt;property name="dataSource" ref="dataSourceAS400" /&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;com.appllication.model.TableA&lt;/value&gt;
  &lt;value&gt;com.appllication.model.TableB&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;props&gt;
                  …
                  &lt;prop key="hibernate.default_schema"&gt;Library1&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>And added the definition of the schema on class TableB:</p>
  
  <pre><code>@Entity(name="TableB")
  @Table(schema="Library2")
  public class TableB implements Serializable {
  …
  }
  </code></pre>
  
  <p>This time my test ran OK and gave me the correct query:</p>
  
  <pre><code>SELECT * FROM Library1.TableA ta INNER JOIN Library2.TableB tb ON ta.column1 = tb.column1 AND ta.column2 = tb.column2
  </code></pre>
  
  <p>Now my problem is that the schema definition on TableB is hardcoded instead of being loaded from a configuration file which is the perfect scenario, since we have different environments where the libraries names are different.</p>
  
  <p>Is there a way to have the schema definition on TableB come from a configuration on spring or any other way to join these tables?</p>
  
  <p>Thanks in advance for your time.</p>