Spring + Hibernate SessionFactory + AbstractRoutingDataSource

Question

I have an Spring+Hibernate/Flex application that needs to switch dynamically between database schemas. To accomplish that I implemented a AbstractRoutingDataSource following this article. Unfortunately it doesn't work. It actually executes the SQL in the default schema(logical_public). Any help would be greatly appreciated. Thanks.

Here's my setup:

applicationContext.xml contains the two datasources. Each datasource connects to the database with a different login role. The routing datasource picks the right datasource by using a String key. The SchemaConstants class contains a couple of public static final fields.

<bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="org.postgresql.Driver"/>
    <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/mystore"/>
    <property name="acquireIncrement" value="3"/>
    <property name="minPoolSize" value="1"/>
    <property name="maxPoolSize" value="15"/>
    <property name="maxStatementsPerConnection" value="100"/>
    <property name="automaticTestTable" value="c3p0_test_table"/>
    <property name="numHelperThreads" value = "20"/>
</bean>

<bean id="publicDS" parent="parentDataSource">
    <property name="user" value="postgres"/>
    <property name="password" value="password"/>
</bean>

<bean id="tempSchemaDS" parent="parentDataSource">
    <property name="user" value="temp_role"/>
    <property name="password" value="tmppsw"/>
</bean>

<bean id="routingDS" class="flex.RoutingDataSource">
   <property name="targetDataSources">
      <map key-type="java.lang.String">
         <entry key="flex.SchemaConstants.LOGICAL_PUBLIC" value-ref="publicDS"/>
         <entry key="flex.SchemaConstants.TEMP_SCHEMA" value-ref="tempSchemaDS"/>
      </map>
   </property>
   <property name="defaultTargetDataSource" ref="publicDS"/>
</bean>

RoutingDataSource implementation: Nothing much to add here.

public class RoutingDataSource extends AbstractRoutingDataSource
{
    @Override
    protected Object determineCurrentLookupKey()
    {
        return Globals.getSchema();
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException
    {
        // TODO Auto-generated method stub
        return null;
    }
}

The Globals class: Used to store and lookup the datasource key.

public class Globals
{
    private static final ThreadLocal<String> schemaHolder 
        = new ThreadLocal<String>();

    public static void setSchema(String schema)
    {
        schemaHolder.set(schema);
    }

    public static String getSchema()
    {
        return schemaHolder.get();
    }

    public static void clearCustomerType()
    {
        schemaHolder.remove();
    }
}

Test code: Tries to insert a couple of records, each in different schema(and different tables)

@RemotingInclude
@Transactional
public void test()
{
    Globals.setSchema(SchemaConstants.TEMP_SCHEMA);

    SomeDataOther someOtherData = new SomeDataOther();
    someOtherData.setName("Jorjinio");
    this.sessionFactory.getCurrentSession().save(someOtherData);


    Globals.setSchema(SchemaConstants.LOGICAL_PUBLIC);

    SomeData someData = new SomeData();
    someData.setFirstName("Hulio");
    someData.setLastName("Julio");
    this.sessionFactory.getCurrentSession().save(someData);
}

A secondary question. What is the correct way to do keep my data integrity in such a situation? I have annotated the method with @Transactional attribute but I'm far from certain that this would work so easily. The transactionManager I am using is of type org.springframework.orm.hibernate3.HibernateTransactionManager. I haven't yet research anything on the matter but if someone can provide information it will be greatly appreciated as well.

So did you manage to have a JTA transaction encapsulate both the saves? How? — Vedran, Sep 11 '12 at 09:15

score 8 · Accepted Answer · answered Sep 11 '11 at 14:58

It's clear that selection of a particular DataSource actually happens when AbstractRoutingDataSource.getConnection() is called, i.e. when transaction-bound Hibernate Session is created. In your case it happens when you enter a @Transactional method.

Thus, you can't switch schemes inside a transaction. You have to execute separate transactions against different schemes. To execute several transactions inside the same method you can use programmatic transaction management (TransactionTemplate) instead of @Transactional.

Spring + Hibernate SessionFactory + AbstractRoutingDataSource

1 Answers1

Linked