Multi Datasource #78

jkdntc · 2016-06-22T05:09:11Z

How to config two datasource.

kopax · 2016-08-09T03:09:49Z

+1

jacks808 · 2016-08-17T13:57:56Z

+1

lrhgoo · 2016-08-22T09:47:07Z

+1

h3adache · 2016-08-24T16:05:14Z

It looks like you can not atm. It uses a single datasource.

Refer to http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/ - quick setup section and the source code.

Looks like an enhancement request if you need it to support multiple datasource. Or submit a patch?

junegod · 2016-10-14T02:29:58Z

+1

jacks808 · 2016-11-03T04:50:23Z

Here is my double datasource config:

maven dependency:

        <!-- stater mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- jdbc -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>


datasource:
  km:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/km?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    max-active: 100
    max-idle: 10
    max-wait: 10000
    test-while-idle: true
  esb:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/esb?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    max-active: 100
    max-idle: 10
    max-wait: 10000
    test-while-idle: true

Datasource km config class:

import com.package.km.api.commons.config.datasource.annotation.UseDatasourceKM;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;
import java.sql.SQLException;

/**
 * km datasource config
 * Created by BeeNoisy on 16/5/23.
 */
@Configuration
@MapperScan(basePackages = "package.km", annotationClass = UseDatasourceKM.class, sqlSessionFactoryRef = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class KMDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryKm";
    public static final String TX_MANAGER = "txManagerKm";
    private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Bean(name = "datasourceKm")
    @Primary
    @ConfigurationProperties(prefix = "datasource.km")
    public DataSource dataSourceKm() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Primary
    public PlatformTransactionManager txManagerKm() {
        return new DataSourceTransactionManager(dataSourceKm());
    }

    @Bean(name = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-conf.xml"));
        sqlSessionFactoryBean.setDataSource(dataSourceKm());
        return sqlSessionFactoryBean.getObject();
    }
}

Here is datasource esb config class:

import com.package.km.api.commons.condition.EsbEnabledCondition;
import com.package.km.api.commons.config.datasource.annotation.UseDatasourceESB;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;

/**
 * Created by BeeNoisy on 16/5/23.
 */
@Configuration
@Conditional(EsbEnabledCondition.class)
@MapperScan(basePackages = "package.esb", annotationClass = UseDatasourceESB.class, sqlSessionFactoryRef = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class EsbDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryEsb";
    public static final String TX_MANAGER = "txManagerEsb";
    private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Bean(name = "datasourceEsb")
    @Conditional(EsbEnabledCondition.class)
    @ConfigurationProperties(prefix = "datasource.esb")
    public DataSource dataSourceEsb() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Conditional(EsbEnabledCondition.class)
    public PlatformTransactionManager txManagerEsb() {
        return new DataSourceTransactionManager(dataSourceEsb());
    }

    @Bean(name = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Conditional(EsbEnabledCondition.class)
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-conf.xml"));
        sqlSessionFactoryBean.setDataSource(dataSourceEsb());
        return sqlSessionFactoryBean.getObject();
    }
}

Then you can use the two annotation: UseDatasourceESB or UseDatasourceKM to annotate your mapper class like:

@UseDatasourceKM
public interface GroupBaseDAO {
    public static final String COL_ALL = " id, name, create_time, last_update_time "; 
    public static final String TABLE = " group_base "; 

    @Select(" select " + COL_ALL + " from " + TABLE + " where id = #{id} ")
    public GroupBase findById(@Param("id") int id);

