AbstractRoutingDataSource

[Nath]

Hi,

I am using the new spring-mybatis integration module to access an
Oracle database through Mybatis. And I need to switch the database
based on user input (and without restarting the app server). To
achieve this, I used Spring's AbstractRoutingDataSource on similar
lines as mentioned in this blog - http://blog.springsource.com/2007/01/23/dynamic-datasource-routing
and it seems that the switch is happening as expected but I am getting
errors (more often than not) like 'insufficient privilege to access
object <Schema>.<DBPackage>'. Its as if the new databse is being
accessed but the credentials used are from
the older datasource.

Any help in identifying why this might occur and the way to solve it
will be highly appreciated. Thanks!

This is my Spring configuration:

<bean id="dataSource"
class="com.x.y.datasource.MyRoutingDataSource">
<property name="targetDataSources">
<map key-type="com.x.y.datasource.AppDbMode">
<entry key="NORMAL" value="jdbc/PrimaryDB"/>
<entry key="MAINTENANCE" value="jdbc/SecondaryDB"/>
</map>
</property>
<property name="defaultTargetDataSource" value="jdbc/PrimaryDB"/>
</bean>

<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath*:com/x/y/ibatis/
data/*.xml" />
</bean>

<bean id="siteSetupMapper"
class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="mapperInterface"
value="com.x.y.ibatis.data.SiteSetupMapper" />
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>

[Bivas]

Please also let me know if AbstractRoutingDataSource is not the
suggested method for swtitching datasource for MyBatis at runtime and
there is a better way to handle this. Thanks.

On Sep 15, 5:55 pm, Nath <bivasn...@gmail.com> wrote:
> Hi,
>
> I am using the new spring-mybatis integration module to access an
> Oracle database through Mybatis. And I need to switch the database
> based on user input (and without restarting the app server). To
> achieve this, I used Spring's AbstractRoutingDataSource on similar

> lines as mentioned in this blog -http://blog.springsource.com/2007/01/23/dynamic-datasource-routing

[Richard Yee]

What database are you using?

 

-Richard

[Bivas]

Database is Oracle 11g and datasource is configured in the app server
and looked up via JNDI...

Regards,
Bivas

> > > </bean>- Hide quoted text -
>
> - Show quoted text -

[Richard Yee]

If you are trying to achieve fault tolerance and are using Oracle, then one solution would be to use Oracle RAC.

 

-Richard

[Brian Hurley]

I've used the AbstractRoutingDatasource with success. However, I've
used it with a properties based database initializer and this worked
well for me. It sounds like to me that you may have a problem in the
context holder not really switching to the other datasource. Trace
that out and let me know if you have any questions.

--
Regards,
Brian Hurley
Secr...@JavaMUG.org
Leader Spring Dallas User Group

[Bivas]

Thanks Brian - you hit the nail on the head there :-)
I had some threading issues with the context holder. But its resolved
and it works fine now....

On Sep 17, 1:46 pm, Brian Hurley <brhur...@gmail.com> wrote:
> I've used the AbstractRoutingDatasource with success.  However, I've
> used it with a properties based database initializer and this worked
> well for me.   It sounds like to me that you may have a problem in the
> context holder not really switching to the other datasource.   Trace
> that out and let me know if you have any questions.
>
>
>
>
>
> Nath wrote:
> > Hi,
>
> > I am using the new spring-mybatis integration module to access an
> > Oracle database through Mybatis. And I need to switch the database
> > based on user input (and without restarting the app server). To
> > achieve this, I used Spring's AbstractRoutingDataSource on similar

> > lines as mentioned in this blog -http://blog.springsource.com/2007/01/23/dynamic-datasource-routing

> Secret...@JavaMUG.org
> Leader Spring Dallas User Group- Hide quoted text -