Joining two tables from two different libraries using java, DB2, spring and hibernate

Question

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:

<bean id="sessionFactory1AS400"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="dataSourceAS400" />
        <property name="annotatedClasses">
            <list>
                <value>com.appllication.model.TableA</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                …
                <prop key="hibernate.default_schema">Library1</prop>
            </props>
        </property>
    </bean>

And

<bean id="sessionFactory2AS400"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="dataSourceAS400" />
        <property name="annotatedClasses">
            <list>
                <value>com.appllication.model.TableB</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                …
                <prop key="hibernate.default_schema">Library2</prop>
            </props>
        </property>
    </bean>

I’m trying to join the tables on my class like this:

@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;

…
}

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:

<bean id="sessionFactory1AS400"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="dataSourceAS400" />
        <property name="annotatedClasses">
            <list>
                <value>com.appllication.model.TableA</value>
<value>com.appllication.model.TableB</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                …
                <prop key="hibernate.default_schema">Library1</prop>
            </props>
        </property>
    </bean>

And added the definition of the schema on class TableB:

@Entity(name="TableB")
@Table(schema="Library2")
public class TableB implements Serializable {
…
}

This time my test ran OK and gave me the correct query:

SELECT * FROM Library1.TableA ta INNER JOIN Library2.TableB tb ON ta.column1 = tb.column1 AND ta.column2 = tb.column2

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.

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?

Thanks in advance for your time.

Answer 1

Joining tables over 2 different libraries is dead easy, all you need are some extra JDBC parameters:

In the JDBC url add parameter "libraries" and "naming". E.g. jdbc:as400://systemName/;libraries="Library1 Library2";naming=system

With the above JDBC url you will be able to perform this query:

select * from TableA inner join TableB on ... = ...

The iSeries machine will use the "libraries" parameter to find the physical files. Make you your physical file names are unique across both libraries to prevent conflicts.

Cheers,