    @Select(" select " +
            COL_ALL +
            " from " +
            TABLE +
            " where id < #{lastId} " +
            " limit #{count} ")
    public List<GroupBase> list(
            @Param("lastId") int lastId,
            @Param("count") int count
    );
...

UseDatasourceESB and UseDatasourceKM is two empty annotation class:

public @interface UseDatasourceESB {
}

And, Don't forget exclude datasource auto configuration on start class:

@SpringBootApplication
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

EDIT:

remove the EsbEnabledCondition


import com.package.km.api.commons.config.datasource.annotation.UseDatasourceESB;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;

/**
 * Created by BeeNoisy on 16/5/23.
 */
@Configuration
@MapperScan(basePackages = "package.esb", annotationClass = UseDatasourceESB.class, sqlSessionFactoryRef = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class EsbDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryEsb";
    public static final String TX_MANAGER = "txManagerEsb";
    private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Bean(name = "datasourceEsb")
    @Conditional(EsbEnabledCondition.class)
    @ConfigurationProperties(prefix = "datasource.esb")
    public DataSource dataSourceEsb() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Conditional(EsbEnabledCondition.class)
    public PlatformTransactionManager txManagerEsb() {
        return new DataSourceTransactionManager(dataSourceEsb());
    }

    @Bean(name = EsbDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Conditional(EsbEnabledCondition.class)
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-conf.xml"));
        sqlSessionFactoryBean.setDataSource(dataSourceEsb());
        return sqlSessionFactoryBean.getObject();
    }
}

tradercentric · 2016-11-04T19:40:48Z

Thanks @jacks808 , here is my double Oracle datasources example in Spring Boot (hopefully it is simpler).

pom.xml:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.1</version>
</dependency>

resources/application.properties:

#Basic Spring Boot Config for Oracle

#This will use the primary datasource (staging)
spring.datasource.schema=import.sql

staging.datasource.driver-class-name=oracle.jdbc.OracleDriver
staging.datasource.url=jdbc:oracle:thin:@staging-db:9999:staging
staging.datasource.username=STAGING_SCHEMA_01
staging.datasource.password=password

crd.datasource.driver-class-name=oracle.jdbc.OracleDriver
crd.datasource.url=jdbc:oracle:thin:@crd-db:9999:crd
crd.datasource.username=CRD_SCHEMA_01
crd.datasource.password=password

CrdDatasoureConfig.java:

package com.cg.crd.etl.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import oracle.jdbc.pool.OracleDataSource;

import javax.sql.DataSource;
import java.lang.invoke.MethodHandles;
import java.sql.SQLException;

import javax.validation.constraints.NotNull;

@Configuration
@ConfigurationProperties("crd.datasource")
@MapperScan(basePackages = "com.cg.crd.etl.mappers.crd", sqlSessionFactoryRef = CrdDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class CrdDatasourceConfig {

    public static final String SQL_SESSION_FACTORY_NAME = "crdSessionFactory";
    private static final Logger log = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @NotNull
    private String username;

    @NotNull
    private String password;

    @NotNull
    private String url;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    @Bean(name = "crdDatasource")
    public DataSource  crdDatasource() throws SQLException {

        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(username);
        dataSource.setPassword(password);
        dataSource.setURL(url);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setFastConnectionFailoverEnabled(true);

        return dataSource;
    }

    @Bean(name = CrdDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        //Can no longer use application.properties for mybatis configuration settings, so this is a hack for
        //application.properties: mybatis.configuration.map-underscore-to-camel-case=true
        org.apache.ibatis.session.Configuration ibatisConfiguration = new org.apache.ibatis.session.Configuration();
        ibatisConfiguration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(ibatisConfiguration);

        sqlSessionFactoryBean.setDataSource(crdDatasource());
        return sqlSessionFactoryBean.getObject();
    }
}

StagingDatasourceConfig.java:

package com.cg.crd.etl.config;

import oracle.jdbc.pool.OracleDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import javax.validation.constraints.NotNull;
import java.lang.invoke.MethodHandles;
import java.sql.SQLException;

@Configuration
@ConfigurationProperties("staging.datasource")
@MapperScan(basePackages = "com.cg.crd.etl.mappers.staging", sqlSessionFactoryRef = StagingDatasourceConfig.SQL_SESSION_FACTORY_NAME)
public class StagingDatasourceConfig {
    public static final String SQL_SESSION_FACTORY_NAME = "stagingSessionFactory";
    private static final Logger log = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @NotNull
    private String username;

    @NotNull
    private String password;

    @NotNull
    private String url;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    @Bean(name = "stagingDatasource")
    @Primary
    public DataSource stagingDatasource() throws SQLException{

        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(username);
        dataSource.setPassword(password);
        dataSource.setURL(url);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setFastConnectionFailoverEnabled(true);

        return dataSource;
    }

    @Bean(name = StagingDatasourceConfig.SQL_SESSION_FACTORY_NAME)
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        //Can no longer use application.properties for mybatis configuration settings, so this is a hack for
        //application.properties: mybatis.configuration.map-underscore-to-camel-case=true
        org.apache.ibatis.session.Configuration ibatisConfiguration = new org.apache.ibatis.session.Configuration();
        ibatisConfiguration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(ibatisConfiguration);

        sqlSessionFactoryBean.setDataSource(stagingDatasource());
        return sqlSessionFactoryBean.getObject();
    }
}

The mapper for CrdDatasource is under package com.cg.crd.etl.mappers.crd

package com.cg.crd.etl.mappers.crd;

import com.cg.crd.etl.dto.from.CsmTranslation;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface CsmTranslationMapper {

    @Select("SELECT * FROM CSM_TRANSLATION")
    List <CsmTranslation> getAllCsmTranslation();

}

The mapper for StagingDatasource is under package com.cg.crd.etl.mappers.staging

package com.cg.crd.etl.mappers.staging;

import java.util.List;
import org.apache.ibatis.annotations.*;
import com.cg.crd.etl.dto.from.RdmIssuer;

@Mapper
public interface RdmIssuerMapper {

