How to connect to several databases using MyBatis Spring Integration?

Question

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?

Answer 1

That's one of the first topics covered in MyBatis 3 User Guide. Basically you should have couple XML configuration files for each database. And the most simple way would be to create mappers by passing configuration

String resource = "org/mybatis/example/Configuration.xml";
Reader reader = Resources.getResourceAsReader(resource);
sqlMapper = new SqlSessionFactoryBuilder().build(reader);

EDIT: Sorry, wasn't carefully reading. Anyway, I believe code snipet is self explanatory:

<jee:jndi-lookup id="jndiDatabase1"                jndi-name="jdbc/database1"/>
<jee:jndi-lookup id="jndiDatabase2"                jndi-name="jdbc/database2"/>

 <bean id="database1" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation"     value="classpath:/some/path/to/database1Config.xml"/>
    <property name="dataSource"         ref="jndiDatabase1"/>
</bean>

<bean id="database2" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation"     value="classpath:/some/path/to/database2Config.xml"/>
    <property name="dataSource"         ref="jndiDatabase2"/>
</bean>

Answer 2

If one is looking for supporting different type of databases, my answer is just for that.
As of Mybatis 3, It supports multi-data internally. for a detail configuration refer to official documentation at here.

The following is how to config it with Spring

<bean id="vendorProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">  
    <property name="properties">  
        <props>  
            <prop key="SQL Server">sqlserver</prop>  
            <prop key="DB2">db2</prop>  
            <prop key="Oracle">oracle</prop>  
            <prop key="MySQL">mysql</prop>  
        </props>  
    </property>
</bean>
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider"> 
     <property name="properties" ref="vendorProperties"/>
</bean>
<bean  id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
      <property name="dataSource" ref="dataSource" />
      <property name="databaseIdProvider" ref="databaseIdProvider" />
</bean>