How to connect to several databases using MyBatis Spring Integration?

[Tural Muradbeyli]

I use MyBatis with Spring Integration in my application. We have several Oracle databases in our company. One query must be executed in one database, another must be executed in other database. How to configure MyBatis to use different database connections to defferent queries?

I use MyBatis Spring configuration and my code doesn't contains Java code what creates MyBatis objects. This is how I configured sqlSessionFactory:

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSource"/>

        <property name="typeAliasesPackage" value="com.llth.paymentgateway.domain"/>

    </bean>

Is it possible to use another configured SqlSessionFactoryBean which uses another dataSource?

[Tural Muradbeyli]

Suppose I configured two dataSources:

    <jee:jndi-lookup id="dataSource1"                jndi-name="jdbc/database1"/>

    <jee:jndi-lookup id="dataSource2"                jndi-name="jdbc/database2"/>

    <bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSource1"/>

        <property name="typeAliasesPackage" value="com.llth.paymentgateway.domain"/>

    </bean>

    <bean id="sqlSessionFactory2" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSource2"/>

        <property name="typeAliasesPackage" value="com.llth.paymentgateway.domain"/>

    </bean>

How to force queries defined in mappers to use any of them?

[Brian Hurley]

What I do is to setup the mapper scanner like this.

  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

    <property name="basePackage" value="com.example.mapper.local" />

    <property name="annotationClass" value="com.example.mapper.LocalRepository" />

    <property name="sqlSessionFactoryBeanName" ref="sqlSessionFactory1" />

  </bean>

One of these would use the annotation class LocalRespository and the other would use another name.   Both annotation classes are just simple annotation classes that extend the spring @Repository annotation.

public @interface LocalRepository extends Repository {}

Then annotate your repository interfaces with the right annotation and MyBatis will wire them to the right datasource via the mapper scanner.

Brian

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[zachv]

If you use spring's transaction management, take care to define a separate transaction manager for each database.

You can't do this for example with @Transactional annotations and have it work with both:
    <tx:annotation-driven transaction-manager="txManager"/>

[Tural Muradbeyli]

I use @Transactional annotations.

[Tural Muradbeyli]

Thanks. You way helped me (with minor corrections).

<property name="sqlSessionFactoryBeanName" ref="sqlSessionFactory1" />. Here "value" must be used instead of "ref".

Repository definition is different (without "extends Repository"):

@Component

@Repository

public @interface AzisRepository {}

@Component

@Repository

public @interface BscsRepository {}

    <!-- define the SqlSessionFactory -->

    <bean id="sqlSessionFactoryAzis" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSourceAzis"/>

        <property name="typeAliasesPackage" value="com.llth.paymentgateway.domain"/>

    </bean>

    <bean id="sqlSessionFactoryBscs" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSourceBscs"/>

        <property name="typeAliasesPackage" value="com.llth.paymentgateway.domain"/>

    </bean>

    <!-- scan for mappers and let them be autowired -->

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

        <property name="basePackage" value="com.llth.paymentgateway.dao"/>

        <property name="annotationClass" value="com.llth.paymentgateway.repositories.AzisRepository"/>

        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryAzis"/>

    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

        <property name="basePackage" value="com.llth.paymentgateway.dao"/>

        <property name="annotationClass" value="com.llth.paymentgateway.repositories.BscsRepository"/>

        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryBscs"/>

    </bean>

// this mapper uses dataSourceBscs

@BscsRepository

public interface CustomerBalanceDaoMapper {

    CustomerBalance getCustomerBalance(int customerId);

    ...

}

// this mapper uses dataSourceAzis

@AzisRepository

public interface PaymentDaoMapper {

    List<BigDecimal> getPrepaidAmountList();

    ...

}

But this solition has one lack (but not in my case): Only one dataSource can be configured for each transaction manager. If I'm not wrong, I can define only one "<tx:annotation-driven/>" if I want to use @Transaction annotation. So, transactions via only one dataSource can be controlled by transaction manager configured for using with @Transaction annotation. In my case it is not a problem, becuase I use dataSourceBscs only for SELECT statements.

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

        <property name="dataSource" ref="dataSourceAzis"/>

    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>

[zachv]

The danger is that you can think your code is executing in a transaction when it isn't. It may not be relevant in your case but if ever your other db starts getting updates and not just selects, you will probably not realise the problem until it has done damage...

As a principle I stay away from: <tx:annotation-driven transaction-manager="transactionManager"/>

A specific transaction manager documents the way it is used and you don't have "magic" in the background obscuring the fact that it only works for one data source.

In any case I will post two solutions to using transaction managers with multiple db's in spring:
1. With annotations:
Use these annotations with the below transaction managers...
@Transactional("db1TM")
@Transactional("db2TM")

  <bean id="db1TM" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource1"/>
  </bean>

  <bean id="db2TM" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource2"/>
  </bean>

2. With AOP:
Specify an advice with pointcuts that hit the classes that execute db queries:
  <bean id="db1TransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource1"/>
  </bean>

  <!-- Advice dealing with which methods should be transactional -->
  <tx:advice id="db1TransactionAdvice" transaction-manager="db1TransactionManager">
    <tx:attributes>
      <tx:method name="*"/>
    </tx:attributes>
  </tx:advice>
 
  <!-- Pointcut for crosshair service methods to be transactionally advised -->
  <aop:config>
    <aop:pointcut id="db1TransactionPointcut" expression="execution(* com.mysite.spring.app.IDb1MybatisService.*(..))"/>
    <aop:advisor advice-ref="db1TransactionAdvice" pointcut-ref="db1TransactionPointcut"/>
  </aop:config>

[Tural Muradbeyli]

You're right. I've implemented the first way what you suggested.