22

I'm working with Spring and Mybatis and I have two databases, the configuration for the first database was relative easy, but I can't get to work the second database with Spring and transactions, here is my code

@Configuration
@ComponentScan(basePackages = {"hernandez.service", "hernandez.dao"})
@EnableTransactionManagement
@MapperScan(basePackages="hernandez.mapper" ) 
@Import(DbConfig2.class)
public class AppConfig {

@Bean(name = "dataSource")
public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource("com.mysql.jdbc.Driver",
            "jdbc:mysql://localhost:3306/northwind", "root", "");
    return ds;
}

@Bean
public SqlSessionFactoryBean sqlSessionFactory() {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource()); 
    return factoryBean;
}

@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
}
}

@Configuration
@MapperScan("loli.mapper" ) 
public class DbConfig2 {
@Bean(name = "dataSource_2")
public DataSource dataSource2() {
    DriverManagerDataSource ds = new DriverManagerDataSource("com.mysql.jdbc.Driver",
            "jdbc:mysql://localhost:3306/dmsolut_dmsms", "root", "");
    return ds;
}

@Bean
public SqlSessionFactory sqlSessionFactory2() throws Exception{
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource2());
    return factoryBean.getObject();
}

@Bean(name = "transactionManager_2")
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource2());
}
}

Is there a way to get this working with pure Spring Java configuration or at least with some XML? There's no official documentation to get two databases working in the Mybatis-Spring project

Improve this question

5 Answers 5

Reset to default
24

Multi datasources with mybatis are used in my project right now. This is an Example, add to your application.xml

  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="${center.connectionURL}"/>
    <property name="username"  value="${userName}"/>
    <property name="password" value="${password}"/>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.xxx.dao.center"/>
    <property name="sqlSessionFactoryBeanName" value="cneterSqlSessionFactory"/>
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" name="cneterSqlSessionFactory">
    <property name="dataSource" ref="dataSource"></property>
    <property name="mapperLocations" value="classpath*:mapperConfig/center/*.xml"/>
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
<!--center db end-->
<!--exdb-->
<bean id="dataSourceEx" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="${ex.connectionURL}"/>
    <property name="username"  value="${userName}"/>
    <property name="password" value="${password}"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.xxx.dao.ex"/>
    <property name="sqlSessionFactoryBeanName" value="exSqlSessionFactory"/>
</bean>
<bean id="sqlSessionFactoryEx" class="org.mybatis.spring.SqlSessionFactoryBean" name="exSqlSessionFactory">
    <property name="dataSource" ref="dataSourceEx"></property>
    <property name="mapperLocations" value="classpath*:mapperConfig/ex/*.xml"/>
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<bean id="transactionManagerEx" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceEx"/>
</bean>
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Is there a way to do this using Java Configuration?
I have not tried yet. I only use mybatis with spring by XML config.
Excellent answer man, it saved me a lot of time searching other sites. +1000
17

Add answer with java config example we use in our project:

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@ComponentScan(basePackages = "com.mycompany")
@EnableTransactionManagement(proxyTargetClass = true)
public class ApplicationConfig2 {
    public static final String DATA_SOURCE_NAME_1 = "jdbc/dataSource1";
    public static final String DATA_SOURCE_NAME_2 = "jdbc/dataSource2";

    public static final String SQL_SESSION_FACTORY_NAME_1 = "sqlSessionFactory1";
    public static final String SQL_SESSION_FACTORY_NAME_2 = "sqlSessionFactory2";

    public static final String MAPPERS_PACKAGE_NAME_1 = "com.mycompany.mappers.dao1";
    public static final String MAPPERS_PACKAGE_NAME_2 = "com.mycompany.mappers.dao2";

    @Bean
    public DataSource dataSource1() {
        JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
        return dsLookup.getDataSource(DATA_SOURCE_NAME_1);
    }

    @Bean
    public DataSource dataSource2() {
        JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
        return dsLookup.getDataSource(DATA_SOURCE_NAME_2);
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }


    @Bean(name = SQL_SESSION_FACTORY_NAME_1)
    public SqlSessionFactory sqlSessionFactory1(DataSource dataSource1) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setTypeHandlersPackage(DateTimeTypeHandler.class.getPackage().getName());
        sqlSessionFactoryBean.setDataSource(dataSource1);
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();
        sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
        sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
        return sqlSessionFactory;
    }

    @Bean(name = SQL_SESSION_FACTORY_NAME_2)
    public SqlSessionFactory sqlSessionFactory2(DataSource dataSource2) throws Exception {
        SqlSessionFactoryBean diSqlSessionFactoryBean = new SqlSessionFactoryBean();
        diSqlSessionFactoryBean.setTypeHandlersPackage(DateTimeTypeHandler.class.getPackage().getName());
        diSqlSessionFactoryBean.setDataSource(dataSource2);
        SqlSessionFactory sqlSessionFactory = diSqlSessionFactoryBean.getObject();
        sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
        sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
        return sqlSessionFactory;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer1() {
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setBasePackage(MAPPERS_PACKAGE_NAME_1);
        configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_1);
        return configurer;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer2() {
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setBasePackage(MAPPERS_PACKAGE_NAME_2);
        configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_2);
        return configurer;
    }
}
Improve this answer

