working with multiple sessions / db connections in a single transaction

[cosmin]

I was wondering what the Spring way is for dooing this .

I am dooing an import / data translation tool with spring , and I have to get data from one database , run some logic on it , and save it on another database .

I have DAO's connecting to one database , and obtaining data from there , and dao's connecting to the new database and writting data into it .

Both daos are connected to a manager bean which uses a HibernateTransactionManager . The problem is that I see the HibernateTransactionManager only beeing able to use a single Session Factory .
I have 2 configured ... I have told it to use the new SessionFactory , since that's on top of the DB to which i'll be writting . But when I try to read from the old database I get an nullPointerException inside the HibernateTemplate object ....

Here's my configuration , any sugestions would greately be appreciated :

<bean id="oldSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource"><ref local="oldDataSource"/></property>
         <property name="mappingResources">
            <list>
				<value>old/Record.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
        
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>  
        </props>
         </property>
    </bean> 
    <bean id="newSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    
    	<property name="schemaUpdate"><value>true</value></property>    
        <property name="dataSource"><ref local="newDataSource"/></property>
         <property name="mappingResources">
            <list>
				<value>new/record.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
        
	        <props>
	            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop> 
	        </props>
         </property>
    </bean> 


 
    <!-- DAO -->
    <bean id="oldRecordDAO" class="old.OldRecordDAOImpl">
        <property name="sessionFactory"><ref local="oldSessionFactory"/></property>
    </bean>   
    <bean id="newRecordDAO" class="new.RecordDAOImpl">
        <property name="sessionFactory"><ref local="newSessionFactory"/></property>
    </bean> 
    
    

    
    <!-- BUSINESS LOGIC -->
    
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory"><ref local="newSessionFactory"/></property>
    </bean>

    <!--  manager  -->
    
    <bean id="programManagerTarget" class="agile.importa.ProgramManagerImpl">
        <property name="recordDAO"><ref local="recordDAO"/></property>
        <property name="oldRecordDAO"><ref local="oldRecordDAO"/></property>
    </bean>

    <bean id="programManager"
        class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
        <property name="transactionManager"><ref local="transactionManager"/></property>
        <property name="target"><ref local="programManagerTarget"/></property>
        <property name="transactionAttributes">
            <props>
                <prop key="*">PROPAGATION_REQUIRED</prop>
            </props>
        </property>
    </bean>

[katentim]

The problem is that I see the HibernateTransactionManager only beeing able to use a single Session Factory. I have 2 configured

You can define two transactional services (each with their own transaction manager), but each operation won't occur within a single transaction. To execute the transfer in a single transaction you'll need XA capable datasources and JtaTransactionManager. Your version of PostgreSQL may not support XA.

[cosmin]

i'm using postgres 7.4 which version supports this ?

and what you are saying is that , there is no way arround not using JTA for this ?

[katentim]

i'm using postgres 7.4 which version supports this ?

I'm not sure if any version of PostgreSQL supports it yet. This log indicates they've only just beginning development.

and what you are saying is that , there is no way arround not using JTA for this ?

If this is for a import/translation, you can always skip JTA and add your own logic to check for errors and re-try the import.

[cosmin]

yea there are some ways arround this using my current scenario , but i was asking , thinking about other things i might want to do in the future

are you saying postgresql is the wrong database to use for this sort of transactional support . can you suggest something else ?

[MaximDim]

PostgreSql 7.X doesn't support XA transactions. As fas as I recall they just added it in version 8.1

i'm using postgres 7.4 which version supports this ?

and what you are saying is that , there is no way arround not using JTA for this ?

[katentim]

can you suggest something else ?

I like PostgreSQL because of its similarity to Oracle (my favourite DB if you can afford it), but if you need XA you'll need a version that supports it (8.1 if MaximDim is correct). MySQL used to lack some SQL basics, but is pretty feature rich now, and supports XA, so it may be worth considering.