    @Select("SELECT * FROM RDM_ISSUER WHERE ISSR_UID = #{issrUid}")
    @Options(useCache=true)
    RdmIssuer getIssuerByIssrId(@Param("issrUid") int issrUid);

    @Select("SELECT * FROM RDM_ISSUER")
    @Options(useCache=true)
    List <RdmIssuer> getIssuers();


}

Now, here is the test code

package com.cg.crd.etl;

import com.cg.crd.etl.dto.from.CsmTranslation;
import com.cg.crd.etl.dto.from.RdmIssuer;
import com.cg.crd.etl.mappers.crd.CsmTranslationMapper;
import com.cg.crd.etl.mappers.staging.RdmIssuerMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.retry.annotation.EnableRetry;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.lang.invoke.MethodHandles;
import java.util.List;

@SpringBootApplication
//@EnableRetry
//@EnableScheduling
public class RdmApplication implements CommandLineRunner {

    private static final Logger log = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    @Autowired
    private CsmTranslationMapper csmTranslationMapper;

    @Autowired
    private RdmIssuerMapper rdmIssuerMapper;

    public static void main(String[] args) {
        SpringApplication.run(RdmApplication.class, args);
    }

    @Override
    public void run(String... arg0) throws Exception {

        List<CsmTranslation> csmTranslations = csmTranslationMapper.getAllCsmTranslation();
        csmTranslations.forEach(i -> log.info(i.toString()));

        List<RdmIssuer> rdmIssuers = rdmIssuerMapper.getIssuers();
        rdmIssuers.forEach(i -> log.info(i.toString()));

    }
}

JesseBlackman · 2016-11-15T12:07:25Z

I want to get datasource id through custom annotationClass's in @MapperScan, but I got null.
check here:
mybatis/mybatis-3#835

jacks808 · 2016-11-18T10:31:02Z

@JesseBlackman If you want to use different annotation to mark Mapper class for switch datasource.
Here is the way:

1.Impl 2 different Annotation for mark your mapper class, UseDatasourceA and UserDatasourceB

add this config to your datasource config class both:

@MapperScan(basePackages = "package.a", annotationClass = UseDatasourceA.class, sqlSessionFactoryRef = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME_A)

@MapperScan(basePackages = "package.b", annotationClass = UseDatasourceB.class, sqlSessionFactoryRef = KMDatasourceConfig.SQL_SESSION_FACTORY_NAME_B)

NOTE: notice the attribute of annotationClass this should same as your mark annotation.

And, finally mark mapper class by UseDatasourceAorUserDatasourceB`

You can find more detail in my comment in this post.

lovettli · 2017-01-04T06:31:31Z

@jacks808 Nice! It's works for me . Thanks a lot.

phengtola · 2017-02-26T05:04:02Z

@jacks808 Can you show your EsbEnabledCondition.class ?

jacks808 · 2017-02-27T02:53:06Z

@phengtola EsbEnabledCondition.class is conditional annotation class for check wether the esb datasource enable, it's non business with the dual datasources
And I have already update my answer. tks

Dreampie · 2017-03-02T14:11:09Z

multi datasource only config once ?

mybatis:
  type-aliases-package: service.**.entity
  type-handlers-package: common.type.handler
  configuration:
    map-underscore-to-camel-case: true
    default-fetch-size: 100
    default-statement-timeout: 30

this not work for second datasource?

kazuki43zoo · 2017-03-02T14:13:11Z

The MyBatis Spring Boot AutoConfigure support single datasource(or @Primary datasource) only.

Dreampie · 2017-03-02T14:14:13Z

so . how can add second config?

kazuki43zoo · 2017-03-02T14:28:24Z

You can inject a MybatisProperties bean into your configuration class.
About How to configure, please see https://github.com/mybatis/spring-boot-starter/blob/mybatis-spring-boot-1.2.0/mybatis-spring-boot-autoconfigure/src/main/java/org/mybatis/spring/boot/autoconfigure/MybatisAutoConfiguration.java#L112-L140.

kazuki43zoo · 2017-03-02T14:45:55Z

@Dreampie

If you defined a custom SqlSessionFactory(or SqlSessionFactoryBean) on your configuration class, the MyBatisAutoConfiguration does not create it. In other words, configuration properties provided mybatis-spring-boot(starting with mybatis.) does not apply to custom SqlSessionFactory.

Dreampie · 2017-03-02T14:54:25Z

@kazuki43zoo yes, I add config properties, but get cycle dependencies

@Configuration
@MapperScan(annotationClass = Mapper.class, basePackages = {"service.allocate.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {

    private final MybatisProperties properties;

    private final Interceptor[] interceptors;

    private final ResourceLoader resourceLoader;

    private final DatabaseIdProvider databaseIdProvider;

    public DataSourceConfig(MybatisProperties properties,
                            ObjectProvider<Interceptor[]> interceptorsProvider,
                            ResourceLoader resourceLoader,
                            ObjectProvider<DatabaseIdProvider> databaseIdProvider) {
        this.properties = properties;
        this.interceptors = interceptorsProvider.getIfAvailable();
        this.resourceLoader = resourceLoader;
        this.databaseIdProvider = databaseIdProvider.getIfAvailable();
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        return SqlSessionFactoryBuilder.build(dataSource, properties, resourceLoader, interceptors, databaseIdProvider);
    }
}


@Configuration
@MapperScan(annotationClass = ETLMapper.class, basePackages = {"service.allocate.mapper"}, sqlSessionFactoryRef = "etlSqlSessionFactory")
public class ETLDataSourceConfig {

    private final MybatisProperties properties;

    private final Interceptor[] interceptors;

    private final ResourceLoader resourceLoader;

    private final DatabaseIdProvider databaseIdProvider;

    public ETLDataSourceConfig(MybatisProperties properties,
                               ObjectProvider<Interceptor[]> interceptorsProvider,
                               ResourceLoader resourceLoader,
                               ObjectProvider<DatabaseIdProvider> databaseIdProvider) {
        this.properties = properties;
        this.interceptors = interceptorsProvider.getIfAvailable();
        this.resourceLoader = resourceLoader;
        this.databaseIdProvider = databaseIdProvider.getIfAvailable();
    }

    @Bean(name = "etlDataSource")
    @ConfigurationProperties(prefix = "spring.etl.datasource")
    public DataSource etlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "etlSqlSessionFactory")
    public SqlSessionFactory etlSqlSessionFactory(DataSource etlDataSource) throws Exception {
        return SqlSessionFactoryBuilder.build(etlDataSource, properties, resourceLoader, interceptors, databaseIdProvider);
    }
}


public static SqlSessionFactory build(DataSource dataSource, MybatisProperties properties, ResourceLoader resourceLoader, Interceptor[] interceptors, DatabaseIdProvider databaseIdProvider) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setVfs(SpringBootVFS.class);
        if (StringUtils.hasText(properties.getConfigLocation())) {
            sqlSessionFactoryBean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
        }
        sqlSessionFactoryBean.setConfiguration(properties.getConfiguration());
        if (properties.getConfigurationProperties() != null) {
            sqlSessionFactoryBean.setConfigurationProperties(properties.getConfigurationProperties());
        }
        if (!ObjectUtils.isEmpty(interceptors)) {
            sqlSessionFactoryBean.setPlugins(interceptors);
        }
        if (databaseIdProvider != null) {
            sqlSessionFactoryBean.setDatabaseIdProvider(databaseIdProvider);
        }
        if (StringUtils.hasLength(properties.getTypeAliasesPackage())) {
            sqlSessionFactoryBean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        }
        if (StringUtils.hasLength(properties.getTypeHandlersPackage())) {
            sqlSessionFactoryBean.setTypeHandlersPackage(properties.getTypeHandlersPackage());
        }
        if (!ObjectUtils.isEmpty(properties.resolveMapperLocations())) {
            sqlSessionFactoryBean.setMapperLocations(properties.resolveMapperLocations());
        }
        return sqlSessionFactoryBean.getObject();
    }

***************************
APPLICATION FAILED TO START
***************************

Description:

The dependencies of some of the beans in the application context form a cycle:

┌─────┐
|  dataSourceConfig
↑     ↓
|  thriftServerConfig this inject TAllocateServiceImpl
↑     ↓
|  TAllocateServiceImpl  this inject allocateMapper
↑     ↓
|  allocateMapper  annotation @Mapper
└─────┘

Dreampie · 2017-03-02T15:47:38Z

@kazuki43zoo help

kazuki43zoo · 2017-03-02T16:45:07Z

@Dreampie

I've added a multiple datasource demo application on https://github.com/kazuki43zoo/mybatis-spring-boot-multi-ds-demo.

Please check this.

kazuki43zoo · 2017-03-02T17:18:00Z

@Dreampie I can't help you with just a provided information ... If you need my help, please provide demo(reproduce) application on GitHub.

phengtola · 2017-03-03T01:23:51Z

@jacks808 Thank you. It works now.

Dreampie · 2017-03-03T06:05:44Z

@kazuki43zoo @jacks808 thanks, https://github.com/Dreampie/mybatis-spring-boot-multi-ds-demo, I resolve cycle dependency, but mapperscan with annotation@MapperScan(annotationClass = ETLMapper.class... @MapperScan(annotationClass = Mapper.class... for two datasource not work, these mapper annotationed by @Mapper @ETLMapper also use same datasource

kazuki43zoo · 2017-03-03T08:40:48Z

@Dreampie
I've changed as follow, it's working fine. (the MybatisDemoApplication#ds2() is failing due to does not exist schema definition.)

MybatisConfigurationSupport#build()

The Configuration instance can't share with multiple SqlSessionFactory.

public SqlSessionFactory build(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSource);
    sqlSessionFactoryBean.setVfs(SpringBootVFS.class);
    if (StringUtils.hasText(mybatisProperties.getConfigLocation())) {
        sqlSessionFactoryBean.setConfigLocation(resourceLoader.getResource(mybatisProperties.getConfigLocation()));
    }
+   if(mybatisProperties.getConfiguration() != null) {
+       Configuration configuration = new Configuration();
+       BeanUtils.copyProperties(mybatisProperties.getConfiguration(), configuration);
+       sqlSessionFactoryBean.setConfiguration(configuration);
+   }
-   sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration());
    // ...
    return sqlSessionFactoryBean.getObject();
}

ETLDataSourceConfig#etlSqlSessionFactory()

You should specify a DataSource explicitly. Currently, it was injected a DataSource that annotated the @Primary.

@Bean(name = "etlSqlSessionFactory")
+ public SqlSessionFactory etlSqlSessionFactory(@Qualifier("etlDataSource") DataSource etlDataSource)
- public SqlSessionFactory etlSqlSessionFactory(DataSource etlDataSource) throws Exception {
    return mybatisConfigurationSupport.build(etlDataSource);
}

or

@Bean(name = "etlSqlSessionFactory")
+ public SqlSessionFactory etlSqlSessionFactory() throws Exception {
+   return mybatisConfigurationSupport.build(etlDataSource());
- public SqlSessionFactory etlSqlSessionFactory(DataSource etlDataSource) throws Exception {
-    return mybatisConfigurationSupport.build(etlDataSource);
}

eddumelendez · 2017-03-03T16:50:10Z

Thanks @kazuki43zoo to provide an example

Dreampie · 2017-03-04T07:54:13Z

@kazuki43zoo thanks for your help

kevindai007 · 2017-03-29T02:02:49Z

@jacks808 thank you for your answer

googlefan · 2017-11-20T04:34:51Z

@phengtola EsbEnabledCondition.class is empty annotation class, here is the code:public @interface UseDatasourceESB { }

are you sure?
EsbEnabledCondition is same with annotation interfcae UseDatasourceESB?
It's confuse me.

jacks808 · 2017-11-20T04:50:50Z

@googlefan EsbEnabledCondition is a condition impl class use for check wether the esb datasource enable. It's non business with the dual datasources.

googlefan · 2017-11-20T05:47:20Z

@jacks808 yeah ! Thank you . It works.

jacks808 · 2017-11-20T07:02:17Z

@googlefan That's my pleasure

UrsVela · 2018-02-27T22:23:04Z

Thanks @jacks808 - The solution worked perfectly.

ghost · 2018-06-15T08:04:08Z

@jacks808
I'm using spring-boot 2.0.3, mybatis-spring-boot-starter 1.3.2 . It works well with @select on mapper interface,
unfortnately, it gets this exception "org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): org.jcker.multipleds.dao.zdjd.XtJdJdlxMapper.selectByPrimaryKey" while I using xml files.
any idea what is going on?

baijifeilong · 2018-12-07T06:34:45Z

Just for lazy guys:

package bj;

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.Logger;
import com.zaxxer.hikari.HikariDataSource;
import io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.WebApplicationType;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.util.List;
import java.util.Map;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/12/6 下午9:29
 * <p>
 * MyBatis多数据源演示
 */
@SpringBootApplication(exclude = {SpringBootConfiguration.class})
@Configuration
@MapperScan(annotationClass = Mapper.class, basePackageClasses = MyBatisApp.class,
        sqlSessionFactoryRef = "sqlSessionFactory")
public class MyBatisApp implements ApplicationListener<ApplicationReadyEvent> {

    /**
     * SecondaryMapper配置
     * \@MapperScan 注解一次只能添加一个，所以需要单独再加一个配置类
     * 自定义@MapperScan会替换MyBatis自动添加的默认@MapperScan。所以主@MapperScan也必须显式添加
     */
    @Configuration
    @MapperScan(annotationClass = SecondaryMapper.class, basePackageClasses = MyBatisApp.class,
            sqlSessionFactoryRef = "sqlSessionFactorySecond")
    static class SecondaryMapperConfiguration {
    }

    public static void main(String[] args) {
        new SpringApplication(MyBatisApp.class) {{
            setWebApplicationType(WebApplicationType.NONE);
        }}.run(args);
    }

    @Resource
    private DataSource dataSource;

    @Resource
    private DataSource dataSourceSecond;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private UserMapper userMapper;

    @Resource
    private SecondaryUserMapper secondaryUserMapper;

    private void initLogger() {
        ((Logger) LoggerFactory.getLogger(MyBatisApp.class)).setLevel(Level.DEBUG);
        ((Logger) LoggerFactory.getLogger(JdbcTemplate.class)).setLevel(Level.DEBUG);
    }

    private void initDatabase() {
        String oldDatabase = jdbcTemplate.queryForObject("SELECT DATABASE()", String.class);
        jdbcTemplate.execute("DROP SCHEMA IF EXISTS one");
        jdbcTemplate.execute("CREATE SCHEMA one");
        jdbcTemplate.execute("USE one");
        jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32) CHARSET 'utf8')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的儿子')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的孙子')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的曾孙子')");
        jdbcTemplate.execute("DROP SCHEMA IF EXISTS two");
        jdbcTemplate.execute("CREATE SCHEMA two");
        jdbcTemplate.execute("USE two");
        jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32) CHARSET 'utf8')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的爹')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的爷')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的太爷')");
        jdbcTemplate.execute("INSERT INTO user(name) VALUES ('人民的老太爷')");
        jdbcTemplate.execute("USE " + oldDatabase);
    }

    @Override
    public void onApplicationEvent(ApplicationReadyEvent applicationReadyEvent) {
        initLogger();
        initDatabase();
        System.out.println("Users:");
        userMapper.selectAll().forEach(System.out::println);
        System.out.println("Secondary users:");
        secondaryUserMapper.selectAll().forEach(System.out::println);
    }

    /**
     * 主数据源
     * <p>
     * 如果不添加@Primary注解, MyBatis可以工作，但是JdbcTemplate无法注入
     *
     * @return .
     */
    @Primary
    @Bean
    public DataSource dataSource() {
        return new HikariDataSource() {{
            setJdbcUrl("jdbc:mysql://localhost/one?useUnicode=true&characterEncoding=utf8");
            setUsername("root");
            setPassword("root");
        }};
    }

    /**
     * 副数据源
     *
     * @return .
     */
    @Bean
    public DataSource dataSourceSecond() {
        return new HikariDataSource() {{
            setJdbcUrl("jdbc:mysql://localhost/two?useUnicode=true&characterEncoding=utf8");
            setUsername("root");
            setPassword("root");
        }};
    }

    /**
     * 主SqlSessionFactory。使用主数据源。自定义SqlSessionFactory后，MyBatis就不自动添加SqlSessionFactory了，所以必须有
     *
     * @return .
     * @throws Exception .
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        return new SqlSessionFactoryBean() {{
            setDataSource(dataSource);
        }}.getObject();
    }

    /**
     * 副SqlSessionFactory。使用副数据源
     *
     * @return .
     * @throws Exception .
     */
    @Bean
    public SqlSessionFactory sqlSessionFactorySecond() throws Exception {
        return new SqlSessionFactoryBean() {{
            setDataSource(dataSourceSecond);
        }}.getObject();
    }

    @Mapper
    interface UserMapper {
        @Select("SELECT * FROM user")
        List<Map<String, Object>> selectAll();
    }

    @SecondaryMapper
    interface SecondaryUserMapper {
        @Select("SELECT * FROM user")
        List<Map<String, Object>> selectAll();
    }

    /**
     * 自定义Mapper注解，用于标识使用的数据源
     */
    @Target(ElementType.TYPE)
    @Retention(RetentionPolicy.RUNTIME)
    @interface SecondaryMapper {
    }
}

baijifeilong · 2018-12-07T06:36:31Z

And the console output:

Users:
2018-12-07 14:35:51.780 DEBUG 34437 --- [           main] bj.MyBatisApp$UserMapper.selectAll       : ==>  Preparing: SELECT * FROM user 
2018-12-07 14:35:51.805 DEBUG 34437 --- [           main] bj.MyBatisApp$UserMapper.selectAll       : ==> Parameters: 
2018-12-07 14:35:51.861 DEBUG 34437 --- [           main] bj.MyBatisApp$UserMapper.selectAll       : <==      Total: 3
{name=人民的儿子, id=1}
{name=人民的孙子, id=2}
{name=人民的曾孙子, id=3}
Secondary users:
2018-12-07 14:35:51.865  INFO 34437 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2018-12-07 14:35:51.877  INFO 34437 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2018-12-07 14:35:51.877 DEBUG 34437 --- [           main] b.M.selectAll                            : ==>  Preparing: SELECT * FROM user 
2018-12-07 14:35:51.878 DEBUG 34437 --- [           main] b.M.selectAll                            : ==> Parameters: 
2018-12-07 14:35:51.884 DEBUG 34437 --- [           main] b.M.selectAll                            : <==      Total: 4
{name=人民的爹, id=1}
{name=人民的爷, id=2}
{name=人民的太爷, id=3}
{name=人民的老太爷, id=4}

eddumelendez added the question label Jul 29, 2016

eddumelendez closed this as completed Mar 3, 2017

kcmvp mentioned this issue Apr 12, 2018

Improve documentation showing how to use two different databases spring-projects/spring-boot#3456

Open

Multi Datasource #78

Multi Datasource #78

Comments

jkdntc commented Jun 22, 2016

kopax commented Aug 9, 2016

jacks808 commented Aug 17, 2016

lrhgoo commented Aug 22, 2016

h3adache commented Aug 24, 2016 • edited

junegod commented Oct 14, 2016

jacks808 commented Nov 3, 2016 • edited

tradercentric commented Nov 4, 2016 • edited

JesseBlackman commented Nov 15, 2016

jacks808 commented Nov 18, 2016 • edited

lovettli commented Jan 4, 2017

phengtola commented Feb 26, 2017 • edited

jacks808 commented Feb 27, 2017 • edited

Dreampie commented Mar 2, 2017

kazuki43zoo commented Mar 2, 2017

Dreampie commented Mar 2, 2017

kazuki43zoo commented Mar 2, 2017

kazuki43zoo commented Mar 2, 2017

Dreampie commented Mar 2, 2017 • edited

Dreampie commented Mar 2, 2017 • edited

kazuki43zoo commented Mar 2, 2017

kazuki43zoo commented Mar 2, 2017

phengtola commented Mar 3, 2017

Dreampie commented Mar 3, 2017 • edited

kazuki43zoo commented Mar 3, 2017 • edited

eddumelendez commented Mar 3, 2017

Dreampie commented Mar 4, 2017

kevindai007 commented Mar 29, 2017

googlefan commented Nov 20, 2017

@phengtola EsbEnabledCondition.class is empty annotation class, here is the code:public @interface UseDatasourceESB { }

jacks808 commented Nov 20, 2017 • edited

googlefan commented Nov 20, 2017

jacks808 commented Nov 20, 2017 • edited

UrsVela commented Feb 27, 2018

ghost commented Jun 15, 2018

baijifeilong commented Dec 7, 2018

baijifeilong commented Dec 7, 2018

h3adache commented Aug 24, 2016 •

edited

jacks808 commented Nov 3, 2016 •

edited

tradercentric commented Nov 4, 2016 •

edited

jacks808 commented Nov 18, 2016 •

edited

phengtola commented Feb 26, 2017 •

edited

jacks808 commented Feb 27, 2017 •

edited

Dreampie commented Mar 2, 2017 •

edited

Dreampie commented Mar 2, 2017 •

edited

Dreampie commented Mar 3, 2017 •

edited

kazuki43zoo commented Mar 3, 2017 •

edited

jacks808 commented Nov 20, 2017 •

edited

jacks808 commented Nov 20, 2017 •

edited