2 Comments

How about the transactionManager? which dataSource should it use?
Sometimes you may need to assign datasource, transactionManager, and SqlSessionFactory as primary all.
3

In my experience, you should also add @Primary to one of the DataSource beans. Otherwise it will throw NoUniqueBeanDefinitionException.

@Bean
@Primary
public DataSource dataSource1() {
    JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
    return dsLookup.getDataSource(DATA_SOURCE_NAME_1);
}

@Bean
public DataSource dataSource2() {
    JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
    return dsLookup.getDataSource(DATA_SOURCE_NAME_2);
}
Improve this answer

1 Comment

I would go one step further and name the two beans as @Bean(name="dataSource1") and @Bean(name="dataSource2"). So that when you want to use them, you can use @Qualifier("dataSource1") or @Qualifier("dataSource2") just above the @Autowired. That way, you specify which one is which upon creation and upon automatic wiring.
2

You can use spring's AbstractRoutingDataSource by extending it and overriding the method determineCurrentLookupKey().

Spring Configuration

You can define separate datasource in spring configuration.

<!-- db2 data source -->
<bean id="db2DataSource" class="com.ibm.db2.jdbc.app.DB2Driver">
  <property name="serverName" value="${db2.jdbc.serverName}" />
  <property name="portNumber" value="${db2.jdbc.portNumber}" />
  <property name="user" value="${db2.jdbc.username}" />
  <property name="password" value="${db2.jdbc.password}" />
  <property name="databaseName" value="${db2.jdbc.databaseName}" />
</bean>

<!-- mysql data source -->
<bean id="mysqlDataSource" class="com.mysql.jdbc.Driver">
  <property name="serverName" value="${mysql.jdbc.serverName}" />
  <property name="portNumber" value="${mysql.jdbc.portNumber}" />
  <property name="user" value="${mysql.jdbc.username}" />
  <property name="password" value="${mysql.jdbc.password}" />
  <property name="databaseName" value="${mysql.jdbc.databaseName}" />
</bean>

Associate the datasource with customer:

<bean id="customer" class="com.example.Customer">
  <property name="dataSource" ref="dataSource"/>
</bean>

<bean id="dataSource" class="com.example.datasource.CustomerRoutingDataSource">
  <property name="targetDataSources">
  <map key-type="com.example.Customer">
     <entry key="db2" value-ref="mysqlDataSource"/>
     <entry key="mysql" value-ref="db2DataSource"/>
  </map>
  </property>
  <property name="defaultTargetDataSource" ref="mysql"/>
</bean>

Java

package com.example;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class CustomerRoutingDataSource extends AbstractRoutingDataSource {

 @Bean
 CustomerContextHolder context;

 @Override
 protected Object determineCurrentLookupKey() {
  return context.getCustomerType();
 }
}

Basically, each request will have its context. You can associate datasource with request using mapped key. You can find more details here dynamic-datasource-routing

Improve this answer

Comments

0 

<bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource1" />
    <property name="configLocation">
        <value>classpath:com/dtcc/dao/impl/DaoSqlMapConfig_MyBatis1.xml</value>
  </property>
   <property name="transactionFactory">
        <bean class="org.apache.ibatis.transaction.managed.ManagedTransactionFactory" />
    </property>  
    <property name="mapperLocations" value="classpath*:com/dtcc/dao/impl/DaoEmfMyBatis.sp.xml"/> 
</bean>
<bean id="sqlSession1" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory1" />
</bean> 
<!-- MyBatis Changes Ends -->

<bean id="daoEmf" class="com.dtcc.dao.DaoEmfImpl">
    <property name="connectionType"><ref local="com.dtcc.sharedservices.utils.resources.ConnTypes.IBM_DB2_CONNECTION" /></property>
    <property name="jndiNameForLogging"><ref local="dataSourceName1" /></property>
    <property name="sqlSessionTemplate"> <ref local="sqlSession1" /></property>
    <property name="applicationLog"><ref local="appLog" /></property>
</bean>

As mentioned above, we need to give corresponding sessionFactory in your DaoImpl. You can not autowire SqlSessionTemplate in your DaoImpl class if you have more than one sessionFactory. Give unique name for each session factory and map it to your respective DaoImpl class. All you have to do is just to create object for SqlSessionTemplate with Setter method in DaoImpl class and you can make your db call using sqlSessionTemplate object as below, this.sqlSessionTemplate.selectList("ProcedureID", parameter);

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.