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.

it would solve my problem if I could find a way to define the Schema dynamically like this: @Table(schema={load_from_config}) public class TableB — Euclides Mulémbwè

user2012186 user2012186 · Accepted Answer · 2013-01-25T19:34:34

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,

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

2 Answers